No deploy. No traffic change. Queries just keep getting slower, day over day.

01Symptom

A Postgres table with a heavy UPDATE/DELETE workload gets progressively slower over several days — not a sudden spike, a slow creep. No deploys, no traffic changes correlate.

02Constraints

• Postgres, default autovacuum settings
• A nightly reporting job opens a transaction and holds it open for roughly an hour
• No idle_in_transaction_session_timeout configured
• Table has heavy write churn: frequent UPDATEs and DELETEs

03Evidence

• →pg_stat_user_tables shows n_dead_tup climbing continuously, with last_autovacuum not advancing on this table
• →pg_stat_activity shows a long-lived transaction (matching the reporting job's schedule) with an old xmin
• →pg_relation_size for the table is growing much faster than actual row count would explain
• →Query latency degrades gradually over days, tracking the bloat growth, not a step-function change

→The question

What's preventing autovacuum from doing its job, and how do you confirm it before restarting anything?

04Your prediction