Alright, let’s talk about max_connections. This is the parameter where most people’s tuning journey starts, and it’s often the first place they go horribly wrong. You see a big number, you think “more is better,” and you crank it up to 500. Congratulations, you’ve just built a self-DoS machine. Let me explain why.

PostgreSQL is a process-per-connection server. Unlike some other databases that use a thread pool, each new connection gets its own OS process. This isn’t some quirky design flaw from the 90s; it’s a brilliant, rock-solid stability choice. A crash in one connection won’t take down the entire server. But this architecture has a cost, and it’s a bill you pay in RAM.

The Memory Tax of Each Connection

Every single connection, even the idle ones sipping margaritas in the background, consumes memory. It’s not just a tiny bit for the socket; it’s a whole process with its own dedicated memory areas. The biggest hit comes from work_mem and the various other memory contexts. The crucial thing to understand is that work_mem is a per-operation limit, not a per-connection limit. A single complex query can have several sort or hash operations, each using up to work_mem.

So, let’s do some terrifyingly real math. Suppose you have a generous work_mem setting of 4MB (which isn’t crazy for analytical queries). You also have max_connections set to a seemingly reasonable 300.

-- Check your current settings
SHOW max_connections;
SHOW work_mem;

Now, imagine a worst-case scenario: all 300 connections decide to run a query that uses two hash operations at the same time.

300 connections * 2 operations * 4 MB = 2.4 GB

Boom. You’ve just allocated 2.4 GB of RAM instantly, and that’s just for work_mem. You haven’t even accounted for the shared_buffers, the kernel’s disk cache, the memory for the query plans themselves, or the operating system. Your server is now a slideshow, desperately swapping itself to death. This is the connection overhead avalanche.

The Right Way: Connection Pooling

So, if we can’t just set max_connections to a million, how do we handle a thousand web clients? The answer is so fundamental it’s almost a rite of passage: you use a connection pooler.

The idea is simple. Instead of having your 1000 application threads each hold a dedicated, expensive Postgres connection, they all talk to a lightweight pooler (like PgBouncer or pgpool). The pooler maintains a small, efficient set of actual connections to the database server (say, 50-100) and loans them out to application threads for the duration of a query. The app gets the illusion of a thousand connections; your Postgres server gets the reality of a sane, manageable load.

Not using a pooler for a web application is borderline professional negligence. It’s the single biggest performance win for most applications. Here’s a quick taste of setting up PgBouncer to use the common session pooling mode:

# A snippet from a typical pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = session
max_client_conn = 1000
default_pool_size = 60

Your application now connects to PgBouncer’s port (6432 by default) instead of Postgres’s port (5432).

Setting a Realistic max_connections

Even with a pooler, you need to set max_connections correctly on the Postgres server itself. This number should be: (number of pooler connections) + (a few for administrators).

If your PgBouncer default_pool_size is 60, setting Postgres’s max_connections to 70 is a safe bet. This prevents a misconfigured pooler from accidentally overwhelming the server. To change it, you have to be a little old-school—it requires a server restart. Edit your postgresql.conf:

# postgresql.conf
max_connections = 100                 # A sane, pooler-aware value
superuser_reserved_connections = 3    # So you can always connect when at the limit

Then restart your cluster (the command varies by OS, e.g., sudo systemctl restart postgresql). This value is a hard ceiling. Respect it. Your server’s stability depends on it. The goal is to set it as low as you can possibly get away with, because every connection you don’t use is memory you can give to shared_buffers and the kernel for caching, which is where real performance comes from.