The database that slowed down every Monday

Start with the query plan.

psqllive

Illustrative example of a typical fix.replay

The scenario#

Every Monday at 9 am the app crawled, and support tickets piled up. By lunch it was fine again. The database server was “not that busy” the rest of the week.

The first instinct was a bigger server. That would have cost more every month and only softened the symptom.

Symptoms#

• Page loads of several seconds, only on Monday mornings.
• CPU and disk reads on the database spiking at the same time.
• A weekly sales report scheduled for Monday 9 am, running on the primary.

How it was found#

pg_stat_statements records how much total time each query shape costs. Sorting by total time, not by single slowest run, shows what’s actually eating the server.

SELECT query, calls,
       round(mean_exec_time) AS avg_ms,
       round(total_exec_time) AS total_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

The top entry was the app’s “recent orders for this customer” query. On its own it was slowish. With the report scanning the same table, it fell over. Its plan showed a full scan of the orders table to return 20 rows.

What fixed it#

1. A composite index matching the filter and the sort, built without locking writes.
2. The weekly report moved to a read replica, so it can’t compete with the app.
3. An alert on the top queries by total time, so the next regression shows up early.

CREATE INDEX CONCURRENTLY ON orders (customer_id, created_at DESC);

Result#

In this illustrative version, the query goes from 812 ms to under 1 ms, because it reads 20 rows instead of 2 million. Monday mornings become ordinary, with no hardware change.

Lessons#

• Sort queries by total time. A query that’s slightly slow but runs constantly matters more than a rare slow one.
• Keep reporting off the primary.
• Upgrade hardware last, after the plans say the work is necessary.