PostgreSQL – Does Updating a Row with the Same Value Actually Update the Row?

performancepostgresqlpostgresql-performanceupdate

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?

Best Answer

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.

Index 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. 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, that's a bit cheaper.

SQL Index – Why Does Update Statement Also Update Indexes?

For consistency. There are times the access path will use only the data from the indexes or will start from the index and jump to the table. In both cases the informatoin on these two entities should be compatible.

A index is just a separate ordered set of data from a table. When you remove an entry from the table, the entry on the index pointing to this row should also be removed. The same should happen when you update/insert. That's the burden of indexes: faster select and slower DML (insert, update, delete).

When you do, for example, a

select count(*) from table t1 where c1 = :value;

Your result should be the same whether you have indexes or not. And the beauty of Database Management Systems is that they do that automatically for you. :)

You cannot prevent this update. The only time this will not happen is when you do not change the data referenced on the index. If you have a table with four columns (a, b, c, d), and two of them are indexed (a, b), updating only the other two (c, d) will not trigger this extra update.

MySQL – Does INSERT INTO … ON DUPLICATE KEY UPDATE Execute if Row is Unchanged?

MySQL executes ON DUPLICATE KEY UPDATE the same way it executes UPDATE statements:

It checks the contents of each row (and columns) to be updated and if they are identical to the supplied, it does not do any update. It still has to check them though.

So the result in your case (where you send 3 rows to be inserted) would be:

3 rows to be inserted
3 key collisions
- 0 rows inserted
- 3 to be updated
  - 1 row actually updated
  - 2 identical (no update)

A few things about your syntax:

you don't need to update the UNIQUE key.
you can use VALUES(column) in the UPDATE part.

you can combine the multiple inserts into one:

INSERT INTO user 
  (ID, Name, Surname) 
VALUES 
  (1, 'John', 'Conor'),
  (2, 'Foo',  'Bar'),
  (3, 'Foo',  'Baz') 
ON DUPLICATE KEY UPDATE 
  Name = VALUES(Name), 
  Surname = VALUES(Surname) ;

Best Answer

Related Solutions

SQL Index – Why Does Update Statement Also Update Indexes?

MySQL – Does INSERT INTO … ON DUPLICATE KEY UPDATE Execute if Row is Unchanged?

Related Question