14. Failure modes

Every one of these is something people hit. They are ordered roughly by how often.

1. The process dies with no error, exit code 137

   SYMPTOM   Python exits. No traceback. No
             DuckDB error. Exit 137, or the
             container restarts.

   MECHANISM The kernel OOM-killed it.
             `memory_limit` defaults to 80% of
             SYSTEM RAM, which in a container
             is the host's RAM, not the cgroup
             limit.

SET memory_limit = '2800MB';   -- ~70% of a 4GB container
SET temp_directory = '/scratch';

Permanent fix: set memory_limit explicitly from the container limit in every deployment. This is the single most common DuckDB production failure and chapter 13 has the arithmetic.

2. `Could not set lock on file`

   IO Error: Could not set lock on file
   "analytics.duckdb": Conflicting lock is
   held in /usr/bin/python3 (PID 48210)

Another process holds the write lock (chapter 11). The message names it, so ps -p 48210 tells you who.

   COMMON CULPRITS
   ─ a Jupyter kernel you forgot about
   ─ a previous run that did not close its
     connection
   ─ a debugger holding the process open
   ─ two replicas of the same pod

Immediate: kill it, or open read_only=True if nothing is writing. Permanent: exactly one writer, and the architecture from chapter 11 — shared files, not a shared database file.

3. `Out of Memory Error` with plenty of RAM free

   Out of Memory Error: could not allocate
   block of size 256KB (12.0GB/12.0GB used)

DuckDB hit its own memory_limit, not the machine’s. It is telling you the truth about its own budget.

   IN ORDER:
   1. is temp_directory set? If not, it could
      not spill.        → set it
   2. is it a COUNT(DISTINCT) or a high-
      cardinality GROUP BY?
                        → approx_count_distinct
   3. is it an ORDER BY that wants a LIMIT?
                        → add the LIMIT
   4. is preserve_insertion_order holding
      output?           → SET it false
   5. lower threads     → fewer per-thread
                          hash tables
   6. raise memory_limit, if the machine has it

4. The CSV sniffer guessed wrong

   Conversion Error: Could not convert string
   '1002-B' to INT64 in column "product_code"
   at line 412,388

The value that broke it appears after the 20,480-row sample (chapter 5).

SELECT * FROM read_csv('x.csv',
  types = {'product_code': 'VARCHAR'});

Permanent fix: declare types in anything that runs unattended. A sniffer is for exploration. Use store_rejects = true — never ignore_errors = true, which silently drops rows and will eventually cost you a number in a report.

5. Everything is single-threaded and you do not know why

   SYMPTOM   one core at 100%, fifteen idle.

   CAUSES, in order of frequency:

   1. the input is .csv.gz — gzip cannot be
      split. Decompress it, or convert to
      Parquet.
   2. the table has fewer than ~123k rows —
      one row group, one morsel, one thread.
   3. one Parquet file with one row group.
      Same problem.
   4. SET threads was left at 1 somewhere.
   5. hash-join skew — one key holds most of
      the rows (chapter 8).

-- how many row groups does this file have?
SELECT count(*) FROM parquet_metadata('big.parquet');

6. The join built on the wrong side

   SYMPTOM   a join between an obviously small
             table and a large one spills, or
             takes minutes.

   DIAGNOSE  EXPLAIN ANALYZE. Look at the
             HASH_JOIN's build side (left
             child) row count. Compare EC
             with the actual.

Fix: materialise the small side so the count is a fact rather than an estimate.

CREATE TEMP TABLE small AS SELECT ... WHERE ...;
SELECT ... FROM big JOIN small USING (id);

There are no join hints (chapter 8). This is the supported fix.

7. A filter that never pushes down

   SYMPTOM   the profile shows the full table
             scanned when you filtered on the
             partition column, or on a
             clustered column.

   CAUSE     the column is wrapped in a
             function.

-- ✗ scans everything
WHERE date_trunc('month', ts) = DATE '2026-09-01'
WHERE lower(country) = 'de'
WHERE cast(id AS VARCHAR) = '12345'

-- ✓ prunes
WHERE ts >= DATE '2026-09-01' AND ts < DATE '2026-10-01'
WHERE country ILIKE 'de'
WHERE id = 12345

Keep the column bare on one side of the comparison.

8. Reading from S3 is slow on every run

   SYMPTOM   a query over 900 Parquet files in
             S3 takes 40 seconds every time,
             even repeated.

   CAUSE     every run re-fetches every file
             footer. 900 round trips before
             any data.

SET enable_object_cache = true;
SET enable_http_metadata_cache = true;

And the structural fix: fewer, bigger files. Nine hundred files of 3 MB should be thirty files of 90 MB. MinIO’s chapter 7 is the same lesson from the storage side. Better still, an Iceberg table, where the manifest replaces the listing entirely.

9. `read_only` connections do not see new data

   SYMPTOM   a writer commits; the dashboard
             process still shows old numbers,
             indefinitely.

   MECHANISM a read-only connection holds the
             snapshot it opened with. It does
             not poll.

