duckdb out of memory reading large csv

TL;DR

Read the CSV in streaming fashion: push filters into the scan, select only needed columns, and write results out instead of materializing everything. DuckDB can process larger-than-memory CSVs, but only if the query plan avoids holding it all.

duckdb out of memory reading large csv

Use this when

• read_csv on a large file throws out of memory or gets killed
• The CSV is bigger than available RAM
• You need aggregates or filtered extracts, not the whole file in memory

Not for this skill when

• Reading parquet fails on schema mismatch
• Type inference is wrong on a file that fits in memory
• The read completes but is slow

Steps

1. Never SELECT * the whole file into memory. Filter and project in the scan:

SELECT user_id, amount FROM read_csv('huge.csv')
WHERE event_date >= '2026-01-01';

Expected output: DuckDB pushes the filter and projection into the CSV reader, so discarded rows and columns never materialize. This alone fixes most OOMs.

1. Write the result to a file instead of returning it to the client:

COPY (SELECT user_id, sum(amount) FROM read_csv('huge.csv') GROUP BY user_id)
TO 'agg.parquet' (FORMAT PARQUET);

Expected output: the aggregated parquet file on disk. The client never holds the full result, and parquet is far cheaper to scan next time.

1. Convert once, query many times. CSV parsing is the expensive part:

COPY (SELECT * FROM read_csv('huge.csv')) TO 'huge.parquet' (FORMAT PARQUET);

Expected output: a parquet copy of the data. All later analysis scans the parquet, which supports predicate pushdown and column pruning properly.

1. If you must hold a lot in memory, raise DuckDB's memory limit explicitly:

con.execute("SET memory_limit='8GB'")
con.execute("SET temp_directory='/mnt/bigdisk/tmp'")

Expected output: DuckDB may use up to 8GB and spill to the given temp directory. The default limit is a fraction of system RAM, and the default temp dir may be a small disk.

1. For truly huge files, process in chunks with a row filter and append:

-- chunk by a range column, e.g. an id or date, and UNION or append per chunk
COPY (SELECT * FROM read_csv('huge.csv') WHERE id BETWEEN 1 AND 1000000)
TO 'chunk1.parquet' (FORMAT PARQUET);

Expected output: N manageable parquet files. Slower than one streaming pass, but bounded in memory no matter the input size.

Variant phrasings

duckdb csv reading killed

The OS OOM killer fired during the read. Steps 1-2 are the fix; the query was materializing the whole file.

duckdb memory_limit setting

SET memory_limit='8GB' raises the cap, but a query that needs 50GB still dies. Restructure the query before raising the limit.

duckdb read_csv too slow and memory heavy

Convert to parquet once (step 3). CSV has no statistics or column layout, so every scan re-parses everything.

Why it happens

CSV is a row-oriented text format with no statistics, so the reader must parse every byte and DuckDB must hold whatever the query plan materializes. A SELECT * with no filter materializes the entire file, and on a file bigger than RAM that is fatal regardless of how efficient the engine is.

Edge cases

• read_csv_auto samples for types; on huge files pass explicit types to skip sampling surprises.
• Delimiters and quoting misdetected on large files cause parse errors midway, set delim and quote explicitly.
• The temp directory needs free space proportional to the spill, check disk before long runs.
• In-process (Python) DuckDB shares memory with your program, the limit covers both.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstXcFIohsSoERhYYkAqrXlQ