Problem RestatementProblem
JPMorgan asked: queries that filter a table of millions of rows by user and by region are slow. Design an evidence-driven optimization plan. Compare indexes, partitioning, sharding and caching, and say how you'd measure the improvement.
Example table: transactions(id, user_id, region, amount, status, created_at, ...), with 5M rows.
Slow queries:
SELECT * FROM transactions WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;
SELECT region, SUM(amount) FROM transactions WHERE region = 'APAC' AND created_at >= now() - interval '7 days' GROUP BY region;Step 1: Measure, Don't Guess
- Find the slowest and most frequent queries (the slow query log,
pg_stat_statements). - Run
EXPLAIN ANALYZEon each. Look for a Seq Scan (reading the whole table), big row estimates vs actual counts, sorts spilling to disk, and nested loops over many rows. - Record the baseline: p50/p99 latency and rows read.
Deep Dive — Indexing for the queries you actually runDeep dive
Two slow queries: one filters by user_id and shows the most recent rows, the other filters by region over a time range and sums an amount.
An index on every filtered column
Create separate indexes on user_id, on region and on created_at.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
Q["WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20"] --> IDX["Index on user_id"]
IDX --> ROWS["Finds 40,000 rows for that user"]
ROWS --> FETCH["Heap fetch for each one"]
FETCH --> SORT["Sort all 40,000 by created_at"]
SORT --> TOP["Discard all but 20"]
MANY["Three separate indexes"] --> WRITE["Every insert updates all three"]Single-column indexes answer the filter and nothing else, so the database still sorts a large intermediate result to find twenty rows. Meanwhile every write pays to maintain three structures.
A composite index per query
(user_id, created_at DESC) for the first query. The database seeks to that user's section, where rows are already in time order, and stops after twenty.
This is the big win — from scanning millions of rows to reading twenty. What remains is that the index holds only the key columns, so a query needing other columns still does a heap fetch per row. For the second query, summing amount over a range, that is thousands of random reads.
Get the column order right, and cover the query
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
R1["Equality columns first: user_id, region"] --> R2["Then range / sort columns: created_at"]
R2 --> SEEK["Seek directly, read in order, stop early"]
COV["INCLUDE (amount, status)"] --> ONLY["Index-only scan - the table is never touched"]
FN["WHERE date(created_at) = ..."] --> KILL["Function on an indexed column - index unusable"]
KILL --> FIX["Rewrite as a range: created_at >= ... AND < ..."]- Equality columns first, then range or sort columns.
(user_id, created_at)works;(created_at, user_id)cannot seek to a user, because the leading column is spread across the whole index. INCLUDEthe columns the query returns.(region, created_at) INCLUDE (amount)makes the second query an index-only scan — the sum comes entirely from the index with no heap access at all.- Never wrap an indexed column in a function in
WHERE.date(created_at) = '2026-09-19'cannot use an index oncreated_at; the equivalent range predicate can.
Two indexes deliberately chosen beat five added defensively: each one costs write throughput and space, so they should map to measured queries. And confirm with EXPLAIN ANALYZE — the plan either says index-only scan or it does not, and that is the difference between the second rung and the third.
Step 3: Beyond Indexes (only if needed)
- Partitioning (e.g., monthly partitions by created_at): time-range queries skip old partitions (partition pruning), and deleting old data is instant (drop the partition). Useful when the table grows to hundreds of millions of rows.
- Pre-aggregation: for the regional report, keep a
daily_region_totalstable updated incrementally, so the report reads a few hundred rows. - Caching: cache results of frequent identical queries (short TTL) in Redis.
- Read replicas: move heavy reporting reads off the primary.
- Sharding (splitting data across database servers): only when a single server truly can't handle the data or writes. It adds a lot of complexity. A million-row table almost never needs it.
Step 4: Verify
- Re-run
EXPLAIN ANALYZE: expect an Index Scan / Index Only Scan, and far fewer rows read. - Compare p50/p99 before and after under realistic load, and watch write latency (new indexes cost something).
- Roll out index creation without locking the table (
CREATE INDEX CONCURRENTLYin PostgreSQL).
Wrap-UpWrap-up
Start with evidence: the slow query log, EXPLAIN ANALYZE and baselines. Fix most problems with composite indexes that match the filters and sort order (equality columns first, covering where useful), and with query rewrites that keep filters index-friendly. Add partitioning, pre-aggregated tables, caching or read replicas only as data and load grow, keep sharding as a last resort, and verify every change with plans and latency numbers.