MVCC and bloat in Postgres

How rows are stored

Postgres never updates a row in place. Every UPDATE writes a new row version and marks the old one dead. Every DELETE only marks dead. Space is reclaimed later by vacuum.

Consequences:

• UPDATE costs roughly INSERT plus DELETE.
• DELETE temporarily increases table size and never shrinks anything.
• A constantly updated table grows constantly unless vacuum keeps up.
• Updating one column rewrites the whole row, including the untouched 2KB JSONB blob.
• Every index entry must point at the new version, unless the update is HOT.

This is the mechanism behind most "the database got slow and nothing changed" incidents.

Measuring bloat

Dead rows not yet reclaimed. The cost is not disk but that every sequential scan reads them and the buffer cache fills with garbage. A 10 GB table that is 70% bloat performs like 10 GB while holding 3 GB of live data.