Postgres connection pooling on a VPS

Why a small VPS runs out of RAM before max_connections does

Postgres connection pooling on a VPS is not a speed trick. It is what keeps a 4 GB box alive, because every PostgreSQL connection is a separate operating system process holding its own private memory. A pooler puts a small, fixed number of real backend processes behind a large and cheap number of client connections.

The default max_connections is 100. That is a limit, not a budget. PostgreSQL never checks whether your machine can actually hold 100 backends running real queries, so the machine fails first. The kernel out of memory (OOM) killer picks a process, and when it picks a backend, PostgreSQL restarts the whole cluster to make shared memory safe again. The log shows server process (PID 1234) was terminated by signal 9: Killed, then terminating any other active server processes. Every open connection dies, including the healthy ones.

The box runs out of memory because each connection is a process, and because work_mem is granted per sort or hash operation rather than per connection. Both of those multiply.

Every connection is a process, and every process costs memory

PostgreSQL uses one process per connection. The postmaster forks a backend when a client connects, and that backend lives until the client disconnects. It is not a thread. It has its own page tables, its own catalog caches and its own cached query plans. Those caches grow as the connection touches more tables and runs more distinct queries, so a long-lived connection in a busy ORM application costs more than a fresh one.

Shared memory really is shared. shared_buffers is one allocation for the whole cluster, mapped into every backend. Private memory is not shared, which is why top misleads you here: the resident set size (RSS) of a backend includes the shared pages that backend has touched, so adding RSS across 50 backends counts shared_buffers 50 times over.

Measure the private part instead. PSS (proportional set size) divides each shared page by the number of processes mapping it, and USS (unique set size) counts only the pages that belong to that process alone.

sudo apt update
sudo apt install -y smem
sudo smem -k -P '^postgres'

The USS column is the memory that would be freed if that backend exited. That is your real per-connection cost. Published figures usually put an idle backend in the single-digit megabytes, and a backend that has run wide ORM queries at several times that. Treat those as typical published figures, not as your numbers. The only figure worth planning against is the one from your box under your workload.

One per-session allocation is easy to miss. temp_buffers defaults to 8MB and is allocated per session the first time that session touches a temporary table. It is not given back until the session ends.

work_mem is granted per operation, not per connection

This is where the arithmetic escapes people. work_mem defaults to 4MB, and the PostgreSQL documentation is direct about what that means: "a complex query might perform several sort and hash operations at the same time, with each operation generally being allowed to use as much memory as this value specifies before it starts to write data into temporary files." A plan with three sort nodes can use three times work_mem inside one backend, at the same moment.

Hash operations get more. hash_mem_multiplier defaults to 2.0, so a hash join or a hash aggregate may use work_mem times two, which is 8MB on stock settings. Parallel query multiplies again, because each parallel worker is another process with its own allowance.

Run the numbers for a 4 GB VPS. Set shared_buffers to 1 GB, leave work_mem at 4MB, and let 100 connections each run one query with two hash nodes. That is 100 times 16MB, so 1.6 GB of private memory on top of the 1 GB of shared buffers, before the page cache and before anything else on the box. Now raise work_mem to 64MB because the server has RAM to spare, and the same 100 connections describe 100 times 256MB. Nothing warns you. You find out when the OOM killer does.

You can check whether work_mem is too small instead of guessing. Set log_temp_files = 0 in postgresql.conf and reload. Every spill to disk then writes a line naming the file and its size, like temporary file: path "base/pgsql_tmp/pgsql_tmp1234.0", size 20971520. Frequent spills mean a higher work_mem would help. No spills mean a raise buys nothing and costs memory you do not have.

The pool arithmetic that actually bites

Nobody sets out to open 240 connections. They configure a pool of 20, then run the application in more than one place.

[
  {
    "config": "1 worker",
    "backends": 20
  },
  {
    "config": "4 web workers",
    "backends": 80
  },
  {
    "config": "4 web + 2 background",
    "backends": 120
  },
  {
    "config": "2 hosts x 4 workers",
    "backends": 160
  },
  {
    "config": "3 hosts x 4 workers",
    "backends": 240
  }
]

Four Gunicorn workers, each holding a pool of 20, ask for 80 backends. Add two background job workers and it is 120. Grow to 3 hosts x 4 workers and the application is asking for 240 backends against a max_connections of 100. Not one of those 5 setups is misconfigured in any single place. The pool is per process, and no part of the application can see the total.

The library defaults push in the same direction. SQLAlchemy's QueuePool defaults to pool_size=5 with max_overflow=10, so 15 connections per process. HikariCP defaults to 10. Django had no built-in pool before 5.1, one connection per worker process, which is why Django applications meet this later and then meet it all at once when someone sets CONN_MAX_AGE or turns on the newer "pool": True option. If you run a Django app behind Gunicorn and nginx, the number to multiply is your Gunicorn worker count, not your server count.

What Postgres connection pooling on a VPS actually changes

A pooler is a process that speaks the PostgreSQL wire protocol to your application on one side and holds a small set of real server connections on the other. It does not make any query faster. It changes who pays for a connection, and how many real backends exist.

Two things improve. Connecting stops costing a fork plus the catalog lookups that fill an empty backend cache, because the pooler answers the client's connect itself. More importantly, the count of real backends stops tracking the count of application connections, so 500 clients can share 20 backends.

The waiting is the feature, and this is the part people resist. Without a pooler, 500 concurrent queries all get a backend and all run at once on two CPU cores, so every one of them is slow and all of the memory is spent at the same moment. With a pooler, 20 run and the rest wait a few milliseconds, so each running query gets a real share of the CPU and finishes sooner. A queue in front of a small pool beats no queue in front of a large one.

What a pooler does not do is bound anything else on the machine. If Postgres shares the VPS with an app server or with a vector database on the same VPS, the pooler protects Postgres from your application and nothing more. Put a hard ceiling on the neighbours as well: you can cap the memory and CPU a service may use with systemd so one runaway process cannot take the database down with it. Where the database itself lives changes how you set those limits, which is the practical difference between running Postgres in Docker or straight on the host.

Session pooling versus transaction pooling

One setting decides everything else, and it is pool_mode.

In session pooling, a server connection is assigned to a client for the whole life of that client connection and released when the client disconnects. Everything works, because the pooler is a plain proxy. You save the cost of connecting and nothing else. If the application opens 200 connections, you still need 200 backends.

In transaction pooling, a server connection is assigned to a client only for the duration of one transaction. At COMMIT or ROLLBACK it returns to the pool and the next waiting client gets it. This is what turns 500 clients into 20 backends. It is also what breaks things, and it breaks them by design: your next statement may run on a different backend than your last one did.

PgBouncer's default is pool_mode = session. Install it, change nothing, and you get the cheap half with none of the win. A third mode, statement, returns the connection after every single statement and rejects multi-statement transactions. Leave it alone unless you know exactly why you want it.

What transaction mode breaks, and why

Everything below fails for one reason. It is state that lives inside a single backend, and transaction pooling does not promise you the same backend twice.

• SET and RESET at session level. SET search_path, SET statement_timeout, SET TIME ZONE and SET ROLE land on whichever backend served that statement, and are gone by your next transaction. Use SET LOCAL inside an explicit transaction, which is scoped to that transaction and is therefore safe.
• LISTEN. Notification delivery belongs to the backend that ran LISTEN, and that backend is handed to another client as soon as the transaction ends. NOTIFY still works in transaction mode, which makes this a confusing failure: sending succeeds, receiving never happens. If you need LISTEN, open one extra connection straight to port 5432 that skips the pooler.
• Session-level advisory locks. pg_advisory_lock() is held by the session and released when the session ends. Under transaction pooling your unlock call runs on a different backend, so the lock stays held until PgBouncer retires that server connection, which by default is after server_lifetime, one hour. Use pg_advisory_xact_lock(), which is released at the end of the transaction by the same backend that took it.
• PREPARE and DEALLOCATE, the SQL statements. Never available in transaction mode.
• WITH HOLD cursors, and any server-side cursor expected to outlive its transaction.
• Temporary tables meant to survive a commit. CREATE TEMP TABLE ... ON COMMIT PRESERVE ROWS puts the table in one backend's temporary schema, and your next transaction may not be in that backend.
• LOAD.

Protocol-level prepared statements are the one item that has moved. PgBouncer 1.21.0 added support for them in transaction mode, and 1.24.0 enabled it by default by setting max_prepared_statements to 200. Older builds leave that at 0, which means off. Ubuntu 24.04 ships PgBouncer 1.22.0, so the feature is present but you have to set max_prepared_statements yourself. If you are not sure what your build is doing, the safe setting is on the client: psycopg 3 stops using server-side prepared statements when you set prepare_threshold to None.

Django names its own version of this. The documentation states that "using a connection pooler in transaction pooling mode (e.g. PgBouncer) requires disabling server-side cursors for that connection", because "server-side cursors are only accessible in the connection in which they were created". Set DISABLE_SERVER_SIDE_CURSORS to True in that database's entry, or every .iterator() call becomes an intermittent failure that only appears under load.

Transaction mode is worth taking, and it is a contract. Read the list, check your ORM and your background job library against it, then switch.

Install PgBouncer and point the application at it

The configuration below is for you to run on your own server: Ubuntu 24.04, with PostgreSQL already listening on 127.0.0.1 port 5432.

sudo apt update
sudo apt install -y pgbouncer
pgbouncer --version

Ubuntu 24.04 packages PgBouncer 1.22.0. Upstream is at 1.25.2 as of August 2026. Check which one you have, because the prepared statement behaviour above depends on it.

Create a role whose only job is logging into the PgBouncer admin console, then build the password file. PgBouncer needs the SCRAM (salted challenge response authentication mechanism) secrets out of pg_authid, and only a superuser can read that table.

sudo -u postgres psql -c "CREATE ROLE pgb_admin LOGIN PASSWORD 'change-this'"
sudo -u postgres psql -At -c \
  'SELECT format($$"%s" "%s"$$, rolname, rolpassword) FROM pg_authid WHERE rolpassword IS NOT NULL' \
  > /tmp/userlist.txt
sudo install -o postgres -g postgres -m 640 /tmp/userlist.txt /etc/pgbouncer/userlist.txt
rm /tmp/userlist.txt

Copying the secrets rather than retyping the passwords is what makes this work. PgBouncer can only reuse a SCRAM secret to log in to PostgreSQL when the client also authenticated with SCRAM, when the secret in the file is byte for byte the one in pg_authid (same salt and iteration count, not merely the same password), and when the [databases] line does not pin a user=. Add user=appuser to that line and PgBouncer needs a plaintext password instead. Confirm the file owner matches the account the service runs as, with systemctl show pgbouncer -p User. Rotating a password in PostgreSQL means regenerating this file, or the next connect returns password authentication failed.

Now write /etc/pgbouncer/pgbouncer.ini.

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgb_admin
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
max_db_connections = 80
max_prepared_statements = 200
ignore_startup_parameters = extra_float_digits

listen_addr = 127.0.0.1 keeps the pooler off the public internet, which matters because a pooler reachable from outside is an authentication endpoint you did not mean to publish. max_client_conn is how many application connections PgBouncer will accept, and it is cheap, so it can be large. default_pool_size is how many real backends one database and user pair may hold, and it is the expensive number. max_db_connections caps the whole database at 80, leaving room under max_connections for psql, backups and monitoring. ignore_startup_parameters = extra_float_digits stops PgBouncer rejecting drivers, the JDBC driver among them, that send that parameter at connect time.

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer --no-pager
sudo journalctl -u pgbouncer -n 20 --no-pager

