PostgreSQL max_connections: Why 100 Is Not Enough (and Why 1000 Is Too Many)

The default is 100. That number was chosen in a different era.

Good evening. I see you have arrived with an error message and a plan to raise a number. Allow me to intervene before that plan takes effect.

PostgreSQL's default max_connections = 100 has been the default for decades. It predates connection pooling middleware, serverless compute, and web frameworks that open a connection per request or per thread. For a modern application, 100 connections are exhausted before anyone notices.

The error message is familiar:

FATAL: too many connections for role "myapp"

This is often the first PostgreSQL error developers encounter in production. The instinct is to set max_connections to 1000 and move on. I understand the impulse — the error is urgent and the fix appears simple. But setting the value too high is worse than the problem it solves. The right number is calculated, not guessed, and connection pooling changes the equation entirely.

The default of 100 was a perfectly reasonable choice when PostgreSQL primarily served applications with a small number of persistent connections — a database server behind a single application server with a handful of threads. In that era, 100 connections was generous.

Modern web applications, if you'll permit me the observation, operate rather differently:

• Web frameworks open a database connection per request thread or per async worker. A Django application with 4 Gunicorn workers and 2 threads each needs 8 connections for a single instance. Scale to 10 instances: 80 connections consumed before the first request.
• Microservices multiply the problem. If 5 services each maintain a connection pool of 20, that is 100 connections from application pools alone — before admin tools, monitoring agents, or background workers connect.
• Serverless functions create a new connection per invocation. A moderate traffic spike on AWS Lambda or Vercel can open hundreds of concurrent connections in seconds.

At 100 connections, the math runs out quickly. But the solution is not to raise the ceiling — the solution is to understand what happens when the ceiling is high.

Why 1000 is too many — the RAM tax

Per-connection memory cost

I should be direct about what happens behind the scenes. Each PostgreSQL connection is a forked backend process. Not a thread. Not a coroutine. A full operating system process with its own memory space.

The base memory overhead per connection is approximately 5–10MB, depending on configuration. This includes process stack and heap allocation, catalog cache (system table metadata), shared buffer pointers and local buffer space, and temp_buffers allocation (default 8MB, allocated on demand).

But the base overhead is not the full picture. work_mem is allocated per sort or hash operation, per connection. A query with 4 sort nodes on a system with work_mem = 64MB can consume 256MB from a single connection:

work_mem per operation:  64MB
Sort nodes in query:     × 4
Memory for one query:    = 256MB

The total worst-case memory footprint is:

max_connections × (base_overhead + work_mem × max_sort_operations_per_query)

At 1000 connections with work_mem = 64MB and a worst-case query using 4 sort operations:

1000 × (10MB + 64MB × 4) = 1000 × 266MB = 266GB

No server has that much RAM. I'm afraid that is not a rounding error — it is a fundamental constraint. In practice, not every connection runs a complex query simultaneously. But the risk is real: a traffic spike that activates even 20% of those connections with complex queries consumes 53GB of memory from work_mem alone.

Context switching and lock contention

RAM is not the only cost. The operating system scheduler must manage 1000 processes:

• Context switching between hundreds of active processes adds CPU overhead. Each switch saves and restores process state, flushes CPU caches, and disrupts branch prediction.
• ProcArrayLock contention scales with connection count. Every transaction start and end acquires this lock to update the shared process array. With hundreds of concurrent backends, this becomes a measurable bottleneck.
• LWLock and heavyweight lock contention increases as more backends compete for the same resources. Buffer mapping locks, WAL insertion locks, and relation extension locks all experience more contention with more concurrent backends.

Benchmarks consistently demonstrate this. PostgreSQL throughput on typical OLTP workloads peaks at 200–300 active connections on commodity hardware, then declines — gracefully at first, then with increasing severity:

The exact numbers vary by hardware and workload, but the pattern is consistent and worth committing to memory: more connections does not mean more throughput. It means more contention.

The math for calculating the right number

The core formula

Allow me to walk through the arithmetic. Start from the ceiling — how many connections the server can physically support:

max_connections_ceiling = (total_RAM - shared_buffers - OS_overhead) / per_connection_memory

But the ceiling is not where I would begin. A better starting point:

productive_connections = (CPU_cores × 2) + effective_disk_concurrency

For CPU-bound workloads (complex queries, aggregations), the multiplier is closer to 2. For I/O-bound workloads (many simple reads hitting disk), it can go to 4. The effective_disk_concurrency term accounts for queries waiting on I/O — on fast NVMe storage this adds less than on spinning disks.

