an UPDATE query looks like one of the simplest things in a database.
UPDATE users SET status = 'active' WHERE id = 10;
we read it as: find this row, change this value, done.
but PostgreSQL does something very different internally.
the old row does not disappear. the new row does not replace it.
PostgreSQL creates a new version of that row.
and this simple idea is one of the reasons PostgreSQL can handle thousands of concurrent users without locking the entire table.
Before UPDATE:
users table
id name status
1 Roshan inactive
After UPDATE:
id name status
1 Roshan inactive <-- old version
1 Roshan active <-- new version
this is called MVCC: Multi-Version Concurrency Control.
instead of making readers wait while writers modify data, PostgreSQL allows different transactions to see different versions of the same row.
a running transaction sees the version that was valid when it started. another transaction can create a newer version without blocking it.
this is where the magic starts.
but every magic has a cost.
those old row versions cannot stay forever.
eventually PostgreSQL needs to clean them up.
these old versions are called dead tuples.
Table page:
+----------------+
| Row version 1 | <-- dead tuple
| Row version 2 | <-- dead tuple
| Row version 3 | <-- live tuple
+----------------+
VACUUM comes and cleans old versions.
and this is why VACUUM exists.
VACUUM is not just a maintenance command you run because PostgreSQL asked you to.
it is part of how PostgreSQL survives its own MVCC architecture.
now imagine a high traffic table.
millions of updates every day.
users changing profiles, orders changing status, events being processed.
the table is constantly creating new row versions.
if cleanup cannot keep up, something interesting happens.
the table starts growing even though your actual data size has not increased.
this is called table bloat.
your database says:
"you have 10 million users"
but internally PostgreSQL might be storing millions of old versions that nobody needs anymore.
and now the hidden problems appear.
more disk usage larger indexes more pages to scan more IO slower queries
this is why a query can become slower over time even when the SQL never changed.
the database underneath changed.
another interesting part is indexes.
when you update an indexed column, PostgreSQL may need to create new index entries pointing to the new row version.
so one UPDATE can become much more expensive than it looks.
UPDATE users SET email='new@email.com'
Application sees:
1 UPDATE
PostgreSQL internally:
Create new row version
Update indexes
Write WAL records
Mark old tuple dead
Later VACUUM cleanup
this is why database performance is not only about writing faster queries.
sometimes the real performance problem is understanding what the database is doing after your query finishes.
some lessons I learned:
• high update tables need proper vacuum configuration • long-running transactions are dangerous because they prevent cleanup • adding indexes improves reads but increases write cost • monitoring dead tuples matters for production databases • database size does not always represent actual business data size
the next time you write an UPDATE query, remember:
you are not changing a row.
you are creating a new version of reality, and PostgreSQL has a whole system behind the scenes to make that illusion work.
that invisible engineering is what makes modern databases feel simple on the surface and powerful underneath.