Databases
Migrating to transaction mode: rollout playbook and PgBouncer 1.21 prepared statements
Crux A five-step transaction-mode rollout — audit, size, tune GUCs, canary, observe — plus the 2024 PgBouncer 1.21 breakthrough that unblocked prepared statements in transaction mode.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at middle altitude — in the skyA team has PgBouncer in session mode. They want transaction mode — 10× multiplexing — but are afraid of breakage. The audit-and-canary rollout takes one week, not a year; the prepared-statement concern was real before March 2024 and is solved now.
The five-step rollout
Trace it
1/5 Deploy PgBouncer in transaction mode for the first time. Walk the rollout.
1
Step 1 of 5
Step 1: audit the application for session-state usage.
Search the codebase for: (a) SET (not SET LOCAL) — any bare SET outside a transaction block. (b) LISTEN / NOTIFY — any channel subscriptions. (c) Classic SQL PREPARE name AS ... — any text-level prepared statements. (d) pg_advisory_lock() — session-level advisory locks. For each: map to its transaction-mode-safe equivalent: SET → ALTER ROLE or SET LOCAL inside transactions; LISTEN → dedicated session-mode connection; PREPARE → driver protocol-level prepared statements; advisory locks → pg_advisory_xact_lock. Document findings in an ADR.
2
Locked
Step 2: size the backend pool.
PgBouncer pool_size = (cores × 2) + spindles + ~50% headroom. On a 16-core all-RAM Postgres: pool_size = 32 + 50% = 48, well below max_connections. PgBouncer max_client_conn = 1000–10000 (client connections are cheap). Application client pool per worker: concurrent_requests × db_calls × avg_ms / budget_ms. PgBouncer reserve_pool_size = ~25% of pool_size for burst capacity.
3
Locked
Step 3: tune the GUCs and timeouts.
Postgres: idle_in_transaction_session_timeout = 60s (kills runaway transactions). PgBouncer: query_wait_timeout = 30s (reject clients waiting longer than this), server_idle_timeout = 600s (recycle idle backends), server_lifetime = 3600s (recycle long-lived backends), server_reset_query = DISCARD ALL (clear state between leases), max_prepared_statements = 1000 (PgBouncer 1.21+), ignore_startup_parameters = extra_float_digits.
4
Locked
Step 4: canary rollout.
Point one worker at PgBouncer in transaction mode; leave the rest pointing direct to Postgres or to a session-mode PgBouncer. Monitor for 24 hours: connection counts in pg_stat_activity, PgBouncer SHOW POOLS counters (cl_active, cl_waiting, sv_active, sv_idle), query P50/P99 latency from the canary vs the others, application errors. After 24 h with no regressions, ramp 10%, 50%, 100%. Have the rollback ready: feature-flag flip back to direct Postgres takes seconds.
5
Locked
Step 5: set up ongoing monitoring.
Alerts: (1) PgBouncer cl_waiting > 0 sustained 60 s → page (pool exhaustion imminent). (2) pg_stat_activity count of state='idle in transaction' WHERE now()-xact_start > 30s → page (stuck transaction). (3) Postgres max_connections used > 80% → warn; > 90% → page. Dashboards: PgBouncer pool stats per database; Postgres backend states (active/idle/idle in tx); query latency histograms by DB role (primary vs replica).
✓ Traced.
PgBouncer 1.21 and the prepared-statement breakthrough
Until March 2024, the operational trade-off was: transaction mode (10–100× multiplexing) OR prepared statements (10–30% throughput on repeated queries) — not both.
Why they clashed: prepared statements over the Postgres wire protocol bind a named statement to a specific backend session (PrepareStatement/PQprepare → slot on that backend). PgBouncer transaction mode routes the next EXECUTE to whatever backend is free — if it is a different backend, that statement is unknown there. Drivers returned prepared statement does not exist errors at random. The workaround: disable prepared statements at the driver — losing the performance benefit.
What PgBouncer 1.21 (March 2024) fixed: protocol-level prepared statement tracking. PgBouncer intercepts Parse/Bind/Execute wire messages, maintains a global registry per logical client, and re-prepares on whichever backend is assigned. The application sees consistent prepared-statement behaviour across pool checkouts.
Requires max_prepared_statements > 0 (typical: 1000) in pgbouncer.ini.
PostgreSQL 17 (September 2024) added protocol-level DEALLOCATE, allowing PgBouncer to cleanly close prepared statements on a backend when a client disconnects — closing the resource-leak gap.
In 2026, the production default is: PgBouncer 1.21+ + max_prepared_statements = 1000 + driver-managed prepared statements + transaction mode. All three wins simultaneously. Teams still on PgBouncer 1.20 or earlier should upgrade in the next maintenance window.
| Era | Transaction mode | Prepared statements |
|---|---|---|
| Before PgBouncer 1.21 (pre-2024) | Works | Must disable at driver — lose 10–30% throughput |
| PgBouncer 1.21+ (2024+) | Works | Works — PgBouncer re-prepares transparently |
| PgBouncer 1.21+ + Postgres 17 | Works | Works + clean deallocation via protocol |
Verifying your stack
A team adopting transaction mode + prepared statements in 2026 should verify:
- PgBouncer version: 1.21.0 or later. Check with
pgbouncer -VorSHOW VERSIONon the admin console. - max_prepared_statements: set to a non-zero value in pgbouncer.ini (typical: 1000). Verify with
SHOW CONFIG. - Postgres version: 14+ for stable behaviour; 17+ for protocol-level DEALLOCATE.
- Driver behaviour: confirm it uses protocol-level prepared statements (JDBC
prepareThreshold > 0, node-postgresname:argument, pgx Prepare, asyncpg — all default to prepared). - Load test: hammer with prepared queries for 30 minutes; check PgBouncer logs for “prepared statement does not exist” errors.
Why this works
Why did this limitation exist for so long? PgBouncer is a lightweight C proxy that deliberately avoids parsing SQL. Protocol-level prepared statements required PgBouncer to intercept and understand specific wire protocol messages (Parse, Bind, Execute, Close) — a meaningful change to a codebase designed to be minimal. The feature was requested for years before the 1.21 implementation landed.
- Audit: SET without LOCAL
- → ALTER ROLE
- Audit: LISTEN/NOTIFY
- → dedicated session conn
- Audit: SQL PREPARE
- → protocol-level (driver)
- Audit: pg_advisory_lock
- → pg_advisory_xact_lock
- idle_in_transaction_session_timeout
- 60 s
- max_prepared_statements
- 1000 (PgBouncer 1.21+)
- Canary duration before ramp
- 24 h
Quiz
Completed
Before PgBouncer 1.21, why did driver-managed prepared statements cause errors in transaction mode?
Heads-up Postgres fully supports prepared statements; the issue was the pooler routing them to different backends.
Heads-up The issue is wire-protocol routing in the pooler, not how drivers encode their messages.
Heads-up PgBouncer 1.21 added statement tracking within the existing protocol, not a new protocol version.
Quiz
Completed
Which PgBouncer config option enables protocol-level prepared statement support in transaction mode?
Heads-up There is no such mode; the option is max_prepared_statements within transaction mode.
Heads-up server_reset_query is a SQL command run between client leases for state cleanup, not a prepared-statement permission.
Heads-up ignore_startup_parameters tells PgBouncer to ignore unknown client startup params, not related to prepared statement support.
- 01What is the correct order of a transaction-mode migration rollout and why does the canary step matter?
- 02What did PgBouncer 1.21 add and why does it matter for production performance?
- 03What does server_reset_query = DISCARD ALL do and when is it necessary?
Recap
Migrating from session to transaction mode requires a codebase audit first: find and replace SET with ALTER ROLE, isolate LISTEN on a dedicated session connection, switch SQL-level PREPARE to driver protocol-level prepared statements, replace pg_advisory_lock with pg_advisory_xact_lock. Then size the pool (cores × 2) + spindles, tune safety GUCs (idle_in_transaction_session_timeout = 60s, query_wait_timeout = 30s), and canary-rollout for 24 hours before full ramp. The pre-2024 concern about prepared statements is resolved: PgBouncer 1.21+ with max_prepared_statements = 1000 tracks prepared statements per logical client and re-prepares transparently on whichever backend is assigned — transaction mode and full prepared-statement performance are now compatible by default.
Connected lessons
deepens into
- The Postgres process model and why raising max_connections degrades throughputsenior
- Pooler landscape 2026, serverless connection storms, and the full failure-mode taxonomysenior
- Connection pooling: tame an exhausted poolsenior
- Connection pooling: multiple-choice reviewsenior
- Connection pooling: free-recall reviewsenior
appears again in258
- Why GraphQL gets N+1junior
- DataLoader mechanics: tick-boundary batchingmiddle
- Batch function contracts: ordering, shapes, errorsmiddle
- Federation and lookahead: batching beyond DataLoadermiddle
- Query complexity defences: depth, cost, persisted queriesmiddle
- Senior GraphQL API: scheduling contract, tenant isolation, observabilitysenior
- The journey of a request: seven stops from socket to responsejunior
- Accept and parse: from kernel queue to a typed requestmiddle
- Routing and middleware: choosing what runs, and in what ordermiddle
- Handler and response: from business logic to bytes on the wiremiddle
- Streaming and backpressure: when the client reads slower than you writesenior
- Timeouts and tail latency: budgets, deadlines, and the fan-out trapsenior
- Middleware and DI: the two patterns that shape every backendjunior
- Writing middleware: signatures, next(), and the three framework modelsmiddle
- Inversion of control: how dependencies reach a classmiddle
- DI scopes and lifecycles: singleton, request, transientmiddle
- DI as a testing seam: fakes, mocks, and the boundary that matterssenior
- DI containers in production: resolution graphs, circular deps, and when not tosenior
- Blocking vs non-blocking I/O: two ways to waitjunior
- The event loop: one thread, ordered phasesmiddle
- What blocks the loop: CPU work and sync callsmiddle
- Offloading CPU work: worker threads and the libuv poolmiddle
- Backpressure and bounded concurrencysenior
- Throughput under load: tail latency and saturationsenior
- Why pool: the cost of creating a connectionjunior
- Pool sizing: why bigger is not fastermiddle
- Acquisition and timeouts: the wait queue is the real latency dialmiddle
- Why idempotency: making retries safejunior
- Server-side state machine: four states of an idempotency keymiddle
- Retry strategies: backoff, jitter, and thundering herdmiddle
- Outbox and inbox: effectively-once across the dual-write boundarymiddle
- Concurrency and cache architecture for idempotency at scalesenior
- Observability, production failures, and global-scale designsenior
- The event loop: one thread, three queuesjunior
- Tasks, microtasks, and scheduler.yield()middle
- Timer accuracy, throttling, and idle workmiddle
- Microtask starvation, Long Tasks, and LoAFsenior
- Node.js event loop: phases, nextTick, and loop lagsenior
- React, Vue, and INP observability in productionsenior
- The render pipeline: six stages from bytes to pixelsjunior
- Stage costs and the renderer process modelmiddle
- Invalidation, dirty bits, and containmiddle
- Compositor layers: promotion, overlap, and GPU memorymiddle
- DevTools flame strip and the frame lifecyclemiddle
- Layout thrash: forced synchronous layoutsenior
- BeginMainFrame, compositor-driven animations, and GPU memorysenior
- Production observability: LoAF, INP, and the full attack surfacesenior
- What V8 is and why performance varies 100×junior
- V8''''s four-tier JIT pipeline and profile-guided tieringmiddle
- Hidden classes, transition trees, and memory layoutmiddle
- Inline caches, IC states, and deoptimizationmiddle
- Orinoco GC: parallel scavenger, concurrent marking, and write barriersmiddle
- TurboFan''''s speculative engine and the deopt-loop trapsenior
- V8 in production: isolates, pointer compression, and real failuressenior
- Service worker lifecycle and cache strategiesmiddle
- Service worker edge cases: version skew, durability, and navigation trapssenior
- What the reconciler does: render vs commitjunior
- The fiber object and the double-buffer treemiddle
- Render phase purity and commit phase sub-stepsmiddle
- Reconciliation: diffing heuristics and the key trapmiddle
- Priority lanes, time-slicing, and useTransitionmiddle
- Bailout, memoisation, and tearingsenior
- React Profiler, the Compiler, and production observabilitysenior
- Rendering strategies: SSG, SSR, ISR, streaming, and hydrationjunior
- SSG, SSR, ISR, streaming, and RSC — how each worksmiddle
- Hydration cost: selective, progressive, islands, resumabilitymiddle
- Hydration mismatch: causes, detection, and the determinism rulesenior
- RSC, per-route strategy, and production observabilitysenior
- Core Web Vitals: what LCP, INP, and CLS measurejunior
- LCP: four phases, one dominant costmiddle
- INP: input delay, processing, presentationmiddle
- CLS: why layout shifts happen and how to stop themmiddle
- Lab vs field: why the two disagree and how to use eachmiddle
- Metric tradeoffs, RUM attribution, and the CI+field loopsenior
- The full picture: URL to LCP to INP as a relay racejunior
- Eight layers traced: from the service worker to the second navigationmiddle
- Five canonical breaks: where production reliably diessenior
- The three-track method: reading traces and building a monitored systemsenior
- What is a cache stampede and why it makes things worsejunior
- Lock and single-flight: bounding concurrent rebuildsmiddle
- XFetch: coordination-free probabilistic early expirationmiddle
- Stale-while-revalidate and CDN request coalescingmiddle
- Detecting stampedes and designing TTL for productionmiddle
- Metastable failure, fencing tokens, and production postmortemssenior
- Raft roles, terms, and why majority quorums prevent split brainjunior
- How Raft replicates a log entry and decides it is safe to commitmiddle
- Raft leader election: timeouts, voting rules, and the four safety propertiesmiddle
- Raft in the real world: partitions, slow disks, and client routingmiddle
- Raft extensions: pre-vote, learners, snapshots, and linearizable readssenior
- Raft in production: membership changes, Multi-Raft, and observabilitysenior
- Where data fetching happens — and why it decides LCPjunior
- Fetch waterfalls — diagnosis and the Promise.all curemiddle
- React Server Components and Suspense streamingmiddle
- Client-side cache: TanStack Query, SWR, and stale-while-revalidatemiddle
- LCP, prefetch, and race conditions in interactive fetchingmiddle
- Senior internals: RSC payload, caching layers, and production failure modessenior
- Bits on the wirejunior
- Latency mathmiddle
- Bufferbloat and congestionsenior
- The physical frontiersenior
- The three-way handshakejunior
- Sequence numbers and connection statemiddle
- Flow control and congestion controlmiddle
- BBR, production observability, and beyond TCPsenior
- DNS: what it does and why it existsjunior
- The resolver walk: referrals, record types, and gluemiddle
- TTL, caching, and DNS propagationmiddle
- The 1-RTT handshake: key shares and ECDHEmiddle
- Session resumption and 0-RTTmiddle
- CDN: putting content next doorjunior
- Anycast and GeoDNS: routing to the nearest edgemiddle
- Tiered cache and Cache-Controlmiddle
- Vary header and cache keysmiddle
- Stale-while-revalidate and cache stampedesenior
- Edge workers and edge-side compositionsenior
- CDN operations and observabilitysenior
- WebSocket: the HTTP upgrade handshakejunior
- WebSocket frame format: opcodes, masking, fragmentationmiddle
- WebSocket vs SSE vs long-polling: choosing the right transportmiddle
- WebSocket backpressure: when clients can''''t keep upmiddle
- Reconnection: jittered backoff, thundering herd, message resumptionsenior
- WebSocket at scale: HTTP/2 multiplexing, permessage-deflate, C10Msenior
- WebSocket in production: proxies, security, and distributed architecturesenior
- What reverse proxies dojunior
- Balancing algorithms: round-robin to power-of-two-choicesmiddle
- L4 vs L7 load balancing and client-IP preservationmiddle
- Health checks, connection draining, and slow startmiddle
- Session affinity, consistent hashing, and the right fixmiddle
- Retry storms, circuit breakers, and load sheddingsenior
- Resilient LB architecture: anycast, zone-aware routing, and observabilitysenior
- Why QUIC and not TCP+TLSjunior
- QUIC streams and head-of-line blockingjunior
- Integrated handshake and 1-RTTmiddle
- Connection IDs and network migrationmiddle
- Loss detection and congestion controlmiddle
- 0-RTT resumption and packet encryptionsenior
- Deployment tradeoffs and CPU costsenior
- DDoS: what it is and why it worksjunior
- Amplification attacks and state exhaustionmiddle
- Rate limiting: algorithms and architecturemiddle
- WAFs, firewalls, mTLS, and HSTSmiddle
- DNS cache poisoning and BGP hijackingsenior
- Defense-in-depth architecture and attack economicssenior
- The twelve layers: one URL, seven actorsjunior
- DNS, TCP, TLS in sequence: where the milliseconds gomiddle
- Critical render path and Core Web Vitalsmiddle
- Proxy intercepts and security gates: rate limiters, WAF, mTLSmiddle
- Alternate paths: QUIC 0-RTT, WebSocket upgrade, connection migrationmiddle
- Observability: distributed traces, USE/RED, and samplingsenior
- Resilience: cascading retries, circuit breakers, and error budgetssenior
- What the three signals are: logs, metrics, and tracesjunior
- Metrics and cardinality: the cost model of a time-series databasemiddle
- Logs and volume: the cost model of structured loggingmiddle
- Traces and sampling: the cost model of distributed tracingmiddle
- Join keys and exemplars: making the three signals composemiddle
- Observability 2.0: wide events and the cost shiftsenior
- Failure modes and engineering practice: cardinality budgets, PII, and samplingsenior
- Why structured logs exist: the diary vs the spreadsheetjunior
- The production log schema: fields every line must carrymiddle
- Log levels and alert routingmiddle
- Sampling strategies and log costmiddle
- PII redaction and log injectionsenior
- Trace context propagation in logssenior
- OTel Logs Data Model and audit logs as a subsystemsenior
- OTel signals, Semantic Conventions, and the OTLP wire formatmiddle
- Auto-instrumentation and manual spans: the 80/20 of OTelmiddle
- The OTel Collector: receivers, processors, exporters, and deployment patternsmiddle
- Sampling strategies: head, tail, and parent-basedmiddle
- Vendor neutrality, eBPF instrumentation, the Operator, and browser/serverless OTelsenior
- Operating the OTel Collector: reliability, version skew, failure modes, and governancesenior
- RED and USE: two checklists, one triage disciplinejunior
- Instrumenting RED in Prometheus: counters, histograms, and cardinality disciplinemiddle
- USE on Linux: CPU, memory, disk, network, and PSImiddle
- Golden signals, dashboard layout, and service mesh auto-REDmiddle
- Cardinality as a cost driver: labels, PII, exemplars, and samplingmiddle
- Native histograms, SLO tie-in, and production failure patternsmiddle
- SLI, SLO, and the error budget: reliability by the numbersjunior
- Choosing SLIs and SLO targets: ratios, not feelingsmiddle
- Multi-window multi-burn-rate alerting: why AND beats ORmiddle
- Error budget policy, latency SLOs, and composite journeysmiddle
- Iceberg SLIs, composite SLO math, and SLA vs SLOsenior
- Production SLO failures, self-observability, security, and the big picturesenior
- Flame graphs: reading the picture that shows where time goesjunior
- Sampling vs instrumentation profiling: why 99 Hz wins in productionmiddle
- Profile types: CPU, memory, off-CPU, mutex — which one to reach formiddle
- Continuous profiling: always-on flame graphs with eBPF and trace-id correlationmiddle
- How flame graphs are built from samples, and the production workflows that use themmiddle
- Linux perf, eBPF internals, PGO, and the limits of samplingsenior
- Profiling in production: security, war stories, OTel profiles, and the infrastructure designsenior
- The debugging funnel: SLO → RED → trace → profilejunior
- OTel architecture: one SDK, four signals, one wire formatmiddle
- Cost discipline: keeping observability under 5% of infra spendmiddle
- The incident loop: from pager to postmortem to preventionmiddle
- Scale, security, and the ROI of observable systemssenior
- Why profile first: measure where time actually goesjunior
- Amdahl''''s law and self-time: the ceiling on every speedup you can shipmiddle
- The measurement loop: microbench, macrobench, prod profile, observer effectmiddle
- Reading flame graphs: shapes, per-language profilers, and the 60-second scanmiddle
- Statistical baselines: why one run is not a measurementmiddle
- Profiler history and microbenchmark pitfalls: Knuth to GWPsenior
- Hardware counters, cold-start profiles, and profile securitysenior
- Continuous profiling at scale: costs, CI gates, trace correlation, and anti-patternssenior
- What makes a hot path: symptom vs causejunior
- Five shapes of hotspot: CPU, alloc, cache, lock, syscallmiddle
- Reading parent and child chains: where to apply the fixmiddle
- JIT deopt, the fix-and-verify loop, and PR-time profilingmiddle
- Hardware counters and Intel TMA: sub-category diagnosissenior
- False sharing and native-bridge hot pathssenior
- Hot paths in production: security, tail latency, and tooling lineagesenior
- Memory hierarchy: why the same O(N) loop can be 17x slowerjunior
- Row-major vs column-major: access order and the 9x gapjunior
- Cache lines, struct layout, and false sharingmiddle
- Branch prediction and branchless codemiddle
- SIMD, SoA vs AoS, and memory bandwidthmiddle
- Hardware prefetcher, TLB, and memory-level parallelismsenior
- Cache-oblivious algorithms, PGO, and production failuressenior
- GC basics: what the runtime taxes you forjunior
- GC algorithms: generational, concurrent, and per-runtimemiddle
- GC tradeoffs: pause, throughput, heap — and object poolingmiddle
- GC tuning: pacing, heap shape, and allocation observabilitymiddle
- GC internals: tri-color invariant, write barriers, and per-runtime deep-divessenior
- GC in production: observability, security, edge cases, and fleet governancesenior
- N+1: one logical operation, many round-tripsjunior
- Fix families: JOIN, IN, preload, and DataLoadermiddle
- Detecting N+1: query logs, APM traces, and CI gatesmiddle
- DataLoader: batching across resolver treesmiddle
- Cross-protocol N+1: HTTP fan-out and Redis MGETmiddle
- N+1 at scale: pool exhaustion, plan changes, and denormalisationsenior
- Batching: amortize fixed cost per operationjunior
- The batching window: size and wait timemiddle
- Batching in Kafka and Postgresmiddle
- io_uring and observability of batchingmiddle
- From Nagle to io_uring: evolution of batchingmiddle
- Backpressure, failure isolation, and batch security in productionsenior
- What a bundle actually costs: download, parse, compile, executejunior
- Core Web Vitals: LCP, INP, and CLSmiddle
- Code splitting: route-level, component-level, vendor splittingmiddle
- Tree shaking and compression: removing what you don''''t usemiddle
- Third-party scripts: the silent budget killermiddle
- CI enforcement and RUM: making budgets stickmiddle
- V8 JIT pipeline, HTTP priorities, and bundle securitysenior
- The performance loop: discipline, not a projectjunior
- Classify and fix: matching bottleneck families to remediesmiddle
- Observability stack and CI gates: catching regressions before they shipmiddle
- Incident to enforcement: SLO burn to verified fix in 35 minutesmiddle
- Culture, economics, and org-scale performancesenior
- At-most-once, at-least-once, exactly-once: the three delivery contractsjunior
- The three failure legs — where duplicates and losses actually happenmiddle
- Consumer-side dedup: the cheapest path to exactly-once processingmiddle
- Kafka exactly-once semantics: idempotent producer and transactionsmiddle
- SQS visibility timeout, DLQ, and the outbox patternmiddle
- Exactly-once in production: impossibility proof, hybrid patterns, and real incidentssenior
- What OAuth is and why passwords are not the answerjunior
- Authorization code flow with PKCEmiddle
- ID token validation and JWKS cache managementmiddle
- Refresh token rotation and scope-based least privilegemiddle
- Sender-constrained tokens: DPoP and mTLSsenior
- OAuth in production: audience attacks, observability, and real failuressenior