Lesson 25.15 · PostgreSQL Operations · 30 min read

Connections, pools and PgBouncer

Q: Why could I still log in as postgres when the apps could not?

superuserreservedconnections (3 by default) keeps the last slots for superusers. Apps get "remaining connection slots are reserved for roles with the SUPERUSER attribute" first and "sorry, too many clients already" when even those are gone. This is why applications must never connect as a superuser.

Q: What should I look at in PgBouncer during an incident?

SHOW POOLS in the admin console: clwaiting (clients waiting for a server connection), maxwait (how long the oldest has waited), svactive and svidle. Waiting clients with a climbing maxwait mean the pool is too small for the load or transactions are holding server connections too long, often because of slow external calls inside a transaction.

Q: Your app gets "FATAL: sorry, too many clients already" after scaling out. What happened and how do you fix it?

Each instance opens its own connection pool, so connections = replicas x pool size plus every other client; the scale-out pushed that past maxconnections minus superuserreservedconnections. Short term: log in through a reserved superuser slot, count connections by applicationname in pgstatactivity, cap the app with ALTER ROLE ... CONNECTION LIMIT or shrink its pool, and free idle connections so critical clients get back in. Proper fix: PgBouncer in transaction mode (or the platform's pooler) so many clients share a few server connections, pools sized from the server's cores, and an alert at about 80% of maxconnections.

In plain words

A database has a fixed number of chairs (max_connections). Every copy of an app arrives with its own group of friends (its connection pool) and they all sit down, even if most of them only stare at their phones. Add more copies of the app and one day the chairs run out: the next person is turned away at the door.

PgBouncer is a host who keeps a small set of chairs and lets people sit only while they are actually ordering, then gives the chair to the next person. Many more guests, the same few chairs.

Why this lesson exists

FATAL: sorry, too many clients already is the PostgreSQL error most likely to page you, and it is almost never caused by the database. It is caused by arithmetic: every instance of every service opens a pool of connections, the pools are sized by people who never see the others, and one autoscaling event multiplies them past max_connections. This lesson is that arithmetic, how application pools behave, and PgBouncer - the pooler that lets thousands of clients share a few dozen server connections.

What you need to know already: one process per connection and pg_stat_activity (lesson 1), max_connections is a restart setting (lesson 2), pg_hba.conf and passwords (lesson 4), systemd template units (orders-api@1).

The words you need first

Connection pool - a set of open connections inside an application process, lent to request threads and given back. HikariCP (Java), psycopg_pool / SQLAlchemy (Python), pgx (Go), node-postgres. The pool is per instance: 10 pods x pool 10 = 100 connections.
max_connections - the hard limit of the server (default 100). Includes the slots below.
superuser_reserved_connections (default 3) - slots only superusers can use, so you can still get in to fix things. reserved_connections (16+, default 0) - slots for roles with pg_use_reserved_connections (monitoring, admin tooling).
Idle connection - open but doing nothing. Cheap per connection, expensive by the thousand: memory, snapshot work for every query, scheduler pressure.
PgBouncer - a lightweight proxy speaking the PostgreSQL protocol: clients connect to it (port 6432), it keeps a small pool of real server connections and hands them out.
Pool mode - when PgBouncer takes a server connection back: at disconnect (session), at the end of each transaction (transaction), or after each statement (statement).

The apps on this box

The lab has the orders service as three instances of a Spring Boot app, each with a HikariCP pool, connecting to localhost:5432 as orders_app:

$ grep -v '^#' /etc/orders-api/application.properties
server.port=${PORT:8080}
spring.datasource.url=jdbc:postgresql://localhost:5432/orders?ApplicationName=orders-api
spring.datasource.username=orders_app
spring.datasource.password=app-lab-pw
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.connection-timeout=30000
spring.jpa.open-in-view=false
$ sudo systemctl start orders-api@1 orders-api@2 orders-api@3
$ journalctl -u orders-api@1 --no-pager | tail -n 4
Sep 22 20:01:57 oncall-lab java[17414]: 2026-09-22T20:01:57.250+00:00  INFO 17414 --- [orders-api] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
Sep 22 20:02:08 oncall-lab java[17414]: 2026-09-22T20:02:08.000+00:00  INFO 17414 --- [orders-api] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@44064409
Sep 22 20:02:08 oncall-lab java[17414]: 2026-09-22T20:02:08.000+00:00  INFO 17414 --- [orders-api] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
Sep 22 20:02:08 oncall-lab java[17414]: 2026-09-22T20:02:08.000+00:00  INFO 17414 --- [orders-api] [           main] lab.orders.OrdersApiApplication          : Started OrdersApiApplication in 6.414 seconds (process running for 7.714)

HikariPool-1 - Start completed. - and by default Hikari keeps minimum-idle = maximum-pool-size connections open all the time, busy or not. Count them where they land:

$ sudo -u postgres psql -c "select application_name, client_addr, state, count(*) from pg_stat_activity where backend_type = 'client backend' group by 1, 2, 3 order by 1, 3"
 application_name | client_addr | state  | count
------------------+-------------+--------+-------
 orders-api       | ::1         | idle   |    30
 psql             |             | active |     1
(2 rows)

30 server processes for three small app instances, almost all idle. Your own psql is the one active row. That is normal - and it is the budget you have to plan.

The connection budget

$ sudo -u postgres psql -c "select name, setting from pg_settings where name in ('max_connections', 'superuser_reserved_connections', 'reserved_connections')"
              name              | setting
--------------------------------+---------
 max_connections                | 100
 reserved_connections           | 0
 superuser_reserved_connections | 3
(3 rows)

What the applications may use, in total, is:

max_connections - superuser_reserved_connections - reserved_connections - headroom
     100        -              3                  -          0           - (migrations, cron jobs, psql, the exporter)

and what they will use is the sum over every client of instances x pool size:

client	instances	pool	connections
orders-api	3	10	30
billing-worker	1	4	4
postgres_exporter	1	1	1
total			35 of 97

Now the autoscaler takes orders-api to 12 pods on a busy day: 120 + 5 = 125. The first 97 connections succeed, everything after gets this - watch it happen with a lower limit:

$ sudo -u postgres psql -qc "alter system set max_connections = 25"
$ sudo systemctl restart postgresql@18-main
$ journalctl -u orders-api@3 --no-pager | grep -m 2 'too many\|reserved'
Sep 22 20:05:53 oncall-lab java[17416]: 2026-09-22T20:05:53.750+00:00  WARN 17416 --- [orders-api] [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Cannot acquire connection from data source org.postgresql.ds.PGSimpleDataSource (org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute)
$ sudo grep -c 'too many clients\|remaining connection slots' /var/log/postgresql/postgresql-18-main.log
3
$ sudo -u postgres psql -Atc "select count(*) from pg_stat_activity where backend_type = 'client backend'"
23

Look at the two different FATALs in the server log: remaining connection slots are reserved for roles with the SUPERUSER attribute once only the 3 reserved slots are left, then sorry, too many clients already when even those are gone. 22 app connections plus your own superuser psql - you still got in, which is the point of the reserved slots. Never let an application connect as a superuser: it would eat the slots meant for you.

$ sudo -u postgres psql -qc "alter system reset max_connections"
$ sudo systemctl restart postgresql@18-main

Why not simply set max_connections = 2000? Each connection is a process; PostgreSQL is fastest with roughly as many active connections as CPU cores (a few times that at most), and every query has to look at every connection's state to build its snapshot. Thousands of connections - even idle ones - cost memory and make the server slower for everyone. The fix for "many clients" is pooling, not a bigger number.

Sizing an application pool. HikariCP's own guide starts at (cores x 2) + effective spindle count for the database server's cores - a pool of 10 serves a lot of traffic, because a connection is only busy while a query runs. Bigger pools mostly add waiting inside the database instead of inside the app. Set minimum-idle lower than maximum-pool-size if instances mostly sit idle, and remember the multiplication by replicas.

PgBouncer

PgBouncer sits between the apps and the server. Clients connect to it as if it were PostgreSQL; it authenticates them, then hands each one a real server connection from a small pool only for as long as the pool mode says.

mode	server connection returns to the pool	can share	breaks
`session`	when the client disconnects	nothing more than without it; it only queues clients beyond the pool	nothing
`transaction`	at `COMMIT` / `ROLLBACK` (or after each statement outside a transaction)	many clients, few servers - the useful mode	session state: `SET` outside a transaction, SQL `PREPARE`, `LISTEN`, session advisory locks, temp tables across transactions, `WITH HOLD` cursors
`statement`	after every statement; multi-statement transactions are refused	the most	everything transactional

Ubuntu has it as a package, running as the postgres user:

$ sudo apt install -y pgbouncer
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Installing:
  pgbouncer

Installing dependencies:
  libc-ares2  libevent-2.1-7t64

Summary:
  Upgrading: 0, Installing: 3, Removing: 0, Not Upgrading: 7
  Download size: 244 kB
  Space needed: 619 kB / 10.7 GB available

Get:1 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libc-ares2 arm64 1.34.5-1 [81.3 kB]
Get:2 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libevent-2.1-7t64 arm64 2.1.12-stable-10build1 [81.3 kB]
Get:3 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 pgbouncer arm64 1.25.1-1 [81.4 kB]
Fetched 244 kB in 1s (195 kB/s)
(Reading database ... 118422 files and directories currently installed.)
Selecting previously unselected package libc-ares2:arm64.
Preparing to unpack .../libc-ares2_1.34.5-1_arm64.deb ...
Unpacking libc-ares2:arm64 (1.34.5-1) ...
Selecting previously unselected package libevent-2.1-7t64:arm64.
Preparing to unpack .../libevent-2.1-7t64_2.1.12-stable-10build1_arm64.deb ...
Unpacking libevent-2.1-7t64:arm64 (2.1.12-stable-10build1) ...
Selecting previously unselected package pgbouncer:arm64.
Preparing to unpack .../pgbouncer_1.25.1-1_arm64.deb ...
Unpacking pgbouncer:arm64 (1.25.1-1) ...
Setting up libc-ares2:arm64 (1.34.5-1) ...
Setting up libevent-2.1-7t64:arm64 (2.1.12-stable-10build1) ...
Setting up pgbouncer:arm64 (1.25.1-1) ...
Processing triggers for man-db (2.13.1-1) ...
$ systemctl is-active pgbouncer
active
$ sudo grep -v '^\s*;' /etc/pgbouncer/pgbouncer.ini | grep -v '^$'
[databases]
[users]
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = localhost
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

Three things to set: which databases it serves ([databases]), how it authenticates clients, and the pool. Authentication: with auth_type = scram-sha-256, PgBouncer needs the user's SCRAM verifier in /etc/pgbouncer/userlist.txt - the same string PostgreSQL stores, so the password itself never has to be written down:

$ sudo -u postgres psql -Atc "select format('\"%s\" \"%s\"', rolname, rolpassword) from pg_authid where rolname = 'orders_app'" | sudo tee /etc/pgbouncer/userlist.txt > /dev/null
$ sudo cut -c1-40 /etc/pgbouncer/userlist.txt
"orders_app" "SCRAM-SHA-256$4096:hAIUUyy

(In bigger setups auth_query lets PgBouncer look users up in the database instead of a file.) The config - pgbouncer.ini keeps its comments; our settings:

$ sudo sed -i 's/^\[databases\]$/[databases]\norders = host=127.0.0.1 port=5432 dbname=orders/' /etc/pgbouncer/pgbouncer.ini
$ sudo sed -i 's/^;pool_mode = session$/pool_mode = transaction/; s/^;default_pool_size = 20$/default_pool_size = 10/; s/^;max_client_conn = 100$/max_client_conn = 500/' /etc/pgbouncer/pgbouncer.ini
$ sudo grep -E '^(orders|pool_mode|default_pool_size|max_client_conn|listen_)' /etc/pgbouncer/pgbouncer.ini
orders = host=127.0.0.1 port=5432 dbname=orders
listen_addr = localhost
listen_port = 6432
pool_mode = transaction
max_client_conn = 500
default_pool_size = 10
$ sudo systemctl restart pgbouncer

Point the apps at port 6432 instead of 5432 and restart them:

$ sudo sed -i 's#localhost:5432/orders#localhost:6432/orders#' /etc/orders-api/application.properties
$ sudo systemctl restart orders-api@1 orders-api@2 orders-api@3
$ sudo -u postgres psql -c "select application_name, client_addr, state, count(*) from pg_stat_activity where backend_type = 'client backend' group by 1, 2, 3 order by 1, 3"
 application_name | client_addr | state  | count
------------------+-------------+--------+-------
 psql             |             | active |     1
(1 row)

Still 30 client connections on the app side - but now they end at PgBouncer, and the server sees only the few server connections PgBouncer opened for the work actually running. The admin console is a fake database called pgbouncer on the same port:

$ sudo -u postgres psql -p 6432 -h /var/run/postgresql -U pgbouncer pgbouncer -c 'show pools'
 database  |    user    | cl_active | cl_waiting | cl_active_cancel_req | cl_waiting_cancel_req | sv_active | sv_active_cancel | sv_being_canceled | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us |  pool_mode  | load_balance_hosts
-----------+------------+-----------+------------+----------------------+-----------------------+-----------+------------------+-------------------+---------+---------+-----------+----------+---------+------------+-------------+--------------------
 orders    | orders_app |        30 |          0 |                    0 |                     0 |         0 |                0 |                 0 |       0 |       0 |         0 |        0 |       0 |          0 | transaction |
 pgbouncer | pgbouncer  |         1 |          0 |                    0 |                     0 |         0 |                0 |                 0 |       0 |       0 |         0 |        0 |       0 |          0 | statement   |
(2 rows)

The columns to read in an incident: cl_active (clients holding a server connection or idle), cl_waiting (clients waiting for a server connection - this is the queue), sv_active / sv_idle (server connections in use / free), maxwait (how long the oldest waiting client has waited, in seconds). cl_waiting > 0 with maxwait climbing means the pool is too small for the load or something holds server connections too long - usually long transactions. SHOW CLIENTS, SHOW SERVERS, SHOW STATS, SHOW CONFIG and RELOAD (re-read the ini without dropping anyone) work the same way. Who may use the console: the pgbouncer user over the Unix socket from the OS user PgBouncer runs as (here postgres, so sudo -u postgres), and the roles listed in admin_users (everything) or stats_users (only SHOW).

The transaction-mode trap

In transaction mode, two statements from the same client can run on different server connections. Anything a session remembers between transactions is gone or, worse, leaks to another client:

$ PGPASSWORD=app-lab-pw psql -h 127.0.0.1 -p 6432 -U orders_app orders
psql (18.6 (Ubuntu 18.6-0ubuntu0.26.04.1), server 18.3)
Type "help" for help.

orders=> set statement_timeout = '5s';
SET
orders=> show statement_timeout;
 statement_timeout
-------------------
 5s
(1 row)

orders=> \q

Here both statements happened to land on the same idle server connection - under load they would not, and the SET would be applied to whichever server connection ran it, then silently "inherited" by the next client that gets it. The rules for apps behind PgBouncer in transaction mode:

Set per-session things at the role or database level instead (ALTER ROLE orders_app SET statement_timeout = '5s'), or with SET LOCAL inside each transaction.
Prepared statements: PgBouncer 1.21+ tracks protocol-level prepared statements (max_prepared_statements, default 200 in 1.25), which is what JDBC and psycopg use - but SQL-level PREPARE / EXECUTE still breaks. Old drivers or old PgBouncers need prepareThreshold=0 (JDBC) or prepared statements turned off.
LISTEN/NOTIFY, session advisory locks (pg_advisory_lock) and temp tables that live across transactions need a direct connection (or a separate session pool).

Where PgBouncer runs: next to the database (one pooler for everyone, as here), next to each app (a sidecar per pod - it pools only that pod), or as its own small fleet behind a load balancer. Managed services ship one (Azure Flexible Server has a built-in PgBouncer on port 6432; AWS has RDS Proxy).

Quick check

$ sudo -u postgres psql -Atc "show superuser_reserved_connections"

max_connections is 100. How many connections can an application role (not a superuser) open before it gets refused?

100
97: the last 3 slots are reserved for superusers
90: PostgreSQL keeps 10% spare

Answer

97: the last 3 slots are reserved for superusers - superuser_reserved_connections (default 3) keeps slots so an admin can still log in when the apps have used everything else.

In an interview: "Your app gets FATAL: sorry, too many clients already after scaling out - what happened and how do you fix it?" - every instance opens its own pool, so connections = replicas x pool size, and the autoscaler multiplied it past max_connections (minus the reserved slots). Short term: smaller pools or fewer replicas, a CONNECTION LIMIT on the app role so it cannot starve others. Proper fix: PgBouncer in transaction mode (or the platform's pooler), so many client connections share a few server connections - not a huge max_connections.

Quick check

$ sudo -u postgres psql -Atc "show max_connections"

What is max_connections on a stock Ubuntu cluster?

100
1000
unlimited, the OS limits it

Answer

100 - 100 by default, and 3 of them are kept for superusers: 97 slots for every app pool together.

What you can do now

Count connections by application, address and state from pg_stat_activity.
Work out a connection budget: replicas x pool against max_connections and the reserved slots.
Read both FATALs: reserved slots and too many clients.
Install and configure PgBouncer (databases, userlist with SCRAM secrets, pool mode and sizes), read SHOW POOLS, and know what transaction pooling breaks.

Why it helps

"Sorry, too many clients already" is the error most likely to page you, and it is caused by arithmetic, not by the database: instances times pool size, multiplied by an autoscaler. Knowing the budget formula, the reserved superuser slots and how application pools behave lets you predict it and fix it in minutes.

PgBouncer is the standard fix and a standard interview topic, including what transaction pooling breaks (session state, SQL-level prepared statements, LISTEN, advisory locks).

Commands in this lesson

grep systemctl journalctl psql apt cut sed

FAQ

Why not just raise max_connections to a few thousand?

Every connection is a process with memory, and every query has to consider every connection when it builds its snapshot. PostgreSQL works best with roughly as many active connections as CPU cores. A huge max_connections hides the problem until the server is slow for everyone, needs a restart to change, and the next scale-out hits the new limit anyway.

Why could I still log in as postgres when the apps could not?

superuser_reserved_connections (3 by default) keeps the last slots for superusers. Apps get "remaining connection slots are reserved for roles with the SUPERUSER attribute" first and "sorry, too many clients already" when even those are gone. This is why applications must never connect as a superuser.

How big should an application's connection pool be?

Smaller than most people think. HikariCP's guidance starts at about twice the database server's CPU cores plus its disks, for all instances together, because a connection is busy only while a query runs. Bigger pools mostly move the waiting into the database. Then multiply by the number of instances and check it fits the budget.

What is the difference between session and transaction pooling?

In session mode a client keeps its server connection until it disconnects, so PgBouncer only queues extra clients. In transaction mode the server connection goes back to the pool at every COMMIT or ROLLBACK, so many clients share few server connections - the useful mode - but anything the session remembers between transactions is lost.

What should I look at in PgBouncer during an incident?

SHOW POOLS in the admin console: cl_waiting (clients waiting for a server connection), maxwait (how long the oldest has waited), sv_active and sv_idle. Waiting clients with a climbing maxwait mean the pool is too small for the load or transactions are holding server connections too long, often because of slow external calls inside a transaction.

In an interview Mid

Your app gets "FATAL: sorry, too many clients already" after scaling out. What happened and how do you fix it?

Each instance opens its own connection pool, so connections = replicas x pool size plus every other client; the scale-out pushed that past max_connections minus superuser_reserved_connections. Short term: log in through a reserved superuser slot, count connections by application_name in pg_stat_activity, cap the app with ALTER ROLE ... CONNECTION LIMIT or shrink its pool, and free idle connections so critical clients get back in. Proper fix: PgBouncer in transaction mode (or the platform's pooler) so many clients share a few server connections, pools sized from the server's cores, and an alert at about 80% of max_connections.

Also asked: What does PgBouncer transaction pooling break? · How do you size an application connection pool? · What are superuser_reserved_connections for?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.