Stay fast at a million rows
Continues from the last build: Upstream failures are contained by timeouts, careful retries and a circuit breaker.
The circuit breaker is holding. A flaky payment gateway can no longer take the whole API down with it.
What you'll build
The merchant order list answers in under 150ms p95 at 1,000,000 rows and 3,000,000 line items, backed by a composite index, a partial index, a single joined query instead of dozens, and keyset pagination that costs the same at page 1 and page 9,000. You have before/after EXPLAIN ANALYZE output and autocannon numbers to defend every change.
See how we teach, before you sign up
You don't just get code dumped on you. Every starter file and every solution is explained line-by-line, in plain English. Here's one real file from this project:
import { from as copyFrom } from 'pg-copy-streams';
import { Readable } from 'node:stream';
import { pool } from '../src/lib/db';
const TOTAL_ORDERS = 1_000_000;
async function main() {
const client = await pool.connect();
try {
console.time('seed-orders');
// TODO: stream COPY FROM STDIN for orders, then order_items, using generated rows
console.timeEnd('seed-orders');
} finally {
client.release();
}
}
main().catch((err) => {
console.error('seed failed', err);
process.exit(1);
});Reading this file
TOTAL_ORDERS = 1_000_000Keep this a named constant so autocannon and EXPLAIN ANALYZE runs later reference the same known scale.copyFromThis import is the fast bulk-load path, do not replace it with a loop of client.query('INSERT ...') calls.console.time('seed-orders')Timing the seed itself tells you whether the seed script is the bottleneck versus the endpoint you are actually measuring.
Fill in the CSV row generation from the seed milestone's solution, this file is the entry point run with pnpm tsx scripts/seed-million.ts.
That's 1 of 6 explained code blocks in this single project.
The build, milestone by milestone
- 1
Seed a million orders and measure honestly
5 guided stepsEvery optimization in this rung is judged against a before number. If you index first and measure second you are guessing, and you will not be able to tell your merchant, or your future self, whether a change actually helped or just felt like it should.
- 2
Read EXPLAIN ANALYZE for real
5 guided stepsAdding indexes without reading the plan is cargo culting. The planner already tells you exactly what it is doing and why, seq scanning 1,000,000 rows to return 20 is a real cost you can see in milliseconds and buffer reads, and the fix is only obvious once you have looked at it.
- 3
Index deliberately
5 guided stepsIndexes are not free, every INSERT and UPDATE on orders now has to maintain each index too. Adding the two the plan demonstrated a need for, instead of indexing every column defensively, keeps read latency down without silently doubling write latency.
- 4
Kill the N+1s
5 guided stepsAn index fixes the one big query, it does nothing for the 20 orders each triggering 2 more queries to fetch items and payment status. That is 41 round trips for a page of 20 orders, and Postgres round trip latency alone dwarfs any single query's execution time.
- 5
Keyset pagination at depth
5 guided stepsOFFSET 900000 still has to walk and discard 900,000 rows before returning the next 20, the cost grows linearly with page depth no matter how good your indexes are. A keyset cursor tells Postgres exactly where to resume with an index range scan, so deep pages cost the same as page 1.
What's inside when you start
You'll walk away with
This is portfolio-grade. Build it free.
Sign up to unlock every milestone step-by-step, the code skeletons, full reference solutions, and checkable tasks, with your progress saved as you build.
Start building