CockroachDB error 53200: SQL memory budget exhausted and query rejection

Error 53200 is CockroachDB’s PostgreSQL-compatible signal that the per-node SQL memory budget has run out. Applications see SQLSTATE 53200 (“insufficient resources”) with text such as “memory budget exceeded” and byte counts showing what was requested, what is allocated, and what the budget allows.

The SQL memory budget is enforced per-node, bounded by --max-sql-memory (default 25% of system RAM). When a node’s SQL execution layer exhausts its budget, queries on that node either spill to temporary disk storage (slow) or are rejected outright with 53200. One large analytical query on a single gateway node can starve every other session connected to that node, even if the rest of the cluster has memory to spare.

What this means

The SQL execution memory pool covers sorts, hash joins, window functions, result buffering, and other execution operators. The sql_mem_root_current gauge tracks how much of the budget is currently consumed.

As utilization approaches the limit, two things happen in sequence:

1. Spill to disk. The SQL engine spills intermediate results (sort buffers, hash tables) to temporary storage on disk. This keeps queries running but causes dramatic latency spikes. A large sort that spills to disk can be 100x slower than its in-memory equivalent.

2. Query rejection. When the budget is fully exhausted, the node rejects queries that need additional memory with error 53200. The rejection is immediate and visible to the application.

The budget is shared across all sessions on a node with no per-query memory reservation. A single unbounded SELECT ... ORDER BY on a large table can consume the entire budget, causing every other query on that node to spill or fail. This is why 53200 often appears as a burst affecting many sessions simultaneously, even though only one query is the root cause.

flowchart TD
    A["Large query or connection burst"] --> B["sql_mem_root_current climbs"]
    B --> C{"Budget vs --max-sql-memory"}
    C -->|"70-90%"| D["Queries spill to temp storage"]
    D --> E["P99 latency spikes from disk I/O"]
    C -->|"At limit"| F["Queries rejected: error 53200"]
    F --> G["Per-node: all sessions starved"]
    G --> H["Client retries amplify load"]
    H --> B

Rejected queries trigger client retries, which add more load to the same or neighboring nodes, driving memory utilization back up.

Common causes

Quick checks

Run these read-only checks on the affected node.

# Check current SQL memory utilization against the budget
curl -s http://localhost:8080/_status/vars | grep sql_mem_root_current

# Check SQL error rate (includes 53200)
<!-- TODO: verify sql_failure_count is the correct metric name -->
curl -s http://localhost:8080/_status/vars | grep sql_failure_count

# Check process RSS against available memory
curl -s http://localhost:8080/_status/vars | grep -E 'sys_rss|sys_go_allocbytes|sys_cgo_allocbytes'

# Check Go GC pause accumulation
curl -s http://localhost:8080/_status/vars | grep -E 'sys_gc_pause_ns|sys_gc_count'

# Check active connections and goroutine count
curl -s http://localhost:8080/_status/vars | grep -E 'sql_conns|sys_goroutines'

# Check the configured --max-sql-memory flag
ps -p $(pgrep -x cockroach) -o args= | tr ' ' '\n' | grep max-sql-memory

For SQL-level diagnostics (admin privileges required, table structure is version-sensitive):

<!-- TODO: verify column names (key, count, failed) for crdb_internal.node_statement_statistics across versions -->
-- Find failed statements by frequency
SELECT key, count FROM crdb_internal.node_statement_statistics
WHERE failed = true ORDER BY count DESC LIMIT 20;

-- Check active sessions for long-running queries
SELECT * FROM [SHOW CLUSTER SESSIONS]
WHERE active_queries != '' ORDER BY start DESC;

-- Check per-operator spill threshold
SHOW CLUSTER SETTING sql.distsql.temp_storage.workmem;

How to diagnose it

1. Identify which nodes are affected. Pull sql_mem_root_current from every node, not just one. One node may be at 95% while others sit at 30%. The node hitting the ceiling is your gateway for expensive queries.

2. Find the memory-consuming query. Query crdb_internal.node_statement_statistics on the affected node. Look for statements with high row counts or full scan patterns.

If active query dumps are enabled (diagnostics.active_query_dumps.enabled, on by default), check the heap_profiler directory for activequeryprof CSV files written during memory pressure events.

3. Correlate memory with errors. Overlay sql_mem_root_current with sql_failure_count. If errors spike when memory crosses the budget threshold, budget exhaustion is the direct cause. If errors appear before memory is high, look for a different error source.

4. Check for connection pressure. Compare sql_conns against baseline. A failover or client retry storm can double or triple connections on surviving nodes, with each connection adding memory overhead (goroutine stacks at approximately 8 KB minimum plus session state).

5. Assess GC pressure. If sql_mem_root_current is high and sys_gc_pause_ns is accumulating rapidly, the node may be approaching a Memory Pressure -> GC Thrashing -> Raft Liveness Failure cascade. Individual GC pauses approaching 500 ms are a warning sign. Pauses exceeding 1 second risk missing Raft tick intervals and triggering liveness loss.

6. Check CockroachDB version. Several versions had memory accounting bugs that cause false 53200 errors.

Versions before v24.1 had a bug where statistics collection could trigger “memory budget exceeded” when the budget was nearly full. Version v23.1 fixed a memory leak introduced in v22.2.9 that accumulated with connection churn. If you are seeing 53200 on queries that should not be memory-intensive, check the release notes for your version.

Metrics and signals to monitor

Fixes

Kill the runaway query

If a single query is consuming the budget, cancel it:

CANCEL QUERY '<query_id>';

