You’re running 3 workloads against the same Postgres RDS instance. max_connections = 300. They all need a connection pool. None of them want to share.
Here’s the setup:
→ NestJS REST API on ECS Fargate. Short transactions, 800 RPS, 10 tasks × 40 pool = 400 connections requested.
→ Background job workers. Long-running analytics queries. Hold a connection for 30–90 seconds each.
→ Lambda functions. Bursty, cold-start heavy, 0–200 concurrent invocations.
Three clients. One database. 300 max connections. Math doesn’t math.
You need to pool without breaking any of them. Pick your poison:
A) PgBouncer transaction mode for everyone: single pooler in front of RDS, maximum multiplexing.
B) PgBouncer session mode for the workers + transaction mode for the REST API: split pooler config by workload type. Lambda goes through one of them.
C) RDS Proxy for Lambda + PgBouncer transaction mode for ECS: hybrid. Route by client type.
D) Separate read replica for the analytics workers + PgBouncer transaction mode for REST + Lambda: isolate the workload at the DB level.
Three of these will survive the on-call rotation. One of them silently breaks a workload at 3am and you’ll spend a week figuring out why.
This isn’t the “PgBouncer obviously” question. Every option here has a senior engineer who will defend it on a whiteboard — and one of them is wrong in a way that doesn’t show up until the workers start dropping mid-query.
Pick one (A, B, C, or D) and tell me why. Full breakdown in the comments.
If your team has had the “we’re out of Postgres connections” fire drill, share this with them. The argument in the thread is the lesson.
Drop your answer 👇
#30DaysOfSystemDesign #Day13 #Postgres #DatabaseArchitecture

Smiley Face

Joud Awad

A system design challenge exploring connection pool management when three different workloads (NestJS REST API, background analytics workers, and Lambda functions) compete for 300 max connections on a single Postgres RDS instance. Four architectural options are presented: PgBouncer transaction mode for all, split session/transaction mode by workload, hybrid RDS Proxy + PgBouncer, or offloading analytics to a read replica. One option silently breaks a workload under load — readers are challenged to identify which and explain why.

13/30 Days System Design Questions!

Answer: B — PgBouncer session mode for workers + transaction mode for REST API ✅
This one separates engineers who’ve actually run PgBouncer in prod from ones who’ve only read the docs. Here’s the breakdown:
Why B wins (split pooler config by workload type):
The whole point of PgBouncer is multiplexing — one backend Postgres connection serves many client connections, but only when it’s safe to hand the connection back between transactions. That’s transaction mode.
Transaction mode is perfect for the NestJS REST API: short transactions, 800 RPS, no session state, no SET LOCAL, no advisory locks held across queries, no prepared statements that need to survive. The REST API’s 400 requested client connections collapse down to ~30–50 actual backend connections to Postgres. Math suddenly maths.
Transaction mode is catastrophic for the analytics workers. Their queries run 30–90 seconds and span multiple transactions — some hold session state, some use temp tables, some use SET search_path, some use cursors. Hand the connection back between transactions and that state is gone. Worse: at 800 RPS from the API side, the worker’s chance of getting the same backend connection back on the next transaction is basically zero. The worker silently breaks.
So you give the workers their own PgBouncer pool in session mode — one client connection = one backend connection for the whole session. They get fewer connections, but they hold them, which is what they actually need.
The Lambda traffic goes through the REST API’s transaction-mode pool (it’s stateless, short-lived — same shape as the API). One pooler instance, two pool configs, two listening ports. PgBouncer supports this natively.
End state: API gets multiplexing, workers don’t starve, total backend connections to Postgres stay under 300, nobody breaks. That’s the boring correct answer that wins on call.
<img src="https://media.daily.dev/image/upload/s--2Az5EOg6--/f_auto/v1779122964/ugc/content_4e0cd702-9080-4b9e-81a3-9b9f773d1649?_a=BAMAMiWQ0" alt="ChatGPT Image May 18, 2026, 07_42_52 PM.png">

Why C is wrong (RDS Proxy for Lambda + PgBouncer for ECS):
Defensible on paper. RDS Proxy is genuinely good for Lambda — it handles the cold-start connection storm, manages IAM auth, integrates with the VPC. If the question were just “Lambda + Postgres,” C is right.
But C doesn’t fix the workers. They’re still long-running, they still need session state, and you’ve now introduced a second pooling product to operate, monitor, and pay for. RDS Proxy is also more expensive than PgBouncer per connection-hour, and it doesn’t support session pinning across transactions the way session-mode PgBouncer does.
C solves the easiest of the three problems and leaves the hardest one untouched.

Why D is the senior-engineer trap (read replica for workers):
This is the option that fools experienced engineers, because it pattern-matches to “isolate the workload.” And isolating analytics from OLTP at the DB level is genuinely a great idea — for a different problem.
D solves read scaling and noisy neighbor on query plans / cache / IO. It does not solve the connection math. Your read replica still has its own max_connections ceiling. Your workers still hold connections for 30–90 seconds. You’ve just moved the connection exhaustion problem from the primary to the replica — and now you’re paying for an extra RDS instance.
If the problem statement was “analytics queries are starving the OLTP workload,” D would be right. The problem statement is “we don’t have enough connections.” Different pain. Different pattern.
D is what you do after you have B working and you realize analytics is also competing for IOPS and buffer cache.

…Why A is the trap (PgBouncer transaction mode for everyone):
This is the answer most engineers reach for because “transaction mode = best multiplexing.” It works beautifully for the API. It silently destroys the workers.
The moment an analytics query needs anything that doesn’t fit in one transaction — a temp table, a session variable, a SET LOCAL, a prepared statement, a cursor that outlives a transaction — PgBouncer hands the connection back to the pool. Next query? Different backend connection. State gone. Query fails or worse, returns wrong results.
The failure mode is the cruelest kind: it works in dev (low concurrency, same connection by luck), it works in staging (light load), it dies in prod when the API is hammering the pool and the workers can’t get a stable connection. You’ll find this at 3am during a financial close.