i audited my side project like it was about to go viral (before it was)

i ended the last post with a promise: a public stats page that ran three full-table scans on every request and fell over the first time i pointed real concurrency at it. this is that post — the last of three findings from the same pre-launch audit of a little pairwise-voting side project, a nougat-ranking toy where you pick between two flavours and a leaderboard falls out.

the first two findings were bugs: an elo race that ate 79% of every vote , and a rate limiter a rotated header walked straight through . this one is a bug too, but it’s really about the method that surfaced it, because the method is the transferable part. the bug — a page doing linear work per request against a table that only grows — is one you’ve almost certainly got somewhere right now and can’t see, for the exact reason i couldn’t: on your dev box the table has forty rows and everything’s instant. the page isn’t slow. the page is a landmine with the pin still in, and the only way to find it before your users do is to go step on it yourself.

so that’s the method. stand up a throwaway postgres, seed it with the amount of data you’d have if the thing actually worked — if it went viral, if the launch landed — and then measure the hot paths under that load instead of guessing. the very first page i pointed it at turned out to be a denial-of-service i was hosting for free, on purpose, with a smile.

seed for the traffic you’re afraid of

the whole trick is refusing to test against an empty database. an empty database lies to you. every query is fast, every plan is a happy little index-free scan of nothing, and you ship it believing you measured something.

my e2e suite already seeds fixtures with generate_series — a handful of rows so the gated pages have data to render:

test/e2e/run.sh

INSERT INTO "Results"("Id","Pairing","Torro1RatingBefore",/* ... */,"UserId")
SELECT 'e2e-res-'||g, p."Id", 1500,1500, p."Torro1", 1510,1490, 'e2e-user-unlocked'
FROM (SELECT "Id","Torro1" FROM "Pairings" WHERE "Class"='1' LIMIT 1) p, generate_series(1,5) g
ON CONFLICT ("Id") DO NOTHING;

for the audit i pointed that same primitive at volume: a few thousand synthetic users, every pairing cross-joined against a much bigger series, ratings jittered with random(). it landed at 5,051 users, 200,050 Results rows, 20,097 elo snapshots — a plausible “went viral” state. then the step everyone forgets: ANALYZE, so the planner has honest statistics and picks the plan it’d pick in production, not one biased by a stale row-count estimate.

and here’s the thing — 200k isn’t big. it’s a rounding error for postgres. that’s exactly the point. the page came apart at a volume the database itself considers trivial, which tells you the problem was never the data. it was the query.

the one page that stood out

with the box seeded, i walked every read endpoint and timed a single warm request. almost all of it was boring, which is what you want:

/ 3ms · the classes list 6ms · the vote screen 20ms · leaderboard 20ms · stats 22ms · history 16ms · the json api routes 7–15ms.

one page was not boring. the public stats page — a global, screenshot-friendly summary anyone can hit — came back at 480 to 600 milliseconds, solo, warm, with nobody else on the box. a page that slow with a single user is not a slow page. it’s a fast outage waiting for a second user.

three scans and a union

the page shows a few global aggregates: the most-voted flavour, the biggest riser this week, the closest duel. each one is its own aggregation over the entire Results table, and not one of them touched an index. here’s the worst — “biggest riser,” which has to attribute every rating delta to whichever side of the pairing a flavour was on, so it’s a UNION ALL of two full passes:

internal/repository/postgres_press_stats.go

SELECT t."Id", t."Name", t."Image", SUM(deltas."Delta") AS "NetChange"
FROM (
    SELECT p."Torro1" AS "TorroId",
           (res."Torro1RatingAfter" - res."Torro1RatingBefore") AS "Delta"
    FROM "Results" res
    JOIN "Pairings" p ON res."Pairing" = p."Id"
    WHERE res."Timestamp" > NOW() - make_interval(days => $1)
    UNION ALL
    // ... the exact same scan again for the Torro2 side ...
) deltas
JOIN "Torrons" t ON t."Id" = deltas."TorroId"
GROUP BY t."Id", t."Name", t."Image"
ORDER BY "NetChange" DESC
LIMIT 1

count the scans. “most voted” reads Results once. this riser query — the UNION ALL — reads it twice. “closest duel,” a third time. that’s three separate aggregations, four scans once you count the union’s two halves — call it three-plus full sequential scans of a 200k-row table on every request to a public url, and the number climbs for the life of the product, one row per vote, forever.

