## 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.

```text
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:

```sql
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.

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

```sql
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.

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

```sql
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.

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

```python
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.

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

```sql
-- 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/pst_XcFIohsSoERhYYk_AqrXlQ