Your backend is timing out, queries are getting slow, and in the logs there's an error you know all too well: "sorry, too many clients already". The site didn't crash because of a code issue. It crashed because PostgreSQL has a connection limit, and your application exceeded it.
We, at Meteora Web, see this often in projects that come to us for consulting. An e-commerce that goes down during sales, a management system that freezes at month-end, an app that dies when the ad campaign starts. The culprit is almost always the same: unmanaged database connections.
In this guide, we show you how to fix the problem at its root with PgBouncer, the most widely used connection pooler in production. We don't just give you the configuration: we explain why it works, how to size it, and how to avoid the mistakes that cause more damage than the original problem.
Why does PostgreSQL crash with too many connections and how does connection pooling work
PostgreSQL runs one process per connection. Each client that connects starts a server process that consumes memory and CPU. With 100 active connections, you have 100 processes. With 1000, you have 1000. The operating system eventually says enough.
Connection pooling solves the problem with indirection: applications don't connect directly to PostgreSQL, but to a pooler that keeps a fixed number of real connections open. When your app requests a connection, the pooler lends it one that's already open. When it's done, it returns it.
The result is that you can have 500 client applications using only 20 real database connections. The difference between a server that holds up and one that crashes.
The default problem: max_connections and memory
The max_connections parameter defaults to 100. Each idle connection still consumes memory. If each process uses an average of 10 MB, 100 connections mean 1 GB of RAM just to keep ports open.
Sponsored Protocol
Raising max_connections to 500 is not the solution: it's the fastest way to saturate RAM and put the server into swap. Pooling isn't an option, it's a necessity.
-- Check how many connections are active right now
SELECT count(*) FROM pg_stat_activity;
-- Check the current limit
SHOW max_connections;
-- Check memory used per process (on Linux)
ps aux | grep postgres | awk '{sum+=$6} END {print sum/1024 " MB"}'Action checklist: check now how many connections you have active and how much memory PostgreSQL consumes. If the number of connections is close to the limit, you're already in the danger zone.
How do you install PgBouncer and which mode should you choose for your application
PgBouncer is lightweight, configurable in minutes, and works with any application that speaks the PostgreSQL protocol. It installs with a simple command and configures with a text file.
# Installation on Ubuntu/Debian
sudo apt update && sudo apt install pgbouncer
# Installation on CentOS/RHEL
sudo dnf install pgbouncerThe most important choice is the pooling mode. PgBouncer offers three, and getting this wrong means compatibility issues or poor performance.
Session pooling: the default and safest mode
In session mode, a connection is assigned to a client for the entire duration of its session. It's the simplest and most compatible: it works with any application, even those using advanced features like temporary tables or session-level SET.
The limitation is that the number of real connections must be at least equal to the number of simultaneous clients. If you have 200 active users, you need 200 database connections. The pooler doesn't reduce the number of connections, but manages them more efficiently.
Transaction pooling: the mode that saves the server
In transaction mode, a connection is assigned only for the duration of a transaction. When the transaction ends, the connection returns to the pool and can be used by another client.
Sponsored Protocol
Here the savings are enormous: with 20 real connections you can serve hundreds of simultaneous clients. But there are constraints: no temporary tables, no session-level SET, and the application must handle commit/rollback correctly. If you use a modern ORM like Laravel Eloquent or Django ORM, it usually works without issues.
# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20Decision to make: if your application uses temporary tables or session-level SET, choose session pooling. If you use a standard ORM, choose transaction pooling and reduce real connections by 90%.
How do you configure users and passwords in PgBouncer to avoid errors
PgBouncer doesn't read directly from PostgreSQL: it has a userlist.txt file with credentials. This file needs updating when you change database user passwords, and it's one of the most common sources of errors.
The password in the file must be in MD5 format, not plain text. You can generate it with a simple command.
# Generate the MD5 password for user myuser with password 'mypassword'
echo -n "mypassword" | openssl dgst -md5 | awk '{print "md5" $2}'
# Add to /etc/pgbouncer/userlist.txt
# "myuser" "generatedmd5"A common mistake is forgetting to update this file after a password change on the database. The result is an authentication error that confuses because the password on the database is correct.
# Quick connection test to PgBouncer
psql -h 127.0.0.1 -p 6432 -U myuser -d myapp
# If it works, you're operational. If not, check the logs
sudo tail -f /var/log/postgresql/pgbouncer.logImmediate action: create the userlist.txt file with your user credentials and test the connection. If the test fails, look at the logs: 90% of the time it's an unsynchronized password issue.
Sponsored Protocol
How do you size default_pool_size and max_client_conn for your workload
Sizing is the part that separates those who use PgBouncer from those who use it well. Getting the numbers wrong means either not solving the problem, or creating new ones.
The golden rule: don't exceed the number of CPU cores for the real connection pool. PostgreSQL doesn't get faster with more connections: beyond a certain limit, CPU context switching slows everything down.
The practical formula for sizing
For a server with 4 cores and 16 GB of RAM, the typical configuration is:
default_pool_size = 20
max_client_conn = 100020 real connections for 4 cores is 5 per core: a good starting point. If you have complex queries that use a lot of CPU, drop to 3-4 per core. If you have light queries, you can go up to 6-8.
max_client_conn is the limit of connections PgBouncer accepts. There's no need to set it to 10000: if your application needs more than 1000 simultaneous clients, you have an architecture problem, not a configuration one.
Calculation to do now: check how many cores your server has with nproc, multiply by 5, and use that number as default_pool_size. Then monitor for a week and adjust.
How do you monitor PgBouncer with SHOW POOLS and solve the most common problems
PgBouncer has a built-in administration interface. By connecting to the virtual database pgbouncer, you can see the pool status in real time.
# Connect to the administration interface
psql -h 127.0.0.1 -p 6432 -U pgbouncer -d pgbouncer
# Show pool status
SHOW POOLS;
# Show query statistics
SHOW STATS;The cl_active column tells you how many connections are in use. If you see cl_waiting high, it means the pool is too small and applications are waiting. If cl_waiting grows, increase default_pool_size.
Sponsored Protocol
Common error: the pool is too small and queries time out
If your applications time out but PostgreSQL isn't under stress, the problem is that the pool is saturated. Queries wait for a free connection instead of executing. The solution isn't to increase connections, but to reduce the time of each transaction.
Another common mistake is forgetting to configure server_idle_timeout. This parameter closes real connections that stay idle too long, freeing memory.
server_idle_timeout = 300
server_lifetime = 3600Checklist: connect to pgbouncer, run SHOW POOLS, and check if cl_waiting is greater than 0. If it is, you have a bottleneck. If cl_active is always at maximum, reduce transaction time or increase the pool.
How do you integrate PgBouncer with Laravel, Django, and other applications without breaking anything
Integration with applications is almost transparent: just change host and port in the configuration file. But there are details that make the difference between a stable system and one that breaks randomly.
Laravel and PHP: watch out for connection duration
Laravel uses one connection per request. With PgBouncer in transaction mode, it works well. But if you have queues processing background jobs, make sure each job closes the connection when done.
// config/database.php
'pgsql' => [
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '6432'), // PgBouncer port
'database' => env('DB_DATABASE', 'myapp'),
'username' => env('DB_USERNAME', 'myuser'),
'password' => env('DB_PASSWORD', 'mypassword'),
],Django and Python: managing persistent connections
Django keeps connections open for reuse by default. With PgBouncer in transaction mode, this is the ideal behavior. But if you use CONN_MAX_AGE, make sure the value doesn't exceed PgBouncer's server_lifetime.
Sponsored Protocol
# settings.py
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'HOST': '127.0.0.1',
'PORT': '6432',
'CONN_MAX_AGE': 300, # Must be less than server_lifetime
}
}Error to avoid: if your application uses LISTEN/NOTIFY or pg_advisory_lock, transaction pooling mode won't work. These functions require the session, so you need session pooling or a dedicated database for these operations.
Operational step: change the port in your ORM from 5432 to 6432 and test. If everything works, you're operational. If you get errors with temporary tables or SET, switch to session pooling.
What to do now
You have the tools to solve the connection problem. Nothing else is needed. Here are the steps to take right now:
1. Verify the problem. Run SELECT count(*) FROM pg_stat_activity and check if you're close to the limit.
2. Install PgBouncer. Configure the pgbouncer.ini file with the right mode for your application.
3. Create the userlist.txt file. Generate the MD5 passwords and test the connection.
4. Change the port in your application. From 5432 to 6432, and test in staging before going to production.
5. Monitor for a week. Use SHOW POOLS and adjust default_pool_size if you see waiting queues.
If you need a hand, we at Meteora Web do this every day. We don't just configure PgBouncer: we size the pool, optimize queries, and turn the database into a strength, not a bottleneck. Your server deserves to handle the load, and your business deserves not to stop for a configuration error.
