PostgreSQL 53400 Error: Causes and Solutions Complete Guide

PostgreSQL Error 53400: Configuration Limit Exceeded

PostgreSQL error 53400 (configuration_limit_exceeded) occurs when the database server hits a hard boundary defined by its configuration parameters. This commonly happens when the number of client connections surpasses max_connections, or when a transaction attempts to acquire more locks than max_locks_per_transaction allows. Understanding which limit was breached is the first and most critical step toward resolution.

---

Top 3 Causes

1. Exceeding max_connections

This is by far the most common trigger for error 53400. Without a connection pooler, every application thread opens its own direct connection, and the pool drains fast under load.

-- Check current connection usage vs. limit
SELECT
    current_setting('max_connections')::int AS max_connections,
    count(*) AS current_connections,
    round(count(*) * 100.0 / current_setting('max_connections')::int, 2) AS usage_pct
FROM pg_stat_activity;

-- Kill long-running idle connections immediately
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '5 minutes'
  AND pid <> pg_backend_pid();

2. Exceeding max_locks_per_transaction

Each transaction can hold a limited number of object locks. Partitioned tables are a silent killer here — a single TRUNCATE or VACUUM on a table with hundreds of partitions tries to lock every partition at once.

-- Check current lock count per process
SELECT pid, count(*) AS locks_held
FROM pg_locks
WHERE granted = true
GROUP BY pid
ORDER BY locks_held DESC
LIMIT 10;

-- Check the current setting
SHOW max_locks_per_transaction;

-- Increase the limit (requires server restart)
ALTER SYSTEM SET max_locks_per_transaction = 128;

3. Memory Configuration Limits (work_mem / temp_buffers)

When too many sessions run heavy sort or hash operations simultaneously, the aggregate memory demand can push PostgreSQL past internal thresholds, resulting in a configuration limit error.

-- Check memory settings
SHOW work_mem;
SHOW shared_buffers;
SHOW temp_buffers;

-- Temporarily raise work_mem for a single session (no restart needed)
SET work_mem = '128MB';

-- Apply globally with a config reload
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

---

Quick Fix Solutions

-- Step 1: Identify what is consuming resources
SELECT pid, usename, application_name, state, query_start, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

-- Step 2: Terminate blocking or stuck backends
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND state = 'idle in transaction'
  AND state_change < NOW() - INTERVAL '2 minutes';

-- Step 3: Temporarily raise max_connections (restart required)
ALTER SYSTEM SET max_connections = 300;

-- Step 4: Raise lock limit for partitioned table workloads (restart required)
ALTER SYSTEM SET max_locks_per_transaction = 128;

⚠️ Both max_connections and max_locks_per_transaction require a full server restart, not just pg_reload_conf().

---

Prevention Tips

Deploy a connection pooler. PgBouncer in transaction-mode pooling can reduce the number of actual PostgreSQL connections by 10x or more, making max_connections limits far less likely to be hit in practice.

Monitor before you hit the wall. Set up alerting when connections exceed 75–80% of max_connections, and when lock counts per transaction approach the configured limit.

-- Reusable health-check query
SELECT
    'connections' AS metric,
    count(*) AS current_value,
    current_setting('max_connections')::int AS limit_value,
    round(count(*) * 100.0 / current_setting('max_connections')::int, 1) AS pct_used
FROM pg_stat_activity
UNION ALL
SELECT
    'locks',
    count(*),
    current_setting('max_locks_per_transaction')::int * current_setting('max_connections')::int,
    NULL
FROM pg_locks
WHERE granted = true;

Review partition counts regularly. If a partitioned table grows beyond 100 partitions, proactively increase max_locks_per_transaction and implement a partition lifecycle policy (detach, archive, or drop old partitions).

---

Related Errors

Code	Name	Notes
53000	insufficient_resources	Parent class of 53400
53100	disk_full	Disk space exhausted
53200	out_of_memory	Memory allocation failure
55P03	lock_not_available	Often co-occurs with lock limit issues

---

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