PostgreSQL 53200 Error: Causes and Solutions Complete Guide

PostgreSQL Error 53200: Out of Memory

PostgreSQL error code 53200 occurs when the database server fails to allocate sufficient memory from the operating system during query execution, sorting, hashing, or other internal operations. This error can stem from misconfigured memory parameters, runaway queries consuming excessive RAM, or genuine physical memory exhaustion on the host machine. Understanding its root causes is critical for maintaining a stable production environment.

---

Top 3 Causes

1. Misconfigured work_mem

work_mem controls the amount of memory allocated per sort or hash operation, per node, per session. If set too high with many concurrent connections, total memory consumption can easily exceed physical RAM.

-- Check current work_mem setting
SHOW work_mem;

-- Check all memory-related settings at once
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('work_mem', 'shared_buffers', 'maintenance_work_mem', 'max_connections');

-- Adjust work_mem at session level safely
SET work_mem = '32MB';

-- Apply a system-wide change and reload
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

2. Heavy Queries with Sorting, Grouping, or Window Functions

Large ORDER BY, GROUP BY, DISTINCT, or WINDOW FUNCTION operations on millions of rows force PostgreSQL to buffer intermediate result sets in memory. Without proper indexes, these operations trigger sequential scans and in-memory sorts that can exhaust available RAM quickly.

-- Diagnose memory-intensive queries using EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS)
SELECT department_id, SUM(salary), COUNT(*)
FROM employees
GROUP BY department_id
ORDER BY SUM(salary) DESC;

-- Monitor queries generating temp files (sign of work_mem pressure)
SELECT query,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_statements
WHERE temp_bytes > 0
ORDER BY temp_bytes DESC
LIMIT 10;

-- Add a targeted index to reduce sort/scan overhead
CREATE INDEX CONCURRENTLY idx_employees_dept
    ON employees(department_id, salary);

3. Too Many Concurrent Connections

Each PostgreSQL backend process consumes memory independently. With max_connections set too high and no connection pooler in place, the cumulative memory footprint grows rapidly, leading to OOM conditions at the OS level.

-- Check current active connections vs. max allowed
SELECT count(*) AS active,
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_allowed
FROM pg_stat_activity;

-- Identify long-running or idle-in-transaction sessions consuming resources
SELECT pid, usename, state, query_start,
       now() - query_start AS duration, left(query, 80) AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Terminate a specific blocking/runaway session if needed
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = <target_pid>;

---

Quick Fix Solutions

-- 1. Temporarily reduce work_mem for the current session
SET work_mem = '16MB';

-- 2. Use a cursor for large result sets to avoid loading everything into memory
BEGIN;
DECLARE big_cursor CURSOR FOR
    SELECT * FROM large_table ORDER BY created_at DESC;
FETCH 500 FROM big_cursor;
CLOSE big_cursor;
COMMIT;

-- 3. Enable temp file logging to catch memory-hungry queries automatically
ALTER SYSTEM SET log_temp_files = 0;  -- logs all temp file usage
SELECT pg_reload_conf();

---

Prevention Tips

1. Right-size memory parameters based on concurrency.
Use the formula work_mem = available_RAM / (max_connections × avg_nodes_per_query) as a starting point. For a 16 GB server with 100 connections, a conservative work_mem = 32MB is a reasonable baseline. Always deploy PgBouncer or a similar connection pooler to keep actual backend counts low.

2. Monitor temp file usage and set up alerts.
Temp file generation is the earliest warning sign of memory pressure. Set log_temp_files = 0 in postgresql.conf to log every temp file creation. Integrate with Prometheus and pg_stat_statements to alert when temp file sizes exceed a defined threshold, allowing you to tune queries before they escalate to a full OOM crash.

---

Related Error Codes

Code	Name	Relationship
53100	disk_full	Temp file writes fail when disk is also exhausted alongside memory
53300	too_many_connections	Excess connections are a direct driver of 53200
57P02	crash_shutdown	Triggered after Linux OOM Killer terminates the PostgreSQL process

---

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