A healthy start logs a line reporting that PgBouncer is listening on 127.0.0.1:6432. A start that fails on the password file logs the path it could not read, which is almost always a mode or ownership problem rather than a syntax one. Then change the application's connection string from port 5432 to port 6432 and restart it. Nothing else in the application changes.

How to check the pool is doing its job

PgBouncer has an admin console reached through a virtual database called pgbouncer.

psql -h 127.0.0.1 -p 6432 -U pgb_admin pgbouncer

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

SHOW POOLS is the one to watch. cl_active is clients currently attached to a server connection, cl_waiting is clients queued for one, sv_active and sv_idle are the real backends in use and free, and maxwait is how long the client at the front of the queue has been waiting, in seconds. Healthy under normal load means cl_waiting at 0 and maxwait at 0. A maxwait that climbs past a second or two means the pool is too small, or the queries are too slow, and those need different fixes.

Check which one before raising default_pool_size.

SELECT state, count(*) FROM pg_stat_activity
  WHERE backend_type = 'client backend' GROUP BY state;

If most backends sit in idle in transaction, the pool size is not your problem. The application is opening a transaction and then doing something slow inside it, such as an HTTP call, so each backend is held without running a query. idle_in_transaction_session_timeout will cut those off, but the real fix is in the application code. If instead the backends are all active, the pool is genuinely saturated and the queries deserve EXPLAIN (ANALYZE, BUFFERS) before you hand out more connections.

On sizing, the most quoted starting point is the HikariCP heuristic of roughly twice the core count plus one, which on a 2 core VPS is 5. Treat that as a published starting figure, set default_pool_size near it, and move it based on maxwait. Small pools feel wrong and usually measure better, because a backend that is queued costs nothing while a backend that is running costs CPU, memory and lock contention.

Choosing between PgBouncer, PgDog and Pgpool-II

PgBouncer is the answer for the ordinary case: one PostgreSQL server, one VPS, an application that opens more connections than the box can hold. It does one job, its configuration is a single ini file, and it is packaged by Debian and Ubuntu. It runs its connection handling in a single thread, which is ample for a VPS-sized workload and becomes a ceiling only on much larger machines.

PgDog is worth a look when the routing decision belongs in the same network hop as the pooling. It describes itself as a proxy for scaling PostgreSQL, it is written in Rust, and it does transaction and session pooling plus read/write splitting by parsing the query, along with sharding with multi-shard routing and two-phase commit. Reach for it when you have a primary and one or more replicas and you want reads sent to replicas without teaching the application that replicas exist. Two cautions. It is AGPLv3, so the network-use clause is the licence question to settle with whoever owns that decision at your company before it reaches production; the project's own position is that internal use and private modifications do not create a source obligation. It is also young, with weekly releases and 0.x version numbers, so pin a release tag rather than following main.

git clone https://github.com/pgdogdev/pgdog
cd pgdog
cargo build --release
./target/release/pgdog --config pgdog.toml --users users.toml

Building from source needs a current stable Rust toolchain, CMake and a C/C++ compiler. There are also prebuilt Linux binaries and Debian packages on the releases page, and a container image at ghcr.io/pgdogdev/pgdog. Configuration is split across two files. The first holds the general settings and one entry per database, written here as a TOML array of inline tables so the two forms stay easy to tell apart.

databases = [
  { name = "appdb", host = "127.0.0.1" },
]

[general]
port = 6432
default_pool_size = 10

The second holds one entry per user, in the same array form.

users = [
  { name = "appuser", database = "appdb", password = "change-this" },
]

PgDog listens on 6432 by default, the same port as PgBouncer, so the two cannot both take the default on one host.

