Postgres: updating not-changed rows

Question

Say, I have a following query:

UPDATE table_name
SET column_name1 = column_value1, ..., column_nameN = column_valueN
WHERE id = M

The thing is, that column_value1, ..., column_valueN have not changed. Will this query be really executed and what about performance in this case comparing to update with really changed data? What if I have about 50 of such queries per page with not-changed data?

Possible duplicate of [would postgres really update page file when fields are all equals before and after update?](http://stackoverflow.com/questions/40819603/would-postgres-really-update-page-file-when-fields-are-all-equals-before-and-aft) — styvane, Nov 26 '16 at 15:56

score 6 · Accepted Answer · edited May 23 '17 at 12:30

6

You need to help postgresql here by specifying only the changed columns and rows. It will go ahead and perform update on whatever you specify without checking if the data has been changed.

p.s. This is where ORM comes in handy.

EDIT: You may also be interested in How can I speed up update/replace operations in PostgreSQL?, where the OP went through all the troubles to speed up UPDATE performance, when the best performance can be achieved by updating changed data only.

edited May 23 '17 at 12:30

Community

1
1

answered Aug 09 '11 at 02:49

sayap

6,169
2
36
40

By the way, YII framework's ORM doesn't check that and performs UPDATE query anyway. – Vadim Samokhin Aug 09 '11 at 06:21
1

I see. In SQLAlchemy, the ORM layer does the dirty work and only issues UPDATE when necessary. – sayap Aug 09 '11 at 06:42

Postgres: updating not-changed rows

1 Answers1

Linked