How Many Database Connections Can PostgreSQL Handle?

PostgreSQL can handle thousands of concurrent database connections, with the default limit set to 100 connections. However, the practical maximum depends on your server hardware, workload, and configuration—many production systems run comfortably with 500–1,000 connections, while high-end setups can support tens of thousands with careful tuning.

Formula

The calculator above helps you estimate the rate at which connections are established or processed over a given time period. The formula is:

Rate (conn/min) = Connections ÷ Time (minutes)

Where:

• Connections = number of database connections (count)
• Time = duration in minutes (min)

The average interval per connection (in seconds) is calculated as (Time × 60) ÷ Connections. This is useful for understanding connection churn—how frequently new connections are opened.

Worked Example

Using the default values: 100 connections over 60 minutes.

1. Rate = 100 ÷ 60 = 1.67 connections per minute
2. Interval = (60 × 60) ÷ 100 = 36 seconds per connection

So with 100 connections spread over an hour, you open roughly 1.67 new connections each minute, or one every 36 seconds.

Default Connection Limit in PostgreSQL

PostgreSQL’s default max_connections setting is 100. This is defined in the postgresql.conf configuration file. The parameter controls the maximum number of concurrent connections to the database server, including connections used for maintenance and background tasks.

You can check the current value with:

SHOW max_connections;

To change it, edit postgresql.conf and restart the server. For example, to allow 500 connections:

max_connections = 500

Keep in mind that each connection consumes memory and other resources, so raising the limit without increasing hardware capacity can degrade performance.

How Many Connections Can a Server Actually Handle?

The real limit is not a single number—it depends on your hardware and workload. Key factors include:

• RAM: Each connection allocates memory for query execution, sorting, and temporary buffers. PostgreSQL uses about 2–10 MB per idle connection, and more when queries are active.
• CPU: A single query can use multiple cores, but thousands of concurrent queries will saturate the CPU.
• Disk I/O: Heavy read/write workloads will bottleneck on storage speed long before hitting connection limits.
• Connection pool: Using a pooler like PgBouncer allows thousands of application connections while keeping only a few dozen actual database connections.

As a rule of thumb, a modest server (4–8 GB RAM) can handle 200–400 connections if queries are short. A powerful server (64+ GB RAM, fast SSDs) can handle 1,000–2,000 connections. Beyond that, connection pooling is almost always recommended.

Why Too Many Connections Causes Problems

When the number of connections exceeds the server’s capacity, you may see errors like:

FATAL: sorry, too many clients already

This occurs when max_connections is reached. Even before that, high connection counts can cause:

• Memory exhaustion: Each connection consumes RAM, leading to swapping or out-of-memory errors.
• Context switching overhead: The CPU spends more time switching between processes than executing queries.
• Lock contention: More connections mean more concurrent transactions, increasing the chance of lock waits and deadlocks.

PostgreSQL uses a process-per-connection model (unlike threaded databases like MySQL), so each connection is a separate OS process. This makes memory usage more predictable but also heavier per connection.

How to Choose the Right max_connections Value

Start with the default 100 and monitor performance. If you see “too many clients” errors, increase gradually. A practical approach:

1. Check current usage: SELECT count(*) FROM pg_stat_activity;
2. Monitor memory usage with tools like htop or ps.
3. Increase max_connections by 50–100 at a time, then test under load.
4. If you need more than a few hundred, set up a connection pooler.

Also adjust shared_buffers and work_mem appropriately. A common mistake is raising max_connections without increasing shared_buffers, which can lead to poor cache utilization.

Connection Pooling: The Practical Solution

For most applications, the best way to handle thousands of concurrent users is to use a connection pooler. PgBouncer and Pgpool-II are popular tools that sit between your application and PostgreSQL.

With a pooler, your application opens many connections to the pooler, but the pooler maintains a small number of actual database connections (e.g., 50). This dramatically reduces overhead while allowing thousands of application connections.

Example: If your app needs 2,000 connections, you can set max_connections to 200 and configure PgBouncer to use 150 of them, leaving headroom for maintenance.

Comparison: PostgreSQL vs. Other Databases

PostgreSQL’s connection handling differs from other databases:

• MySQL: Uses threads, so each connection is lighter (about 1–2 MB). MySQL can handle thousands of connections more easily on the same hardware, but may hit other limits like thread stack size.
• SQLite: Not a client-server database; only one writer at a time, so connection limits are irrelevant.
• MongoDB: Also uses a thread-per-connection model, but the default max is often higher (e.g., 10,000).

PostgreSQL’s process model is more robust against crashes (one connection crashing doesn’t take down others), but it costs more memory per connection.

Common Mistakes and How to Avoid Them

• Setting max_connections too high: This can cause out-of-memory errors. Always calculate memory: shared_buffers + max_connections * work_mem should fit in RAM.
• Ignoring connection leaks: Applications that don’t close connections properly will exhaust the pool. Use connection timeouts and monitor pg_stat_activity.
• Forgetting to restart after changing config: Some parameters require a restart; use ALTER SYSTEM and restart or reload as needed.
• Not using a pooler for high concurrency: If you need more than ~500 connections, a pooler is almost always the right choice.

Frequently Asked Questions

What happens when I hit max_connections?

New connection attempts are rejected with the error “sorry, too many clients already”. Existing connections continue to work, but no new connections can be established until some are closed.

Can I set max_connections to 10000?

Technically yes, but it’s rarely wise. Each connection uses memory, so 10,000 connections could consume 20–100 GB of RAM just for idle connections. You’d need a very large server, and even then, performance would likely suffer from context switching.

Does using a connection pooler reduce the number of connections?

Yes. The pooler keeps a fixed number of backend connections (e.g., 50) and multiplexes thousands of client connections onto them. This reduces the load on PostgreSQL significantly.

How do I check how many connections are currently open?

Run SELECT count(*) FROM pg_stat_activity; or SELECT usename, application_name, count(*) FROM pg_stat_activity GROUP BY 1,2; to see connections by user or application.

Is there a limit on connections per user or database?

Yes, you can set connection_limit per role or database using ALTER ROLE ... CONNECTION LIMIT or ALTER DATABASE ... CONNECTION LIMIT. The default is -1 (no limit).

What is the ideal max_connections for a small web app?

For a typical small web app with a few hundred users, 100–200 is usually sufficient, especially if you use a connection pooler. Start with 100 and monitor.

Related

• Shopify Print All Orders: How It Works
• Twitch Chat Filter: How to Turn It Off
• Assessing Indian Contract Manufacturers for Export

in t | Total time | min | 60 | 0
in c | Connections | count | 100 | 1
out rate | Connections per minute | conn/min | c / t | 2
out interval | Avg time per connection | sec | (t * 60) / c | 2