PgBouncer in Front of PostgreSQL: Thousands of Connections on One Server

The short answer

PostgreSQL starts a full operating system process for every connection, and each process typically uses 5 to 10MB of memory before it runs a single query, more once heavy queries start allocating work_mem. Five hundred open connections therefore cost around 5GB, more than all the memory on a 4GB server. PgBouncer fixes this by sitting between the application and the database as a lightweight middleman: the application opens thousands of connections to PgBouncer, and PgBouncer runs them over a small fixed set of real connections, say 20, while spending roughly 2KB per client connection itself.

The right mode for Laravel and Django is transaction, where a real connection is lent to the application only for the length of a transaction and then returned to the pool. This mode gives the biggest savings, but it breaks anything that depends on session state: SET commands, session-level advisory locks, LISTEN/NOTIFY, temporary tables, and prepared statements on older PgBouncer releases. Once you know those limits and handle them, a Growth server with two cores and 4GB holds thousands of concurrent connections steadily.

Why each connection costs so much

PostgreSQL uses a process-per-connection architecture rather than threads. Each new connection forks a backend process with its own catalog cache and plan cache, and it reserves work_mem for every sort or hash operation. Opening the connection itself takes 2 to 5 milliseconds with authentication, which is wasted time when the application opens one per request, as PHP-FPM does.

The second problem is less obvious: idle connections are not free either. With 400 connections the kernel spends more time context switching, and computing transaction snapshots slows down because the database has to scan every active backend. Common practice keeps truly active connections near the core count times 2 to 4, which is 8 to 16 on a two-core server and nowhere near 400.

A typical Laravel application on PHP-FPM with 50 workers per web server, three web servers and queue workers easily reaches 200 connections. Add Django with Gunicorn or Celery and you will blow past the default max_connections of 100 at the first traffic peak.

The three pooling modes and what each breaks

Mode	Connection returns to the pool	Savings	What is affected
session	When the client disconnects	Limited	Almost nothing
transaction	After COMMIT or ROLLBACK	Large	SET, advisory locks, LISTEN, temp tables
statement	After every query	Largest	Multi-statement transactions are forbidden

Session mode only reduces the cost of repeatedly opening connections and does nothing for the real connection count at peak. Statement mode suits very simple read workloads and fails for any application that uses transactions, which rules out most ERP systems. That leaves transaction as the practical choice in about 90% of cases.

Prepared statements

This used to be the most famous problem. The application prepares a query on one real connection, then executes it in a later transaction that may land on another connection that has never seen it, producing a prepared statement does not exist error. Since version 1.21 PgBouncer supports protocol-level prepared statements through max_prepared_statements, so set it to 100 or 200. On an older release, enable emulated prepares in the application instead.

Advisory locks and settings

The pg_advisory_lock function ties the lock to the session rather than the transaction, so when the connection goes back to the pool the lock stays attached to a connection that another request may pick up. Use pg_advisory_xact_lock, which releases at the end of the transaction. The same applies to SET: switch to SET LOCAL inside the transaction so the setting ends with it and never leaks into another request.

A practical pgbouncer.ini

Install PgBouncer on the database server to begin with, which avoids an extra network hop. In the [databases] section define the database as app = host=127.0.0.1 port=5432 dbname=app. In the [pgbouncer] section the key values are:

• listen_port = 6432, and the application connects here instead of 5432.
• auth_type = scram-sha-256 with auth_query, so passwords are read from the database rather than copied into a file.
• pool_mode = transaction.
• max_client_conn = 2000 for application connections, which is cheap because each client connection costs about 2KB.
• default_pool_size = 20 real connections per database and user, with reserve_pool_size = 5 for short bursts.
• max_prepared_statements = 100 on version 1.21 and later.
• server_idle_timeout = 600 to close idle server connections after 10 minutes.

Then lower max_connections in PostgreSQL to 50 or 60, since 200 is no longer needed, and give the freed memory to shared_buffers.

Laravel

Change DB_PORT to 6432 in the environment file. If PgBouncer is older than 1.21, add PDO::ATTR_EMULATE_PREPARES set to true in the pgsql connection options. Avoid holding a transaction open while calling an external API, because the real connection stays reserved for the whole transaction, and a transaction waiting 3 seconds for a payment gateway ties up one of your 20 connections for all of that time.

Django

Set PORT to 6432 and turn on DISABLE_SERVER_SIDE_CURSORS = True, since server-side cursors live beyond a single transaction. You can safely raise CONN_MAX_AGE to 60 seconds, because the persistent connection now goes to PgBouncer and not to the database.

Where PgBouncer lives as the system grows

Running it on the database server is the simplest start, but once you have more than one application server you get two options. The first keeps PgBouncer central next to the database, so you control the connection ceiling in one place, which we recommend up to roughly 5 application servers. The second puts an instance on every application server, which removes the first network hop but makes the sum of the pools your real limit: five servers with a pool of 20 each means 100 real connections, so do the arithmetic before it surprises you. PgBouncer itself runs on a single core, and one instance typically handles 10,000 to 20,000 simple queries per second before it becomes the bottleneck, well beyond what most ERP workloads demand.

Before and after on a Growth server

These are approximate results from a reference pgbench run with mixed reads and writes on a server with two cores and 4GB RAM and 400 concurrent clients. Your numbers will differ with your queries, but the direction holds:

Metric	Direct connection	Through PgBouncer
Real PostgreSQL connections	400	20
Database process memory	About 3.4GB, swapping starts	About 0.6GB
Transactions per second	About 900	About 2,600
p95 latency	About 450ms	About 60ms

The surprising part is that cutting real connections from 400 to 20 roughly tripled throughput. Twenty processes working without contention on two cores finish faster than 400 processes fighting over the same cores and memory. Try it yourself with pgbench -c 400 -j 4 -T 120, once against port 5432 and once against 6432.

Monitoring the pool

Connect to the built-in pgbouncer admin database and run SHOW POOLS. The column that matters is cl_waiting: if it stays above zero for more than a few seconds, the pool is too small or long transactions are holding connections. SHOW STATS reports avg_wait_time, and if it stays above 10ms, raise default_pool_size in steps of 5. Do not jump to 100, or you will recreate the original problem.

Also watch long transactions in PostgreSQL through pg_stat_activity and the idle in transaction state, the number one enemy of transaction mode. Set idle_in_transaction_session_timeout to 30 seconds so a forgotten transaction cannot hold a connection forever.

Picking a Cloud plan

For a Laravel or Django application with dozens of concurrent users and a database under 20GB, the Growth plan (2 vCPU, 4GB RAM, 40GB SSD, 500GB transfer) at 6,900 SAR a year runs the database and PgBouncer together. When the database grows and the cache needs more memory, add +2GB RAM for 600 SAR a year before considering a bigger plan. For heavier systems serving hundreds of concurrent users, the Business plan (4 vCPU, 8GB) at 11,500 SAR a year is the natural next step.

Servers sit in Riyadh with about 8ms latency, and activation is automatic after payment. Details are on the Cloud Server page, and every plan and add-on is listed on pricing and plans.

Rollout steps

• Count your peak connections in pg_stat_activity. If they are far above twice your core count, you need PgBouncer.
• Search the code for pg_advisory_lock, SET, LISTEN and temporary tables, and fix them before switching.
• Install PgBouncer in transaction mode with a pool of 20 and point your staging environment at it first.
• Measure with pgbench before and after, then switch production and watch cl_waiting closely for the first week.