In-Memory vs Persistent

In-Memory Versus Persistent Database Files

duckdb.connect() with no path (or :memory:) creates a database that vanishes with the process; a path opens a single-file database, with a .wal log beside it while changes are pending. ATTACH adds more databases (DuckDB 61,228 files, SQLite 4,756 , PostgreSQL 1,289 in The postgres Extension) to one session:

persist.py: memory, files, ATTACH and the write lockPython

import duckdb, subprocess
mem = duckdb.connect()                                    # in-memory, gone at exit
print(mem.sql("SELECT current_setting('memory_limit'), current_setting('threads')").fetchone())
mem.execute("ATTACH 'booknest.duckdb' AS b (READ_ONLY)")   # a database file beside it
mem.execute("CREATE TABLE units AS SELECT title, sum(qty) AS n FROM b.sales GROUP BY ALL")
print(mem.sql("SELECT database_name, path FROM duckdb_databases() WHERE NOT internal")
      .fetchall())
disk = duckdb.connect("scratch.duckdb")                   # read-write: this process owns it
disk.execute("CREATE OR REPLACE TABLE t AS SELECT range AS n FROM range(1000)")
cli = ["/home/dev/v7-l2/tools/duckdb-1.5.5/duckdb", "-readonly", "-csv", "-noheader",
       "scratch.duckdb", "-c", "SELECT count(*) FROM t"]
print(subprocess.run(cli, capture_output=True, text=True).stderr.strip())
disk.close()
print(subprocess.run(cli, capture_output=True, text=True).stdout.strip())

Output

('25.0 GiB', 4)
[('b', '/home/dev/v7-l2/ch04/booknest.duckdb'), ('memory', None)]
IO Error: Could not set lock on file "/home/dev/v7-l2/ch04/scratch.duckdb": Conflicting lock is
held in /usr/bin/python3.14 (PID 2775206). ...
1000

By default DuckDB takes 80 percent of RAM as memory_limit (25 GiB of the 31 GiB WSL2 6 sees) and one thread per core; lower both on a shared machine. The lock is the rule to design around: one process may open a file read-write, or any number may open it read-only, never both. Inside that process, threads read and write under MVCC with optimistic concurrency control (of two updates to one row, the second fails). So several web-server processes cannot share one DuckDB file for writes; route writes through one process, or write Parquet 129 for readers (Querying Files with DuckDB).

MENU