16. Cheat sheet

Everything worth keeping within reach.

Connecting

import duckdb

duckdb.sql("SELECT 42")                        # default, in-memory
con = duckdb.connect()                         # in-memory
con = duckdb.connect('db.duckdb')              # persistent
con = duckdb.connect('db.duckdb', read_only=True)
c2  = con.cursor()                             # independent connection

duckdb                          # in-memory REPL
duckdb db.duckdb                # persistent
duckdb -c "SELECT 42"           # one shot
duckdb -json -c "..."           # JSON output
duckdb -readonly db.duckdb
duckdb < script.sql

The settings that matter

SET memory_limit = '12GB';              -- ~70% of container limit
SET threads = 8;
SET temp_directory = '/scratch';        -- REQUIRED for :memory:
SET max_temp_directory_size = '200GB';
SET preserve_insertion_order = false;   -- big exports, big scans
SET enable_object_cache = true;         -- repeated remote reads
SET enable_http_metadata_cache = true;
SET enable_progress_bar = true;
SET enable_external_access = false;     -- if SQL comes from users
SET checkpoint_threshold = '1GB';
SET extension_directory = '/opt/duckdb-ext';

SELECT name, value, description FROM duckdb_settings();
SELECT current_setting('memory_limit');

Reading files

SELECT * FROM 'x.parquet';
SELECT * FROM 'x.csv';
SELECT * FROM 'x.json';
SELECT * FROM 'data/**/*.parquet';
SELECT * FROM 's3://bucket/path/*.parquet';
SELECT * FROM 'https://host/data.parquet';

read_parquet(path, hive_partitioning = true,
                   union_by_name = true,
                   filename = true)

read_csv(path, delim = '|', header = true,
               types = {'code': 'VARCHAR'},
               sample_size = -1,
               store_rejects = true,
               columns = {...})

read_json(path, format = 'newline_delimited' | 'array',
                columns = {...})

-- inspect before you trust
DESCRIBE   SELECT * FROM 'mystery.parquet';
SUMMARIZE  SELECT * FROM 'mystery.parquet';
SELECT * FROM parquet_schema('x.parquet');
SELECT * FROM parquet_metadata('x.parquet');   -- row groups, stats

Writing

COPY (SELECT * FROM t) TO 'out.parquet' (FORMAT parquet);

COPY (SELECT * FROM t) TO 's3://bucket/out'
  (FORMAT parquet, PARTITION_BY (year, month),
   COMPRESSION zstd, ROW_GROUP_SIZE 1000000,
   OVERWRITE_OR_IGNORE);

COPY t TO 'out.csv' (HEADER, DELIMITER ',');

CREATE TABLE t AS SELECT * FROM 'in.parquet';

EXPORT DATABASE 'backup_dir' (FORMAT parquet, COMPRESSION zstd);
IMPORT DATABASE 'backup_dir';

Secrets and object storage

INSTALL httpfs; LOAD httpfs;

CREATE SECRET s (TYPE s3, PROVIDER credential_chain);

CREATE PERSISTENT SECRET s (
  TYPE s3, KEY_ID '...', SECRET '...', REGION 'eu-west-1');

-- MinIO and other S3-compatible servers
CREATE SECRET m (
  TYPE s3, ENDPOINT 'minio:9000',
  URL_STYLE 'path', USE_SSL false,
  KEY_ID '...', SECRET '...');

SELECT * FROM duckdb_secrets();

Friendly SQL

GROUP BY ALL;                       ORDER BY ALL;
SELECT * EXCLUDE (a, b) FROM t;
SELECT * REPLACE (upper(c) AS c) FROM t;
SELECT min(COLUMNS('.*_at')) FROM t;
FROM t SELECT a, b;                 -- FROM first
FROM t;                             -- a whole query
SELECT a, b, c, FROM t;             -- trailing comma
SELECT x*2 AS y, y+1 AS z FROM t;   -- alias reuse
... UNION ALL BY NAME ...
SELECT 'duckdb'[1:4];               -- 'duck'
WHERE 1 <= x <= 10;
SELECT 5 / 2;   -- 2.5      SELECT 5 // 2;  -- 2

