TopolearnDesigning an app around DuckDB requires knowing what it guarantees for concurrent access and how data is laid out.
A DuckDB database file is split into fixed-size blocks. Tables are stored as row groups of about 122,880 rows; inside each, every column is a separate column segment compressed with lightweight schemes chosen per segment (constant, RLE, dictionary, bit-packing, FSST for strings, ALP for floats). Each segment keeps min/max statistics, called zone maps, so filters skip segments that cannot match, which is why inserting data sorted by a common filter column speeds up queries.
Indexes are rarely needed: zone maps cover range scans. PRIMARY KEY and UNIQUE constraints create ART indexes, which help point lookups but slow bulk loads.
Writes go to a write-ahead log and are folded into the main file at a checkpoint (automatic once the WAL grows past checkpoint_threshold, or via CHECKPOINT). Deleted space is reused but the file rarely shrinks.
Transactions use MVCC with snapshot isolation and optimistic concurrency: within one process, many threads can read and write in parallel, and two transactions updating the same row conflict, with the second failing to commit.
Across processes the rule is strict: either one process opens the file read-write, or any number open it read-only (read_only=True). For multi-writer workloads, funnel writes through one process, or use a server database, or DuckLake / MotherDuck style setups.
Example
import duckdb, os, tempfile
path = os.path.join(tempfile.mkdtemp(), "app.duckdb")
con = duckdb.connect(path)
con.sql("CREATE TABLE t AS SELECT i FROM range(300_000) r(i)")
con.sql("CHECKPOINT")
print(con.sql("""SELECT count(DISTINCT row_group_id)
FROM pragma_storage_info('t')""").fetchone()) # (3,)
con.close()
ro1 = duckdb.connect(path, read_only=True) # many read-only handles are fine
print(ro1.sql("SELECT max(i) FROM t").fetchone()) # (299999,)Remember
- Tables are row groups of ~122k rows, each column compressed separately
- Zone maps (min/max) let scans skip data, so sort on common filter columns
- MVCC gives snapshot isolation within a process; conflicting updates fail
- One read-write process or many read-only processes per file
Watch out for
- Opening the same file read-write from a web server's several worker processes; the others get a lock error.