you don’t have to take my word for the plan — postgres will tell you ↗ . wrap any of these in EXPLAIN (ANALYZE, BUFFERS) ↗ and you get the truth: a Seq Scan on "Results", a couple of parallel workers spun up to brute-force it faster, ~90ms burned per scan, no index anywhere in the plan. the docs are blunt about what a sequential scan means — with no better path, postgres “must scan all the rows of the table.” the join key it wanted, Results."Pairing", had no index on it at all. neither did Results."Winner", the column the most-voted count filters on. both are foreign keys, and here’s the detail that bit me: postgres does not automatically index foreign-key columns. it indexes primary keys and unique constraints; the referencing side is on you. i’d assumed the FK bought me an index. it bought me a constraint and a sequential scan.

the part where it becomes a dos

480ms solo is embarrassing. the actual finding was what happened under concurrency, and this is where seeding pays for itself, because a slow query and a denial-of-service are the same query — the only difference is how many people run it at once.

the perf test fires a small burst at the page and reports the worst latency:

test/e2e/test_perf.sh

_burst=12
for i in $(seq 1 "$_burst"); do
	( curl -s -o /dev/null -w '%{http_code} %{time_total}\n' \
		-H "X-Forwarded-For: 192.0.2.$i" "${BASE_URL}/…" >"$tmp/$i" ) &
