questdb 'out of memory' on a large join: how to bound the query

TL;DR

Bound the join before it runs. Put a tight timestamp filter on both sides, break the range into smaller chunks (daily or hourly), and run the join per chunk instead of all at once. QuestDB builds joins in memory, so an unbounded join over millions of rows will exhaust memory no matter how much RAM you add.

out of memory

Steps

1. Confirm the join is the memory hog, not the scan. Run each side of the join alone with a COUNT and a tight time filter. Expected: both sides return counts quickly; only the joined query dies.
2. Add a timestamp bound to both tables. QuestDB partitions by time, so a WHERE clause on the designated timestamp lets it skip whole partitions. Example: WHERE ts BETWEEN '2026-10-01' AND '2026-10-02'. Expected: EXPLAIN shows only the matching partitions being touched.
3. Chunk the range. Run the join once per day (or hour) and combine the results, instead of one giant query over the full history. Expected: each chunk completes in seconds using a fraction of the memory.
4. Shrink the join width. Select only the columns you need, filter rows before the join rather than after, and for ASOF joins narrow the tolerance window. Expected: peak memory drops and the query stops dying.
5. Only after bounding, raise the memory available to the QuestDB process (container limit or host RAM) if chunks still run hot. Expected: the bounded query completes with headroom to spare.

Use this when

• A QuestDB query dies with "out of memory" and the query joins two large tables
• An ASOF JOIN over a wide time range blows up
• A full-history backfill join never finishes
• Each side of the join runs fine alone but the join itself OOMs

Not for this skill when

• A single-table scan OOMs with no join involved (that is a filter and partition-pruning problem, different fix)
• Out of memory during ingestion (check commit lag and WAL settings instead)
• Join OOMs in Presto, Trino, or Spark (those engines have their own memory models and spill behavior)

Variant phrasings

• questdb join runs out of memory
• questdb asof join out of memory
• questdb large join memory limit
• how to limit memory usage of a questdb query

Why it happens

QuestDB executes joins in memory. It loads the join inputs into RAM, and unlike engines that spill to disk, an unbounded join over a wide time range grows with the data. Memory use ends up proportional to range size rather than constant, so doubling the range doubles the memory until something gives.

Edge cases

• An ASOF JOIN with no time bound scans the entire right-hand table; always bound it.
• Joining on a non-timestamp column cannot use partition pruning, so the whole table is fair game for memory.
• LIMIT without ORDER BY still materializes the full join before cutting rows; chunk by time instead.
• Adding RAM only delays the same blowup on the next bigger range; bound first, scale second.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_r6FL2uCytsNtbYPX6MON3g