Critical severitycapacity
PostgreSQL Error:
53200
What does this error mean?
SQLSTATE 53200 means PostgreSQL tried to allocate memory for an operation — a sort, hash join, CTE materialization, or index build — and the OS refused because the server's available RAM was exhausted. In a data-pipeline context this typically surfaces when an ADF Copy Activity, dbt model, or scheduled ETL query hits a table that grew beyond what the server can handle in a single operation. The engineer sees the query terminate mid-execution with ERROR: out of memory and the client receives a fatal connection error. Downstream tables that depend on the failed query remain stale until the issue is resolved and the pipeline is re-run.
Common causes
- 1A query performing a large sort or hash join that exceeds the per-operation `work_mem` limit, forcing PostgreSQL to request more memory than the OS can provide.
- 2Too many concurrent connections — each connection consumes its own `work_mem` allocation per sort/hash node, so 100 connections each sorting 256 MB can request 25 GB+ simultaneously.
- 3An `ORDER BY` or `DISTINCT` on an unindexed column over a multi-million-row table, forcing a full in-memory sort instead of an index scan.
- 4CTE materialization (`WITH` queries) pinning large intermediate result sets in memory rather than streaming them — common in dbt models that chain multiple CTEs.
- 5A `CREATE INDEX CONCURRENTLY` or `REINDEX` operation on a large table consuming `maintenance_work_mem` (default 64 MB, but often set much higher) while other queries compete for the same RAM.
- 6The Linux OOM killer terminating the PostgreSQL postmaster because `vm.overcommit_memory` is set to 0 (heuristic) and the kernel decides PostgreSQL is the largest consumer.
How to fix it
- 1Step 1: Identify the query that caused the OOM. Check `pg_stat_activity` and PostgreSQL logs: `SELECT pid, query, state, backend_start FROM pg_stat_activity WHERE state != 'idle' ORDER BY backend_start;`
- 2Step 2: Check current memory settings: `SHOW work_mem; SHOW shared_buffers; SHOW maintenance_work_mem;` — compare against total server RAM. A safe rule of thumb: `work_mem` × expected concurrent sorts should not exceed 25% of total RAM.
- 3Step 3: Lower `work_mem` at the session level for heavy queries instead of raising the server default: `SET LOCAL work_mem = '128MB';` in the specific transaction. This prevents one query from starving others.
- 4Step 4: Kill connections that are stuck consuming memory: `SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '30 minutes';`
- 5Step 5: Rewrite memory-intensive queries — replace `ORDER BY` on unindexed columns with indexed lookups, convert CTEs to subqueries (PostgreSQL 12+ can inline them), and use `LIMIT` where possible to reduce intermediate result set sizes.
- 6Step 6: Deploy a connection pooler (PgBouncer in transaction mode) to cap concurrent connections. Set `max_client_conn = 200` and `default_pool_size = 20` as a starting point.
- 7Step 7: On managed services (Azure Database for PostgreSQL, Amazon RDS), scale to the next tier if the instance has less than 8 GB RAM for analytical workloads. On self-managed servers, set `vm.overcommit_memory = 2` and `vm.overcommit_ratio = 80` to prevent the OOM killer from targeting PostgreSQL.
Example log output
2026-05-11 14:32:07.891 UTC [12847] ERROR: out of memory
2026-05-11 14:32:07.891 UTC [12847] DETAIL: Failed on request of size 67108864 in memory context "ExecutorState".
2026-05-11 14:32:07.892 UTC [12847] STATEMENT: SELECT customer_id, SUM(amount) FROM transactions GROUP BY customer_id ORDER BY SUM(amount) DESC;