Our PostgreSQL cluster crossed 10TB sometime in late 2024. I remember because that was also the week our reporting service started returning HTTP 504s intermittently, and the on-call rotation became a lot less fun. Seven engineers on the team, one database, and a growing pile of angry Slack messages from the analytics team.<\/p>\n
This is what I learned<\/a> over the next two months of<\/a> digging.<\/p>\n The first sign was a query in our reporting pipeline that had been running in under 500ms for months \u2014 suddenly averaging 40 seconds. Not always. Just… sometimes. Which is almost worse than always, because intermittent slowness is genuinely hard to reproduce.<\/p>\n My first instinct was “missing index.” Wrong. Or \u2014 okay, not entirely wrong, but that wasn’t the root cause. The table in question ( See that The actual culprit was index bloat from dead tuples. Autovacuum hadn’t kept up with a particularly write-heavy period a few weeks earlier, and the index had swelled with dead entries. The planner was traversing enormous amounts of garbage to reach live rows.<\/p>\n Before I arrived at the<\/a> autovacuum diagnosis, I spent a few days inside A few things surprised me. The query I was fixated on \u2014 the 40-second monster \u2014 wasn’t even in the<\/a> top 5 by total execution time. Three other queries were collectively burning more CPU, each running thousands of times per day. Short calls, individually fast, but hammering the database constantly from a background job polling every 15 seconds. Nobody had noticed because no single call looked alarming.<\/p>\n So the reporting crisis was user-visible and urgent. But the background polling was quietly degrading our read replicas, and I hadn’t known about it until I looked here.<\/p>\n The One thing I noticed after a planned failover: Here is the thing about partial indexes: I knew they existed. I’d used them on smaller databases. But at 800M rows, the difference between a full index and a well-scoped partial index is enormous \u2014 in query time and in ongoing maintenance cost.<\/p>\n We had:<\/p>\n When realistically 90% of our queries only touched the last 90 days and a handful of event types. The index covered three years of<\/a> data we almost never queried. After analyzing access patterns, I replaced it with:<\/p>\n Smaller index, faster scans, less bloat surface. Query time on the reporting query dropped from 40 seconds to about 1.2 seconds on the first run. I was genuinely shocked \u2014 I’d been expecting maybe a 50% improvement, not 97%.<\/p>\n One warning: I pushed this on a Friday afternoon. Specifically, a Friday at 4pm. For the bloat specifically \u2014 I diagnosed it with The Autovacuum is mentioned in every PostgreSQL tutorial. Most teams then ignore it until something breaks. We ignored it. Something broke.<\/p>\n The fix was per-table autovacuum configuration:<\/p>\n The default I’m not 100% sure this exact configuration scales beyond our specific write pattern. If your table takes large bulk deletes rather than steady row churn, the math changes. Your mileage may vary.<\/p>\n What genuinely caught me off guard: none of our monitoring dashboards were surfacing any of this. We had query time alerts, CPU alerts, disk alerts. We had zero visibility into That dashboard has saved us more than any other single change from this whole project.<\/p>\n The biggest mistake wasn’t any specific technical choice. It was treating database performance as a reactive problem \u2014 queries slowed, we investigated; bloat grew, we responded. We had no proactive visibility into what was quietly building up.<\/p>\n Honest answer: I’d flip the order of everything I actually<\/a> did. The Grafana panel pulling from How a 40-Second Query Killed Our Reporting Service<\/h2>\n
user_events<\/code>, ~800M rows, partitioned by month) had indexes. The query planner was using them. But the EXPLAIN ANALYZE<\/code> output showed something I hadn’t seen before at this scale:<\/p>\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT\n user_id,\n event_type,\n COUNT(*) AS event_count\nFROM user_events\nWHERE\n created_at >= '2024-10-01'\n AND created_at < '2024-11-01'\n AND event_type = ANY(ARRAY['click', 'purchase', 'view'])\nGROUP BY user_id, event_type;\n<\/code><\/pre>\nIndex Scan using idx_user_events_created_at on user_events_2024_10\n (cost=0.57..892341.22 rows=2847293 width=24)\n (actual time=0.843..38421.337 rows=2847293 loops=1)\n Buffers: shared hit=12847 read=1829403 dirtied=0 written=0\n I\/O Timings: read=36891.442 write=0.000\n<\/code><\/pre>\nread=1829403<\/code> vs hit=12847<\/code>? Buffer hit ratio under 1%. Everything was going to disk. But the index existed and was supposedly being used, so I spent two days assuming the problem was work_mem<\/code> or shared_buffers<\/code> being too low. Tuned both. Query was still slow.<\/p>\nWhat pg_stat_statements Actually Told Me (vs. What I<\/a> Expected)<\/h2>\n
pg_stat_statements<\/code>. If you’re not already querying this regularly, you’re missing probably the most useful thing Postgres ships with.<\/p>\nSELECT\n query,\n calls,\n round(mean_exec_time::numeric, 2) AS mean_ms,\n round(total_exec_time::numeric, 2) AS total_ms,\n round(stddev_exec_time::numeric, 2) AS stddev_ms,\n rows \/ NULLIF(calls, 0) AS avg_rows\nFROM pg_stat_statements\nWHERE calls > 100\nORDER BY total_exec_time DESC\nLIMIT 20;\n<\/code><\/pre>\nstddev_exec_time<\/code> column is something I now look at almost more than mean. A query with 200ms mean and 800ms stddev concerns me more than a flat 300ms mean. High variance usually signals something inconsistent \u2014 sometimes cache-warm, sometimes cold, sometimes hitting lock contention.<\/p>\npg_stat_statements<\/code> resets on PostgreSQL restart unless you’ve set pg_stat_statements.save = on<\/code>. We hadn’t. I lost two weeks of<\/a> history and had to start fresh. Set that to on<\/code> immediately.<\/p>\nWhy My Indexes Were Wrong, and What<\/a> Index Bloat Actually Does to You<\/h2>\n
CREATE INDEX idx_user_events_event_type\n ON user_events (event_type, created_at);\n<\/code><\/pre>\nCREATE INDEX CONCURRENTLY idx_user_events_recent_active\n ON user_events (event_type, user_id, created_at)\n WHERE created_at >= '2024-01-01'\n AND event_type IN ('click', 'purchase', 'view', 'signup');\n<\/code><\/pre>\nCREATE INDEX CONCURRENTLY<\/code> on a large table runs for hours (ours took 3.5), and while it avoids an exclusive lock on the table for most of that time, it does acquire locks during the final phase. It also hammers disk I\/O during construction. Production slowed noticeably for about 20 minutes near the end. The analytics team filed a P2. I learned<\/a> my lesson about Friday deployments the hard way.<\/p>\npgstattuple<\/code>:<\/p>\nSELECT * FROM pgstattuple('idx_user_events_created_at');\n<\/code><\/pre>\ndead_leaf_percent<\/code> was sitting at 31%. Anything over roughly 10% is worth addressing. A REINDEX CONCURRENTLY<\/code> brought it down under 2% and immediately improved our buffer hit ratio. That single operation recovered most of the query time regression before I’d even touched the index structure itself.<\/p>\nAutovacuum Was the Thing Nobody on My Team Wanted to Talk About<\/h2>\n
user_events<\/code> was receiving about 2 million writes per day. The default autovacuum settings \u2014 sized for much smaller tables \u2014 couldn’t keep up. Dead tuples accumulated, bloated the indexes, and eventually pushed the planner toward worse choices.<\/p>\nALTER TABLE user_events SET (\n autovacuum_vacuum_scale_factor = 0.01, -- trigger when 1% of rows are dead (default 0.2)\n autovacuum_analyze_scale_factor = 0.005,\n autovacuum_vacuum_cost_delay = 2 -- less sleep between pages; default is 20ms\n);\n<\/code><\/pre>\nautovacuum_vacuum_scale_factor<\/code> of 0.2 on an 800M-row table means autovacuum doesn’t trigger until 160 million dead rows have accumulated. That’s completely unworkable for a write-heavy table. Dropping it to 0.01 triggers at 8 million rows \u2014 still substantial, but manageable. Combined with bumping autovacuum_max_workers<\/code> from 3 to 6 globally, the bloat stabilized within a week.<\/p>\nn_dead_tup<\/code> on our largest tables. I added a Grafana panel pulling from pg_stat_user_tables<\/code> \u2014 n_dead_tup<\/code>, last_autovacuum<\/code>, n_tup_del<\/code> \u2014 and within two days it revealed three other tables with similar bloat building up, none of which had degraded yet into visible slowness.<\/p>\nWhat I’d Do Differently Starting from Zero<\/h2>\n
pg_stat_user_tables<\/code> should have been week one, not week eight. Slow query logging should have been set to 500ms from the start, not 5 seconds \u2014 at 5 seconds you’re only seeing things that are already on fire. At 500ms you catch them while they’re still cheap to fix. I knew this in theory. I didn’t actually do it.<\/p>\n