DuckDB 1.5.6: Querying 40 MB CSV Without a Database

I took a 40.1 MB, 1,000,000-row CSV on a 16-thread Windows 11 machine and stopped importing it into a database before querying it. Same file, same machine, same aggregation: end to end time dropped from 5.06 seconds to 0.32 seconds. Below are the raw numbers I measured, the scripts that produced them, and the eight errors I actually hit along the way — with their exact text. Nothing here is quoted from a second-hand tutorial.

TL;DR: DuckDB 1.5.6 runs a GROUP BY over that 40.1 MB CSV in 0.311 s (0.323 s median) with no database file at all. Converted to zstd Parquet the same data is 7.7 MB, and a single-column sum() drops from 0.283 s to 0.0142 s — 19.9x faster. For contrast, SQLite spent 4.426 s just inserting the rows row by row, then 0.6347 s median on the same aggregation, which is 138.9x slower than DuckDB's in-memory table.

When DuckDB is the right tool, and when it is not

DuckDB is not "a faster SQLite". It is a columnar analytical engine that runs inside your process. There is really only one question that decides it: does your query scan a lot of rows but touch only a few columns?

• Use it for: ad-hoc analysis of logs, event streams and export dumps; one-off reconciliation; merging a pile of CSV or Parquet files into one result; deciding locally whether a problem is worth a real warehouse.
• Do not use it for: concurrent writers in multiple processes; row-level transactions and high-concurrency small writes; as the OLTP primary store for a live service (it is positioned as an OLAP system).

The tempting mistake is "it is fast, so let us replace SQLite". These solve different problems. SQLite's strength is single-row writes and transactions; DuckDB's strength is whole-column scans and aggregates. Everything I measured below is analytical, full-scan work and says nothing about write-heavy or point-lookup loads.

Environment and method (reproducible)

Every value below was read on this machine. The DuckDB version was cross-checked against the official GitHub Releases page for duckdb/duckdb, whose latest release is v1.5.6, published 2026-09-28:

Item	Measured
OS	Windows 11 (10.0.26300)
Python	3.13.14
duckdb	1.5.6 (PRAGMA version returns v1.5.6)
pandas / pyarrow	3.0.6 / 26.0.0
sqlite3	3.53.1
threads (default)	16
memory_limit (default)	12.5 GiB

The last two rows agree with the official Configuration reference, which lists memory_limit (alias max_memory) as 80% of RAM and threads as the number of CPU cores. Eighty percent of this machine's 16 GB is exactly the 12.5 GiB I measured.

The dataset is synthetic access-log data I generated: 1,000,000 rows x 6 columns, 5.87 s to write, 42,029,551 bytes (40.1 MB) on disk:

import csv, random
N = 1_000_000
PATHS = ["/", "/pricing", "/docs", "/api/v1/query", "/blog/post-42", "/login", "/assets/app.js", "/search"]
STATUS = [200, 200, 200, 200, 301, 404, 500]
COUNTRIES = ["US", "DE", "JP", "CN", "GB", "FR", "BR", "IN", "AU", "CA"]
rnd = random.Random(20261020)
with open("events.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f)
    w.writerow(["ts", "user_id", "path", "status", "bytes", "country"])
    for i in range(N):
        w.writerow([1750000000 + i // 10, rnd.randrange(1, 500001), rnd.choice(PATHS),
                    rnd.choice(STATUS), rnd.randrange(120, 900000), rnd.choice(COUNTRIES)])

Querying the file directly: 40 MB in 0.32 seconds

There is no CREATE TABLE step. A file path works as a table name:

import duckdb
con = duckdb.connect()
sql = "SELECT status, count(*) AS n, avg(bytes) FROM 'events.csv' GROUP BY status ORDER BY status"
print(con.sql(sql).fetchall())
# [(200, 571537, 450285.33), (301, 143115, 450638.17),
#  (404, 142243, 449954.56), (500, 143105, 449404.49)]

Three runs each, taking the minimum and the median:

Query path	Time
CSV directly, GROUP BY status	min 0.311 s / median 0.323 s
CSV directly, sum(bytes) (one column)	0.283 s
CSV directly, count(*) with WHERE status = 500	0.306 s
CREATE TABLE AS SELECT into an in-memory table	0.357 s
Same aggregation once loaded	min 0.0045 s / median 0.0046 s (70.7x faster than querying the CSV)

One thing is easy to miss: querying a CSV re-parses the text every time, so "fast" is relative to "import into a database first", not relative to "data already in memory". If you will hit the same data repeatedly, spend the 0.357 s to load it once and every later aggregation lands in the 4.6 ms range.

I also confirmed the inferred schema: ts, user_id, status and bytes came back as BIGINT, path and country as VARCHAR.

Why it is fast: columnar storage, projection pushdown, zonemaps

Exporting the same data to zstd Parquet makes the mechanism obvious:

Metric	Measured
COPY to Parquet	0.273 s
Parquet size	8,043,277 bytes (7.7 MB)
Compression vs CSV	5.23x (80.9% smaller)
sum(bytes) on Parquet (one column)	0.0142 s (19.9x faster than the 0.283 s on CSV)
count(*) with status = 500 on Parquet	0.0082 s (37.4x faster than the 0.306 s on CSV)

Two mechanisms are at work, and the official Parquet page states both plainly. Projection pushdown: "when querying a Parquet file, only the columns required for the query are read". Filter pushdown: filters are pushed into the scan and "can even be used to skip parts of the file using the built-in zonemaps". I verified the first one with a column-count ladder:

Query	Time (relative to count(*))
count(*)	0.0015 s (1.0x)
sum(bytes) (1 column)	0.0073 s (4.8x)
4 columns summed	0.0150 s (9.9x)
6 columns summed (incl. two string columns)	0.0196 s (13.0x)

Cost climbs roughly linearly with the number of columns you touch, which means SELECT * followed by filtering in Python is the worst possible pattern. Push the filter into SQL or you are paying for every column.

The second mechanism I could not demonstrate, and that is worth saying out loud. I meant to measure row-group skipping from sorting, but my generated ts is 1750000000 + i // 10 — already monotonically increasing. The "unsorted" file had essentially the same per-row-group statistics as the sorted one, and the two queries came in at 0.0088 s and 0.0082 s, inside the noise. The lesson transfers though: check whether your column is naturally correlated before paying to sort for it.

Same machine, same data: SQLite and pandas

Approach	Time / size
SQLite row-by-row executemany import	4.426 s, database file 33.9 MB
SQLite aggregation (same GROUP BY)	min 0.6057 s / median 0.6347 s
SQLite WHERE status = 500, no index	0.1143 s
Building an index on status	0.613 s (query then drops to 0.0074 s)
pandas read_csv	1.596 s, DataFrame 55.7 MB in memory
pandas groupby().agg()	0.0391 s
DuckDB in-memory table aggregation	median 0.0046 s

The end-to-end totals are the honest comparison: DuckDB querying the file with no database took 0.32 s; SQLite needed 5.06 s including import (about 15.7x more); pandas needed 1.635 s including the read (about 5.1x more). On memory, pandas holds a 55.7 MB DataFrame resident, while DuckDB ran the same aggregation under a 512 MB limit and wrote zero spill files.

This is not a claim that DuckDB beats SQLite. I used SQLite for the full-table aggregation it is not built for, and I never tested the single-row transactional work it is built for. The scope is analytical full scans, and on point lookups and small writes the ranking would reverse.

Memory and spill: what happens at a 48 MB limit

I pointed temp_directory at an empty directory, then lowered memory_limit step by step against the same high-cardinality grouping:

memory_limit	Result
512 MB	OK, 0.038 s, 0 spill files / 0 bytes
128 MB	OK, 0.060 s, 8 spill files / 12,910,592 bytes
48 MB	OutOfMemoryException (text below)

The 128 MB row is the important one: the query did not fail, it got 58% slower, and the cost was 12.3 MB of temporary files on disk. "No OOM" does not mean "no spill" — if things get slower, go look at temp_directory.

The full error at 48 MB, unedited:

OutOfMemoryException: Out of Memory Error: failed to pin block of size 256.0 KiB (45.6 MiB/45.7 MiB used)

Possible solutions:
* Reducing the number of threads (SET threads=X)
* Disabling insertion-order preservation (SET preserve_insertion_order=false)
* Increasing the memory limit (SET memory_limit='...GB')

I tested the second suggestion: flipping preserve_insertion_order from its default true to false still hit OOM at the 48 MB limit (reported usage moved from 45.8 MiB to 46.5 MiB, no meaningful change). It helps in some shapes of query, just not this one — do not treat it as a universal escape hatch.

Troubleshooting: 8 errors, with the exact messages I hit

Every one of these was triggered on this machine.

Error 1 — wrong column name, one candidate offered

BinderException: Binder Error: Referenced column "nope" not found in FROM clause!
Candidate bindings: "bytes"

Cause: nope does not exist in SELECT bytes, nope FROM ev. Fix: run DESCRIBE ev first and use the inferred names rather than guessing from the CSV header — casing and spacing get normalised.

Error 2 — dirty data silently demotes the whole column to VARCHAR

This is the nastiest one. A four-row CSV where one value is not-a-number:

BinderException: Binder Error: No function matches the given name and argument types 'sum(VARCHAR)'. You might need to add explicit type casts.
	Candidate functions:
	sum(DECIMAL) -> DECIMAL
	sum(BIGINT) -> HUGEINT

Cause: the sniffer sees a non-numeric value and types the entire column as VARCHAR, and sum() has no VARCHAR overload. sniff_csv() shows the decision: [{'name': 'id', 'type': 'BIGINT'}, {'name': 'amount', 'type': 'VARCHAR'}]. Fix: declare column types explicitly, or read with all_varchar=true and TRY_CAST yourself.

Error 3 — declaring the type surfaces the dirty row

ConversionException: Conversion Error: CSV Error on Line: 4
Original Line: 3,not-a-number
Error when converting column "amount". Could not convert string "not-a-number" to 'INTEGER'
...
  sample_size = 20480
  ignore_errors = false

Cause: types={'amount':'INTEGER'} was set and row 4 cannot be converted. Note the sample_size = 20480 in the diagnostic block: the sniffer only samples 20,480 rows, so dirty data past row 20,480 is invisible during auto-detection and the failure is deferred until conversion time.

Error 4 — `ignore_errors=true` drops rows silently

# four rows; the bad one is skipped, so sum(amount) = 10 + 20 + 40 = 70
SELECT count(*), sum(amount) FROM read_csv_auto('bad.csv', types={'amount':'INTEGER'}, ignore_errors=true)
-- [(3, 70)]

Cause: no error, no warning — four rows became three. Fix: keep ignore_errors=true for exploration where you accept losing data. For reconciliation work use TRY_CAST so bad values become explicit NULLs, and turn "how many did I lose" into a number you can query.

Error 5 — type mismatch in a predicate

ConversionException: Conversion Error: Could not convert string 'yesterday' to INT64

LINE 1: SELECT * FROM ev WHERE (ts = 'yesterday')
                                    ^

Cause: ts is BIGINT and I compared it to a string. Fix: convert the timestamp properly; do not count on implicit casting (the official old_implicit_casting default is false).

Error 6 — table already exists

CatalogException: Catalog Error: Table with name "ev" already exists!

Fix: use CREATE OR REPLACE TABLE, or start the script with DROP TABLE IF EXISTS.

Error 7 — two processes, one .duckdb file

IOException: IO Error: Cannot open file "...\locktest2.duckdb": another program is using this file, process cannot access it.

File is already open in
D:\...\python.exe (PID 18316)

Cause: the persistent file holds a single-process write lock; if a Jupyter kernel or an ETL script still has it open, nothing else gets in. Note that calling duckdb.connect() on the same path inside the same process does not raise (the instance is shared), which is exactly why this survives local testing and then breaks under a scheduler. Fix: for analytical work, query read-only files (CSV or Parquet) instead of writing a .duckdb file; if you must persist, guarantee a single writer at a time.

A related one: opening a read_only=True connection while a read-write connection exists gives ConnectionException: Can't open a connection to same database file with a different configuration than existing connections — the configuration has to match.

Error 8 — OOM at the memory limit

Text in the previous section. Priority order for fixing it: first question whether the intermediate result needs to be that large (filter before aggregating, select fewer columns); then consider threads; and only then raise memory_limit.

FAQ

Q: Can DuckDB replace my PostgreSQL?

A: No. It is an in-process analytical engine, not a multi-user OLTP database. For server-side PostgreSQL work, see PostgreSQL 18.6 replication slots: when a slot nobody reads eats your disk.

Q: Why is my Parquet query not faster?

A: Check whether you wrote SELECT *, and whether the filtered column has usable zonemaps — the official docs are explicit that filter pushdown "will depend on whether or not your Parquet file contains zonemaps". Also, on a single file of a few tens of megabytes the OS cache often masks the difference, so run it several times and take the minimum.

Q: How much memory does a million rows need?

A: For this 1,000,000 x 6 dataset, aggregation under a 512 MB limit produced no spill files at all; it only started writing to disk at 128 MB, where it spilled 12.3 MB. Persisted as a .duckdb file the data is 8.3 MB.

Q: How does it fit with pandas?

A: The pattern I found cleanest: let DuckDB filter and aggregate so the result set is small, then hand it over with df = con.sql(...).df(). Doing the reverse — reading everything into pandas and filtering there — is the 1.635 s path above.

Scope of this article

All of the following is untested, and I am not extrapolating: multi-process concurrent writes; real remote S3 or HTTP files (httpfs); datasets larger than this machine's memory; write-heavy and point-lookup workloads. Every conclusion is bounded to "Windows 11 + Python 3.13.14 + duckdb 1.5.6 + one million synthetic rows on a single machine".

If the "how does memory actually get carved up" angle is what you came for, the same local-measurement method is used in vLLM 0.31.0 KV cache: paged blocks measured against two allocators and Attention from scratch: NumPy line by line, checked against PyTorch.

👉 Join Xiaomi MiMo Platform: Leading AI model platform with cost-effective inference

👉 Join Aliyun AI: Top AI products with exclusive coupons for business innovation

📌 This article was AI-assisted generated and human-reviewed | TechPassive — An AI-driven content testing site focused on real tool reviews