What 23,000 hours on a real-time dispatch system taught us about Postgres

"Postgres scales fine" is one of those things engineers say to each other to avoid harder conversations. It's not wrong, but it's not the whole answer. After more than 23,000 person-hours running a real-time emergency dispatch platform on top of it — events arriving by the second, sub-200ms latency budgets, distributed responders in the field — here's what we've actually learned.

1. Your connection pool is the database

Long before you hit a CPU ceiling on Postgres itself, you hit a connection ceiling. Postgres backends are processes, not threads, and they're expensive. The default 100 connections looks generous until you have a couple of dozen app servers, each with its own pool, opening idle connections you forgot about.

The interesting question isn't "how many connections do you have?" It's "what pool mode are you in?" PgBouncer transaction mode looks like a free win — until your app uses session-level features like prepared statements, advisory locks, SET LOCAL, or temp tables, and the symptoms surface as inexplicable, intermittent errors weeks later. Decide on pool mode the day you choose your pooler, not the day production breaks.

Practical rule we've settled on: every service declares an explicit maximum connection budget, and the sum of budgets is less than max_connections with margin for migrations and admin sessions. Treat connections like a shared finite resource, because they are.

2. JSONB is a tool, not a schema

JSONB is brilliant for legitimately polymorphic data: webhook payloads, third-party API responses, audit logs you want to keep without committing to a shape. The trap is using it as a way to "move fast" on data that's actually relational. Six months later you've built half a schema inside a JSONB column, your indexes are GIN, every read is a full-row deserialization, and any new analytics query requires either a migration anyway or a heroic jsonb_path_query.

Our rule: if the same key appears in 80%+ of rows and is queried by name, it's a column. JSONB is for the long tail. The discipline matters most early, when "we'll fix it later" is the cheapest lie a team tells itself.

3. Replica lag is a feature of your application, not just your infra

Read replicas look like a free scaling lever until someone hits read-your-write. A user updates a dispatch record, the next page reads from the replica, the change isn't there yet, and now you're explaining to operations why their UI is "broken."

The fix isn't usually "make replication faster." It's deciding, per code path, whether you need strong consistency. A dashboard polling for nearby incidents is fine with 200ms staleness. A dispatcher confirming an assignment is not. We tag each repository method with its read intent — primary or replica — and the wrong default is a code-review issue, not a runtime bug.

4. Indexes earn their keep, and bloat costs you in production

Every index you add costs writes. In a write-heavy event stream, that math gets painful fast. Two patterns that paid for themselves repeatedly:

• Partial indexes on the hot subset. If 95% of your queries hit WHERE status = 'active', the index doesn't need to include the dead rows. A partial index is smaller, faster to write, and faster to scan.
• BRIN over BTREE on append-only, time-correlated tables (events, audit logs). Orders of magnitude smaller, and the access pattern lines up naturally.

And vacuum. Watch vacuum. Long-running transactions and slow replicas extend the horizon, dead tuples pile up, and one day a routine query starts seq-scanning a table you thought was indexed. Set up monitoring for n_dead_tup, last_autovacuum, and the age of relfrozenxid on every table that matters. This is unsexy and saves you at 2am.

5. Scale Postgres by not scaling it

The pattern that's worked best for us isn't sharding or replicas or partitioning — those came later, when we needed them. It's keeping the database boring. Move expensive aggregation out into materialized views refreshed on a schedule. Push fan-out work to a job queue. Cache aggressively at the read edge. Treat the primary as the system of record and only the system of record. A boring primary scales a long way before you need anything fancy.

The actual takeaway

None of this is exotic. It's all in the Postgres docs, and most senior engineers have heard versions of it. What changes when you spend 23,000 hours operating a single system is which advice you actually believe. The lessons that stick are the ones you've personally paid for at 3am. The lessons that don't stick are the ones you'll pay for again.