PostgreSQL 53300 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 53300: Too Many Connections

PostgreSQL error code 53300 (too_many_connections) is thrown when the number of client connections attempts to exceed the max_connections limit defined in postgresql.conf. Once this ceiling is hit, PostgreSQL immediately rejects all new incoming connections, causing cascading failures across your application. This is one of the most common and painful production incidents for PostgreSQL deployments of any scale.

Top 3 Causes

1. No Connection Pooling (or Misconfigured Pool)

Every application instance opens its own direct connections without a pooler like PgBouncer. In microservice architectures, this becomes catastrophic fast — 10 services × 50 connections each = 500 connections, easily exhausting a default max_connections = 100 setup.

-- Check current connections grouped by application
SELECT 
    application_name,
    usename,
    state,
    count(*) AS conn_count
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY application_name, usename, state
ORDER BY conn_count DESC;

-- See how close you are to the limit
SELECT 
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn,
    count(*) AS current_conn,
    round(count(*) * 100.0 / 
        (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 2) AS usage_pct
FROM pg_stat_activity;

2. Idle and "Idle in Transaction" Connection Accumulation

Applications that fail to close connections properly leave them in idle or idle in transaction state indefinitely. The idle in transaction state is especially dangerous — it holds locks and blocks other queries while consuming a connection slot.

-- Find long-running idle in transaction sessions
SELECT 
    pid,
    usename,
    application_name,
    state,
    now() - state_change AS idle_duration,
    left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction')
  AND state_change < now() - interval '5 minutes'
ORDER BY idle_duration DESC;

-- Terminate dangerous idle in transaction connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '5 minutes'
  AND pid <> pg_backend_pid();

3. max_connections Set Too Low (or Too High Without Enough RAM)

The default max_connections = 100 is designed for development, not production. However, blindly increasing it causes memory pressure — each connection consumes roughly 5–10 MB of RAM. A server with 8 GB RAM cannot safely handle 1,000 connections.

-- Check all relevant connection settings
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
    'max_connections',
    'superuser_reserved_connections',
    'work_mem',
    'shared_buffers'
);

-- Estimate memory per connection (rough calculation)
SELECT 
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn,
    (SELECT setting::int FROM pg_settings WHERE name = 'work_mem') AS work_mem_kb,
    round(
        (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') *
        (SELECT setting::int FROM pg_settings WHERE name = 'work_mem') / 1024.0 / 1024.0,
        2
    ) AS potential_work_mem_gb;

Quick Fix Solutions

-- 1. Kill all idle connections older than 10 minutes (emergency use)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - interval '10 minutes'
  AND pid <> pg_backend_pid();

-- 2. Set automatic timeout for idle in transaction sessions
ALTER DATABASE your_database 
    SET idle_in_transaction_session_timeout = '5min';

-- 3. Apply timeout at role level for application users
ALTER ROLE app_user 
    SET idle_in_transaction_session_timeout = '300000';  -- 5 min in ms

-- 4. Confirm the setting took effect
SELECT name, setting, unit 
FROM pg_settings 
WHERE name = 'idle_in_transaction_session_timeout';

Prevention Tips

1. Deploy PgBouncer in transaction mode. Place PgBouncer between your application and PostgreSQL. In transaction pooling mode, thousands of app-side connections can be multiplexed into a small number of real server connections (e.g., 1,000 app connections → 50 DB connections). This is the single most effective mitigation strategy.

2. Set guardrail timeouts globally in postgresql.conf:

-- Recommended postgresql.conf settings for production
-- idle_in_transaction_session_timeout = '5min'
-- statement_timeout = '30s'
-- lock_timeout = '10s'

-- Monitor with a reusable view
CREATE OR REPLACE VIEW v_conn_monitor AS
SELECT
    state,
    count(*) AS total,
    max(now() - state_change) AS max_idle_time,
    round(count(*) * 100.0 / 
        (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 2
    ) AS pct_of_max
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY state;

Set up alerts when connection usage exceeds 75–80% of max_connections using tools like Prometheus pg_stat_activity exporter, Datadog, or CloudWatch RDS metrics. Catching the trend early gives you time to act before the server starts rejecting connections entirely.

Related Errors

Code	Name	Relationship
`53200`	`out_of_memory`	Triggered when `max_connections` is set too high for available RAM
`57014`	`query_canceled`	Raised by `statement_timeout`, which helps prevent connection exhaustion
`55P03`	`lock_not_available`	Often co-occurs when idle in transaction connections hold locks

📖 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.