Does locking rows in a PostgreSQL table modify the table

Question

I have a few places where I lock a large number of table rows (e.g. 1/10 of table content) for further processing with a statement like follows

SELECT ......
FROM my_table
WHERE ......
FOR UPDATE;

But eventually the process doesn't always end up updating the locked rows. My concern is whether the table gets physically modified when I lock rows in a way that defragments the table or its indexes. In other words does locking rows invalidates or upsets table statistics.

This table is expected to be clustered by an index. I am trying to figure out if I need to CLUSTER it after table rows were locked but none were updated.

You should flag this and ask the admins to move it to Database Administrators — Evan Carroll
– Evan Carroll, Commented Sep 7, 2017 at 14:53

Evan Carroll · Accepted Answer · 2017-09-07 14:53:32Z

1

My concern is whether the table gets physically modified when I lock rows in a way that defragments the table or its indexes. In other words does locking rows invalidates or upsets table statistics.

Of course not.

CREATE TABLE foo AS SELECT id::int FROM generate_series(1,1e6) AS gs(id);

BEGIN; SELECT * FROM foo FOR UPDATE; COMMIT;
BEGIN
Time: 0.166 ms
Time: 771.232 ms
COMMIT
Time: 76.982 ms

BEGIN; UPDATE foo SET id=42; COMMIT;
BEGIN
Time: 0.171 ms
UPDATE 1000000
Time: 1882.144 ms
COMMIT
Time: 87.863 ms

Massively slower to rewrite even the most simple row.

This table is expected to be clustered by an index. I am trying to figure out if I need to CLUSTER it after table rows were locked but none were updated.

Nope, if you don't update the rows nothing happens.

answered Sep 7, 2017 at 14:53

Evan Carroll

1

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

1 Comment

oᴉɹǝɥɔ Over a year ago

Even though this test is indirect the result it is very convincing. PG docs are awesome but I wish there was a line/paragraph there regarding this. Thanks!!

Collectives™ on Stack Overflow

Does locking rows in a PostgreSQL table modify the table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related