Databases & System Design•Part 2: Relational Databases (RDBMS / PostgreSQL / MySQL) (6 - 25)
MVCC and VACUUM in PostgreSQL:
In Simple Words:
In Postgres, updating a row writes a new row version rather than overwriting old bytes. Old versions become "dead tuples". VACUUM reclaims disk space occupied by dead tuples.
sql
-- Check dead tuple count on a table:
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- Manually vacuum and analyze table:
VACUUM ANALYZE orders;