DuckDB Memory Limit: Default Settings, PRAGMA max_memory, and OOM Fixes

DuckDB defaults its buffer manager memory limit to 80% of detected system RAM, but in sandboxed containers and virtualized environments, this calculation frequently reads host hardware rather than container limits. When an analytical query exhausts the real memory boundary before DuckDB's buffer manager can spill intermediate state to disk, the operating system terminates the process with exit code 137 rather than raising a catchable database exception.

DuckDB memory limit is governed by the max_memory configuration parameter, which by default is automatically set to 75% to 80% of total physical RAM detected on the host system.

In official settings tables, DuckDB designates both max_memory and memory_limit as interchangeable aliases for the maximum memory threshold of the database engine. Understanding how DuckDB computes this threshold requires distinguishing between the memory managed directly by its internal buffer manager and the untracked memory consumed by surrounding application runtimes.

When DuckDB initializes, its storage engine queries the operating system kernel for total installed physical memory. On a dedicated workstation with 32 GB of RAM, DuckDB reserves approximately 25.6 GB for its buffer manager, leaving the remaining 6.4 GB for the operating system, terminal utilities, and background daemons.

You can verify the current memory limit assigned to your active DuckDB instance by querying either the configuration scalar function or the system settings table:

-- Query the active memory limit via scalar function
SELECT current_setting('memory_limit') AS active_limit;

-- Inspect configuration details in the settings table
SELECT name, value, description, input_type
FROM duckdb_settings()
WHERE name IN ('memory_limit', 'max_memory');

The table below compares how DuckDB detects available RAM across common execution runtimes and the operational risks associated with default settings:

While the default 80% memory allocation functions reliably on a developer system where DuckDB operates as the primary workload, it introduces severe failure modes in multi-tenant, virtualized, and agentic environments.

• Docker Memory Limit: Sizing Containers and Preventing OOM CrashesA Docker memory limit restricts the physical host RAM and swap space a container can allocate using the --memory and...
• In-Memory File Systems: RAM Ceilings, Performance, and Persistent WorkspacesAn in-memory file system is a file storage abstraction that stores all file contents and directory hierarchies directly...
• Best Knowledge Graph Tools for RAG: Enhancing Agent MemoryRAG systems often fail when they only use vector search. Adding a knowledge graph gives AI agents the context to...
• Best AI Agent Memory Solutions: Top 7 Tools for 2026AI agents without persistent memory start from scratch every session, losing context and repeating much of their work....
• How to Implement LangGraph Persistence for Long-Term MemoryLangGraph persistence allows AI agents to maintain state and memory across multiple sessions by saving graph...
• How to Set Up Agentic AI Storage for Persistent Agent MemoryAgentic AI storage provides persistent file and data access that autonomous agents need to complete multi-step tasks...

More on this subject: Agent Memory and Storage (250 guides)

You can configure DuckDB memory limits using standard SQL statements or programmatic client connection parameters. DuckDB treats SET memory_limit and PRAGMA max_memory as identical directives that instruct the buffer manager to enforce a specific ceiling.

To set a static memory limit in SQL, execute either statement immediately after establishing your connection:

-- Set memory limit using standard SQL syntax
SET memory_limit = '4GB';

-- Set memory limit using PRAGMA syntax
PRAGMA max_memory = '4GB';

-- Verify that the setting took effect
SELECT current_setting('memory_limit');

DuckDB supports multiple unit qualifiers, parsed case-insensitively:

• Bytes: B
• Kilobytes: KB or KiB
• Megabytes: MB or MiB
• Gigabytes: GB or GiB
• Terabytes: TB or TiB

To return the buffer manager to its automatic calculation based on system hardware, use the RESET command:

-- Restore default system memory detection
RESET memory_limit;

Setting Memory Limits in Python and Client Drivers

When embedding DuckDB within Python applications or data pipelines, passing configuration parameters during connection initialization is preferable to running SQL statements post-connection. Initializing with configuration parameters ensures that DuckDB never attempts to allocate memory based on erroneous host hardware detection during startup.

import duckdb

connection = duckdb.connect(
    database='analytics.duckdb',
    config={
        'max_memory': '4GB',
        'threads': '4',
        'preserve_insertion_order': 'false'
    }
)

result = connection.execute("SELECT current_setting('memory_limit');").fetchone()
print(f"Configured memory limit: {result[0]}")

In Node.js applications, supply configuration options inside the database constructor:

import duckdb from 'duckdb';

// Initialize database with explicit memory constraints
const db = new duckdb.Database('analytics.duckdb', {
  max_memory: '2GB',
  threads: '2'
});

Dynamic Adjustments During Active Sessions

You can execute SET memory_limit dynamically during an active analytical session. When you increase the limit, DuckDB immediately allows the buffer manager to pin additional memory pages.

When you decrease the limit, DuckDB does not forcibly crash active queries. Instead, the buffer manager identifies unpinned cache blocks and evicts them to disk using its concurrent LRU (least recently used) eviction algorithm until resident buffer memory drops below the new ceiling. However, if active queries currently pin more working memory in hash tables than your requested new limit, the engine cannot drop below that working threshold until the query pipeline completes.

