SproutStackSproutStack home
BeginnerPostgreSQL

A slow query on a table that never stops taking writes

Order lookups by customer take seconds. The table takes writes all day.

The setup today

An orders table in PostgreSQL has grown to 80 million rows. The customer page runs a query for a customer's recent orders, and it has become slow. The table receives inserts and updates all day, every day.

Current configuration

SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20;
-- indexes: orders_pkey (id) only
-- EXPLAIN: Seq Scan on orders ... rows=80,000,000

What you know

  • The query takes 4 to 9 seconds.
  • Checkout writes about 300 orders a second, and must not pause.

The new requirement

The customer page must load in under 200 ms, and you cannot take downtime or block checkout to fix it.

Make the call

2 questions · first-try answers earn the most XP

1. Which change fixes the query?
2. How do you create the index without blocking checkout?
What a strong answer also covers
  • Check the plan with EXPLAIN ANALYZE before and after, not just the timing.
  • Watch for a failed concurrent build: it leaves an INVALID index that still slows writes until you drop it.
  • Every index costs something on each write; check you are not duplicating an existing one.
  • Selecting only the needed columns (or a covering index with INCLUDE) can avoid reading the table at all.
Variations to think through

Change one fact and decide again. There is no single right answer here.

  • The page also filters by order status. Does that column belong in the index, and where?
  • The table is 2 TB and the concurrent build will take hours. What do you monitor while it runs?
  • Most queries only want orders from the last 30 days. Could a partial index help?

The background

More drills