postgres-under-load

Load-testing my own production schema to find out what breaks first — and finding that the two real problems were both things I had not predicted.

Tournly is a multi-tenant tournament platform for padel and tennis on Postgres via Supabase. I built it and I operate it alone. Production is small: 10 tournaments, 368 live matches, 29,255 notifications. So rather than argue about whether it scales, I cloned the real schema locally and pushed it to 100× production.

Everything below is measured, not estimated. Scripts in lab/, results in lab/results/.

Full EXPLAIN dumps are deliberately not committed: they inline the complete RLS policy predicates of a live system. The timings and the foreign-key trigger output are the substance, and both are here unedited.

---

Method

Postgres 17 in Docker, loaded with the real production schema — 38 tables, 114 RLS policies, 104 indexes, 72 functions — dumped via supabase db dump. A shim (lab/00-supabase-shim.sql) recreates the auth schema, auth.uid() and the anon/authenticated/service_role roles, so the real policies load and execute unmodified.

Synthetic data at three scales:

	tournaments	matches	notifications	users
scale 10 (≈ production)	10	300	2,400	200
scale 100	100	3,000	24,000	2,000
scale 1000	1,000	30,000	240,000	20,000

Every benchmark runs twice — once as table owner with RLS bypassed, once as authenticated with a real JWT claim. The difference between the two is the cost of the policy. Tracking that ratio across scale is the only way to distinguish "RLS is expensive" from "RLS scales badly", and they turn out to be very different claims.

---

Result 1: it scales. Nothing broke.

100× the data, and read latency barely moved.

Benchmark (as authenticated)	scale 10	scale 100	scale 1000
Notification feed	0.24 ms	0.32 ms	0.89 ms
Unread count	0.20 ms	0.26 ms	0.49 ms
Matches by bracket	3.27 ms	3.38 ms	4.12 ms
Full tournament page	6.67 ms	6.46 ms	7.07 ms
Bulk insert 500 matches	37.89 ms	37.09 ms	37.92 ms

240,000 notifications still serve a user's feed in under a millisecond. The composite index (user_id, created_at DESC) and the partial index (user_id) WHERE read = false do exactly what they should.

My predictions were mostly wrong. I expected unbounded notification growth to bite first, and I expected the RLS ratio to worsen with scale. Neither happened.

---

Result 2: RLS costs a fixed planning tax, not a scaling one

This is the finding I did not predict, and it is the more interesting one.

Matches-by-bracket	Planning	Execution
owner, scale 1000	0.20 ms	0.18 ms
authenticated, scale 1000	2.45 ms	1.67 ms
authenticated, scale 10	2.21 ms	1.06 ms

Planning time is flat across 100× data — 2.21 → 2.16 → 2.45 ms. It is not driven by row count at all. It is driven by policy complexity, and it is paid on every single query.

On the full tournament page it is worse: planning 4.28 ms versus execution 2.79 ms. More time deciding how to run the query than running it.

The cause is visible in the plan. Querying matches applies a policy calling is_tournament_manager_or_club_owner(get_tournament_id_for_bracket(bracket_id)), which reads brackets — which has its own policies — which read tournaments — which have their own policies — which read club_members. The planner inlines all of it. EXPLAIN output reaches SubPlan 37, four levels of policy nesting deep, for one query against one table.

That cost will not shrink as the app grows, and it will not grow either. It is a constant several-millisecond floor under every query on the policy-heavy tables.

---

Result 3: the cost is policy design, not RLS

The controlled comparison. Two tables, same database, same feature, measured the same way at scale 1000:

Table	Policies	Owner	Authenticated	Overhead
notifications	3 simple, user_id = auth.uid()	0.27 ms	0.89 ms	1.3×
matches	nested, recursive through 3 tables	0.39 ms	4.12 ms	10.6×

Same RLS, an order of magnitude apart. notifications is essentially free because its policy is one indexed equality. matches is expensive because its policy recurses through tables that have their own policies.

This is the whole argument for keeping RLS predicates shallow and indexable.

Bulk writes pay a consistent penalty too — a 500-row insert goes from 9.5 ms to 37.9 ms (~3.9×), stable across every scale. That one I did predict.

---

Result 4: the actual bug — an unindexed self-referencing foreign key