For a 4-core server: approximately 10–20 connections can be productively active at once. Everything beyond that is queuing in the operating system scheduler or waiting on locks.

The gap between "connections the application wants to open" and "connections PostgreSQL can productively serve" is exactly what connection pooling fills.

Worked examples

Small application — 4-core server, 16GB RAM:

shared_buffers = 4GB
OS overhead   ≈ 2GB
Available     = 10GB
Per-connection ≈ 10MB (work_mem = 4MB, modest queries)

RAM ceiling   = 10GB / 10MB = ~1000 connections
Productive target = (4 cores × 2) + 2 = 10 active connections

Set max_connections = 50–100 with a connection pooler in front. The pooler handles application-side demand; PostgreSQL never has more than 10–20 connections doing actual work.

Medium application — 16-core server, 64GB RAM:

shared_buffers = 16GB
OS overhead   ≈ 4GB
Available     = 44GB
Per-connection ≈ 15MB (work_mem = 32MB, moderate queries)

RAM ceiling   = 44GB / 15MB = ~2900 connections
Productive target = (16 cores × 2) + 4 = 36 active connections

Set max_connections = 100–200 with a pooler. The productive target is 36 connections, but leave headroom for admin, monitoring, and replication connections.

Large application — 64-core server, 256GB RAM:

shared_buffers = 64GB
OS overhead   ≈ 8GB
Available     = 184GB
Per-connection ≈ 20MB (work_mem = 64MB, complex analytical queries)

RAM ceiling   = 184GB / 20MB = ~9200 connections
Productive target = (64 cores × 2) + 8 = 136 active connections

Set max_connections = 200–400 with aggressive pooling. Even on a 64-core machine, productive concurrency tops out well below what RAM allows. CPU scheduling and lock contention, not RAM, are the constraint.

The pattern across all three examples: the productive target is far below what RAM allows. RAM is not the bottleneck — CPU and lock contention are. This is the arithmetic that matters, and it points consistently in one direction.

How connection pooling changes the equation

What a pooler actually does

If you'll follow me to the next room, I believe the arrangement here will interest you.

