Three databases, before and after

The problem

Checkout latency doubled every Friday evening. The team had added read replicas twice; the primary still sat at 90% CPU.

What the report showed

The top statement by total time was an order lookup filtered by customer and status. It ran 48,000 times an hour and every run read the whole orders table: a sequential scan removing 4.2 million rows by filter, then a sort. An index on created_at existed but was never used, because the filter columns were not in it.

The fix

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx ON orders (customer_id, status, created_at DESC); the unused legacy index was dropped a week later.

Before → after

	Before	After
Mean time of the statement	412 ms	1.8 ms
Primary CPU at peak	90%	34%
Checkout p95	2.9 s	640 ms

The problem

The dashboard's usage widget timed out for the largest accounts. Engineers suspected the ORM; they were about to add a caching layer.

What the report showed

pg_stat_statements showed a count(*) executed 120,000 times a day with a mean of 220 ms and a plan that used a bitmap scan on account_id alone, then filtered six million rows by date, with 48,000 lossy heap blocks because work_mem was 4 MB.

The fix

A composite index on (account_id, occurred_at) turned the filter into an index condition; the widget now reads an hourly rollup for accounts above a threshold.

Before → after

	Before	After
Mean time of the statement	220 ms	3.4 ms
Statement's share of total DB time	31%	under 1%
Widget timeouts per day	1,400	0

The problem

A trivial UPDATE by session id had a mean of 95 ms and ran a quarter of a million times an hour. Nobody had looked at it because each call was 'fast enough'.

What the report showed

There was no index on session_id, so every update scanned 2.3 million live rows plus a million dead ones: the table was 31% dead tuples and autovacuum had not caught up in two days. The statement was third by total time despite the small mean.

The fix

An index on (session_id), a per-table autovacuum_vacuum_scale_factor of 0.01, and one manual VACUUM (ANALYZE) during a quiet hour.

Before → after

	Before	After
Mean time of the statement	95 ms	0.4 ms
Dead tuples	31%	2%
Total DB time per hour	6.6 h	1.9 h