QUALIFY row_number() OVER (PARTITION BY a ORDER BY b) <= 3;
PIVOT t ON month USING sum(amount) GROUP BY country;
UNPIVOT t ON jan, feb INTO NAME month VALUE amt;
SELECT * FROM t USING SAMPLE 1%;

-- joins
ASOF JOIN p ON t.symbol = p.symbol AND t.ts >= p.ts
POSITIONAL JOIN b
LATERAL (SELECT ... WHERE x = outer.id LIMIT 3)

Nested types

[1,2,3]                            -- LIST
{'name': 'ana', 'age': 33}         -- STRUCT
MAP {'a': 1}                       -- MAP

s.field           s.a.b.c          -- struct access, prunes
l[1]              l[1:3]           -- list index / slice
unnest(l)                          -- list → rows
unnest(s, recursive := true)       -- struct → columns
list(x)  list(DISTINCT x)          -- aggregate into a list
list_transform(l, x -> x*2)
list_filter(l, x -> x > 3)
list_reduce(l, (a,b) -> a+b)
j->'$.a.b'    j->>'$.a.b'          -- JSON: value / text

Useful functions

approx_count_distinct(x)      -- HyperLogLog, small memory
approx_quantile(x, 0.99)      -- T-Digest
arg_max(a, b)   arg_min(a, b)
mode(x)  median(x)  quantile_cont(x, [0.5, 0.9])
string_agg(x, ', ' ORDER BY x)
regexp_extract(s, 'p=([^&]+)', 1)
date_trunc('month', ts)   date_diff('day', a, b)
strftime(ts, '%Y-%m')     strptime(s, '%Y-%m-%d')

Macros

CREATE MACRO tax(a, rate := 0.2) AS a * (1 + rate);

CREATE MACRO recent(days) AS TABLE
  SELECT * FROM sales WHERE ts >= now() - to_days(days);
SELECT * FROM recent(30);

Attaching other databases

INSTALL postgres; LOAD postgres;

ATTACH 'dbname=app host=db user=ro' AS pg (TYPE postgres, READ_ONLY);
ATTACH 'x.sqlite' AS lite (TYPE sqlite);
ATTACH 'other.duckdb' AS o;
DETACH pg;

SELECT * FROM pg.public.orders;

-- push real work to the server
SELECT * FROM postgres_query('pg', 'SELECT c, count(*) FROM t GROUP BY 1');

Pushdown into an attached DB: projection and simple filters only. Joins and aggregates run in DuckDB over rows pulled across the wire.

Iceberg and Delta

INSTALL iceberg; LOAD iceberg;
SELECT * FROM iceberg_scan('s3://bucket/wh/db/table');
SELECT * FROM iceberg_snapshots('s3://bucket/wh/db/table');
SELECT * FROM iceberg_metadata('s3://bucket/wh/db/table');

ATTACH 'wh' AS ice (TYPE iceberg, ENDPOINT '...', SECRET s);

INSTALL delta; LOAD delta;
SELECT * FROM delta_scan('s3://bucket/delta/table');

Extensions

INSTALL httpfs;  LOAD httpfs;
INSTALL h3 FROM community;
SELECT * FROM duckdb_extensions();

   httpfs  json  icu  parquet  iceberg  delta
   postgres  mysql  sqlite  spatial  fts  excel
   aws  azure  autocomplete  inet  tpch  tpcds

Diagnostics

EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;

PRAGMA enable_profiling = 'query_tree';   -- or 'json'
PRAGMA profiling_mode = 'detailed';
PRAGMA profiling_output = '/tmp/p.json';

SELECT * FROM duckdb_memory();
SELECT * FROM duckdb_temporary_files();
SELECT * FROM duckdb_tables();
SELECT * FROM duckdb_databases();
SELECT * FROM pragma_database_size();
SELECT * FROM pragma_storage_info('t');

CHECKPOINT;         FORCE CHECKPOINT;

Read a profile in this order: files pruned → columns pruned → rows out of the scan → EC vs actual → which side built → is one thread doing everything.

Python patterns

