Question 182219 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Does Postgres optimize out no-op update calls?

I have a table in my database with many (10+) int4 columns, and I am also developing an application server which updates these values.

My current approach requires multiple round trips, because I do something like this, each as a separate call:

UPDATE mytable SET a = a + $2 WHERE id = $1;
UPDATE mytable SET b = b + $2 WHERE id = $1;
UPDATE mytable SET c = c + $2 WHERE id = $1;
UPDATE mytable SET d = d + $2 WHERE id = $1;
UPDATE mytable SET e = e + $2 WHERE id = $1;

I was thinking to instead abstract this into a general function which can update any number of columns. Something like this:

const EMPTY = {
  a: 0, 
  b: 0, 
  c: 0, 
  d: 0, 
  e: 0
};

let changes = {
  a: +5,
  b: -3,
  ...EMPTY
};

And then the database query would be something like this:

UPDATE mytable SET a = a + $2, b = b + $3, c = c + $4, d = d + $5, e = e + $5 WHERE id = $1;

My questions are:

  1. Is this good practice? Or is there another way to avoid all the round trips and separate calls?

  2. Is Postgres smart enough to optimize out the no-op calls? The vast majority of these calls are doing nothing. Like in the above example, c = c + 0, d = d + 0, e = e + 0. Are these actually performing writes, or does the query planner disregard it? Is it efficient?

Tags:

2

Answers


  1. 0

    In general, it’s best to use the fewest SQL commands necessary to achieve a desired result.

    PostgreSQL will create new tuples for each row meeting an UPDATE‘s selection criteria, even if none of the column values have changed.

    Unnecessary updates can be avoided by using checks in the WHERE clause to exclude rows where none of the column values would change. Another option is to use a BEFORE UPDATE trigger to exclude unchanged rows by returning NULL for rows where all of the old and new column values match. An advantage of the trigger appoach is that all updates to the table are handled instead of having to include the checks in each UPDATE.

    Avoiding unnecessary row updates can help keep a database performant by minimizing the number of dead tuples. Only performing necessary updates is also important when the value of audit columns, such as updated_at, need to be preseved except when actual changes occur.

    Login or Signup to reply.
  2. 0

    Reducing the number of UPDATEs is a very smart idea in PostgreSQL, for which many UPDATEs are a particularly hard workload.

    PostgreSQL won’t skip updates that change nothing (they might trigger a trigger). But there is the system trigger function suppress_redundant_updates_trigger() which you can use for exactly that purpose.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search