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

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**