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? — Sede
– Sede, Commented Nov 26, 2016 at 15:56

Community · Accepted Answer · 2017-05-23 12:30:52Z

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, 2017 at 12:30

CommunityBot

11 silver badge

answered Aug 9, 2011 at 2:49

sayap

6,2852 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vadim Samokhin Over a year ago

By the way, YII framework's ORM doesn't check that and performs UPDATE query anyway.

sayap Over a year ago

I see. In SQLAlchemy, the ORM layer does the dirty work and only issues UPDATE when necessary.

Collectives™ on Stack Overflow

Postgres: updating not-changed rows

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related