3,500 connections to PostgreSQL, and almost none of them working

A problem

One day, our application started failing with an error I hadn’t seen before:

FATAL:  sorry, too many clients already

PostgreSQL has a hard limit on how many connections it accepts, set by max_connections. We had hit it. New requests couldn’t get a connection at all, so they failed.

My first reaction was: how much traffic are we getting? The answer was: not much more than usual. So where were all these connections coming from?

Looking at what the connections were doing

PostgreSQL will tell you exactly what every connection is doing, through pg_stat_activity:

SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'app'
GROUP BY state
ORDER BY count(*) DESC;

        state         | count
----------------------+-------
 idle                 |  3104
 idle in transaction  |   341
 active               |    38

About 3,500 connections. And only 38 of them were actually doing anything.

idle: connected, but not running anything. Just holding a slot.
idle in transaction: worse. Someone ran BEGIN, maybe a query or two, and then… nothing. The transaction is still open. These don’t just take a slot. They hold an old snapshot, which stops VACUUM from cleaning up, and they may be holding locks.
active: actually running a query.

So we weren’t running out of capacity. We were running out of slots, because almost every slot was taken by a connection doing nothing.

Why a Tornado app ends up like this

Our application ran on Tornado, Python’s async web framework. Tornado apps are long-running processes, and the way ours was written, connections to PostgreSQL were opened and kept around for a long time, and not always handed back properly.

Looking at the code and the connection timestamps, a few patterns explained most of it:

Connections opened and never closed. Some code paths opened a connection for a task and just left it there when they were done.
Transactions that never finished. A request would open a transaction, hit an error or an early return, and never reach the commit() or rollback(). The connection stayed idle in transaction until the process restarted.
Many processes. We ran several Tornado processes per server, on several servers. A few leaked connections per process, multiplied across all of them, adds up fast.

Fixing every code path was the real long-term fix, and we did go through them. But that takes time, and in the meantime I needed the database to protect itself.

Fix 1: let PostgreSQL close what’s been abandoned

PostgreSQL has timeouts for exactly this situation:

# close a session that has been idle *inside a transaction* for too long
idle_in_transaction_session_timeout = '5min'

# close a session that has been idle (outside a transaction) for too long (PostgreSQL 14+)
idle_session_timeout = '30min'

idle_in_transaction_session_timeout was the important one. A transaction that’s been sitting open for five minutes in our app was never going to finish. Killing it releases its locks, lets VACUUM do its job, and frees the slot.

You can also set these per role, if different applications need different limits:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

The number of connections dropped right away. But it created a new problem.

Fix 2: the app has to notice when a connection is gone

When PostgreSQL closes an idle connection, it doesn’t ask the application first. From the app’s point of view, the connection object still looks fine. It finds out the connection is dead only when it tries to use it:

psycopg2.OperationalError: server closed the connection unexpectedly

So now, instead of “too many connections”, some requests failed because they picked up a connection that the server had already closed. The same thing happens if a network device in between quietly drops long-idle TCP connections, or if the database restarts.

I found a great article from Bitworks about this exact problem with MySQL and Tornado. The ideas carried over to PostgreSQL nicely:

Check the connection before using it, and reconnect if it’s dead.

def get_connection():
    global conn
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT 1")
    except (psycopg2.OperationalError, psycopg2.InterfaceError):
        conn = psycopg2.connect(DSN)
    return conn

Ping long-lived connections periodically, so dead ones are found and replaced in the background instead of during a customer’s request. Tornado has a built-in way to run something on a timer:

from tornado.ioloop import IOLoop, PeriodicCallback

def keepalive():
    get_connection()  # pings, and reconnects if needed

PeriodicCallback(keepalive, 60_000).start()  # every 60 seconds
IOLoop.current().start()

Turn on TCP keepalives, so the operating system detects a dead connection on its own, even if the application never touches it. The client side, through libpq connection parameters:

conn = psycopg2.connect(
    DSN,
    keepalives=1,
    keepalives_idle=60,      # start probing after 60s of silence
    keepalives_interval=10,  # probe every 10s
    keepalives_count=5,      # give up after 5 failed probes
)

And the server side, in postgresql.conf, so PostgreSQL also notices clients that vanished without saying goodbye:

tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 5

Together: PostgreSQL closes what’s been abandoned, and the application quietly reconnects instead of failing.

What I’d do differently now

The timeouts and keepalives fixed the incident. But looking back, the deeper issue was that each process managed its own connections with no limit. Today I’d put a connection pooler in front of PostgreSQL, like PgBouncer, so thousands of client connections share a small, fixed number of real database connections. And I’d use a pool in the application too, so connections are always borrowed and returned, never just left lying around.

And I’d add an alert on the number of connections in idle in transaction state. That number creeping up is an early warning that some code path forgot to commit.

What I took away

“Too many connections” doesn’t always mean “too much traffic”. Look at what the connections are doing before adding capacity.
idle in transaction is the dangerous state. It wastes a slot, holds locks, and blocks VACUUM.
If the database closes connections, the application must expect it. Check before use, ping in the background, and let TCP keepalives catch the rest.