DuckDB supports larger-than-memory workloads through out-of-core execution. When intermediate query data exceeds the configured memory_limit, DuckDB evicts data blocks from RAM and writes temporary structures to disk.

By default, DuckDB defines the disk spill destination based on how the database was opened:

• Persistent Databases: DuckDB creates a directory named <database_file_name>.tmp in the same directory as the database file.
• In-Memory Databases (:memory:): DuckDB creates a hidden .tmp directory in the current working directory from which the process was launched.

In many automated environments, launching an in-memory database creates .tmp in an unintended location, such as a root directory with restrictive write permissions or an ephemeral container layer with limited capacity.

You can configure the spill destination explicitly using the temp_directory setting:

-- Define an explicit scratch directory for disk spilling
SET temp_directory = '/tmp/duckdb_spill.tmp';

-- Or use the equivalent PRAGMA directive
PRAGMA temp_directory = '/mnt/scratch/duckdb.tmp';

You can also restrict the maximum volume of disk space DuckDB is permitted to consume for temporary files:

-- Cap temporary disk usage at 20 GB
SET max_temp_directory_size = '20GB';

By default, DuckDB sets max_temp_directory_size to 90% of available disk space on the target volume.

Operators That Support Out-of-Core Processing

DuckDB's execution engine is built around a push-based streaming vector architecture. Most relational operators process data in standard vector morsels without materializing full tables.

However, certain operations cannot emit their first output row until they have inspected every input row. These blocking operators require intermediate buffering and rely on temp_directory when memory limits are reached:

1. Sorting (ORDER BY): DuckDB performs an external merge sort, sorting chunks in memory, flushing sorted runs to disk, and merging them hierarchically.
2. Hash Joins (JOIN): When building a hash table on the inner relation, DuckDB partitions the hash table across memory and disk blocks when memory limits are reached.
3. Hash Group By (GROUP BY): Groups are aggregated in partitioned hash tables, offloading cold partitions to temporary storage under memory pressure.
4. Window Functions: Window partitions are streamed through temporary sorted buffers.

Operations That Cannot Spill to Disk

While DuckDB continues expanding out-of-core coverage, several analytical operations currently cannot spill to disk. Running these operations on datasets that exceed memory_limit triggers an immediate OutOfMemoryException:

• Nested List Aggregations: The list() aggregate function accumulates all elements into variable-length structures in memory.
• String Concatenation Aggregations: string_agg() collects uncompressed strings directly on the heap.
• Dynamic Pivot Queries: The PIVOT clause uses list() internally to determine unique column values.
• Sorted Aggregations: Aggregate functions containing an internal ORDER BY clause require complete materialization before evaluation.
• In-Memory Table Storage: In-memory tables created via CREATE TABLE t AS SELECT ... in a :memory: database reside entirely in the buffer pool. If total table size exceeds memory_limit, the operation fails unless backed by a persistent file.

Disabling Insertion Order Preservation

When exporting or importing massive Parquet or CSV datasets, DuckDB attempts by default to preserve the exact row sequence of the input file. Preserving order forces the execution pipeline to maintain reordering buffers in memory.

Setting preserve_insertion_order to false disables this requirement, allowing DuckDB to stream data directly into target storage without tracking sequence numbers:

-- Disable insertion order preservation to reduce import/export memory footprint
SET preserve_insertion_order = false;

For multi-gigabyte data migrations, this single configuration change often prevents out-of-memory errors without requiring adjustments to physical hardware.

When DuckDB runs out of memory, failures present in two distinct ways: an internal database exception or an abrupt process termination. Identifying which failure occurred is the first step toward implementing the correct fix.

Distinguishing OutOfMemoryException from OS OOM Killer

An internal database exception indicates that DuckDB's buffer manager intercepted the memory limit and stopped execution cleanly:

Out of Memory Error: failed to allocate data of size 256.0MB (3.8GB/4.0GB used)

This error occurs when a query requires memory for a non-spillable operator, or when temporary disk space is exhausted. Because the database engine caught the boundary, your application process remains alive, handles the exception, and rolls back the transaction.

Conversely, if your Python script, Docker container, or background agent terminates abruptly with Killed or Exit Code 137, the Linux kernel OOM Killer terminated the process. In this scenario, total process memory exceeded the operating system or container cgroup limit before DuckDB's buffer manager could react.

Concrete Solutions for Memory Exhaustion

Apply the following technical adjustments to stabilize memory consumption during heavy analytical workloads:

1. Adopt Conservative Headroom Ratios in Constrained Containers

Never set memory_limit to match the full container memory ceiling. In a container restricted to 4 GB of RAM, set DuckDB's memory limit to 2 GB:

-- Reserve half of total memory for process heap, OS, and client runtimes
SET memory_limit = '2GB';

This headroom accommodates Python interpreter allocations, Arrow table materialization, and unmanaged string buffers without triggering kernel eviction.

2. Restrict Thread Concurrency

