A critical 1 TB PostgreSQL table is 60 percent bloat and queries are slowing. How do you fix it without downtime and stop it coming back?

What the interviewer wants to hear

• Measure bloat with pgstattuple or estimates, and find the cause
• Remove what blocks vacuum: long transactions, abandoned slots, prepared transactions, hot_standby_feedback
• Rebuild online with pg_repack, or REINDEX CONCURRENTLY for indexes, after checking disk space
• Tune per-table autovacuum: lower scale factor, higher cost limit
• Consider fillfactor for HOT updates or partitioning for retention

Model answer

First I would confirm the bloat with pgstattuple or a reliable estimate, and separate table bloat from index bloat. Then I would find why vacuum fell behind: very long or idle-in-transaction sessions, an abandoned replication slot, old prepared transactions, or hot_standby_feedback from a replica running long reports. Fixing that comes before any rebuild, otherwise the bloat returns. To recover space online I would use pg_repack, which builds a new copy and swaps it in with brief locks, after checking there is enough free disk and replica lag headroom; for indexes REINDEX CONCURRENTLY is enough. To prevent it, I would set per-table autovacuum settings with a much lower scale factor and higher cost limit, lower fillfactor if updates are frequent so HOT updates work, add timeouts for idle transactions, and alert on dead tuple counts.

More hard Database Administrator questions

• Design a high-availability setup for a critical PostgreSQL database with RPO near zero and RTO under one minute.
• Your single database is reaching its limits. How do you decide whether to shard, and how would you do it?
• How would you upgrade a large production PostgreSQL database to a new major version with minimal downtime?
• The application is timing out and you suspect lock contention in production. Walk through how you diagnose and fix it.
• Someone ran DELETE without a WHERE clause on an important production table 20 minutes ago. What do you do?
• Design a disaster recovery plan for a critical database that must survive the loss of a whole cloud region.
• How would you migrate a 5 TB on-premises PostgreSQL database to a managed cloud service with less than 15 minutes of downtime?
• The business expects traffic to grow ten times over the next 18 months. How do you plan database capacity?
• Your company must meet compliance rules (such as PCI DSS or GDPR) for a database holding personal and card data. What controls do you put in place?