How PingRoom Got 4× Faster in One Day

Exactly two seconds is not just slow. It is a clue.

A production quick-action trigger was repeatedly finishing in 1.9–2.1 seconds, even when it sent one Ping to one recipient. The consistency pointed away from random contention and toward a fixed cost paid on every request.

On August 26, the same trigger fell from 2,235ms to 536ms. The result came from cutting work, reusing a change-detection contract that already existed, and finally moving the application server closer to its database.

These are observed request timings from the August 26 production investigation, not APNs or FCM end-to-end delivery measurements. They are point measurements, not a published percentile benchmark suite.

Sixty-Five Small Trips

Per-request database logging made the shape visible:

65 queries, 1,997.81ms in the database, 2,235.4ms total.

Almost none of the individual statements was slow. Primary-key lookups, inserts, and cache-table access each took roughly 25–30ms.

Laravel was running in Amsterdam. CockroachDB was in Frankfurt. Each sequential SQL statement paid for the network trip and driver overhead before the next statement could begin. At roughly 28ms per statement, 65 ordinary queries explain most of a two-second request.

The numbers reduced to:

request time ≈ query count × per-query cost

Both query count and per-query cost had to fall.

Let The Response Leave First

The trigger's 65 queries included mandatory checks and writes: membership, cooldown and idempotency, the Ping row, unread state, and durable outbox records that prevent a delivery from disappearing.

They also included work the sender did not need to wait for. After the transaction committed, the request claimed its own outbox rows, resolved managed-agent bindings, handed work to Redis, and marked the drain complete before returning the HTTP response.

Under the Octane worker, that drain can run after the response is flushed. The cron sweepers already recover anything a dying worker leaves behind, so moving the fast path past the response does not remove the durable fallback. The callback still runs inline in non-Octane commands and tests.

Attribution is now fetched once instead of three times, and the room's public/private state is reused instead of queried again per notification.

The result was 65 → 46 queries and 2,235ms → 1,656ms. The response reports delivery as pending while the handoff continues. These numbers measure request latency, not push-delivery time.

Stop Asking For The Same Feed Twice

The mobile app refetched the complete rooms feed along two paths that already had fresh data.

A push tap always fetched the feed even when the foreground push handler had already mirrored that exact Ping into local state. The personal-room management screen also fired two identical feed requests on focus after its refresh listener had moved elsewhere.

Both paths now respect freshness. A push tap refetches only when no recent server sync exists within 90 seconds; the management screen uses a 60-second focus window while keeping the cheaper single-room refresh authoritative.

Those client changes ride the next store build. They were not shipped as an over-the-air update on August 26.

The Clever SQL That Did Not Ship

The two heaviest feed queries contain the privacy predicate that decides which Pings a viewer may see. Several rewrites looked promising: grouped counts, per-room LATERAL top-N, null shortcuts, and an uncorrelated subquery.

Each version was checked against production data with row-parity tests. The original correlated predicate took about 100ms of execution. The alternatives were equal or slower.

The query shape was not hiding the missing second. Even the cheapest database statement still took roughly 28ms from Amsterdam. Rewriting privacy-sensitive SQL without a measured win would have added risk and no speed, so none of those variants shipped.

Use The Change Stamp To Reuse The Body

The rooms feed already maintained a per-user version stamp in Redis. Every Ping, read-state change, membership change, room edit, scan, merge, or deletion bumps it. The original purpose was to answer an If-None-Match request with 304 Not Modified after one Redis lookup.

Shipped clients were not sending that header, so the path had never fired in production.

The server now uses the same stamp to key a rendered rooms-feed body by viewer, agent-route flavor, page size, and version. If the stamp is unchanged, the server can reuse the exact JSON after a Redis MGET and one database-cache read, skipping the 22-query rebuild.

When a mutation bumps the stamp, the key changes and the old body becomes unreachable. Narrowly room-scoped agents bypass the cache. Bodies expire after ten minutes, matching the existing version contract.

A full rebuild pays an extra 30–60ms to store roughly 550KB. One hit per twelve misses covers that cost; a 90-second background poll can clear that bar.

Move The App, Not The Database

After the query cuts, every remaining statement still had the same geographic floor.

A multi-region CockroachDB setup would have added replicas and cost while writes still waited for cross-region agreement. Moving the application from Amsterdam to Frankfurt was a redeploy, not a data migration.

The replacement server kept the same 2 vCPU and 8GB of memory, moved to a shared Premium AMD plan, and increased disk capacity fourfold. Its monthly price fell from $64 to $42, while database statements fell from about 28ms each to 4–10ms.

The final production readings were:

Remove Distance Before Adding Hardware

The day did not end with a larger server, a rewritten privacy layer, or a more elaborate database topology.

It ended with fewer request-visible queries, fewer duplicate client requests, a cached answer guarded by an existing correctness stamp, and two services in the same region.

The fastest query is the one that does not run. The next fastest is the one that does not have to travel.