DuckDB executes queries in parallel across available CPU cores. Each active thread maintains its own intermediate state, thread-local hash tables, and output buffers. On an 8-core machine, eight threads building join tables simultaneously consume up to eight times the memory of a single-threaded plan.

Reducing thread count reduces concurrent memory footprint:

-- Restrict parallel worker threads to control concurrent memory usage
SET threads = 2;

3. Stream Query Results Instead of Materializing DataFrames

Calling .df() or .fetchall() in Python instructs DuckDB to convert every output row into client memory at once. If your query returns millions of rows, Python client memory is exhausted regardless of DuckDB's internal buffer manager settings.

Use streaming Arrow record batches or chunked fetches instead:

import duckdb

con = duckdb.connect()

cursor = con.cursor()
cursor.execute("SELECT * FROM 'large_dataset.parquet'")

while True:
    chunk = cursor.fetchmany(100000)
    if not chunk:
        break

4. Inspect Real-Time Memory Consumption with duckdb_memory()

DuckDB provides the duckdb_memory() table function to monitor memory allocations across different subsystem tags:

-- Inspect active memory and disk spill allocations by tag
SELECT
  tag,
  memory_usage_bytes,
  temporary_storage_bytes
FROM duckdb_memory()
WHERE memory_usage_bytes > 0
ORDER BY memory_usage_bytes DESC;

Common tags include HASH_TABLE (joins and aggregations), ORDER_BY (sorting buffers), PARQUET_READER (file decompression caches), and BASE_TABLE (persistent storage pages). Tracking these tags during query development reveals which operator drives memory spikes.

Keep Heavy Analytical Files Out of Constrained Agent Sandboxes

Store large Parquet datasets, documents, and media in an intelligent Fast.io workspace. Your agents search and retrieve indexed data through MCP without exhausting container memory or disk limits. Monthly plans start with a 14-day free trial, which requires a credit card.

Start 14-Day Trial

Modern autonomous agents, including coding assistants and analytical agents running in isolated Docker containers, Kubernetes pods, or cloud sandboxes, encounter unique memory constraints when running embedded DuckDB queries.

Unlike interactive human analysts operating on high-spec workstations, AI agents run in tightly restricted virtual environments where memory limits typically range from 1 GB to 4 GB. Within these boundaries, several architectural traps cause unexpected crashes.

First, legacy container runtimes running cgroup v1 do not expose container memory limits through standard system files like /proc/meminfo. When DuckDB starts inside such a container, it reads the host server's memory capacity (such as 128 GB) and calculates its buffer manager threshold based on that host total rather than the container allocation. The first time the agent runs a moderate analytical query on a 2 GB Parquet file, DuckDB attempts to allocate memory based on host capacity. The container cgroup immediately detects an overrun past its 2 GB allocation and executes a SIGKILL termination.

Second, agent containers frequently mount their root file systems with strict quotas or read-only flags, preventing DuckDB from creating its default .tmp directory for disk spilling. When memory fills and DuckDB attempts to write intermediate sorted runs to disk, it encounters a filesystem permission error or disk full error, causing the analytical workflow to fail mid-execution.

To run DuckDB reliably within agentic workflows, configure your sandbox execution environment with explicit memory boundaries and dedicated writable scratch volumes:

docker run --rm -it \
  --memory="4g" \
  --memory-swap="4g" \
  -v /tmp/agent_scratch:/scratch \
  agent-runner:latest

Inside the agent container, initialize DuckDB with strict parameters that reserve space for the agent runtime:

-- Enforce safe container parameters inside agent tasks
SET memory_limit = '2GB';
SET temp_directory = '/scratch/duckdb.tmp';
SET threads = 2;
SET preserve_insertion_order = false;

Decoupling Analytical Corpora from Agent Memory

While configuring DuckDB parameters prevents internal container crashes, stuffing hundreds of gigabytes of unstructured files, PDF reports, and raw analytical datasets directly into an ephemeral agent container remains an anti-pattern. Ephemeral containers lack persistent storage across task retries, and holding massive data catalogs inside container disks rapidly exhausts storage volume quotas.

Teams building production multi-agent workflows decouple persistent storage from the ephemeral compute sandbox. In this architecture, reference documents, research corpora, and large datasets reside in a persistent intelligent workspace such as Fast.io Storage for Agents.

Rather than forcing an agent to clone gigabytes of data into container RAM, the agent connects to the workspace using the Model Context Protocol (MCP) through the remote endpoint https://mcp.fast.io/mcp/tools. With workspace intelligence enabled, files are indexed automatically upon arrival for hybrid search (full-text and semantic retrieval). The agent searches and extracts specific data points, contract clauses, or summary metrics on demand, streaming only necessary subsets into local DuckDB tables.

When the analytical workflow completes, the agent saves generated summaries, structured extractions, and refined Parquet files back to the shared workspace. Both human operators and downstream agents access the versioned output without maintaining multi-gigabyte database replicas inside container memory.

Every organization starts with a 14-day free trial, which requires a credit card. Subscription options scale across Starter ($9.99/mo), Business ($49.99/mo), and Enterprise ($199.99/mo) tiers on the pricing page.