DuckDB Persistent Files vs In-Memory Mode: A Practical Decision Guide

Every DuckDB connection starts with one line — duckdb.connect('my.duckdb') or duckdb.connect(':memory:') — and that choice determines whether your data survives the process, how large your dataset can grow, and how fast queries start. Picking the wrong mode is a common source of confusion: analysts lose work because they assumed an in-memory database persisted, and pipelines waste time writing files they never reuse. This guide lays out the constraints, compares the two modes, and shows a concrete way to validate the behavior yourself.

The decision and its constraints

The core question is simple: does the data need to exist after this process exits? If yes, use a persistent file. If the workload is a transient transformation — load, aggregate, export, done — in-memory is usually the better fit. Before deciding, weigh these constraints:

• Dataset size vs. available RAM. In-memory mode holds everything in RAM. Persistent mode can spill to disk and query datasets larger than memory.
• Persistence requirements. Results, intermediate tables, or imported raw data that must be reused across sessions need a file.
• Concurrency. DuckDB allows multiple read-only connections to one file, but only one process can write to a given database file at a time. Concurrent writers are not supported and risk corruption.
• Startup and shutdown overhead. Persistent connections pay file I/O costs on open and checkpoint on close; in-memory starts with effectively zero latency.
• Storage hardware. In persistent mode, query performance on large scans depends on disk speed. An SSD is strongly recommended; network filesystems can be slow and may have locking quirks.

Comparing the two modes

Trade-offs in practice

The in-memory mode's speed advantage is real but narrower than people expect for moderate data. DuckDB's vectorized engine is the same in both modes; the difference shows up mainly on cold scans of large tables, where persistent mode must read pages from disk, and on write-heavy workloads, where durability requires flushing to storage. Once a persistent database's working set is cached in the OS page cache, repeated analytical queries often approach in-memory times.

The more decisive trade-off is failure semantics. An in-memory database that runs out of RAM will error or be killed by the OS, and everything is gone. A persistent database degrades gracefully: it spills, runs slower, but finishes. For unattended batch jobs, that resilience usually outweighs the startup cost.

A hybrid pattern also works well: attach a persistent database for durable inputs and outputs, and use the in-memory default (or temporary tables) for scratch work. DuckDB also supports a temp_directory setting so even persistent databases control where spill files land — useful when the database lives on a slow volume but fast local scratch space exists.

Concrete implementation and validation

The following Python script demonstrates the behavioral difference. Run it anywhere with the duckdb package installed (pip install duckdb); no special permissions are needed beyond write access to the working directory. It creates a table in both modes, closes the connections, reconnects, and checks what survived.

import duckdb

# --- Persistent mode ---
con = duckdb.connect('demo.duckdb')
con.execute("CREATE TABLE IF NOT EXISTS events AS SELECT * FROM range(5) AS t(id)")
con.close()

con = duckdb.connect('demo.duckdb')  # reopen the same file
print(con.execute("SELECT COUNT(*) FROM events").fetchone())  # expect (5,)
con.close()

# --- In-memory mode ---
con = duckdb.connect(':memory:')
con.execute("CREATE TABLE events AS SELECT * FROM range(5) AS t(id)")
con.close()

con = duckdb.connect(':memory:')  # brand-new empty database
try:
    con.execute("SELECT COUNT(*) FROM events").fetchone()
except duckdb.CatalogException as e:
    print("Expected failure:", e)  # table does not exist
con.close()

Expected outcome: the persistent query returns 5 rows after reconnection, and a demo.duckdb file appears on disk. The in-memory query raises a catalog error because each :memory: connection is a fresh, empty database. If you see different behavior, check that you actually closed the first connection — DuckDB buffers writes, and an unclosed connection may not have checkpointed to the file yet.

Checking the performance trade-off yourself

To make the size/speed trade-off concrete, generate a synthetic table and time the same aggregation in both modes:

import duckdb, time

for target in (':memory:', 'bench.duckdb'):
    con = duckdb.connect(target)
    con.execute("""
        CREATE TABLE t AS
        SELECT range AS id, random() AS val, range % 100 AS grp
        FROM range(100_000_000)
    """)
    start = time.perf_counter()
    con.execute("SELECT grp, SUM(val) FROM t GROUP BY grp").fetchall()
    print(target, "aggregation:", round(time.perf_counter() - start, 2), "s")
    con.close()

Run this on your own hardware rather than trusting published numbers — results depend heavily on CPU, RAM, and disk. On 100M rows, expect in-memory table creation to be fast but RAM-hungry (roughly a gigabyte or more for this schema), while the persistent version writes a file you can inspect with ls -lh bench.duckdb. Re-run the aggregation after reopening the persistent file to see the warm-cache effect.

Limitations to keep in mind

• Only one process may write to a DuckDB file at a time. For multi-process access, open secondary connections with read_only=True or coordinate with external locking.
• Persistent performance on spinning disks or network mounts can be dramatically worse than on a local SSD.
• In-memory mode gives no crash recovery; if the process dies mid-pipeline, you restart from scratch.
• Exact memory limits and spill behavior vary by DuckDB version; the examples above assume a recent 0.x/1.x release. Check SELECT version(); and the release notes for your installed build.

The rule of thumb: default to a persistent file for anything you would be annoyed to recompute, and use :memory: for disposable, RAM-sized transformations where startup speed and zero cleanup matter.