Hey everyone, Alex here. Welcome back to another edition of Coding with Alex on sysseder.com!
If you have ever run a PostgreSQL database in production under heavy load, you already know the dreaded feeling of watching your application’s latency spike while your CPU utilization redlines at 100%. More often than not, the culprit isn't a missing index or a poorly written recursive CTE. It's connection overhead.
Postgres uses a process-per-connection model. Every single client connection spins up a brand-new backend process on the database server. If your microservices pool thousands of connections, your database spends more time context-switching and managing lock contention than actually executing queries. To solve this, we throw PgBouncer—the lightweight, industry-standard connection pooler—in front of our database.
But what happens when PgBouncer itself becomes the bottleneck? Because PgBouncer is single-threaded, it eventually hits a wall. Today, we are diving deep into how to scale PgBouncer to 4x throughput, bypass single-threaded CPU bottlenecks, and handle massive connection spikes without breaking a sweat.
The Bottleneck: Why Single-Threaded PgBouncer Hits a Wall
To understand how to scale PgBouncer, we first have to understand why it slows down. Unlike multi-threaded applications, PgBouncer relies on a single-threaded event loop driven by libevent. It is incredibly efficient at what it does, but at high concurrency, that single thread gets saturated.
Consider a typical high-traffic microservices architecture:
[ 1000+ App Instances ]
│ (Thousands of TCP Connections)
▼
[ Single PgBouncer Process ] <--- Single-threaded event loop bottlenecks here!
│ (Few Managed Connections)
▼
[ PostgreSQL Database ]
When you hit tens of thousands of transactions per second (TPS), PgBouncer’s single CPU core starts running at 100%. When this happens, client connections experience queuing delay, TLS handshake latency rises, and overall throughput plateaus, even though your underlying database server still has plenty of idle CPU cores. To get past this, we have to scale PgBouncer horizontally on the same machine or across a cluster.
The Strategy: Multi-Process PgBouncer with SO_REUSEPORT
How do we scale a single-threaded process? We run multiple instances of it! But running multiple PgBouncer instances on different ports (like 6432, 6433, 6434) forces you to manage complex load-balancing logic in your application.
Instead, we can leverage a powerful Linux socket option: SO_REUSEPORT. This kernel-level feature allows multiple independent processes (in this case, multiple PgBouncer daemons) to bind to the exact same TCP port. The Linux kernel then automatically distributes incoming TCP connections across the active processes using a hash of the client IP and port.
Step 1: Enabling SO_REUSEPORT in PgBouncer
Modern versions of PgBouncer have built-in support for socket reuse. To enable this, you need to configure your pgbouncer.ini file to allow address reuse. Here is a production-ready configuration snippet:
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
; Listen on all interfaces on port 6432
listen_addr = *
listen_port = 6432
; The magic flag to allow multiple processes on the same port
so_reuseport = 1
; Connection pooling settings
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 5
By setting so_reuseport = 1, we tell the operating system that it is perfectly fine for other PgBouncer processes to listen on port 6432.
Step 2: Spawning Multiple Instances via Systemd
To scale to 4x throughput, we want to run one PgBouncer process per CPU core (up to 4 or more cores). The cleanest way to manage this on modern Linux distributions is using Systemd template unit files.
Create a template service file at /etc/systemd/system/pgbouncer@.service:
[Unit]
Description=PgBouncer connection pooler for PostgreSQL (Instance %i)
After=network.target
[Service]
Type=simple
User=postgres
ExecStart=/usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer.%i.ini
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
LimitNOFILE=65536
[Install]
WantedBy=multi-user.target
Now, you can create four distinct configuration files (pgbouncer.1.ini, pgbouncer.2.ini, etc.) in your /etc/pgbouncer/ directory. Each config file should point to its own log and PID file to avoid write conflicts, but they will all share the so_reuseport = 1 flag and bind to port 6432.
To enable and start four independent instances of PgBouncer, run the following commands:
sudo systemctl daemon-reload
sudo systemctl enable --now pgbouncer@1 pgbouncer@2 pgbouncer@3 pgbouncer@4
Boom! You now have four PgBouncer processes listening on port 6432. The Linux kernel will automatically load-balance incoming TCP handshakes across these four processes, successfully quadrupling your connection-handling capacity.
Optimizing the Linux Kernel for High-Throughput Pooling
When you push PgBouncer to 4x throughput, you will quickly hit kernel-level limitations. If your OS isn't tuned to handle rapid connection churn and massive socket queues, you'll start seeing "connection refused" or "connection timeout" errors in your application logs.
Tuning the TCP Backlog
When thousands of clients attempt to connect at once, they enter the TCP SYN queue. If this queue is too small, connections are dropped. Add the following parameters to your /etc/sysctl.conf file to handle high connection volume:
# Increase the maximum number of open files (file descriptors)
fs.file-max = 2097152
# Increase the maximum backlog of connections waiting to be accepted
net.core.somaxconn = 65535
# Increase the TCP SYN backlog queue
net.ipv4.tcp_max_syn_backlog = 262144
# Enable rapid reuse of TIME_WAIT sockets for fast connection recycling
net.ipv4.tcp_tw_reuse = 1
Apply these changes immediately by running sudo sysctl -p.
Transaction vs. Session Pooling: Choose Wisely
Even with scaled PgBouncer instances, your pooling strategy dictates your ultimate throughput limits. PgBouncer offers three pooling modes, but for high-throughput scaling, two are most relevant:
- Session Pooling: When a client connects, PgBouncer assigns a database connection to it for the entire duration of the client's session. This provides 100% compatibility with all Postgres features (like temporary tables and prepared statements), but it severely limits your scaling capacity.
- Transaction Pooling: PgBouncer assigns a database connection to the client only for the duration of a transaction. Once the transaction ends, the connection goes back to the pool. This allows thousands of clients to share a very small pool of database connections.
If you want to achieve 4x throughput, you must use Transaction Pooling. However, beware of the trade-offs: in transaction mode, you cannot use features like LISTEN/NOTIFY, temporary tables, or session-level variables without careful workarounds.
The Multi-Bouncer Architecture
With our optimizations in place, our architecture now looks like this:
[ 1000+ App Instances ]
│
▼ (Single Virtual IP / Port 6432)
┌──────────────────────────────────────────────┐
│ Linux Kernel (SO_REUSEPORT) │
└──────┬───────────┬───────────┬───────────┬───┘
│ │ │ │
▼ (Proc 1) ▼ (Proc 2) ▼ (Proc 3) ▼ (Proc 4)
[PgBouncer] [PgBouncer] [PgBouncer] [PgBouncer]
│ │ │ │
└───────────┼───────────┼───────────┘
▼ (Optimized Connection Pool)
[ PostgreSQL Database ]
By utilizing this architecture, you eliminate PgBouncer as a single-point-of-bottleneck. Each CPU core works independently, distributing the cryptographic load of TLS handshakes and packet parsing, while presenting a unified interface to your application tier.
Wrapping Up & Next Steps
Scaling Postgres doesn't always mean upgrading your instance size or setting up complex read replicas. Often, the easiest win is optimizing the pathway between your code and your data. By deploying multiple PgBouncer instances utilizing SO_REUSEPORT and tuning your Linux kernel parameters, you can easily unlock up to 4x throughput on your existing hardware.
Are you currently hitting database connection limits? Have you tried multi-process PgBouncer, or are you utilizing other pooling solutions like Odyssey or Supabase's Supavisor? Let me know in the comments below!
Don't forget to subscribe to the newsletter for more deep dives into backend engineering, cloud architecture, and database performance. Until next time, happy coding!