Connection Pooling: PgBouncer vs Oracle Connection Manager

The Migration Item That Doesn't Show Up in a Schema Diff

Connection pooling never appears in an object inventory, but it's one of the most common reasons a freshly-migrated PostgreSQL falls over under production load. Oracle quietly multiplexes connections; PostgreSQL gives every connection its own operating-system process. Point an Oracle app that opens 2,000 connections straight at PostgreSQL and you'll exhaust memory long before you hit a throughput limit. The fix is a pooler in front — almost always PgBouncer.

Why the Connection Model Is Different

A PostgreSQL connection is a forked backend process — each one carries real memory and scheduling cost, so a realistic max_connections is in the low hundreds, not thousands. Oracle hides this: with Shared Server, DRCP, and Connection Manager, many client sessions share a small pool of server processes. Apps written against Oracle often assume connections are cheap and open far more than PostgreSQL can afford. PgBouncer restores that assumption by sitting between the app and the database and reusing a small set of real PostgreSQL connections across many clients.

Mapping Oracle's Pooling to PostgreSQL

PgBouncer Pool Modes

PgBouncer has three modes, and the choice is the whole decision: session (a server connection is tied to a client for its whole session — safest, least sharing), transaction (the server connection returns to the pool at the end of every transaction — the workhorse, enormous fan-out), and statement (returned after each statement — aggressive, rarely used). Transaction mode is what lets a few dozen real PostgreSQL connections serve thousands of clients, and it's the closest match to Oracle DRCP.

Transaction Mode: What Breaks

Because a client no longer keeps the same server connection across statements, anything that relies on per-session server state breaks in transaction mode — plan for these:

Server-side prepared statements (fine in PgBouncer 1.21+, a problem before it) · session SET / search_path set once at connect · advisory locks held across statements · LISTEN/NOTIFY · session temp tables and WITH HOLD cursors. If a component genuinely needs session state, give it a separate session-mode pool rather than forcing everything into session mode.

Sizing: Few Real Connections, Many Clients

The point of the pooler is the gap between the two numbers: max_client_conn (clients PgBouncer accepts — thousands) is large, while default_pool_size (real PostgreSQL connections per user/database — a few dozen) stays small. Keep PostgreSQL's own max_connections modest; let PgBouncer absorb the churn.

; pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 5000        ; clients the app can open
default_pool_size = 25        ; real PG connections per (user, db)
; PostgreSQL itself stays modest:  max_connections = 200

Then point the application at PgBouncer's port (6432) instead of PostgreSQL's 5432 — usually a one-line connection-string change.

How DBMigrateAIPro Handles It

The assessment flags when the source leans on Oracle Shared Server / DRCP or when the application opens high connection counts — the signals that PostgreSQL will need a pooler — and recommends a PgBouncer transaction-mode setup with starting pool sizes, plus the transaction-mode caveats to check in the app layer. It's part of the honest pre-cutover picture, not a surprise the night of go-live.