Skip to Content
We are live but in Staging 🎉
Data EnginesSQLPerformance & layout

Performance & layout

Primary key

A table with a primary key is a keyed table: every write is addressed by key, duplicates are refused with 23505, and transactions detect conflicts per key. A table without one accepts appends only through a hidden _rowid and has no conflict detection — use it for logs and staging, not for rows you update.

Composite keys are supported (PRIMARY KEY (tenant_id, id)). Range predicates on a composite key (WHERE (tenant_id, id) BETWEEN …) are refused; filter on the leading column and let the rest resolve by equality or subquery.

SERIAL / BIGSERIAL / GENERATED … AS IDENTITY produce a 64-bit snowflake id with no sequence round-trip. Read it back with RETURNING id.

Partitioning

CREATE TABLE events (
  id          BIGINT PRIMARY KEY,
  event_type  VARCHAR(32) NOT NULL,
  occurred_at TIMESTAMP   NOT NULL,
  payload     JSON
) PARTITIONED BY (event_type);

PARTITIONED BY controls the physical layout. A read whose WHERE fixes the partition column reads only that directory. Choose a low-cardinality column that appears in most filters (a type, a region, a month bucket you maintain yourself). Do not partition on a high-cardinality key: thousands of tiny directories are slower than one well-compacted table.

Aggregates over a partitioned table fan out across partitions in parallel; joins and window functions run in one place. This is a planner decision, not a request flag.

Indexes

CREATE INDEX ON orders (customer);                 -- plain: exactly one column
CREATE UNIQUE INDEX ON orders (tenant_id, ref);    -- unique: composite allowed, enforced (23505)
CREATE INDEX ON docs USING hnsw (emb vector_cosine_ops)
  WITH (m = 16, ef_construction = 64, async);      -- vector ANN
KindShapeNotes
Plainone columnAccelerates WHERE col = v. Queryable as soon as the statement returns, on populated tables too since 0.1.41
Uniqueone or more columnsEnforced23505 on the write path — the way to get uniqueness beyond the primary key
VectorUSING hnsw or USING ivfflatOperator classes vector_l2_ops (default), vector_ip_ops, vector_cosine_ops. WITH accepts m, ef_construction, lists, quantization (f16 / i8 for hnsw, sq8 for ivfflat) and async (build in the background; watch SHOW VECTOR INDEXES). An unknown key is Refused
Multi-column plain, expression index—Refused Use one index per column, or a unique index, or materialise the expression into a column

An index is a table named __idx_<table>_<column> (unique: __uidx_…). DROP INDEX is refused; drop the index table instead:

DROP TABLE __idx_orders_customer;

Tune ANN recall per session with SET hnsw.ef_search = 100 or SET ivfflat.probes = 20; force exact scan with SET dodil.vector_exact_scan = on. Vector search semantics are on Vector.

Maintenance after a batch

dodil data table compact  orders -b <bucket>   # fold pending writes into the table now
dodil data table optimize orders -b <bucket>   # bin-pack small files (optionally z-order)
dodil data table vacuum   orders -b <bucket>   # remove files older than the retention window

You do not need these for steady traffic — pending writes are folded in continuously. Reach for them after a bulk load or a nightly job:

  1. compact folds pending writes into the table so the next reads take the pure scan path.
  2. optimize rewrites many small files into few large ones; scans get faster and cheaper.
  3. vacuum deletes files no current version references. Retention floor is 168 hours with no override — history younger than a week is always kept, so time travel within the week always works, and a vacuum never frees space faster than that.

compact then optimize is the canonical sequence. The same operations exist as gRPC RPCs (Compact, OptimizeTable, VacuumTable) for schedulers; see Tables & maintenance.

Residency: LOAD / RELEASE

LOAD TABLE orders;        -- pin: readers keep a warm copy
RELEASE TABLE orders;     -- unpin
LOAD VECTOR INDEX docs (emb);
LOAD GRAPH social;

LOAD marks a table (or a vector index, or a graph) for readers to hold in RAM or on local disk, so point reads and ANN queries skip object storage. RELEASE clears the pin. How much can be resident, and whether you share the free tier or hold a dedicated reservation, is governed per bucket on Reservation & Hot Cache — a reservation is a budget, LOAD is what spends it.

Large results

A SELECT whose result exceeds the inline limit is spooled: the plane writes the rows to object storage as presigned parts. Large results are streamed to pg clients; over gRPC and HTTP they arrive as a manifest of parts you fetch. For interactive work add LIMIT; the CLI tells you when a result was spooled and suggests one.

Repeated identical reads over an unchanged table are answered from a result cache; a committed write to the table invalidates it, so the cache never serves a stale row.

EXPLAIN

EXPLAIN SELECT customer, SUM(total) FROM orders WHERE event_type = 'sale' GROUP BY customer;

Renders the typed plan as one row and executes nothing: which strategy the planner chose, whether the read fans out across partitions, and which columns prune. Use it to confirm a partition filter is recognised before you load a large table.

JSON columns

JSON is a first-class type and DuckDB’s operators (->, ->>, json_extract, json_keys, unnest(json_extract(col, '$.tags[*]'))) work directly. Extraction is evaluated per row, so for a field every hot read filters on, promote it to a typed column (and index it if you filter by equality). Keep JSON for the long tail and for payloads whose shape you do not own.

Reserved words

DuckDB’s reserved words apply. at, end, order, table, to and user are common column names that must be double-quoted everywhere ("at" TIMESTAMP, WHERE "at" > …). Prefer occurred_at, ends_at, user_id.

Errors & SQLSTATEsLimits & differences