Hard Database Systems Query Optimization
Explain table bloat and VACUUM in PostgreSQL.
Answer
PostgreSQL MVCC keeps old row versions (dead tuples) until no transaction needs them. Bloat: dead tuples waste space, degrade performance. VACUUM marks space reusable but doesn't return to OS. VACUUM FULL rewrites table (locks it). Autovacuum runs automatically based on thresholds. Bloat causes: long transactions, heavy updates/deletes, autovacuum lag. Monitor: pg_stat_user_tables dead tuple count, pgstattuple extension. Prevention: tune autovacuum, avoid long transactions, consider fillfactor for update-heavy tables.
IIT Certified
Master These Concepts with IIT Certification
175+ hours of industry projects. Get placed at Bosch, Tata Motors, L&T and 500+ companies.
Relevant for Roles
Database Administrator Senior Software Engineer DevOps Engineer