The client had already decided what was wrong before they called me. Their product was slow, dashboards were timing out for larger customers, the database would occasionally reject connections, and the user base had grown steadily all year. The conclusion felt obvious: they'd outgrown Postgres, and they needed more of everything.
Their instinct was the cloud-native one — scale out. Autoscale more application pods, add read replicas, maybe start sharding so the load spread across more machines. More traffic, more boxes. They wanted to know if I could build it.
It was a real engagement with a real invoice attached, and all I had to do to win it was agree. I asked if I could look at the system for a day first.
A capacity problem and a bad query can look very similar
From the outside, a genuine capacity problem and a pathological query produce almost the same symptoms: the app slows down, database CPU rises, timeouts start appearing, and everything gets worse as the data and traffic grow. The difference is that the fixes have completely different price tags. If you've genuinely exhausted capacity, scaling out is the right move; if one query is doing far too much work, more compute is just an expensive way to postpone the real problem — you pay every month to run the same inefficient workload on larger machines.
So before touching the architecture, I wanted to answer two simpler questions. How expensive is one unit of work? And how much concurrency are we pushing into the database? Only after those two would I start talking about capacity.
There turned out to be two problems hiding behind the same symptoms, and neither one required the project they'd originally planned.
Problem one: one query was doing almost all the work
I started with RDS Performance Insights and looked at database load by SQL statement. The load wasn't spread evenly across the workload — one query dominated it. pg_stat_statements told the same story from inside Postgres: enormous cumulative execution time, called on essentially every dashboard load. So I pulled the query and ran EXPLAIN (ANALYZE, BUFFERS), and the problem was immediately obvious.
The dashboard was filtering a large events table by customer and time range, then ordering the results by time. Conceptually, the query was doing something like:
SELECT ...
FROM events
WHERE customer_id = ?
AND created_at BETWEEN ? AND ?
ORDER BY created_at;There were tens of millions of rows in the table, but the only meaningful index was the primary key. So Postgres did what it had to do: scan a huge portion of the table, throw away almost everything, and sort what remained — on every dashboard request.
The important part was that the query hadn't suddenly become bad. It had always been bad. A sequential scan over a couple of million rows had been fast enough that nobody cared, and the same scan over tens of millions became an eight-to-twelve-second dashboard request. Nothing changed in the code; the data quietly crossed the point where brute force stopped being survivable.
The fix was an index that matched the access pattern:
CREATE INDEX ON events (customer_id, created_at);Now Postgres could jump directly into the customer's slice of the index and walk the relevant time range instead of scanning and sorting the whole table. The sequential scan became an index-backed plan touching a tiny fraction of the buffers, and the dashboard went from eight-to-twelve seconds to under a second.
That fixed the visible slowness. The connection errors were a completely separate problem.
Problem two: autoscaling was creating a connection storm
Every so often, under load, Postgres would stop accepting new connections. To the client, this looked like the strongest evidence yet that the database itself was too small.
It wasn't.
Every application pod had its own connection pool, which meant the real database connection budget looked roughly like this:
number of pods × pool sizeTen pods with a pool of twenty connections can ask for two hundred database connections. Autoscale to forty pods during a traffic spike and that becomes eight hundred. Postgres doesn't raise its connection ceiling just because Kubernetes scaled the application tier — each database connection has real server-side overhead, and max_connections is finite. So at exactly the moment the application tried to protect itself by adding pods, it multiplied the number of clients competing for the same database. The autoscaling wasn't relieving the pressure; it was creating another kind of it.
That was the deeper lesson for me: elastic application capacity should not automatically create elastic pressure on a dependency that has a fixed concurrency budget.
We put PgBouncer in front of Postgres and used transaction pooling, since this application didn't rely on session-level database state. Now the shape became many application connections → PgBouncer → bounded set of Postgres connections, so forty pods could come and go without turning the database connection count into forty independent pools. We sized the backend pool deliberately and kept database concurrency bounded independently of how many application replicas existed. The connection count flattened out and the rejections stopped.
The database hadn't needed more capacity. It needed fewer simultaneous clients.
Why more infrastructure wouldn't have fixed it
The client's original plan had three parts, and none of them addressed both problems.
More application pods wouldn't make the slow query cheaper — every new pod would still wait on the same expensive database work — and because each pod opened another pool, more pods actually made the connection problem worse. They wanted more of the exact thing knocking the database over.
Read replicas could spread some read traffic and connection load, but they wouldn't fix the underlying query or the pool design. An inefficient query is still inefficient on a replica, and oversized per-pod pools are still oversized per-pod pools.
Sharding might have made the query appear faster, because each shard would contain fewer rows — which is exactly why it would have been such a dangerous "success." We'd have taken one missing index and turned it into permanent operational complexity: routing, rebalancing, cross-shard queries, migrations, failure handling, all to avoid fixing an access pattern that Postgres already knew how to serve efficiently.
The client had jumped straight to capacity before checking two simpler things: is each request efficient, and is concurrency bounded? Neither was.
Two days, not two months
Between the index and the connection pooler, the whole thing took a couple of days, most of it verification. We re-ran query plans against realistic data volumes, load-tested the application while forcing the pod count up, and watched the database connection count stay flat behind the pooler. The database CPU that had looked like a capacity emergency dropped to something unremarkable, and the connection rejections disappeared.
The dangerous part is that scaling out probably would have looked successful at first. A larger database or more read capacity might have bought the slow query some time, and the dashboards could have improved enough for everyone to declare victory while the underlying query stayed inefficient and the connection model stayed fragile. That's how expensive architecture mistakes survive — they often work just well enough to hide the problem that caused them.
The project I didn't build
Then I had to tell the client they didn't need most of what they'd asked me to build.
I'll be honest about the pull there, because it's the point of the story. The larger architecture would have been a much bigger engagement — more pods, replicas, sharding, migration work, plenty to design and plenty to bill for. Saying "it's a two-day fix, an index and a connection pooler" meant turning a multi-month invoice into a two-day one and talking myself out of most of the money on the table. There's a real temptation, easily dressed up in solid technical justification, to build the impressive thing the client is already sold on.
But by that point the evidence was sitting in front of us. The query plan showed what the index had changed, and the connection graph showed what the pooler had changed. Building the original plan anyway would have meant solving a problem we'd already proved they didn't have — and shipping something I knew was the wrong answer is the fastest way to become a contractor a client doesn't trust the next time something's slow. So I showed them the before-and-after plans, explained why the connection count had been climbing with the pod count, and told them to keep the simpler architecture until the metrics gave them a reason not to.
They came back later for other work, and sent a couple of other founders my way. Turning down the wrong project was the best business decision I made that quarter, because trust compounds faster than any single invoice.
One good Postgres instance can go a long way
I've seen a lot of teams reach for more database capacity much earlier than they need to. A properly indexed Postgres instance, with sane queries and controlled connection concurrency, can handle far more than people tend to assume. So when a database starts looking "too small," I now look for the cheaper explanations first: a missing or poorly chosen index, an ORM query doing far more work than expected, N+1 queries, bloated tables or vacuum problems, or too many database connections from an elastic application tier. Every one of those produces the same outward symptom — latency climbing as the system grows — without the database actually being out of capacity.
The cloud makes the mistake easier because adding infrastructure is frictionless. Another pod, a bigger instance, another replica — all of it is easier than opening a query plan and working out why one request is expensive. That's genuinely useful when capacity is the problem. It's expensive when it isn't.
If you take one thing from this
Before scaling capacity, check two things first: how much work does one request make the system do, and how much concurrency are you allowing against the constrained resource? In this case the first problem was a query doing orders of magnitude more work than necessary, and the second was application autoscaling multiplying database connections against a fixed ceiling. Neither required sharding, neither required a migration, and one of them was actively made worse by adding more replicas.
EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, Performance Insights, and a look at your connection count against max_connections are cheap diagnostics. A new architecture is not.
The client hadn't outgrown Postgres. They needed one index, a bounded connection pool, and a day of looking at the system before deciding what to build.