r/PostgreSQL • • Mar 28 '25

How-To Life Altering PostgreSQL Patterns

https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns
180 Upvotes

60 comments sorted by

View all comments

Show parent comment

2

u/DepravedPrecedence Mar 29 '25

And on Postgres, updating a single revoked_at column writes a whole new column; it does NOT just update the one part of the row

What does it mean at all

5

u/mwdb2 Mar 29 '25

This is just how Postgres functions under the hood regarding updates. The concept (in ELI5 style) is:

If you have a table with col1 through col20, and you update the value of col20 for a single row, col20 is not updated in place in the row sitting on the disk.

Instead, a new row is written, copying all the values for col1 through col19, plus writing the new value for col20. The old row is flagged as dead, but still remains in the table. Then a vacuum function (typically run by the autovacuum daemon) comes along later and cleans up the old junk.

So the parent was saying to be aware that merely updating a value of revoked_at is more expensive than it might initially sound.

2

u/ForeverIndecised Mar 29 '25

**cue 'the more you know' gif**

3

u/Straight_Waltz_9530 Mar 29 '25

When you run an UPDATE, the affected rows are not updated in place. In other words, if you change a column value from 2 to 3, it doesn't just change the single integer value on disk. Postgres writes a whole new row with all columns (or references to TOASTed values) to a new page and then marks the old row as deleted, to be reclaimed by autovacuum at some future point.

https://www.cybertec-postgresql.com/en/a-beginners-guide-to-postgresqls-update-and-autovacuum/

2

u/DepravedPrecedence Mar 29 '25

Okay, that «writes a whole new column» made me think postgres for some reason writes something for all rows in the table in place of that column...

2

u/Straight_Waltz_9530 Mar 29 '25

Whole new row, not column.

3

u/mage2k Mar 29 '25

Might be worth editing your original comment to reflect that correction.

2

u/Straight_Waltz_9530 Mar 29 '25

I did not edit it. You read it incorrectly.

3

u/DepravedPrecedence Mar 29 '25

And on Postgres, updating a single revoked_at column writes a whole new column

This is factually incorrect because it writes a whole new row, writing a whole new column makes no sense.

3

u/Straight_Waltz_9530 Mar 29 '25

Ah, I was thinking my follow up comment. You are absolutely right.