API latency doubled but errors stayed flat

Troubleshooting, Medium

You are on call for an orders API. Since 09:40 the response time doubled, but the error rate did not move. At 10:00 you have these facts: - Latency for GET /customers/{id}/orders: p50 went from 120 ms to 240 ms and p95 from 400 ms to 800 ms. Other endpoints that do not read orders are unchanged. - The error rate is flat at 0.2%. Requests per second are flat at about 900. Client timeout is 30 seconds. - Application CPU is flat at 35% on all 6 instances. The cache hit rate is unchanged at 92%. - Database CPU rose from 30% to 75% at 09:40. The slow-query log now shows SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20 taking about 95 ms on average. It took about 4 ms yesterday. - A release finished at 09:35. Its notes say "migration: tidy orders indexes". The orders table has 8 million rows, and busy customers have thousands of orders each. It has an index on customer_id alone, and had one on (customer_id, created_at) until the release. Say what you check, in order, with the evidence each check gives. Then say the cause, the fix, and how you confirm and prevent it.

Examples

Input EXPLAIN (ANALYZE, BUFFERS) of the slow orders query for one busy customer

Output Index Scan using orders_customer_id_idx (rows=3,012), then Sort (top-N heapsort), Execution Time: 94 ms.

Why Shows how the database runs the query and where the time goes. Compare it with what the query asks for.

Input SELECT indexname FROM pg_indexes WHERE tablename = 'orders';

Output orders_pkey, orders_customer_id_idx

Why Shows which indexes exist now. Compare the list with the release notes and with the query.

Input Decoy: requests per second, application CPU and cache hit rate before and after 09:40

Output All three are flat.

Why Load and application code did not change. This does not say which part of the database work grew.

Constraints

You have read access to the metrics, logs and database, and you can run an index build in a quiet window. The table receives writes all day.
There is no incident ticket yet. Customers have not complained, but a dashboard alert fires at p95 over 700 ms.
Do not take the database offline.

Your answer must contain

An ordered list of checks, and what each one shows
The cause, tied to the facts above
The fix, with a safe way to apply it on a busy table
How you confirm it works and how you stop it coming back