01Rate limiting02Databases03Caching04Flash sale05Idempotency06Circuit breaker07Queues & DLQ

Lab / 02 · Databases

Most “slow APIs” are really slow queries.

Three experiments on things I’ve fixed in production: a missing index, the N+1 problem, and a race condition that pays out money twice. Run each one and read the database’s side of the story.

Numbers here are illustrative, from a 1M-row test table. On the live site they come from real EXPLAIN ANALYZE runs against Postgres.

CONNECTED TO BACKEND · Spring Boot (:3333) + PostgreSQL
Real EXPLAIN ANALYZE · Hibernate Queries · Concurrency Race
EXPERIMENT 01 · INDEXING5,225x faster
418 ms➔0.08 ms
Composite index eliminates full-table scan and in-memory sort across 1M rows.
EXPERIMENT 02 · N+1 QUERIES11.2x faster
42.6 ms➔3.8 ms
`JOIN FETCH` replaces 51 serial DB round-trips with a single batch join.
EXPERIMENT 03 · CONCURRENCY0% lost updates
100% loss➔0% loss
Pessimistic row locking (`FOR UPDATE`) & `@Version` protect double payout.
EXPERIMENT 01 · INDEXES & QUERY PLANS

“Show me a user’s last 20 orders”

SELECT * FROM orders WHERE user_id = 4821 AND created_at > now() - interval '30 days' ORDER BY created_at DESC LIMIT 20;
-- no index on orders(user_id, created_at)
⚡ Execution Time Comparison(1,000,000 rows in PostgreSQL)
No index● active
1,000,000 rows~418.2 ms1x (Baseline)
Index on user_id
1,180 rows~5.8 ms72x faster
Composite (user_id, created_at)
20 rows~0.08 ms5,225x faster
Rows scanned
1,000,000
Full table scan (999,980 discarded)
Execution time
~418.2 ms
k6 Postgres benchmark baseline
Relative work done (log scale)
Limit (actual time=418.2..418.3 rows=20) -> Sort (sort key: created_at DESC) -> Seq Scan on orders Filter: user_id = 4821 AND created_at > ... Rows Removed by Filter: 999,972
EXPERIMENT 02 · THE N+1 PROBLEM

GET /lab/db/courses — 50 courses, each with its instructor

⚡ Round-trip Latency & Network Overhead(50 courses with instructors)
51 separate round-trips over TCP
Lazy Loading (Naive N+1)~42.6 ms
51 queries fired individually across 51 network round-trips
JOIN FETCH (Eager Batch)~3.8 ms
1 single joined query fetched over 1 single network round-trip
SQL queries fired
51
1 query for courses + 50 queries for instructors
Execution time
~42.6 ms
Accumulated round-trip database latency
courseRepo.findAll() .forEach(c -> c.getInstructor() .getName());
// Hibernate statement inspector SQL log
Click “Call the endpoint” to watch queries fire in real-time.
EXPERIMENT 03 · TRANSACTIONS & LOCKING

Two ₹700 withdrawals hit a ₹1,000 wallet at the same moment

// Concurrent thread execution timeline~2.1 ms (unsynchronized race)
Select a strategy and click “Fire both requests” to start.
Final balance
₹1,000
Pick a strategy and fire both requests.
Wallet w = repo.findById(id); if (w.balance >= 700) { w.balance -= 700; repo.save(w); }

⚡ k6 Database Load Test Benchmarks

k6 v0.50.0 · 1,000,000 rows
Query Plan & Index Speed
No index:412.4 ms (1M rows)
Single index:5.8 ms (1,180 rows)
Composite:0.08 ms (20 rows)
N+1 vs JOIN FETCH
Naive N+1:51 queries (42.6 ms)
JOIN FETCH:1 query (3.8 ms)
11.2x latency reduction + 98% fewer round-trips
Concurrency Race Isolation
No lock:100% lost updates (double payout)
Pessimistic lock:0% lost updates (safe reject)
Optimistic lock:0% lost updates (@Version check)

Trade-offs I weigh

Indexes aren’t free

Every index speeds up reads and slows down writes. I index for the queries that actually run hot, not every column.

Column order matters

(user_id, created_at) serves “a user’s recent orders”. (created_at, user_id) mostly doesn’t. Equality first, then range.

Pessimistic vs optimistic

Row locks are simple when conflicts are common, like a hot wallet. A version column scales better when they’re rare.

← 01 Rate limitingNext: why backend is the hero of AI →