26

I have a performance-related question. Let's say I have a user with first name Michael. Take the following query:

UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123

Will the query actually execute the update, even though it is being updated to the same value? If so, how do I prevent it from happening?

  • 1
    Why would you execute a statement and simultaneously expect it to not execute? – Max Vernon Oct 15 '15 at 19:46
  • @MaxVernon Ruby on Rails' ORM does not update the record so I was curious if PostgreSQL did the same thing. – OneSneakyMofo Oct 15 '15 at 19:53
  • 1
    I'd suggest if Ruby on Rails is doing that, it probably is doing a select first to see if the row needs an update. – Max Vernon Oct 15 '15 at 19:56
  • x-posted to SO: stackoverflow.com/q/33156712/939860 – Erwin Brandstetter Oct 19 '15 at 6:21
34

Due to the MVCC model of Postgres, and according to the rules of SQL, an UPDATE writes a new row version for every row that is not excluded in the WHERE clause.

This does have a more or less substantial impact on performance, directly and indirectly. "Empty updates" have the same cost per row as any other update. They fire triggers (if present) like any other update, they have to be WAL-logged and they produce dead rows bloating the table and causing more work for VACUUM later like any other update.

Indexes entries and TOASTed columns where none of the involved columns are changed can stay the same, but that is true for any updated row. Related:

It's almost always a good idea to exclude such empty updates (when there is an actual chance it may happen). You did not provide a table definition in your question (which is always a good idea). We have to assume first_name can be NULL (which wouldn't be surprising for a "first name"), hence the query has to use NULL-safe comparison:

UPDATE users
SET    first_name = 'Michael'
WHERE  id = 123
AND   first_name IS DISTINCT FROM 'Michael';

If first_name IS NULL before the update, a test with just first_name <> 'Michael' would evaluate to NULL and as such exclude the row from the update. Sneaky error. If the column is defined NOT NULL, use the simple equality check, though, because that's a bit cheaper.

Related:

  • 1
    Indexes entries and TOASTed columns where none of the involved columns are changed can stay the same But wouldn't they have to be updated to point to the new location of the row? – dvtan Oct 26 '17 at 18:06
  • 1
    @dtgq: Not with H.O.T. updates, where the index can keep pointing to the old location, and heap fetches have to traverse the HOT chain to get the live tuple. I added links to more explanation above. – Erwin Brandstetter Oct 28 '17 at 1:59
  • 1
    What about MVCC calls for a noop update to write a new tuple? – jberryman May 23 '18 at 22:19
  • @jberryman: Not sure I understand. Either way, please ask your question as new question. You can always link to this one for context. And you can leave a comment here to link back (and get my attention). – Erwin Brandstetter May 24 '18 at 1:05
  • 2
    @jberryman: I don't actually know the reasons why the project went this way. That was established long ago. But I assume it would be needlessly expensive to check every row for equality and have a separate code-path for unchanged rows. The handling of transaction-IDs would be more complicated - special casing for rollback, snapshot handling, lock management, WAL, an what not ... – Erwin Brandstetter May 24 '18 at 11:29
4

ORM's like Ruby on Rail's offer deferred execution which mark a record as changed (or not) and then when needed or called, then submit the change to the database.

PostgreSQL is a database and not an ORM. It would have decreased performance if it took the time to check if a new value was the same as the updated value in your query.

It will therefore update the value regardless of whether it is the same as the new value or not.

If you wish to prevent this, you could use code like Max Vernon suggested in his answer.

2

You could simply add to the where clause:

UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123
    AND (first_name <> 'Michael' OR first_name IS NULL);

If first_name is defined as NOT NULL, the OR first_name IS NULL part can be removed.

The condition:

(first_name <> 'Michael' OR first_name IS NULL)

can also be written more elegantly as (in Erwin's answer):

first_name IS DISTINCT FROM 'Michael'
  • Not knowing whether the column can be NULL, that might introduce a sneaky bug. – Erwin Brandstetter Oct 16 '15 at 6:57
  • 1
    @ErwinBrandstetter I was updating the answer - then I saw the comment and your answer! – ypercubeᵀᴹ Oct 16 '15 at 7:00
  • thanks for the edit, @ypercube - and for the comment about NULL @erwin – Max Vernon Oct 16 '15 at 14:23
1

From a database point of view

The answer to your question is YES. The update will take place. The database does not check the previous value, it only sets the new value.

As this happen in memory (and will only be written to the datafiles after a commit is issued) the performance would not be an issue.

From an ORM perspective

Normally you will have an Object representing a single row of the database (it can be a lot more complex than that, but let's keep it simple). This object is managed in memory (at the app server level) and only the latest commited version of that object will actually make it to the database at a certain point.

That may explain the different behaviour.

Now, let's not compare a cargo ship with a 3D printer. The fact that you can send 3D printers using cargo ships doesn't mean that there may be any kind of comparison between them.

Enjoy!

I hope this clarified some concepts.

  • 4
    Performance is and issue. Every update has to be written on disk (the log and the table). – ypercubeᵀᴹ Oct 16 '15 at 7:01
  • It will depend on the actual RDBMS you use. But most of them do not commit every single update, but only the last commited block they have in memory. You never read or write a single row in a database. You read/write blocks and keep them in memory until you have to flush it out to put a new block in the same place. While in memory, not every change in a row will be written to disk, but only the block contents when the "database writer" process is signaled to dump that memory block into a datafile. So, no... Is not an issue unless your application holds the block uncommited for too long. – Silvarion Oct 16 '15 at 12:30
  • 1
    the question is about Postgres, not about any arbitrary DBMS. And while the updates do not all have to be written one by one, every write on the database has to be written to the log. If a change is not written on persistent storage, how will the DBMS survive a system crash? – ypercubeᵀᴹ Oct 16 '15 at 13:19
  • Yes, it writes in the logs, from memory as well during checkpoints. Unless you have an awfully huge number of concurrent users, it shouldn't be a problem at all. Logs are written in batches as well. I think we're talking about servers. If you are talking about a Postgres database in a laptop with a 5400RPM HDD, yes... you will always have performance issues. So, the final answer would be the first one... It depends on too many things. – Silvarion Oct 16 '15 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.