done
wait
maxms=$(cat "$tmp"/* | awk '{print $2}' | sort -rn | head -1 | /* ... */)
non200=$(cat "$tmp"/* | awk '$1!="200"' | wc -l)

that’s the regression guard. the real audit ran it harder — 50 concurrent — and the page collapsed: p95 of 13.8 seconds, max 14 seconds, while the static homepage next to it never budged off 126ms. the mechanism is a chain reaction. each request grabs parallel workers to run its scans. fifty requests want a hundred-plus workers on a box with a handful of cores, so every core saturates and every scan slows down. slow scans mean each request holds its database connection longer. and the pool is bounded — SetMaxOpenConns ↗ caps the whole app at 25:

internal/api/api.go

dbConnection.SetMaxOpenConns(25) // Maximum total connections (in-use + idle)

twenty-five connections, fifty in-flight requests each squatting on one for whole seconds. the pool empties, new requests queue for a connection that isn’t coming, and the whole thing backs up until it slams into the server’s 15-second WriteTimeout and starts handing back truncated 500s. one page. one link shared to the wrong-sized audience, one launch that actually worked — and the page built to show off the launch is the page that takes the site down. a denial-of-service with no attacker. i’d built the exploit and shipped it as a feature.

fix one: index the columns postgres wouldn’t

the queries were begging for the two indexes the schema never had. the fix is a migration and it is not clever:

migrations/000019_perf_indexes.up.sql

CREATE INDEX IF NOT EXISTS idx_results_pairing ON "Results"("Pairing");
CREATE INDEX IF NOT EXISTS idx_results_winner ON "Results"("Winner");

that’s it. two plain B-tree indexes ↗ — what the default CREATE INDEX gives you, the kind that “fit the most common situations,” nothing exotic — on the two foreign-key columns the aggregations join and filter on. idx_results_pairing turns the riser and duel queries’ Results→Pairings join from a sequential scan into an index lookup; idx_results_winner does the same for the most-voted count and the champion’s SELECT COUNT(*) ... WHERE "Winner" = $1. re-run the EXPLAIN and the Seq Scan is gone, replaced by an index scan; the parallel workers stand down.

fix two: stop recomputing a global aggregate per request

but an index alone only treats the symptom. the deeper wrong was recomputing a global aggregate — a number identical for every visitor on earth — from scratch on every single request. the most-voted flavour does not change between two page loads a second apart. so the honest fix isn’t just a faster query, it’s not running the query: memoize the whole stat block behind a short time-to-live and serve it from memory.

internal/http/press_handler.go

const pressStatsCacheTTL = 60 * time.Second

func (h *Handler) pressStats(ctx context.Context) (pressStatsBlock, error) {
	// Fast path: a fresh cached value serves the vast majority of
	// requests under a single read lock.
	pressCache.mu.RLock()
	if pressCache.hasData && time.Now().Before(pressCache.expiry) {
		block := pressCache.block
		pressCache.mu.RUnlock()
		return block, nil
	}
	pressCache.mu.RUnlock()

	// Stale or cold: serialize the refresh so a burst arriving at expiry
	// triggers a single recomputation, not one expensive query per request.
	pressCache.refresh.Lock()
	defer pressCache.refresh.Unlock()
	// ... re-check under the refresh lock, then computePressStats() once ...
}

two properties matter here. the first is the ttl: at most one recomputation per minute no matter how much traffic hits the page, which turns “three-plus scans per request” into “three-plus scans per minute.” the second is subtler, and it’s the part i almost got wrong — the refresh mutex. without it, the moment the cache expires under load, every concurrent request sees the stale entry at the same instant and they all stampede the database at once, which is precisely the pileup i was trying to prevent, now on a timer. the lock means the first request through recomputes while the rest wait a beat and then read the fresh value. one refresh, not fifty. (the code also keeps serving the last good value if a refresh errors, so a transient db hiccup degrades to slightly-stale instead of a 500 — a stats page has no business going down because one query timed out.)

the honest part: an index and a cache, not a miracle

this is a real fix that closed a real DoS, and it’s also two of the least glamorous tools in the box. worth naming what it isn’t.

the index is a plain btree, not anything clever. i considered a partial index (only recent rows) and a covering index (fold the summed columns in so the heap never gets touched), and for a hotter path i’d have reached for one. here the two default b-trees dropped the page under 20ms and i stopped, because the right amount of index is the least index that makes the plan stop scanning. adding a covering index i didn’t need would’ve been cargo-culting a benchmark i never ran.

the cache trades freshness for survival, and that’s a real trade, not a free lunch. the stats page can now be a full minute stale. for a leaderboard-flavoured toy that’s completely fine — nobody refreshing a public stats page is owed second-accurate numbers. but i want to be honest that “cache it” is a decision to be wrong, on purpose, for up to sixty seconds, in exchange for not falling over. on a page where staleness actually mattered — a balance, an inventory count — this exact fix would be a bug. it works here because i thought about what the number is for.

it’s still one VPS, and the ceiling didn’t move so much as recede. i haven’t made the box handle viral traffic; i’ve made the expensive page cost a fixed three-plus-scans-per-minute instead of scaling its cost with concurrency. that’s the difference between a page that dies at 50 concurrent and one that doesn’t — the difference that mattered before launch — but it is not horizontal scale, and if the underlying box ever gets genuinely buried, the answer is a different architecture, not a longer ttl.

and the whole thing is only visible because i seeded to volume. at forty rows in dev the offending query returns in single-digit milliseconds and the seq scan is free — there’s nothing to scan. the bug doesn’t exist at small scale; it’s created by data i didn’t have yet. the seeding wasn’t setup for the investigation. the seeding was the investigation. everything after it was cleanup.

lessons learned

• test against the database you’re scared of, not the one you have. an empty table makes every query look fine and every plan look free. seed to the volume you’d have if you succeeded — then ANALYZE, so the planner isn’t lying to you either. the bugs that only appear at scale are exactly the ones your happy path can’t see. (i keep a whole series about pointing tests at a real postgres and still had to re-learn this one under load.)
• measure, don’t infer. i could’ve read that stats page a hundred times and reasoned it was “probably fine.” EXPLAIN (ANALYZE, BUFFERS) ↗ isn’t a vibe; it’s postgres telling you, to the millisecond and the buffer, exactly what it did. a Seq Scan in the plan is a fact. run it.
• a slow query under concurrency is a denial-of-service you host yourself. latency and availability aren’t separate problems; the second is the first plus a crowd. anything doing linear work per request against a growing table is a self-DoS with a fuse whose length is set by your traffic.
• postgres does not index your foreign keys. it indexes primary keys and unique constraints and leaves the referencing columns bare. every FK you join or filter on is a sequential scan until you say otherwise.
• cache the thing that’s identical for everyone — and lock the refresh so expiry doesn’t stampede. a global aggregate recomputed per request is pure waste. a ttl fixes it; a single-flight refresh fixes the thundering herd the ttl would otherwise create at every expiry.

next, and last from this box before it actually ships: four public endpoints that each allocate eight megabytes and peg a whole core to render a share-image, with no concurrency cap between them and a stranger holding a for loop. turns out you can OOM your own VPS with a nice-looking png — the other denial-of-service i was hosting for free.

the audit took an afternoon per finding, and every one came from the same move: stop admiring the code and go measure it under the load you’re hoping for. the postgres notes on reading a query plan ↗ say everything i re-learned the slow way, and i should’ve had them open before i wrote the query, not after it fell over.