Catalog pages turn slow the morning after an upgrade
Your task
It is 08:25 on Monday. The shop runs on Amazon RDS for PostgreSQL. On Sunday night the database had a major version upgrade, from Postgres 14 to 16. It finished at 01:40, and RDS reported success. The app has not been deployed since Thursday. This alert fires:
CatalogLatencyP95: p95 above 1 s for 10 minutes (current 5.8 s)
What you see:
- p95, the time 95% of requests finish within, was about 600 ms for catalog pages overnight, against 120 ms last week. It passed 1 s from 07:30 as shoppers arrived. Checkout and order history are as fast as last week.
- Database CPU is 96%, against 35% at this hour last Monday. Request volume matches last Monday's.
- The top query in
pg_stat_statementsreadsproductsby category, ordered by rank, 48 rows at a time. Its mean is 2.4 s. Last week's dashboard shows it at 9 ms. EXPLAIN ANALYZEof that query shows a different plan from last week's. It expects about 30,000 rows in the category, where about 2,000 match. It walks the index onrankand throws away almost every row it reads.productshas 6 million rows. It changes only in Wednesday's catalog import.
How do you get the catalog fast again this morning, and what goes into the upgrade runbook?
It covers
- Major upgrade
- Planner statistics
- Rds postgres