Only one operation degraded with scale: bulk delete, i.e. tournament teardown. Production runs this constantly — matches has taken 8,249 inserts and 11,385 deletes to hold 368 live rows.

EXPLAIN ANALYZE breaks out per-constraint trigger time, which is where it showed up. Same 15 rows deleted at both scales:

FK constraint trigger	scale 10	scale 1000
elo_rating_history_match_id_fkey	0.386 ms	0.647 ms
match_schedules_match_id_fkey	0.469 ms	0.450 ms
match_score_details_match_id_fkey	0.162 ms	0.217 ms
matches_next_match_id_fkey	0.605 ms	26.800 ms

Three flat, one 44× worse.

matches.next_match_id is a self-referencing foreign key — each match points to the next match in the bracket. On delete, Postgres must verify no surviving row still references the deleted one. matches has nine indexes, and not one of them covers next_match_id, so every deleted row causes a sequential scan of the whole table.

The cost is rows_deleted × table_size. Deleting an entire tournament's matches, which is what teardown does, makes both factors grow together.

The fix

CREATE INDEX idx_matches_next_match_id
    ON matches (next_match_id)
    WHERE next_match_id IS NOT NULL;

Partial, because most matches have no successor, which keeps it small.

At scale 1000	Before	After
matches_next_match_id_fkey trigger	26.800 ms	0.443 ms	60×
Bulk delete, total execution	30.141 ms	4.881 ms	6.2×

Raw trigger output, unedited: lab/results/fk-trigger-times.txt.

Status: verified in the lab, not yet applied to production. The measurements above are from the local clone at 30,000 matches. Production currently holds 368 live matches, where the cost is still sub-millisecond, so this is queued rather than urgent. The number that matters is the trend, not today's latency.

Postgres does not index the referencing side of a foreign key automatically. It indexes the referenced side, because that side needs a unique constraint. The referencing side is left to you, and nothing warns you — the cost only appears on DELETE or on UPDATE of the referenced key, and only once the table is big enough to notice.

---

What I got wrong

Prediction	Outcome
notifications unbounded growth bites first	Wrong. 240,000 rows, still 0.89 ms.
RLS overhead worsens with scale	Wrong. Ratio flat at ~10×; the cost is in planning.
Bulk writes pay a meaningful RLS penalty	Right. ~3.9×, consistent.
Realtime subscriber ceiling	Untestable locally — separate service, see below.
Connection exhaustion	Untestable locally — pooler layer.

Both real findings — the planning-time tax and the unindexed FK — were things I had not predicted. They were found by measuring, not by reasoning about the schema. Reading the schema produced four hypotheses, of which one was right; running it produced two findings I would never have guessed.

---

Scope limits

Realtime is not covered. Supabase Realtime is a separate Elixir service that cannot exist in a local container; the shim creates an empty realtime schema purely so the dump loads. In production, realtime.list_changes() — its polling loop — accounts for ~92% of tracked query time, though that says more about a small denominator than about Realtime. Full analysis in SUMMARY.md.

Connection exhaustion is not covered. That is a pooler-layer limit.

Synthetic data is uniform. Real tournaments vary in size and activity; this generator makes them identical. Skew changes plans, so real-world numbers will differ in detail.

---

Incidental finding: the schema defends itself

The generator failed six times before producing a row, each time on a different guard: a missing subscription_plans lookup, a subscription tournament limit, bracket scoring mode inheritance, gender-restricted bracket validation, bracket player capacity, and a required sport_id.

Every failure was a constraint or trigger correctly refusing incoherent data. brackets alone carries 22 CHECK constraints encoding which scoring configurations are valid. The capacity check even takes pg_advisory_xact_lock per bracket to serialise seat-takers — which is correct for preventing overbooking, and is also a per-bracket concurrency ceiling worth knowing about before a 300-player registration rush.

Hard to load synthetic data into. That is the system working.

---

Reproduce

supabase db dump -f schema.sql          # schema must be current
cd lab
.\run.ps1 -Setup -Scale 10
.\run.ps1 -Scale 100
.\run.ps1 -Scale 1000

File
lab/00-supabase-shim.sql	auth schema, roles, auth.uid()
lab/02-generate.sql	synthetic workload, scale-parameterised
lab/03-benchmark.sql	owner vs authenticated, six benchmarks
audit/	read-only RLS and Realtime audits against production