Why I Replaced Pandas with DuckDB and Parquet in My Analytics Engine

The analytics engine I built — a natural language to SQL system that takes a plain English question, generates SQL from schema context, and executes it against production data — was working. 92% accuracy, 1,600+ queries a day, users happy. Then the dataset grew. And Pandas, which had been quietly doing the execution layer, started showing its limits.

This is the story of replacing Pandas with DuckDB and Parquet, why it matters technically, and what the actual numbers look like.

What Was Wrong with Pandas

Pandas is not a bad tool. It's an excellent tool for the wrong job. The job I was asking it to do was analytical query execution over datasets that were growing into the hundreds of megabytes and beyond. For that specific workload, Pandas has three fundamental problems.

It loads everything into memory eagerly. When you call pd.read_csv() or pd.read_parquet(), Pandas reads the entire file into a DataFrame before you can touch it. If your query only needs 3 columns out of 40, Pandas still loads all 40. If your filter eliminates 90% of rows, Pandas still reads all the rows first. You pay the full I/O and memory cost before a single computation runs.

It stores data in row-oriented format in memory. DataFrames are conceptually row-oriented — each row is contiguous in memory. Analytics workloads are column-oriented — a GROUP BY merchant_id, SUM(amount) query only touches two columns out of potentially dozens. Row-oriented storage means loading and scanning data that your query never uses.

It doesn't parallelize across cores. Python's Global Interpreter Lock (GIL) prevents true multi-threaded execution in Pandas operations^[5]. On a machine with 16 cores, heavy aggregations use one. The other 15 watch.

For a single analyst running exploratory queries on a laptop, none of this matters. For a system serving 1,600+ queries a day with response time SLAs, it mattered a lot. Average response latency was climbing. On heavy aggregation queries — GROUP BY across large date ranges, multi-table joins — we were hitting 8-12 seconds. Timeouts were occurring on the largest queries.

The Alternative: Columnar Storage + Columnar Execution

The combination that solved this was Parquet files as the storage format and DuckDB as the query engine. These two tools are designed around the same insight: for analytical workloads, reading less data is faster than reading all data quickly.

Parquet: What's Actually Inside the File

Parquet is a columnar file format, but "columnar" undersells what it actually does^[3]. Understanding the internal structure is what makes the performance numbers make sense.

A Parquet file is organized into row groups — horizontal slices of the dataset, each containing the same N rows. Within each row group, data is stored by column — all values for column A together, then all values for column B, and so on. Each column within a row group is called a column chunk, which is further split into pages of compressed data.

This structure enables two critical optimizations:

• Projection pushdown. If your query only needs columns A and C out of 40 columns, a Parquet reader can seek directly to column chunks A and C in each row group and read only those. The other 38 columns are never touched — not read from disk, not loaded into memory. The DuckDB benchmark shows this delivers an 11x speedup over Pandas on a simple projection query (0.19s vs 2.13s)^[1].
• Predicate pushdown. Each row group stores statistics per column: the minimum and maximum value. Before reading any actual data, a query engine can compare the filter condition against these statistics and skip entire row groups where the filter can't possibly match. If you're filtering for WHERE date = '2026-01-15' and a row group's date column has min=2026-03-01, the entire row group is skipped without reading a single byte of row data. The benchmark result: 57x speedup on filter-heavy queries (0.04s vs 2.29s)^[1].

Additionally, Parquet applies dictionary encoding before compression on low-cardinality columns. A column with values like active, inactive, pending gets encoded as integers 0, 1, 2 — and the compression algorithm works on that integer stream, not the repeated string. This is why Parquet files are 3-5x smaller than equivalent CSV files for typical business data^[6], and why that compression compounds with ZSTD or Snappy applied on top.

DuckDB: The Query Engine That Reads Only What It Needs

DuckDB is an in-process OLAP database^[2]. No server to run, no network round-trip — it runs embedded inside your Python process like SQLite, but designed for analytical rather than transactional workloads.

Its query execution model is vectorized^[4]: instead of processing one row at a time (like traditional row engines) or writing custom compiled code per query (like some JIT engines), DuckDB processes data in batches — vectors of 1,024 values by default. Each operation applies to an entire vector at once, taking advantage of SIMD CPU instructions. The hardware is doing multiple operations per CPU cycle instead of one.

DuckDB also parallelizes across all available cores automatically. A GROUP BY aggregation on a large dataset fans out across every thread, reduces locally, then merges. No GIL. No manual partition logic. The parallelism is the default.

When DuckDB queries a Parquet file, it performs the full optimization chain:

