umzzil nng

Posted on Sep 19 Originally published at oraerror.com

PostgreSQL 53300 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 53300: Too Many Connections — Causes, Fixes & Prevention

What Is This Error?

PostgreSQL error code 53300 too_many_connections is thrown when the number of client connections attempting to connect to the server exceeds the value defined by the max_connections parameter in postgresql.conf. By default, PostgreSQL allows only 100 simultaneous connections, and every new connection request beyond that limit is immediately rejected. This error is especially common in web applications experiencing traffic spikes or systems lacking a proper connection pooling strategy.

Top 3 Causes

1. No Connection Pooling in Place

Applications that open a new database connection per request — without using a pooler like PgBouncer or HikariCP — quickly exhaust available connection slots. Each PostgreSQL connection consumes roughly 5–10 MB of memory, so simply raising max_connections is not a scalable solution.

-- Check current connection count vs. max allowed
SELECT 
    COUNT(*) AS current_connections,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') - COUNT(*) AS free_slots
FROM pg_stat_activity;

2. Idle and Leaked Connections Accumulating

When applications fail to return connections to the pool (connection leaks) or pool idle_timeout is too long, idle connections pile up and occupy slots that could serve active requests.

-- Identify idle connections and how long they've been idle
SELECT 
    pid,
    datname,
    usename,
    state,
    now() - state_change AS idle_duration,
    application_name
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction')
ORDER BY idle_duration DESC;

-- Terminate idle connections older than 5 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - INTERVAL '5 minutes'
  AND pid <> pg_backend_pid();

3. Long-Running or Stuck Transactions

Transactions left open due to application bugs (missing COMMIT/ROLLBACK) keep connections occupied indefinitely. These sessions appear as idle in transaction in pg_stat_activity and can also block other queries by holding locks.

-- Find transactions open longer than 10 minutes
SELECT 
    pid,
    now() - xact_start AS txn_duration,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > INTERVAL '10 minutes'
ORDER BY txn_duration DESC;

-- Terminate stuck transactions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - INTERVAL '10 minutes';

Quick Fix Solutions

Step 1 — Immediate relief: Terminate idle and stuck sessions (shown above).

Step 2 — Adjust per-database or per-role limits:

-- Limit connections for a specific database
ALTER DATABASE myapp_db CONNECTION LIMIT 80;

-- Limit connections for a specific role
ALTER ROLE app_user CONNECTION LIMIT 50;

Step 3 — Set automatic timeouts to prevent recurrence:

-- Auto-kill idle-in-transaction sessions after 10 minutes (globally)
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';

-- Apply per role
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

-- Reload config without restart
SELECT pg_reload_conf();

Prevention Tips

1. Always Use a Connection Pooler

Deploy PgBouncer in transaction pool mode between your application and PostgreSQL. This allows thousands of application-level connections to share a small pool of actual server connections efficiently.

-- After PgBouncer setup, monitor real server-side connections
SELECT COUNT(*) AS server_connections
FROM pg_stat_activity
WHERE application_name = 'pgbouncer';

2. Monitor Connection Usage and Alert Early

Set up monitoring (Prometheus + Grafana, Datadog, etc.) using the query below, and trigger alerts when usage exceeds 80% of max_connections.

-- Connection usage percentage — alert if > 80%
SELECT 
    ROUND(
        COUNT(*) * 100.0 / 
        (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 
    2) AS connection_usage_pct
FROM pg_stat_activity;

Related Errors

Code	Name	Notes
`53200`	`out_of_memory`	Often co-occurs when `max_connections` is set too high
`53100`	`disk_full`	Same `insufficient_resources` error class
`57P03`	`cannot_connect_now`	Server unavailable, similar client-facing impact
`08006`	`connection_failure`	Network-level connection drop, sometimes confused with 53300

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community