---
title: "PgBouncer Transaction Mode: Why Your ORM Is Silently Breaking"
published: true
description: "PgBouncer transaction mode silently breaks prepared statements, advisory locks, and SET LOCAL. Here are the exact mitigations and pool sizing formula that prevent exhaustion without over-provisioning."
tags: postgresql, architecture, api, performance
canonical_url: https://mvpfactory.co/blog/pgbouncer-transaction-mode-orm
---
## What You Will Learn
By the end of this article you will know exactly which ORM features break silently under PgBouncer transaction mode, how to fix each one at the driver layer, and how to size your pool correctly using a formula derived from production data. No hand-waving — concrete configuration for Hibernate, SQLAlchemy, and Prisma.
## Prerequisites
- A running PostgreSQL instance behind PgBouncer (or planning to add one)
- Familiarity with at least one of: Hibernate/JPA, SQLAlchemy, or Prisma
- Basic understanding of connection pooling concepts
---
## The Trap Every Team Falls Into
A team hits connection exhaustion at scale, adds PgBouncer in transaction mode, ships it, and watches mysterious bugs appear two weeks later in staging. Same failure sequence, different company. Every time.
Transaction mode is the right default for high-concurrency backends. A PostgreSQL instance comfortable with 100 real connections can serve 10,000+ application-level "connections" because each server connection is held only for the duration of a single transaction. That is the promise. The cost: anything *session-scoped* disappears between statements.
Here is the full picture:
| Feature | Session mode | Transaction mode |
|---|---|---|
| Prepared statements (named) | Persisted | **Broken** |
| `SET LOCAL` / `SET` variables | Persisted | **Broken** |
| Advisory locks | Persisted | **Broken** |
| Temporary tables | Persisted | **Broken** |
| `LISTEN/NOTIFY` | Persisted | **Broken** |
| Simple queries / unnamed stmts | Works | Works |
Hibernate, SQLAlchemy, Prisma, and Exposed all use at least two of these features by default — without advertising it loudly.
---
## ORM-Specific Fixes
### Hibernate / JPA
Hibernate's `PreparedStatementCache` caches *named* prepared statements server-side. In transaction mode, PgBouncer routes your next statement to a different backend — one that has never seen that prepared statement. You get `ERROR: prepared statement "S_1" does not exist`.
Append `prepareThreshold=0` to your JDBC URL:
jdbc:postgresql://host/db?prepareThreshold=0
For pg JDBC 42.2.9+, the `pgBouncer=true` parameter handles this automatically, switching the driver to protocol-level unnamed prepared statements.
### SQLAlchemy
SQLAlchemy's `autocommit=False` default keeps a transaction open across multiple ORM operations. Combined with `SET LOCAL search_path`, this silently leaks schema context between requests. Safe configuration:
python
engine = create_engine(
url,
pool_pre_ping=True,
connect_args={"options": "-c statement_timeout=30000"}
)
psycopg3 users should additionally set `prepare_threshold=0` on the connection to disable named prepared statements at the protocol level.
### Prisma
Prisma's interactive transactions are directly compatible with transaction mode. However, Prisma's advisory lock-based migration system will deadlock or silently fail under transaction pooling. Run migrations against a direct connection — never through PgBouncer.
---
## The Pool Sizing Formula
Most teams over-provision connections trying to prevent exhaustion, which causes the opposite problem: connection storms under PostgreSQL's process-per-connection model. Let me show you a pattern I use in every project.
plaintext
pool_size = (num_cores × 2) + effective_spindle_count
For a modern cloud instance (8 vCPU, NVMe SSD — count as 1 spindle):
plaintext
pool_size = (8 × 2) + 1 = 17 connections per PgBouncer node
Add a 10–20% overflow buffer via `reserve_pool_size`. But nail down your topology first. If PgBouncer runs as a **sidecar** (one per application pod), each pod owns the full pool — total PostgreSQL `max_connections` is `17 × pod_count`. If PgBouncer runs as a **shared proxy**, the budget divides across all pods:
python
per_pod_pool = floor(17 / pod_count) + 2 # 2 for reserve
Conflating these two topologies is a common source of both under- and over-provisioning.
This feels aggressively small. It works because PostgreSQL thrives under low connection counts — contention for shared buffers drops, query planner cache hit rates increase, and the process-scheduling tax disappears.
---
## Gotchas
Here is the gotcha that will save you hours: the fix is not to switch to session mode. The fix is to know *exactly* which ORM features to disable at the driver layer.
- **Advisory locks**: Move them to Redis, or use `pg_try_advisory_lock` inside a dedicated single-connection pool separate from the main one.
- **`SET LOCAL` calls**: Move them to pool-level `search_path` configuration via PgBouncer's `server_reset_query`.
- **LISTEN/NOTIFY consumers**: These belong on a separate unpooled or session-mode connection. One session-scoped operation should not force your entire service onto the wrong pooling mode.
- **Migrations**: Always run on a direct connection, bypassing PgBouncer entirely.
The docs do not mention this, but auditing your ORM before enabling transaction mode is mandatory. Set `log_min_duration_statement=0` for one hour and grep for `prepared statement.*does not exist` — if it appears, your driver needs reconfiguration before production traffic hits.
---
## Conclusion
Three things worth doing before your next deploy:
1. Audit your ORM's prepared statement strategy and disable server-side caching at the driver layer.
2. Apply the `(cores × 2) + spindles` formula — but model your topology first. A 17-connection pool serving 500 application threads is correct behavior, not under-provisioning.
3. Isolate session-scoped workloads (migrations, advisory locks, `LISTEN/NOTIFY`) onto a separate unpooled connection.
Transaction mode is the right call. The work is in knowing what breaks and fixing it once, correctly.
Top comments (0)