We deleted our realtime layer, then tried to break what replaced it

Our database plan allowed 200 concurrent realtime connections. Our mobile app opened one for every session, before the user touched anything.

That channel watched the user's own usage row, because the app locks itself when access expires and unlocks when the laptop reports more usage. It was open for 100% of sessions for their whole length. A second channel watched the social feed and opened the first time you visited that tab. So the app had a structural ceiling of 200 simultaneous users, and in practice fewer. The 201st got nothing, and nothing we could tune on our side would move that number.

There was also a gap. Comment threads had no live updates at all: one fetch on mount and nothing after. Oddly, the comments, reactions and posts tables had been registered in the realtime publication back in migration 0006. The backend was built expecting live comments. The client had never subscribed.

So the change had three targets, each with a different tolerance for staleness:

We replaced all of it with polling. That sounds like a step backwards and mostly is. This is about why it was the right step backwards, and what happened when we tried to prove it.

The bottleneck moves, it does not disappear

That heading is a sentence from the decision record, written before any code changed.

The old ceiling was 200 concurrent connections. It fails gracefully and locally: only the 201st simultaneous session is affected, and only while it is simultaneous.

The new ceiling is a monthly egress budget. It fails globally: past it, the limit applies to the whole project, not to one session. You do not degrade; you run out, for everyone, on whatever day the month's traffic happens to add up.

That is a worse failure shape. It was chosen anyway, because the connection cap bound immediately at a number the product needed to pass, while the egress budget bound at a number that could be measured and managed.

The loud error we were about to lose

The old code had a piece of load-bearing ugliness. Both subscriptions lived in module-level singletons (a Map keyed by user ID, a Set of listeners) with manual refcounting, so that React's mount and unmount churn could never create two subscriptions for one thing. A comment above it said why: an earlier, more natural version had thrown tried to subscribe multiple times.

Our design reviews are AI-assisted: an architect role proposes, a critic role attacks, and the team decides. The critic spotted what that meant for the replacement:

A setInterval-based version has a worse failure mode than the channel version had — Supabase threw a loud error on double-subscribe; a duplicated setInterval fails silently, just quietly doubling or tripling your poll frequency per uid if the teardown timing is off during a remount race.

A silent duplicate interval inflates the exact egress number this change existed to protect. So the rule was to keep the scaffolding and swap only what happens on each tick:

// app/src/lib/data.ts — shape preserved, internals swapped
export function onMyLimitChange(uid: string, cb: (l: Limit) => void) {
  let entry = registry.get(uid);
  if (!entry) { entry = { listeners: new Set(), … }; registry.set(uid, entry); }
  entry.listeners.add(cb);
  return () => {
    entry.listeners.delete(cb);
    if (entry.listeners.size === 0) { clearInterval(entry.timer); registry.delete(uid); }
  };
}

Both public functions kept their signatures, so the two production call sites (counted with grep, not assumed) did not change. Rollback was one file's internals. The concurrency property became a unit test: one interval per key, however many components subscribe at once.

The same failure found a second door. An async tick holds a reference to its registry entry. If the last listener unsubscribes while the query is in flight, the tick comes back and reschedules an interval on an entry nothing holds any more, so nothing will ever clear it. The thread poller checks for that after every await:

// Re-check after the await: if the last listener unsubscribed (or this
// postId was re-subscribed under a fresh entry) while this query was in
// flight, `entry` is now orphaned — bail out instead of rescheduling an
// interval on it that nothing will ever clear again.
if (letterEntries.get(postId) !== entry) return;

Ask whether anything changed, not what changed

The naive poller refetches the feed on every tick, because that is what the old realtime callback did on every event. That would mean a full feed payload every 20 seconds per open session.

Instead the feed poller asks the cheapest question available, the newest row's timestamp:

supa.from('posts').select('created_at').order('created_at', { ascending: false }).limit(1)

One indexed column, one row. If the timestamp has not moved, nothing else happens. If it has, the existing full-fetch path runs. The index already existed, so no migration was needed. Change detection and data fetching are different operations, and collapsing them is what makes polling expensive.

The three pollers run at different cadences:

• The gate runs for every session, so it is the volume driver. The record said to piggyback it on the existing 30-second countdown tick. The implementation gave it its own interval with independent jitter instead, because a shared tick means every session in the world polls in lockstep, and jittering the countdown would visibly change the timer. The post-merge conformance check scored the work 14 of 15 claims implemented and flagged this one as "diverged, reason stated", so a human had to sign it off.
• The feed polls its timestamp every 20–30 seconds, jittered, while the tab is focused.
• Comment threads poll every 15 seconds while a thread is open, and back off after four quiet ticks:

const FAST_CADENCE_MS = 15_000;
const SLOW_NO_CHANGE_THRESHOLD = 4;
const slowCadenceMs = () => 30_000 + Math.random() * 15_000;

Every cadence is jittered. Without it, clients that launched around the same event poll in step and arrive as a spike. Background sessions stop polling entirely. Resuming deliberately does not reset the baseline timestamp, so a post made while you were away is still detected on the next tick.

All three read through PostgREST directly, not through an Edge Function. That keeps reads off the separate monthly function-invocation quota and avoids paying function latency for a read that row-level security already protects.

Then we tried to break it

The design review made the load test a gate, not a follow-up: run it against a real staging project, with our own migrations applied, before production traffic.

