Databases
PgBouncer session, transaction, and statement modes
Crux Transaction mode gives 10–100× multiplexing but breaks SET, LISTEN, SQL PREPARE, and session-level advisory locks; each has a transaction-mode-safe equivalent.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at middle altitude — in the skyA team switches PgBouncer from session to transaction mode. Their deployed SET search_path = 'app, public' suddenly stops working. The app has been in production for six months. Nothing changed — except the pooler mode.
The three PgBouncer pooling modes
Session mode: a backend is leased to a client for the entire duration of the client’s connection. PgBouncer is essentially acting as a connection broker — 1:1 between client connections and backend connections. Everything works: SET, LISTEN, temp tables, session-level advisory locks, classic SQL PREPARE. Multiplexing is zero; you gain only auth offload and a connection queue.
Transaction mode: a backend is leased only for the duration of one transaction (BEGIN…COMMIT, or one autocommit statement). Between transactions the backend returns to the pool and can be handed to a different client. This is the mode that enables real multiplexing — a 10,000-client app can operate on 20–50 backends. The cost is that session state is not preserved across transaction boundaries.
Statement mode: backend returned after every single statement. Multi-statement transactions are forbidden. Used almost exclusively for PL/Proxy-style horizontal sharding; not a general-purpose mode.
The production default is transaction mode — session mode gives up the multiplexing win; statement mode is too restrictive for OLTP.
| Feature | Session mode | Transaction mode | Fix in transaction mode |
|---|---|---|---|
| Multiplexing ratio | None (1:1) | 10–100× | — |
SET search_path at connect | Works | Lost after first transaction | ALTER ROLE app SET search_path = … |
SET LOCAL inside a transaction | Works | Works (scoped to that transaction) | — |
LISTEN / NOTIFY | Works | Broken — NOTIFY lands on the wrong backend | Dedicated session-mode connection for LISTEN |
SQL-level PREPARE name AS … | Works | EXECUTE lands on different backend | Use protocol-level prepared statements (driver-managed); PgBouncer 1.21+ handles these |
pg_advisory_lock() session-level | Works | Lock owned by whichever backend handled the call | pg_advisory_xact_lock() (transaction-level) |
| Temp tables | Works | May not survive to next transaction | ON COMMIT DROP or promote to real table |
Why SET breaks in transaction mode
In session mode, the connection owned by a client is always the same backend. A SET search_path = 'app, public' issued at startup runs on backend B1 and stays on B1 forever.
In transaction mode, PgBouncer returns B1 to the pool after the first COMMIT. The next query from the same client may land on B2, which has never seen the SET. The setting is invisible — silently gone.
Fix: ALTER ROLE app SET search_path = 'app, public' — Postgres applies this to every new connection for that role, so every backend starts with the right setting regardless of which one is assigned.
Why LISTEN breaks, and the dedicated-connection fix
LISTEN channel registers the current backend to receive NOTIFY channel messages. In session mode the backend is permanent — events arrive reliably. In transaction mode the backend is returned to the pool between transactions. A NOTIFY lands on whatever backend Postgres picks, which may be serving a different client; the original listener never hears it.
Fix: open one dedicated connection that bypasses PgBouncer or uses session mode exclusively for LISTEN. Keep all OLTP queries on the transaction-mode pool. The two connection strings coexist.
Why this works
Why does PgBouncer even have session mode? For teams migrating gradually and for applications that genuinely require session semantics (legacy PREPARE, session advisory locks, long-lived temp tables). Session mode is the safe default; transaction mode is the performance upgrade that requires an application audit first.
- Session mode: multiplexing
- None (1:1)
- Transaction mode: multiplexing
- 10–100×
- Statement mode: use case
- PL/Proxy sharding only
- Default pool_size
- 20
- Default max_client_conn
- 100
- PgBouncer memory per pooled conn
- ~2 KB
- Postgres backend memory
- ~5–10 MB
- Prepared statements in tx mode
- PgBouncer 1.21+
Quiz
Completed
Your team switches PgBouncer from session to transaction mode and `SET search_path = 'app, public'` issued at connect time stops working. Why?
Heads-up SET still works; the issue is which backend executes which statement across transaction boundaries.
Heads-up PgBouncer does not cache SETs; it simply routes each transaction to a potentially different backend.
Heads-up The root cause is the multiplexing model, not casing.
Quiz
Completed
Best fix when an application on PgBouncer transaction mode needs LISTEN/NOTIFY for cache invalidation?
Heads-up That throws away all multiplexing for a feature that only needs one dedicated connection.
Heads-up Works but wastes load; LISTEN on a dedicated session connection is the clean solution.
Heads-up NOTIFY in transactions works; LISTEN is the broken side in transaction mode. They are asymmetric.
Order the steps
Completed
Order the things to verify when adopting PgBouncer transaction mode, from highest-leverage to lowest:
- 1 Audit the app for SET (not SET LOCAL) — migrate to ALTER ROLE or SET LOCAL
- 2 Audit for classic SQL PREPARE — switch to driver protocol-level prepared statements
- 3 Audit for LISTEN — isolate on a dedicated session-mode connection
- 4 Audit for pg_advisory_lock (session-level) — switch to pg_advisory_xact_lock
- 5 Enable max_prepared_statements 1000 (PgBouncer 1.21+) for protocol prepares
- 6 Set server_reset_query = DISCARD ALL if any state leakage is possible
- 7 Set idle_in_transaction_session_timeout in Postgres GUC as a safety net
- 01Explain what changes when an app moves from PgBouncer session mode to transaction mode — what works, what breaks, and what becomes possible.
- 02Why does LISTEN break in PgBouncer transaction mode and what is the correct architectural fix?
- 03What is server_reset_query = DISCARD ALL in PgBouncer and when must it be set?
Recap
PgBouncer offers three modes: session (1:1, zero multiplexing, everything works), transaction (10–100× multiplexing, requires app audit), and statement (only for sharding proxies). Transaction mode is the production standard. It breaks four patterns — SET outside a transaction (fix: ALTER ROLE), LISTEN (fix: dedicated session-mode connection), SQL-level PREPARE (fix: driver protocol-level prepared statements with PgBouncer 1.21+), and session-level advisory locks (fix: pg_advisory_xact_lock). Each break has an exact equivalent that is safe in transaction mode; none requires dropping the feature, only moving it.
Connected lessons
unlocks
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