Diagnosing and Resolving Memory Exhaustion in SurrealDB

The Problem: Unexpected OOM Crashes and Memory Spikes

When SurrealDB processes complex SurrealQL queries on large datasets, the engine may consume all available system RAM, leading to an Out of Memory (OOM) crash or a kernel OOMKill in containerized environments. This typically happens when the result set size or the intermediate processing state exceeds the physical memory allocated to the process.

Identifying the Memory Pressure Condition

You are likely facing a memory exhaustion issue if you observe the following symptoms during query execution:

Symptom	Likely Cause	Diagnostic Indicator
Process terminates abruptly without an error log	Container OOMKill	dmesg | grep -i oom shows "Out of memory: Kill process"
Extreme system latency/disk thrashing	Swap exhaustion	vmstat shows high swap-in/swap-out rates
Query hangs, then fails with memory error	Unbounded result set	Memory growth correlates linearly with table size
Spike during graph traversals	Combinatorial explosion	Memory jumps sharply during -> or <- operations

Step-by-Step Diagnostic Workflow

Follow these checks in order to isolate the specific cause of the memory pressure.

1. Check for Unbounded Result Sets:

   Review the query for SELECT * statements lacking a LIMIT clause. If the table contains millions of records or large nested objects, SurrealDB must buffer these results before returning them to the client.

2. Analyze Graph Traversal Depth:

   Examine queries using the -> (outbound) or <- (inbound) operators. If the traversal depth is unrestricted or the graph is highly connected, the number of intermediate nodes tracked in memory can grow exponentially.

3. Inspect Aggregation Fields:

   Check for GROUP BY clauses. If the field being grouped is not indexed, the engine may be forced to perform an in-memory sort and aggregation of the entire dataset.

4. Evaluate Transaction Concurrency:

   Check for multiple long-running transactions. SurrealDB uses Multi-Version Concurrency Control (MVCC), meaning it must maintain versions of data for active transactions. Long-lived transactions prevent the cleanup of old data versions, increasing memory overhead.

Fixes Based on Findings

Issue: Result Set Overflow

Fix: Implement pagination. Replace unbounded selects with LIMIT and START AT (offset) to process data in chunks.

-- Avoid this:
SELECT * FROM user;

-- Use this:
SELECT * FROM user LIMIT 100 START AT 0;

Issue: Graph Traversal Explosion

Fix: Restrict the traversal path or use specific field selection instead of returning the entire related record.

-- Avoid this (fetches all fields of all related records):
SELECT ->friend FROM user:john;

-- Use this (fetches only the name of related records):
SELECT ->friend.name FROM user:john;

Issue: In-Memory Sorting/Grouping

Fix: Create an index on the field used in the GROUP BY clause. This allows the engine to leverage the index structure rather than allocating a temporary memory buffer for sorting.

-- Run as administrator on the SurrealDB shell:
DEFINE INDEX user_city_idx ON TABLE user FIELDS city;

Issue: Large Blob Handling

Fix: Avoid retrieving large binary objects (Blobs) within a loop or as part of a large result set. Retrieve the Blob ID first, then fetch the binary data individually as needed by the application.

Verification and Limitations

To verify the fix, run the query in a development environment with a representative dataset while monitoring memory usage. On Linux, use the following command to track the process resident set size (RSS):

# Replace [PID] with the SurrealDB process ID
watch -n 1 "ps -o rss,vsz,pcpu -p [PID]"

Expected Result: The RSS (Resident Set Size) should plateau or remain stable during query execution, rather than climbing until the process terminates.

Limitations: Increasing system swap space may prevent the process from crashing, but it will introduce severe I/O latency, often making the query effectively time out. Memory limits imposed by Docker or Kubernetes resources.limits.memory will trigger an OOMKill regardless of SurrealDB's internal state if the container limit is reached.

Escalation Criteria

If the following conditions persist after applying the above fixes, escalate to infrastructure scaling or database architecture review:

• Memory usage remains high even with LIMIT 1 and indexed fields.
• MVCC overhead remains high despite short transaction windows.
• The dataset size exceeds the available physical RAM of the largest available node, necessitating a shift in data modeling (e.g., breaking large tables into smaller, normalized entities).