The worry was concrete. If a post goes viral and 500 people hold its thread open, each polling every 15 seconds, that is about 30 requests per second against one hot row's index on shared hardware. That is the same many-users-one-resource shape the realtime design failed at. Had we moved the failure from the connection layer to the query layer?

Test 1, the isolated worst case. One comment thread, fixed 15-second cadence, backoff disabled. 100 virtual users for 12 minutes, then 500 for 12 more.

26,311 requests over 25 minutes 18 seconds, 17 MB transferred in total.

Test 2, all three pollers at once. 500 gate pollers, 150 feed heartbeats and 50 comment threads for 12 minutes: 17,843 requests, zero failures, p95 143.41 ms, and 15 MB for the run.

That last figure is the one the exercise existed to produce. It works out to about 1.71 KB of egress per open session per minute, dominated by the gate poll as predicted. Scaled to a seven-minute average session, that is roughly 17 KB of polling per session. The scoping document had estimated 15–20 KB before any of this code existed. The estimate landing inside its own range is reassuring about the estimate, which is what the capacity model is built on. It says less about the system.

The test that failed

Tests 1 and 2 fix a population and watch it. Neither looks for a breaking point. Test 3 did: 50 distinct seeded accounts instead of one shared token, randomised sessions of 90–420 seconds with writes as well as reads, and a ramp from 2,000 to 10,000 virtual users over about 19 minutes.

A 31% failure rate and a 45-second p95. On the face of it, we had found the wall.

We had been polling database-side telemetry every 15 seconds during the run, and it said something else:

• total connections flat at about 23 for all 21 minutes, against a pooler cap of 200
• waiting connections at 0 almost throughout, with one blip of 2
• cache hit ratio 99.98%

The shape of the failure agreed. Median latency held at 112 ms for the whole run while p95 and max went to 45.85 s and 60 s. A backend under pressure slows the middle of the distribution. This was a healthy median with a catastrophic tail. Simulated sessions ran to 9 minutes 24 seconds against a scripted maximum of 420 seconds. A slow server cannot make a session overrun its own script, but an unscheduled process can. The first interrupted iterations appeared during the ramp-down, not at peak. Write failures (60%) tracked every other endpoint (61–81%).

That is one 8-core machine failing to simulate 10,000 sessions: CPU scheduling, socket exhaustion, TLS handshakes. The record's conclusion:

this run did not find Supabase's real breaking point — it found the local load generator's ceiling first, somewhere around 8,000-9,000 simulated concurrent sessions.

We stopped there. Finding the real server-side limit would need distributed load generation, and the record judged the tested range already "far beyond this app's realistic near-term traffic". Building that infrastructure would have bought a bigger number and no decision.

We did not report the thresholds as passed. The run failed its thresholds, and the failure was in the instrument. Both sentences are in the record, and the instrumentation that told them apart was running before the surprise, not added after it.

The bug the load test found, which had nothing to do with load

Under the ramp, some writes failed in a way that did not look like congestion.

Three interactions (reacting to a post, saving one, following someone) used .upsert(). All three tables were given select, insert and delete policies and deliberately no UPDATE policy, because nothing should ever update a reaction. You have reacted or you have not.

supabase-js compiles .upsert() to ON CONFLICT DO UPDATE. The moment a user repeats the same action on the same row, the update arm hits the missing policy and returns a 403 (Postgres 42501). A double-tap, stale client state or a retry all trigger it, in production, with no load required. The load test simply did the same thing thousands of times, which is the one thing manual testing never does.

The fix is one option:

await supa.from('reactions').upsert(row, { ignoreDuplicates: true });  // ON CONFLICT DO NOTHING

The fix we did not apply was adding an UPDATE policy, which would have silenced the error by opening a write surface that was closed on purpose. When an error is telling you a boundary exists, the cheap fix usually removes the boundary.

What it cost

• Five test files rewritten from channel mocks to interval mocks. Known and scoped in advance, and the largest single piece of the work.
• New load that did not exist before. Nothing polled comment threads until now. Backoff mitigates it and the load test bounds it, but it is net-new query volume.
• A limit that fails globally. A hard, project-wide ceiling has to be watched rather than discovered. The measured 1.71 KB per session-minute is what turns a monthly egress budget into a number of session-hours we can plan against.
• Three near-identical polling registries. Feed, gate and threads each have their own. The backlog records a trigger: extract a shared helper when a fourth polling target appears.
• An analysis with an expiry date. The gate's RLS policy contains a subquery over the follows graph. For a user polling their own row it short-circuits on the primary key and costs nothing. A future "who else is cooked right now" feature would use the other half of that policy, and none of this analysis covers it. That is recorded as a flagged risk.

What we would do differently

• Budget for distributed load generation before the ramp test, or don't write the ramp test. A breaking-point test on one machine can only find that machine's breaking point. We only knew which machine had broken because the database telemetry was already running.
• Grep every .upsert() against the table's policies. The reaction bug was three call sites and one missing option. It was findable by reading, and it waited for a load test instead.

Know which thing broke

A failed threshold is a hypothesis about the system, not a result. A 31% failure rate at 10,000 users was the headline. A flat connection count, a 112 ms median and sessions overrunning their own scripts were the story.

And when you swap a mechanism, check what its errors used to tell you. The most valuable property of the code we removed was that misusing it threw.