Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 3 · Lesson 20 · 4 min read

PostgreSQL Connection Pooling with PgBouncer

On this page
  1. Why migrated apps run out of connections
  2. Server-side pooling with PgBouncer
  3. PgBouncer pool modes
  4. Query Store becomes pg_stat_statements
  5. Check yourself

Why migrated apps run out of connections

Here's a scenario that catches many migrations. The application runs on 20 servers, each with a connection pool of 100. On SQL Server that's 2,000 connections, and nobody noticed, because SQL Server serves connections with lightweight worker threads.

On PostgreSQL, each connection is a separate operating system process with its own memory (Day 1). Two thousand of them, mostly idle, waste memory and CPU scheduling, and max_connections is usually set to a few hundred. The application fails at startup with "sorry, too many clients already", or the server slows under the overhead.

Server-side pooling with PgBouncer

Your .NET driver still pools on the client, Npgsql just as SqlClient did. That keeps each app server from opening new connections constantly. But client pools don't share across servers, so the total grows with every instance you add. The usual answer is a server-side pooler between the application and PostgreSQL. It accepts thousands of client connections and runs them over a few dozen real ones.

PgBouncer is the standard choice. It's small, fast and does one job:

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

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 30
max_prepared_statements = 200

The application connects to port 6432 instead of 5432. Nothing else in the connection string changes.

PgBouncer pool modes

ModeA server connection is held forBreaks
sessionThe whole client connectionNothing, but it barely reduces connections
transactionOne transactionAnything that relies on session state between transactions
statementOne statementMulti-statement transactions, so it's rarely used

Transaction mode gives the big savings and is what most deployments use. Know what it breaks, because SQL Server code often relies on session state:

  • Session settings. SET search_path or SET app.tenant_id may land on a connection another client uses next. Use SET LOCAL inside the transaction, or set defaults per role.
  • Temporary tables that live across transactions.
  • Session-level advisory locks and LISTEN.
  • Prepared statements worked only with session mode until PgBouncer 1.21. Newer versions support them in transaction mode when max_prepared_statements is set.

Also keep max_connections modest, a few hundred at most on typical hardware. Size default_pool_size near the number of queries the server can usefully run at once, which is a few times its CPU cores, not the number of users.

Pgpool-II is the other name you'll hear. It adds read load balancing and failover features on top of pooling, but it's heavier and more complex. Most teams use PgBouncer for pooling and handle failover with Patroni (Day 25).

Query Store becomes pg_stat_statements

The pg_stat_statements extension records statistics for every query shape the server runs. It's the answer to "which queries cost the most?":

SQL
-- once: shared_preload_libraries = 'pg_stat_statements' in postgresql.conf, then restart
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT left(query, 60) AS query, calls,
       round(total_exec_time) AS total_ms,
       round(mean_exec_time, 1) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total time first. A 3 ms query run two million times a day usually matters more than a slow report run twice. Compared with Query Store, it's simpler: cumulative counters since the last reset, with no plan history and no plan forcing. Snapshot it periodically, or use a monitoring tool that does, to see trends.

The DMVs you know map like this:

SQL ServerPostgreSQL
sys.dm_exec_requests, sp_who2pg_stat_activity
sys.dm_tran_locks, blocking chainspg_locks, pg_blocking_pids(pid)
sys.dm_exec_query_stats, Query Storepg_stat_statements
sys.dm_db_index_usage_statspg_stat_user_indexes
KILL spidSELECT pg_cancel_backend(pid), or pg_terminate_backend(pid)

Finding who blocks whom, the PostgreSQL way:

SQL
SELECT pid, pg_blocking_pids(pid) AS blocked_by, state,
       now() - query_start AS running_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running_for DESC NULLS LAST;

For dashboards and alerts, the common stack is Prometheus with postgres_exporter and Grafana. Managed services offer their own, such as Amazon RDS Performance Insights. DataGrip isn't a monitoring tool, but it's where you investigate a query once monitoring flags it.

Check yourself

Put PgBouncer in transaction mode between the application and PostgreSQL, so thousands of client connections share a few dozen server connections. Raising max_connections to thousands only moves the problem.

Later transactions can run on server connections whose setting came from another client. Use SET LOCAL inside each transaction, or set the search_path per role with ALTER ROLE.

Show them pg_stat_statements ordered by total_exec_time, which keeps cumulative statistics per normalized query: calls, total and mean time, rows and buffer usage. It doesn't keep plan history like Query Store, so pair it with auto_explain to log the plans of slow queries.

In session mode a client keeps one server connection until it disconnects, so everything works but pooling is limited. In transaction mode a connection is returned to the pool after each transaction, which scales far better but breaks session state such as SET, temporary tables and session-level advisory locks.

Fewer than most people expect. Each connection is a process with its own memory, and past a few hundred active connections throughput usually falls. Size it for what the server can actually run in parallel, and let PgBouncer queue the rest of the application's clients.