1. Reads the Parquet file footer (metadata only — column statistics, row group offsets, schema).
2. Uses predicate pushdown to identify which row groups can be skipped based on the filter condition.
3. Uses projection pushdown to identify which column chunks need to be read.
4. Reads only the qualifying column chunks from qualifying row groups.
5. Decompresses and applies vectorized operators (filter, aggregate, join) on the data.
6. Returns the result — without the source data ever being fully materialized in memory.

The memory difference is dramatic. For a streaming benchmark on the NYC Taxi dataset, DuckDB used 0.3 GB peak memory vs Pandas' 248 GB — an 800x reduction — because DuckDB processes row groups sequentially and discards them rather than holding the entire dataset in RAM^[1]. For large datasets, this isn't an optimization. It's the difference between the query running and the machine running out of memory.

The Migration

The analytics engine's architecture before migration: the LLM generates SQL, the execution layer reads the relevant tables from PostgreSQL into Pandas DataFrames using pd.read_sql(), runs the SQL logic as Pandas operations, and returns results.

The problem with this approach is that PostgreSQL's strengths — transactional integrity, row-level locking, ACID compliance — are wasted on analytical read-only queries. And routing large analytical reads through Pandas on top of it compounded the inefficiency.

The new architecture:

1. Analytical data exports from PostgreSQL are written as Parquet files (partitioned by date where applicable) on the local filesystem or S3.
2. The LLM generates SQL as before — same prompt, same schema context.
3. Instead of Pandas, DuckDB executes the SQL directly against the Parquet files: duckdb.sql("SELECT ... FROM 'data/transactions/*.parquet' WHERE ...").
4. DuckDB returns an Arrow table; we convert to the response format needed downstream.

The migration surface was small. DuckDB speaks standard SQL — the LLM's generated queries required almost no changes. The main adaptation was switching from pd.read_sql() against a live PostgreSQL connection to DuckDB querying Parquet snapshots. For analytical queries (aggregations, trend analysis, cross-table joins over historical data), this is the right architecture anyway — you don't want ad-hoc analytical loads hitting your transactional database.

Concrete Code: Before and After

Before — Pandas over PostgreSQL:

df = pd.read_sql(generated_sql, postgres_conn)
result = df.to_dict(orient="records")

After — DuckDB over Parquet:

result = duckdb.sql(generated_sql).fetchdf().to_dict(orient="records")

The SQL the LLM generates is the same. The table references resolve to Parquet paths instead of PostgreSQL tables. DuckDB handles the rest.

What Changed After Migration

The improvements weren't marginal. For aggregation-heavy queries — the ones Pandas was slowest at — average response time dropped from 8-12 seconds to under 1 second. Complex multi-table joins over large date ranges went from timeout territory to 2-3 seconds. Memory usage during peak query load dropped significantly, eliminating the spikes that were occasionally causing OOM conditions on the analytics server.

The Parquet storage format itself contributed independently: the same data that was stored as CSV for Pandas ingestion was now stored as Parquet with Snappy compression. Storage footprint dropped by roughly 3x on the same data. Read I/O dropped proportionally, since Parquet reads only the columns and row groups the query touches.

The performance characteristics align closely with the benchmarked numbers: DuckDB's 57x advantage on filter-heavy queries and 11x advantage on projection queries^[1] map directly to the kinds of analytical SQL the NLQ engine generates — date filters, merchant filters, aggregations across a subset of columns.

When This Swap Makes Sense

This isn't a universal replacement for Pandas. Pandas remains the right tool for:

• Row-level transformations and feature engineering where you're operating on individual records.
• Small datasets where load time is negligible and the API ergonomics matter more than raw speed.
• Exploratory data science workflows where you're mutating and inspecting DataFrames interactively.

DuckDB + Parquet is the right stack when:

• Your workload is predominantly analytical — aggregations, group-bys, multi-column filters, joins across large tables.
• Your dataset is large enough that eager full-load into memory creates latency or OOM risk.
• You need SQL semantics — DuckDB executes standard SQL directly, no DataFrame API translation required.
• You're already storing data in Parquet (or willing to) — the predicate and projection pushdown only help if the storage format supports them.

The NLQ analytics use case sits squarely in the DuckDB + Parquet zone. The LLM generates SQL. The data is historical and append-only. The queries are read-only aggregations. Pandas was never the right fit — it was just the default.

The Core Insight

Pandas operates on a simple model: load data into memory, then operate on it. That model breaks down as datasets grow because the load step becomes the dominant cost — both in time and memory — before any computation runs.

DuckDB and Parquet together implement a different model: push the query into the data. Read only the columns you need. Skip the row groups you can prove are irrelevant. Process in parallel using all available cores. Return a result without ever fully materializing the source dataset in memory.

For analytical workloads, "read less" beats "read fast." The tools that implement this properly — columnar storage, vectorized execution, predicate and projection pushdown — deliver order-of-magnitude improvements not because they have faster CPUs, but because they do dramatically less work.