Pgpool-II, at 4.7.2 as of June 2026, offers pooling plus load balancing, with a watchdog for automatic failover. The extra features bring extra failure modes, and its pooling model is the thing to understand before choosing it. Pgpool-II pre-forks num_init_children child processes, and each child caches up to max_pool server connections, so the ceiling on backends is num_init_children multiplied by max_pool. Each child serves one client at a time, so the number of clients you can accept equals num_init_children and is fixed at startup, and an idle client still occupies a child. Set num_init_children to 100 and max_pool to 4 and you have authorised 400 backends, which is precisely the problem you installed a pooler to solve. Choose Pgpool-II when you want its failover and query routing, and then do that multiplication carefully. If all you want is fewer backends, it is more machinery than the job needs.

The managed proxy question, and the self-hosted equivalent

Managed platforms sell this as a separate product. AWS puts RDS Proxy in front of RDS, and Supabase puts its own pooler, Supavisor, in front of Supabase Postgres. Both do the job described here: hold client connections cheaply, hand out a smaller number of real backends. Supavisor is open source and can be self-hosted, so the choice is not proprietary against free.

The self-hosted equivalent of a managed proxy is not a different idea. It is the same idea with the config file in your hands: PgBouncer in transaction mode, on the same VPS as the database, listening on 127.0.0.1. Two differences are real. A managed proxy sits a network hop away, so it adds latency and it keeps holding client connections while the database restarts underneath it. PgBouncer on the database host adds a loopback hop, which is close to free, and it dies when that host dies. If you want the survive-a-restart behaviour, you need failover machinery as well, which is where Pgpool-II's watchdog or PgDog's health checks start to earn their complexity.

One more option belongs on the list. If the connection count is the main thing making your deployment complicated, an embedded database has no connection model to pool, because it is a library inside your process rather than a server on a port. For a single application server with modest write volume, running SQLite in production on a VPS removes this entire problem instead of managing it. When you do need a real server, size the pool before you size the machine.

FAQ

Do I still need PgBouncer if my application already has a connection pool?

Usually yes, because an application pool is per process and cannot see the others. Four Gunicorn workers each holding a pool of 20 request 80 backends, and adding two background workers makes it 120. PgBouncer is the only component that sees the total and can cap it. The good arrangement is both: a small pool inside each worker so requests do not pay for a TCP connect, and PgBouncer in transaction mode bounding the real backends behind them.

What exactly breaks when I switch PgBouncer to transaction mode?

Anything that keeps state in one backend across transactions. Session-level SET and RESET, LISTEN, WITH HOLD cursors, the SQL PREPARE and DEALLOCATE statements, session-level advisory locks, temporary tables that must outlive a commit, and LOAD. NOTIFY keeps working, which makes broken LISTEN look like a delivery bug rather than a pooling one. On Django, set DISABLE_SERVER_SIDE_CURSORS to True. On psycopg 3, either set prepare_threshold to None or run PgBouncer 1.22 or newer with max_prepared_statements above 0. Replace pg_advisory_lock() with pg_advisory_xact_lock().

How large should default_pool_size be on a 2 core VPS?

Smaller than feels right. The widely published HikariCP heuristic is about twice the core count plus one, so roughly 5 on two cores, and that is a starting point rather than an answer. Set it, then read maxwait and cl_waiting in SHOW POOLS under real load. Zero on both means the pool is big enough. A rising maxwait means clients are queueing, and before raising the number, check pg_stat_activity: backends stuck in idle in transaction are an application bug that more connections will only hide.

PgBouncer or PgDog?

PgBouncer for one PostgreSQL server on one VPS, which is most deployments. It is packaged in Ubuntu, its behaviour is well documented, and its whole configuration is one ini file. PgDog when read/write splitting across replicas or sharding belongs in the same hop as the pooling, so the application does not have to know the topology. Before committing to PgDog, settle the AGPLv3 question with whoever owns licensing where you work, and pin a specific release, because the project is still on 0.x version numbers and ships weekly.