Find the query ID from SHOW CLUSTER SESSIONS or the DB Console Active Queries page. This is the fastest way to restore service but does not prevent recurrence.

Tune the per-operator spill threshold

The sql.distsql.temp_storage.workmem cluster setting (default 64 MiB) controls how much memory each SQL operator can use before spilling to disk. This is a cluster-wide change that takes effect immediately without restart.

-- Raise per-operator work memory (adjust for your workload)
-- This affects all nodes in the cluster immediately
SET CLUSTER SETTING sql.distsql.temp_storage.workmem = '128MiB';

Raising it lets individual operators hold more data in memory, reducing spills for large sorts and hash joins. The tradeoff: each operator that stays in-memory holds budget that cannot serve other concurrent queries, reducing effective concurrency.

Lowering the value has the opposite effect: more queries spill sooner, but more can run concurrently within the same budget. This is the right lever when you are seeing 53200 from concurrency pressure rather than from a single runaway query.

Increase --max-sql-memory

This requires a node restart. CockroachDB’s memory planning guideline:

(2 * --max-sql-memory) + --cache <= 80% of system RAM

The factor of 2 accounts for temporary memory overcommit during certain SQL execution phases. Both --max-sql-memory and --cache default to 25% of system RAM each. In containers, set these explicitly as a fraction of the container memory limit, not the host memory. The CockroachDB Kubernetes Operator sets both to 25% by default, but these are static values that do not adjust to container resource limits.

Do not set --max-sql-memory too aggressively. The Go runtime has allocations (GC metadata, goroutine stacks, internal buffers) not accounted for in the SQL memory monitor. If total memory consumption exceeds available RAM, the process can OOM crash or start swapping, which is worse than rejecting queries.

Note: in systemd unit files, percentage values must be escaped as %% because systemd interprets % in ExecStart directives. Use --max-sql-memory=25%% or specify an absolute value.

Fix the query or schema

If the same query repeatedly triggers 53200, the root cause is likely in the query or schema:

• Missing index causing full table scan with sort. The optimizer scans the entire table, sorts in memory, then returns a small result. Add an index that covers the ORDER BY and WHERE predicates.
• Hash join on large unindexed tables. The hash table exceeds available budget. Add an index to enable a merge or lookup join instead.
• Query with LIMIT but no pushdown. A SELECT ... LIMIT n without an index-backed ORDER BY materializes the full result set before truncating. The optimizer cannot push the limit below the sort.

Manage connection storms

If failover-induced connection bursts are the cause:

• Ensure client libraries use exponential backoff with jitter on reconnect.
• Deploy PgBouncer in transaction pooling mode in front of CockroachDB to cap direct connections.
• Monitor sql_conns per node against baseline to catch storms before they trigger memory pressure.

For abandoned sessions holding resources:

CANCEL SESSION '<session_id>';

Prevention

• Alert on sql_mem_root_current trend, not just threshold. A node at 40% with a steady upward slope over weeks is heading toward 53200. Alert on sustained utilization above 70% with an upward trend before rejection begins.
• Verify memory planning at deploy time. Use the (2 * --max-sql-memory) + --cache <= 80% formula. Account for Go runtime overhead in the remaining 20%.
• Set memory flags explicitly in containers. Do not rely on defaults that reference host memory when running in a cgroup-limited container.
• Watch for automatic statistics collection. Stats jobs can trigger 53200 when the budget is already near capacity, particularly on versions before v24.1. If errors correlate with stats collection windows, consider scheduling stats collection during off-peak hours.
• Track Go GC behavior alongside SQL memory. The Memory Pressure -> GC Thrashing -> Raft Liveness Failure cascade starts with memory growth and ends with node liveness loss. GC CPU above 15% or individual pauses above 500 ms are leading indicators.
• Keep CockroachDB current. Multiple memory accounting bugs have been fixed across versions.

How Netdata helps

• Per-second sql_mem_root_current tracking catches burst patterns that 15- or 30-second scrape intervals miss entirely, which matters because a single runaway query can exhaust the budget in seconds.
• Correlation between sql_mem_root_current, sql_failure_count, and sql_service_latency in a single view reveals the exact moment budget exhaustion translates into query rejection and latency spikes.
• Go runtime signals (sys_gc_pause_ns, sys_go_allocbytes, sys_rss) alongside SQL memory metrics distinguish SQL budget exhaustion from broader process memory pressure and catch the GC thrashing cascade before it threatens node liveness.
• ML-based anomaly detection on memory utilization trends flags the slow upward drift toward budget exhaustion days before it becomes an incident.

For details, see database monitoring with Netdata.

Related guides

• CockroachDB clock_offset_meannanos high: catching clock drift before self-termination
• CockroachDB clock skew cascade: how shared NTP drift causes quorum loss
• CockroachDB clock synchronization error: this node is more than 500ms away from at least half of the known nodes
• CockroachDB compaction backlog growing: when Pebble can’t keep pace with writes
• CockroachDB context deadline exceeded: timeouts, slow ranges, and overload
• CockroachDB detecting hot ranges: per-range QPS, CPU asymmetry, and the Hot Ranges page
• CockroachDB disk space running out: capacity_available trends and the 20% rule
• CockroachDB disk stall detected: storage_disk_stalled and node self-termination
• CockroachDB file descriptor exhaustion: SSTables, connections, and ulimit
• CockroachDB hot range bottleneck: one leaseholder saturated while the cluster idles
• CockroachDB intent accumulation cascade: abandoned transactions and intentcount growth
• CockroachDB storage_l0_sublevels climbing: the earliest warning of write stalls