rel = con.sql("SELECT ...")     # lazy
rel.arrow()          # zero copy
rel.pl()             # zero copy
rel.df()             # zero copy for numerics, not strings
rel.fetchall()       # NEVER for large results
for b in rel.record_batch(100_000):   # streaming, constant memory
    ...

con.execute("SELECT * FROM t WHERE c = ?", ['DE'])   # parameterise
con.executemany("INSERT INTO t VALUES (?,?)", rows)
con.execute("INSERT INTO t SELECT * FROM my_dataframe")  # fastest

# query variables directly (replacement scans)
duckdb.sql("SELECT * FROM my_pandas_df JOIN my_polars_df USING (id)")

CLI dot commands

.mode box | markdown | csv | json | line | duckbox
.maxrows 40      .maxwidth 200
.timer on        .output file.csv       .once
.read script.sql .shell ls
.databases  .tables  .schema  .indexes
.excel           .help                  .quit

Numbers to remember

   vector size                2,048 values
   row group                  122,880 rows (2048 × 60)
   CSV sniffer sample         20,480 rows
   default memory_limit       ~80% of SYSTEM RAM
   default threads            core count
   default checkpoint         16 MB of WAL
   Python wheel               ~40 MB
   duckdb-wasm                ~4 MB compressed
   Spark crossover            100 GB – 1 TB

Troubleshooting

Symptom	Cause	Fix
Exit 137, no error	cgroup OOM kill	SET memory_limit to ~70% of container
Could not set lock	another writer	ps -p <PID from message>; one writer only
Out of Memory Error	DuckDB’s own budget	temp_directory; approx_count_distinct; add LIMIT; preserve_insertion_order=false; fewer threads
Conversion error at line N	sniffer sampled 20,480 rows	declare types; store_rejects
One core busy	.csv.gz, too few row groups, or skew	convert to Parquet; check parquet_metadata
Join spills unexpectedly	wrong build side	CREATE TEMP TABLE the small side
Full scan despite a filter	column wrapped in a function	keep the column bare
Slow S3 every run	footer round trips	enable_object_cache; fewer, bigger files
Reader sees stale data	read-only snapshot	reconnect; atomic rename
.wal file present	unclean shutdown	CHECKPOINT; back up both files
Extension will not load	version mismatch	pin version; rm -rf ~/.duckdb/extensions
Huge DELETE OOMs	MVCC undo in RAM	rewrite the table instead
Dies after the query succeeded	.df() / fetchall()	record_batch(), or COPY to a file
Wrong columns from a glob	positional matching	union_by_name = true, filename = true

The decision

   Multiple PROCESSES writing?
     → a server. Not DuckDB.

   Many concurrent USERS?
     → ClickHouse, a warehouse, Trino.

   Bigger than one machine?
     → Spark, Trino, a warehouse.

   Row lookups and small transactions?
     → SQLite or Postgres.

   Otherwise
     → DUCKDB, and it is probably faster and
       cheaper than what you were going to
       use.

The operational checklist

   □ memory_limit set, ~70% of the container
   □ temp_directory on a real disk, sized
   □ threads from the CPU quota, not the host
   □ extensions baked into the image, version
     pinned
   □ preserve_insertion_order = false for bulk
     exports
   □ enable_object_cache = true for remote reads
   □ enable_external_access = false for user SQL
   □ exactly one writer per file
   □ a rebuild script rather than a backup,
     where the data is derived
   □ parameterised queries everywhere

The whole guide in ten lines

1. A LIBRARY, not a server. Everything good
   and everything limiting follows.
2. COLUMNS, vectors of 2048, all your cores,
   morsel by morsel.
3. FILES ARE TABLES. Parquet, CSV, JSON,
   local or S3, no import step.
4. PUSHDOWN is where the speed comes from:
   files pruned, columns pruned, row groups
   skipped.
5. The SQL is deliberately nicer, and the
   nested types are real.
6. IT SPILLS. Larger-than-memory gets slower,
   not fatal — set temp_directory.
7. ONE WRITER. Share files, not the database.
8. SET memory_limit IN CONTAINERS, or the
   kernel kills you with no message.
9. EXPLAIN ANALYZE before optimising anything.
10. If several processes must write, or many
    users must query, you want a server —
    and that is the only question that
    really decides it.