How the Default Autovacuum Scale Factor Let 11 Million Dead Rows Pile Up Behind One Badge Count

03:14 UTC. PagerDuty: "p99 latency on /api/notifications/unread-count above 5s." That endpoint runs on every page load, it renders the little number on the bell icon. Nobody had touched that code in months.

the setup

The notifications table backs one query almost every logged-in request makes: how many unread notifications does this user have. Rows get inserted when something happens (a reply, a follow, a mention), updated once when the user reads them, and hard-deleted after 30 days by a nightly cleanup job. In January the table held about 2 million rows and the query, a count against an index on (user_id, read_at), ran in single-digit milliseconds as an index-only scan.

In June we shipped mentions as a new notification type. Anyone who got @-mentioned in a comment thread now generated a row for every participant in that thread, not just the person they replied to. Insert volume on the table went up roughly 20x almost overnight, and by the start of September the table had grown to 48 million rows. Nobody flagged this as a database change, because from the schema's point of view nothing had changed. It was the same table, the same index, the same query. Only the row count had moved.

the scramble

First theory: the mentions feature introduced an N+1 somewhere in the notification fan-out path, and the badge endpoint was getting caught behind a backlog of writes. Reasonable guess, wrong target. The badge endpoint only ever runs one query, a COUNT against notifications, and it doesn't touch the fan-out path at all.

Second theory: connection pool exhaustion. Mentions had added write load, maybe reads were queueing behind it. PgBouncer's stats ruled that out inside a few minutes, pool utilization sat at 40%, nothing was waiting on a connection. Whatever was slow, it was slow once it had a connection, running.

the hunt

Running the actual badge query by hand against prod with EXPLAIN ANALYZE was the first thing that pointed anywhere useful.

Bitmap Heap Scan on notifications  (cost=118420.55..2004112.09 rows=41 width=0)
                                    (actual time=891.204..6104.877 rows=6 loops=1)
  Recheck Cond: (user_id = $1)
  Filter: (read_at IS NULL)
  Rows Removed by Filter: 9814662
  Heap Blocks: exact=41203 lossy=612884
  ->  Bitmap Index Scan on idx_notifications_user_read
        (cost=0.00..118420.44 rows=2841233 width=0)
        (actual time=612.331..612.331 rows=9814708 loops=1)
Planning Time: 4.112 ms
Execution Time: 6106.203 ms

Rows Removed by Filter: 9814662 for a query that returns 6 rows. The planner had given up on an index-only scan and was instead pulling nearly 10 million rows off the heap and throwing almost all of them away. That's not a slow query problem, that's a "the table itself is full of dead weight" problem. pg_stat_user_tables confirmed it.

SELECT n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'notifications';

 n_live_tup | n_dead_tup |    last_autovacuum     |   last_autoanalyze
------------+------------+------------------------+------------------------
   48312904 |   11203481 | 2026-08-19 02:11:04+00 | 2026-08-19 02:11:04+00

Nineteen days since the last completed autovacuum on a table taking heavy write and delete traffic every hour. Not because autovacuum was disabled. Because it was doing exactly what its settings told it to do.

the find

autovacuum_vacuum_scale_factor defaults to 0.2 and nobody had ever overridden it on this table, because at 2 million rows it never needed overriding. The trigger formula is threshold + scale_factor * n_live_tup, with the default threshold at 50. At 2 million rows that's roughly 400,000 dead tuples before autovacuum fires, easily reached and cleared within a day under normal churn. At 48 million rows the same formula demands roughly 9.6 million dead tuples before autovacuum even starts. The setting hadn't changed. What it meant had changed completely, and it changed the moment the table's size changed, not the moment anyone edited a config file.

Once autovacuum finally did trigger, it ran into the second half of the problem: autovacuum_vacuum_cost_limit was still at its default of 200, a global I/O budget shared across every autovacuum worker in the cluster. A full pass over a table this size, throttled to that budget, took about 74 minutes. During those 74 minutes, mentions traffic and the nightly cleanup job kept generating dead tuples of their own, at a rate that outpaced what a single throttled pass could reclaim. Vacuum was running. It just could never catch up to the table it was running on, and the index on (user_id, read_at) bloated right alongside the heap, which is what pushed the planner off the index-only scan it used to rely on.

the fix

First, we reclaimed the existing bloat. A plain VACUUM FULL takes an exclusive lock for the duration, unacceptable on a table this hot, so we used pg_repack instead, which rebuilds the table and its indexes in the background and swaps them in with a brief lock at the end.

pg_repack --table=notifications   --host=prod-primary --dbname=app   --jobs=4 --no-superuser-check

Second, we fixed the setting that should have scaled with the table years ago. A fixed percentage makes sense for a table that stays roughly the same size. It stops making sense the moment a table's row count moves by an order of magnitude, and nothing before this incident checked for that.

ALTER TABLE notifications SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 2000
);

0.01 means autovacuum now triggers at roughly 1% of live rows instead of 20%, around 480,000 dead tuples on the current table size rather than 9.6 million, and the higher cost limit lets each pass move ten times as much I/O before throttling kicks in. Third, we stopped relying on latency to notice this happening again. A dead-tuple ratio alert now watches pg_stat_user_tables directly.

SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / greatest(n_live_tup, 1), 4) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup::numeric / greatest(n_live_tup, 1) > 0.05
ORDER BY dead_ratio DESC;

Longer term, the cleanup job that hard-deletes notifications after 30 days is being replaced with monthly partitioning, so old data leaves by dropping a partition instead of by generating dead tuples in the first place. That's a bigger migration and it's queued separately. It removes the root cause of the churn; the scale factor fix removes the blind spot that let the churn go unnoticed for nineteen days.

the aftermath

Nothing about this table's schema, indexes, or query was ever wrong. The badge query today is character-for-character what it was in January. The only thing that changed was scale, and the setting governing when cleanup happens was defined as a percentage of a number that grew 24x without anyone deciding it should.

The mentions feature that triggered this shipped through a normal review process. Nobody reviewing it thought to ask what a 20x increase in insert volume would do to a maintenance setting three layers removed from the code they were writing. That's the actual gap: growth planning covered load and cost, not the assumptions baked into settings nobody remembered were there.