A connection pooler (PgBouncer, PgCat, or a provider's built-in pooler) sits between your application and PostgreSQL. The application opens connections to the pooler. The pooler maintains a small pool of actual PostgreSQL backend connections.

Application (500 connections) → Pooler (30 backends) → PostgreSQL

In transaction pooling mode (the most common and most effective mode), a backend connection is assigned to a client for the duration of a single transaction. When the transaction commits or rolls back, the backend is returned to the pool and becomes available for another client.

This means 500 application connections can share 30 PostgreSQL backends — as long as most transactions are short (which, for typical web workloads, they are). The average web request holds a database transaction for 5–50ms. At 30 backends each serving a 20ms transaction, the pool can handle 1,500 transactions per second — more than sufficient for 500 application connections with typical request patterns.

Pooler sizing

The pooler's pool size should match the productive concurrency target:

pool_size = CPU_cores × 2   to   CPU_cores × 4

max_connections on PostgreSQL should then be:

max_connections = pool_size + admin_connections + monitoring_connections + replication_slots

A typical production configuration:

The application gets all the connections it wants. PostgreSQL stays in the productive concurrency range. Everyone is well served.

Transaction pooling gotchas

Transaction pooling is powerful but introduces constraints that I would be remiss not to mention. The backend connection may change between transactions, which breaks features that depend on session state:

Prepared statements:

Named prepared statements (PREPARE, EXECUTE) are bound to a specific backend. In transaction pooling mode, the next transaction may land on a different backend where the prepared statement does not exist.

-- This breaks in transaction pooling mode:
PREPARE my_query AS SELECT * FROM orders WHERE id = $1;
-- ...next transaction, different backend...
EXECUTE my_query(42);  -- ERROR: prepared statement "my_query" does not exist

Solutions: configure your ORM to use unnamed prepared statements (protocol-level, not SQL-level), use PgBouncer 1.21+ with DEALLOCATE ALL at transaction end, or disable prepared statements in your connection library.

Session-level state:

-- These do NOT survive transaction boundaries in transaction pooling:
SET search_path TO myschema;
SET statement_timeout TO '5s';
CREATE TEMPORARY TABLE temp_results (...);
SELECT pg_advisory_lock(42);
LISTEN my_channel;

Each of these commands sets state on the backend process. When the transaction ends and the backend is returned to the pool, that state is invisible to the next client that receives the backend.

Session pooling mode preserves session state but eliminates the multiplexing benefit — each application connection holds a dedicated backend for the entire session, which is equivalent to no pooling for connection reduction purposes.

Know which mode your pooler is running. The wrong mode either breaks your application quietly or provides no benefit whatsoever. Neither outcome reflects well on the household.

Serverless and the connection pressure problem

Serverless platforms (AWS Lambda, Vercel Functions, Cloudflare Workers, edge functions) create a uniquely intense connection pressure pattern, and I should note it deserves particular attention. Each function invocation typically opens a new database connection. During a traffic spike, hundreds or thousands of concurrent invocations each attempt to connect to PostgreSQL simultaneously.

Without a pooler, a moderately popular serverless application can exhaust even generous max_connections settings:

100 concurrent Lambda invocations × 1 connection each = 100 connections
Traffic spike: 500 concurrent invocations = 500 connections
Black Friday: 2000 concurrent invocations = FATAL: too many connections

No max_connections value solves this. Setting it to 5000 means 5000 forked backend processes consuming tens of gigabytes of RAM and thrashing on lock contention. This is not a configuration problem. It is an architecture problem, and the architecture requires a pooler.

Provider-specific solutions:

• Neon: Serverless driver uses HTTP-based queries (no persistent connection). The -pooler connection endpoint provides built-in PgBouncer.
• Supabase: Built-in Supavisor pooler with configurable pool modes.
• AWS RDS: RDS Proxy provides managed connection pooling with IAM authentication.
• Cloudflare Hyperdrive: Connection pooling at the edge, reducing round-trips and connection overhead.
• Vercel: Neon integration with pooled connection strings.

The serverless connection problem is architectural, not configurational. A pooler between the serverless compute and PostgreSQL is the solution — and every provider listed above has arrived at the same conclusion independently, which I find rather reassuring.

Provider-specific defaults and limits

Managed PostgreSQL providers set max_connections based on instance size. I should be clear: these defaults are RAM-based ceilings, not productivity-optimized recommendations.

Key observations:

• RDS's formula is purely RAM-based. A db.r6g.xlarge defaulting to 3,400 connections does not mean you should use 3,400 connections. It means you can if you need to, but you almost certainly should not.
• Heroku imposes hard limits that cannot be overridden. If your application exceeds the limit, the solution is connection pooling or a larger plan, not a configuration change.
• Every provider offering a built-in pooler is telling you the same thing: use the pooler, keep direct backend connections low.

Do not assume the provider's default is optimal. It is a safe maximum, not a recommendation. The distinction matters enormously.

Honest counterpoint — when you actually need high max_connections

I should be forthcoming about the limits of the advice above, because presenting it as universal would be a disservice. The guidance to keep max_connections low assumes a workload of short-lived, poolable transactions. Not every workload fits that model.

• Long-running listeners: Applications using LISTEN/NOTIFY for real-time updates hold a connection indefinitely. Each listener needs a dedicated backend that cannot be pooled.
• Long transactions: Batch processing jobs, complex reporting queries, and ETL pipelines hold connections for minutes or hours. These connections cannot be released to the pool during execution.
• Background workers: PostgreSQL's own background workers (logical replication workers, custom background workers) each consume a connection slot.
• Replication: Each streaming replication standby and each logical replication subscription holds a persistent connection.
• Monitoring and admin: Monitoring agents and admin tools need their own connection slots. These should not compete with application traffic.

The math changes for these workloads:

max_connections = pooler_pool_size
               + listener_connections
               + batch_worker_connections
               + replication_slots
               + monitoring_connections
               + admin_headroom

If your workload includes 50 listeners, 10 batch workers, 3 replicas, and 5 monitoring agents, that is 68 connections before the application pool even starts. max_connections = 50 will not work here — max_connections = 150 with a 30-connection application pool is more realistic.

The principle holds: calculate, do not guess. But the inputs to the calculation change with the workload. The formula CPU_cores × 2 applies to the application pool size, not to max_connections as a whole. Total max_connections is the application pool plus everything else that needs a connection.

The right number is the one you arrived at through arithmetic, not the one someone recommended in a blog post — including this one. Your workload is yours. The math is the math. If you will attend to the calculation, the calculation will attend to you.