Fix the Five Database Query Shapes That Collapse at Scale

This pack joins five reels that look different in the UI but fail in the same place: the shape of the database work.

R013: a lookup without the index its query plan needs;
R015: one list query followed by one hidden query per row;
R072: a join that multiplies one order into several rows;
R073: deep offset pagination that scans and discards earlier rows;
R074: substring search that reads the whole table.

Do not start by increasing the database size. Count rows, round trips, and work in the query plan.

1. Missing index

Run EXPLAIN on the real query with production-shaped row counts. A full table scan is not always wrong, but it is suspicious when a selective login or lookup predicate grows slower every week. Add the smallest index that matches the filter and ordering. Remember that every index also costs storage and write work.

2. N plus one

Count SQL statements for one page. One query for 100 orders plus 100 customer queries is 101 round trips. Join the required relation or fetch all referenced customers in one batch and match them in memory. Assert a query-count ceiling in a test so the loop cannot return quietly.

3. Join multiplication

Define the intended row grain before writing SQL: one row per order, item, customer, or payment. Joining two one-to-many tables multiplies combinations. Aggregate each child table to the intended grain before joining. DISTINCT can hide duplicate-looking rows while totals remain wrong.

4. Cursor pagination

OFFSET 100000 still makes the database walk past 100,000 rows. Use a stable order and a cursor containing the last sort value plus a unique tie-breaker:

WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50

Test equal timestamps, inserts between pages, deletion of the cursor row, and both navigation directions.

5. Full-text search

LIKE '%term%' usually cannot use a normal prefix index. Use the database's full-text or trigram index based on the search contract. Decide tokenization, stemming, typo tolerance, ranking, and language. Then use EXPLAIN ANALYZE to prove the new path is used.

One acceptance matrix

For every change, record before and after:

query plan and rows examined;
number of database round trips;
median and slow-percentile latency;
returned row identity and order;
totals and pagination continuity;
write cost and index size where relevant.

Use production-shaped fictional or sanitized data, not five seed rows. Claude can compare plans and draft a candidate query. Tests and measurements decide whether it is correct.

Run the starter locally

npm test
npm run validate
npm run sample

The starter validates query evidence and returns a draft diagnosis. Docker Compose includes n8n and the Node workflow boundary. It never connects to or changes a production database.