PostgreSQL 53200 Error: Causes and Solutions Complete Guide

PostgreSQL Error 53200: Out of Memory

PostgreSQL error code 53200 (out of memory) occurs when the database server or a backend process fails to allocate the memory it needs from the operating system to complete an operation. This error can appear during memory-intensive operations such as large sorts, hash joins, or bulk aggregations, and in severe cases it can cause entire backend processes to crash. Unlike application-level memory errors, this issue often reflects a systemic resource problem that requires tuning at both the query and server configuration level.

---

Top 3 Causes

1. Oversized or Misconfigured work_mem

work_mem is applied per operation, per query node — not per connection. A single complex query with multiple Sort and Hash nodes can consume several multiples of the configured work_mem value. Multiply that by hundreds of concurrent sessions and the total memory demand can overwhelm your server.

-- Check current work_mem setting
SHOW work_mem;

-- Reduce work_mem for the current session before running a heavy query
SET work_mem = '32MB';

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY total DESC;

-- Reset to default after the session
RESET work_mem;

2. Memory-Intensive Queries Without Proper Indexes

Queries using ORDER BY, GROUP BY, DISTINCT, or large hash joins must load significant data into memory. When indexes are missing or table statistics are stale, the query planner may choose inefficient plans that drag millions of rows into memory unnecessarily.

-- Analyze the execution plan to find memory-hungry nodes
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > NOW() - INTERVAL '6 months'
GROUP BY c.name
ORDER BY total DESC;

-- Add indexes to reduce Sort/Hash operations
CREATE INDEX CONCURRENTLY idx_orders_customer_date
ON orders (customer_id, order_date);

-- Refresh table statistics to help the planner
ANALYZE orders;
ANALYZE customers;

3. Global Memory Parameters Exceeding System RAM

When global settings like shared_buffers, maintenance_work_mem, or autovacuum_work_mem are configured too aggressively relative to available RAM, the OS may invoke the OOM Killer, terminating PostgreSQL backend processes. Running VACUUM, CREATE INDEX, and heavy queries simultaneously can spike memory consumption far beyond what was planned.

-- Review all memory-related settings at once
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'autovacuum_work_mem',
    'effective_cache_size',
    'wal_buffers'
)
ORDER BY name;

-- Apply updated config without a full restart
SELECT pg_reload_conf();

---

Quick Fix Solutions

Immediate actions when the error occurs:

-- 1. Identify active memory-heavy queries
SELECT pid,
       usename,
       state,
       LEFT(query, 100) AS query_preview,
       query_start
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start ASC;

-- 2. Terminate a problematic query safely
SELECT pg_cancel_backend(<pid>);

-- 3. Use a cursor to process large datasets in chunks
BEGIN;
DECLARE big_cursor CURSOR FOR
    SELECT order_id, amount FROM orders ORDER BY order_id;
FETCH 500 FROM big_cursor;
-- Repeat FETCH as needed
CLOSE big_cursor;
COMMIT;

Recommended postgresql.conf values for a 32GB RAM server:

Parameter	Recommended Value
shared_buffers	8GB (25% of RAM)
work_mem	32–64MB (conservative)
maintenance_work_mem	1–2GB
effective_cache_size	24GB (75% of RAM)

---

Prevention Tips

1. Monitor memory-intensive queries continuously

Install pg_stat_statements and query it regularly to catch expensive queries before they cause outages.

-- Top memory/time-consuming queries
SELECT LEFT(query, 80) AS query,
       calls,
       ROUND((total_exec_time / calls)::numeric, 2) AS avg_ms,
       rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 5
ORDER BY total_exec_time DESC
LIMIT 10;

2. Limit concurrent connections with a connection pooler

Deploy PgBouncer in transaction pooling mode to cap the number of real PostgreSQL sessions. Unbounded connections multiply work_mem consumption unpredictably. Also, keep max_connections set to a realistic value based on your server's RAM capacity.

-- Monitor active connection counts by state
SELECT state, COUNT(*) AS count
FROM pg_stat_activity
GROUP BY state
ORDER BY count DESC;

By combining conservative work_mem settings, proper indexing, regular ANALYZE runs, and connection pooling, you can reliably prevent error 53200 from disrupting your PostgreSQL workloads.

---

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