Right, let’s talk about one of PostgreSQL’s few genuine design flaws: its process-per-connection model. You see, unlike some databases that use a lightweight threading model, every single connection to a PostgreSQL backend spawns a full-blown OS process. Yes, a process. It’s the architectural equivalent of using a sledgehammer to crack a nut for every single web request. It’s not wrong, per se, but it’s incredibly resource-hungry.

Think about what happens when that psql client or your Python application connects. The Postmaster (the main daemon) forks a new process. This new backend process then allocates memory for its various buffers—most notably the shared_buffers and work_mem. The shared_buffers allocation is a slice of the total pool you configured in postgresql.conf, but work_mem? That’s allocated per-operation (sorts, hashes) within that connection. A single complex query can ask for multiple work_mem allocations. Now multiply that by 200 simultaneous connections from your moderately busy application. Your server isn’t just running out of RAM; it’s having a fire sale on swap space.

The Memory Overhead of a Connection

Let’s get specific. A mostly idle PostgreSQL connection isn’t free. It’s a process sitting there, holding onto its chunk of shared_buffers and other overhead. You can see this yourself. Connect to a database and check the process size. On Linux, ps can show you the Resident Set Size (RSS).

-- Find the PID of your backend in one window
SELECT pg_backend_pid();
-- Then, in a shell, check its memory usage (replace 12345 with your PID)
ps -o pid,rss,command -p 12345

You’ll see it’s already using several megabytes, just for showing you a prompt. Under load, this balloons. Each connection is a potential memory volcano, dormant until a query triggers an eruption of work_mem usage. This is why slapping work_mem = 256MB in your config file is a hilariously bad idea for a database with any non-trivial number of connections. You’re essentially writing a blank check that your server’s RAM will gladly cash, leading to catastrophic OOM (Out-Of-Memory) kills.

The Connection Stampede and Its Fallout

This model creates a brutal feedback loop. Your app gets a traffic spike. The number of database connections skyrockets. Each new connection is a fork() bomb in slow motion. The OS now has to juggle hundreds of active processes, and the CPU spends more time context-switching than actually running your queries. Performance tanks. Application timeouts increase. This causes requests to pile up, which… opens more database connections. Congratulations, you’ve engineered a distributed denial-of-service attack against yourself.

The worst part? Most of these connections aren’t even doing anything most of the time. A typical web request might use the database connection for, say, 50 milliseconds to run a few queries. The other 1950 milliseconds of that second, the connection is just idling, waiting for the application logic to finish its work, waiting for network I/O, waiting for the user’s browser to blink. It’s holding onto precious resources just to sit there. It’s the most overpaid security guard in the world.

Where PgBouncer Enters the Chat

This is the absurdity that PgBouncer solves. Instead of letting your application open 500 direct, expensive connections to PostgreSQL, you stick PgBouncer in the middle. Your app opens 500 lightweight TCP connections to PgBouncer. PgBouncer, in turn, maintains only a small, sane pool of actual PostgreSQL connections (say, 50). It acts as a traffic cop.

When your app sends a query, PgBouncer quickly assigns it an idle real PostgreSQL connection from its pool, forwards the query, gets the result, and immediately returns the connection to the pool for someone else to use. The expensive PostgreSQL backend processes are kept busy doing real work instead of sitting around idly. The memory savings are staggering. The reduction in context-switching is a godsend for your CPU. You’ve effectively tricked PostgreSQL into behaving like a modern, threaded database.

The magic is in the pooling modes. Using the wrong one is the most common pitfall, so pay attention. session pooling is the most straightforward—it assigns a real connection to a client for its entire database session. It’s better than nothing, but it’s not the star of the show. transaction pooling is where you get the real gains. A client only holds onto a real connection for the duration of a single transaction. As soon as you hit COMMIT or ROLLBACK, boom, the connection is yanked back into the pool. This is what lets you handle thousands of app connections with dozens of real ones. Then there’s statement pooling, which is even more aggressive (assigning per-query), but it breaks anything that requires multiple statements or transaction state, so use it only if you truly know what you’re doing. Most of us live in transaction mode.