Fix: reconnect. The robust version is chapter 11’s atomic rename — write to a new file, mv it into place, have readers reconnect on mtime change.

10. A `.wal` file that will not go away

   analytics.duckdb
   analytics.duckdb.wal     ← still here

The last process did not shut down cleanly. This is not corruption — the next open replays it.

-- force it in
CHECKPOINT;
-- or, if queries are running in this process
FORCE CHECKPOINT;

The danger is backup. Copying only the .duckdb while a .wal exists gives you a database missing its recent commits, silently.

11. Version and extension mismatch

   Extension "httpfs" is not compatible with
   this version of DuckDB
   ...
   The file was written by DuckDB version
   1.2.0 and cannot be read by this version

Extensions are built per DuckDB version. A cached extension in ~/.duckdb/extensions/ from an older install will not load.

   FIX
   ─ pin the DuckDB version in your lockfile
   ─ bake matching extensions into the image
   ─ rm -rf ~/.duckdb/extensions to clear a
     stale cache
   ─ for a file written by a NEWER version:
     upgrade, or ask the writer to
     EXPORT DATABASE

Since 1.0 the storage format is backwards compatible, so this is mostly a pre-1.0 and a forwards-compatibility problem.

12. A huge `UPDATE` or `DELETE` exhausts memory

Chapter 3’s MVCC undo buffers, which live in RAM and cannot spill.

-- ✗ 300 million rows in one transaction
DELETE FROM events WHERE ts < '2020-01-01';

-- ✓ rewrite instead
CREATE TABLE events_new AS
  SELECT * FROM events WHERE ts >= '2020-01-01';
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;

For a column store, rewriting is usually cheaper than deleting.

13. `fetchall()` or `.df()` kills the process after the query succeeded

   SYMPTOM   the profile shows the query
             finished in 2 seconds. The
             process died afterwards.

   CAUSE     you materialised 200 million rows
             into Python objects or a pandas
             DataFrame, in the same address
             space.

# ✓ stream it
for batch in con.sql("SELECT ...").record_batch(200_000):
    handle(batch)

# ✓ or never bring it back at all
con.execute("COPY (SELECT ...) TO 'out.parquet' (FORMAT parquet)")

Chapter 12’s rule: Arrow and Polars are free; pandas is free for numbers; fetchall() is never free.

14. Silent wrong answers from `union_by_name` and schema drift

   SYMPTOM   a glob over Parquet files returns
             fewer columns than expected, or
             values in the wrong column.

   CAUSE     files whose schemas differ. By
             default DuckDB matches columns
             POSITIONALLY across files.

SELECT * FROM read_parquet('data/*.parquet',
                           union_by_name = true,
                           filename = true);

filename = true is how you find the odd file. And this class of problem is precisely what a table format exists to prevent — Iceberg’s schema evolution resolves columns by field ID, so a rename or a reorder cannot silently misalign anything.

The diagnostic sequence

   Query slow?
     → EXPLAIN ANALYZE
       → files pruned?   (partition/zone maps)
       → columns pruned? (projection)
       → EC vs actual?   (estimates)
       → which side built?
       → one thread busy? (skew, or morsels)

   Query dies?
     → error message, or exit 137?
       137 → memory_limit vs cgroup
       OOM error → temp_directory, then the
                   blocking operator
       lock error → who holds it (PID is in
                    the message)

   Wrong answer?
     → schema drift across files
       (union_by_name, filename)
     → CSV type sniffing
     → a read_only connection with a stale
       snapshot

What to take from this chapter

Exit code 137 with no message is the most common production failure — memory_limit defaults to system RAM, not your cgroup. Set it explicitly, at ~70% of the container limit.
The lock error names the PID holding it. Usually a forgotten Jupyter kernel or a second replica.
An Out of Memory Error is DuckDB’s own budget: check temp_directory first, then COUNT(DISTINCT), then a missing LIMIT, then preserve_insertion_order, then lower threads.
The CSV sniffer samples 20,480 rows. Declare types in anything unattended; store_rejects, never ignore_errors.
Single-threaded usually means .csv.gz, too few row groups, or skew — not a missing setting.
No join hints exist. Materialise the small side to replace an estimate with a count.
A column wrapped in a function cannot prune. Keep it bare.
Repeated slow S3 reads are footer round trips — enable_object_cache, and fewer, bigger files.
Read-only connections hold their opening snapshot and must reconnect.
A .wal is not corruption, but backing up without it loses commits.
Extensions are version-matched; pin and bake them.
Rewrite rather than delete for large row removals.
The query can succeed and .df() still kill you. Stream, or COPY to a file.
Schema drift across globbed files is silent — union_by_name, filename, and ultimately a table format.

Next: when to use DuckDB, and when not — the honest comparison against ClickHouse, Polars, SQLite, Postgres, Spark and a warehouse.

Written by Sepuri Sai Krishna