Skip to content
RED SAGSkills
mediumDatabase Administrator interview question

A busy PostgreSQL table keeps getting bloated. How do you tune autovacuum?

What the interviewer wants to hear

  • Check dead tuples and last autovacuum time in pg_stat_user_tables
  • Lower scale factor per table for big tables
  • Raise cost limit or reduce cost delay so vacuum works faster
  • Find long transactions or slots blocking cleanup
  • Use pg_repack to remove existing bloat

Model answer

I start with pg_stat_user_tables to see n_dead_tup and when autovacuum last ran. The default autovacuum_vacuum_scale_factor of 0.2 means a 100 million row table waits for 20 million dead rows, which is too late, so I set a lower scale factor or a fixed threshold on that table with ALTER TABLE ... SET. If vacuum runs but cannot keep up, I raise autovacuum_vacuum_cost_limit or lower the cost delay, and maybe add more workers. I also check what is stopping cleanup: long-running or idle in transaction sessions, old replication slots, or hot_standby_feedback from replicas all hold back the oldest visible transaction, so dead rows cannot be removed. Setting idle_in_transaction_session_timeout helps. For bloat that already exists, I use pg_repack instead of VACUUM FULL, which locks the table.

Can you answer it under time pressure?

Take a timed medium round and get scored on every answer.

Start mock interview

More medium Database Administrator questions