Postgres connection pool exhaustion caused by a zombie canary instance

I finally tracked down a persistent "too many clients" error that only cropped up on Friday afternoons. It was a classic case of the database reporting the truth while the application metrics lied.

The failure manifested as intermittent 500 errors in our checkout service, hitting roughly 2% of requests. The logs were blunt:

error: sorry, too many clients already

Our connection pool was capped at 20, and our metrics showed we were only using about 6 connections during those windows. There was no traffic spike, yet Postgres was refusing the 21st connection.

Why the obvious suspects were wrong

I first assumed we had a slow query holding connections open. I checked pg_stat_activity during the outage, but I found 20 connections sitting in an idle state. Not idle in transaction, just idle. If they were truly idle, the pool should have been recycling them, so a slow query wasn't the culprit.

Then I looked for a connection leak in the application code. We had a report-generation endpoint using raw queries outside the ORM, which is usually where .release() calls get missed. I wrapped every connection call in a try/finally block and ran a load test in staging for an hour, but the connection count remained flat. No leak in the current build.

I even checked for rogue cron jobs. We found a weekly analytics export running Fridays at 1 pm, but that job used a separate DB user. The 20 idle connections were all tied to the checkout service user.

The root cause was a zombie instance

The problem wasn't the code running in production, but a forgotten canary instance from months ago. It was still alive but not receiving traffic, meaning it never appeared in our request metrics or error dashboards.

This zombie instance had two issues:
1. It maintained its own pool of 20 connections.
2. A bug in its health-check pinger was opening raw connections every few seconds without closing them.

Because an auto-scaling policy recycled idle instances every week, this zombie process restarted on Mondays, clearing the leak. By Friday afternoon, the leaked connections from the pinger had finally pushed the total database connection count to the limit.

How to avoid this

The fix was simply decommissioning the old instance. Total database connections dropped from a Friday peak of 94 back down to a steady 12.

If you are debugging similar pool issues, don't trust your app metrics. Run this query in Postgres to see exactly who is holding the connections:

SELECT application_name, state, count(*) 
FROM pg_stat_activity 
GROUP BY application_name, state;

Grouping by application_name makes it immediately obvious when a "ghost" service is eating your slots.