Catalog pages turn slow the morning after an upgrade

On-call incidentEasy15 min

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_statements reads products by 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 ANALYZE of 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 on rank and throws away almost every row it reads.
  • products has 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