DuckDB vs Polars: SQL engine or DataFrame library

DuckDB and Polars land on the same shortlist because they do the same work: fast analytical queries on one machine, over Parquet, using every core. So the question gets asked as "which one is faster", and that is the least useful way to ask it.

Both are columnar, multi-threaded, Arrow-friendly and lazy. Published benchmarks put them in the same class and hand the crown back and forth depending on who ran them, which versions they used, and what the query looked like. Choose on speed and you are deciding between two engines that will both be fast enough, on evidence that changes with the next release.

The difference that lasts is interface and role. DuckDB is a SQL database engine. Polars is a DataFrame library. One has tables, a file, and a query language much of your team already reads. The other has expressions, and lives inside your Python code.

The short answer

Pick Polars when the work is Python: row-level transformation logic, feature engineering for a model, validation rules, anything that belongs in a function you can unit-test. The expression API composes, your editor can check it, and a twenty-step pipeline stays readable where SQL would become a stack of nested CTEs.

Pick DuckDB when the work is a question about data: aggregations, joins across several tables, window functions, ad-hoc exploration. Also when anything other than your Python process needs the result — a BI tool, dbt, a colleague at a terminal, or you in six months.

Then stop choosing. They share Arrow, so data moves between them inside one process with no export step. For most Python teams the right answer is both, often in the same file.

What each one is

DuckDB is an in-process analytical database — the usual shorthand is "SQLite for analytics". You import it into Python, Node, Go, Rust or the CLI, and a columnar query engine runs inside that process. It has its own database file with real tables, schemas and transactions, and it reads Parquet, CSV and JSON directly, including from object storage. Extensions add the rest: httpfs for S3, postgres and sqlite to query those databases in place, spatial, full-text search, Iceberg and Delta. There is a command-line shell and a local web UI, which matters more than it sounds — a database someone can open and poke at is a different thing from a library. What it is good at in production is its own post.

Polars is a DataFrame library with a Rust core and bindings for Python, Rust and Node — Python being the one nearly everyone uses. You do not write SQL; you write expressions:

import polars as pl

monthly = (
    pl.scan_parquet("transactions.parquet")
    .filter(pl.col("amount") > 0)
    .with_columns((pl.col("amount") * 0.029).alias("fee"))
    .group_by("merchant_id")
    .agg(pl.col("fee").sum())
    .collect()
)

scan_parquet returns a LazyFrame, so nothing runs until collect. In between, Polars optimises the plan — reading only the columns the query mentions, pushing the filter down to the file reader. That mechanism, and why it matters more than raw speed, is the subject of Polars vs pandas.

What Polars does not have is a database. No persistent format of its own, no catalog, no transactions, nothing a second process can connect to. It reads and writes files — Parquet, Arrow IPC, CSV, Delta — and you manage them. That is not a gap in the library. It is the category it is in.

At a glance

	DuckDB	Polars
Category	SQL database engine, in-process	DataFrame library, in-process
Core	C++	Rust
You write	SQL	Expressions, in Python or Rust
Lazy	Every query is planned and optimised	LazyFrame — scan_* then collect
Persistence	Its own database file, plus files	Files you manage; no format of its own
Transactions	Yes, ACID, one writing process	None
Larger than memory	Spills to a temp directory	Streaming engine, processes in batches
Reaches other systems	Extensions: httpfs, postgres, sqlite, Iceberg	Python's ecosystem, and IO plugins
Who else can use it	CLI, local UI, BI tools via drivers, dbt	Your Python code, and nothing else
Natural home	Batch SQL, serving, notebooks, dbt models	Pipelines, ML feature code, tested transforms

The difference that decides it: who reads the code

This is the question I would ask first, and it has nothing to do with performance.

SQL is the widest-read language in any data team. An analyst who has never opened your repository can read a DuckDB model, change a filter and see what happens. dbt has a DuckDB adapter (dbt-duckdb), so the same SQL fits a transformation framework people already staff for. BI tools connect over ODBC and JDBC drivers. SQL is also declarative, which is exactly right for "group these rows and sum that column" and exactly wrong for "apply these eleven business rules in order".

Polars expressions are read by Python engineers. That is a narrower audience and a real benefit within it. Expressions are values, so you can build them in a loop, store them in a dictionary, and pass them around as arguments. A transformation becomes a function with a signature, which means it can be imported, type-checked, and tested with pytest against a small frame. Twenty chained steps stay legible. Twenty CTEs do not.

So: if the next person to touch this code is an analyst, write SQL. If it is a Python engineer maintaining logic that changes often, write expressions. Most teams have both kinds of work, which is why most teams end up with both tools.

Persistence: DuckDB has a database, Polars has files

DuckDB gives you a .duckdb file with tables inside it, schemas, views, constraints and ACID transactions. One process holds write access at a time; many can read. That is a real database with a real durability story, and it means state can live between runs without you inventing a convention for it.

Polars has no such thing. State between runs is Parquet files in a directory you designed, named by a scheme you chose, with correctness enforced by your code. For a pipeline that is often fine — Parquet on object storage as the source of truth is a pattern I recommend anyway, and it keeps you out of any one engine's format. But notice the work that moves onto you: partitioning, atomic swaps, schema evolution, and a story for what happens when a job dies halfway through writing.

If you want an engine whose file is the store, that is the DuckDB side of this comparison, and it is the same argument as DuckDB vs SQLite from a different direction.

Larger than memory

Both claim to handle data bigger than RAM. Both do, with caveats worth knowing.

DuckDB has a memory_limit setting and a temp_directory. When a query needs more than the limit, it spills intermediate state to that directory and carries on. This applies to ordinary SQL — there is no separate mode — and the practical advice is to set the memory limit below the container's limit and give the temp directory real room, because discovering this under load is an unpleasant way to learn it.

Polars handles it with a streaming engine that processes data in batches instead of materialising everything. It has come a long way, but its operation coverage and the exact way you enable it have changed between releases, so check the current docs rather than a post like this one. The engine is under active development; anything I pin here about flags will age badly.

Neither is unlimited. A join whose output is far larger than either input can exhaust a machine in either tool. The reliable fix is the same for both: partition the data and read less of it.

Using both, over Arrow

Here is the part most comparison posts skip. Both engines hold data in Arrow buffers, so handing a table from one to the other costs no serialisation and no re-encoding. The practical result is that "DuckDB or Polars" is a false choice inside a Python process.

import duckdb
import polars as pl

# Polars: row-level logic, lazy, only the columns it needs
tx = (
    pl.scan_parquet("transactions/*.parquet")
    .filter(pl.col("status") == "settled")
    .with_columns((pl.col("amount") * 0.029).alias("fee"))
    .collect()
)

# DuckDB: the aggregation, in SQL, straight over that DataFrame
monthly = duckdb.sql("""
    SELECT
        date_trunc('month', booked_at) AS month,
        merchant_id,
        sum(amount) AS amount,
        sum(fee)    AS fee
    FROM tx
    GROUP BY ALL
    ORDER BY month, merchant_id
""").pl()          # and back to Polars

print(monthly.head())

Two things are happening there. FROM tx works because DuckDB looks for that name in the calling scope and finds the Polars DataFrame — a replacement scan. There is no register call and no copy into a DuckDB table first. And .pl() turns the result back into a Polars DataFrame through Arrow. Install pyarrow alongside both; the conversions lean on it.

Two details to keep in mind. The name has to be visible in the frame that calls duckdb.sql, so inside a function it must be a local or a global there, not something three frames up. And recent DuckDB versions will scan a LazyFrame directly, so you can skip the .collect(); on an older version, collect first.

That is the whole integration. It is why I treat the two as one toolbox rather than a decision.

When Polars is the right call

• The logic is Python and will keep changing: business rules, feature engineering, cleaning with real exceptions in it.
• You want transformations as functions — importable, type-checked, unit-tested against small frames.
• The pipeline is long. Ten or more dependent steps read better as a chain than as CTEs.
• You are feeding a machine-learning stack that already speaks Python and Arrow.
• Nothing outside your process needs to query the result.

When DuckDB is the right call

• The work is expressible as a query: joins, aggregations, window functions, ad-hoc questions.
• Someone who is not a Python engineer will read or run it — an analyst, a dbt project, a BI tool.
• You want persistent tables and transactions, not a directory of files and a naming convention.
• You need to reach data where it lives: Parquet on S3, a Postgres database, a SQLite file, an Iceberg table. If the source is Postgres and you are weighing whether to query it in place, DuckDB vs Postgres is the closer comparison.
• The result has to be served to something, or explored interactively by a person.
• The same query must run identically on a laptop, in CI and on the server.

Where pandas still fits

Pandas is not the loser in this story, it is just the eager one. If your data fits in memory with room to spare, your team knows the API, and the job takes two seconds either way, a rewrite buys nothing — and pandas still has the deepest ecosystem for plotting, statistics and machine-learning glue. Switch on a symptom, not a benchmark: a job killed for memory, or a transformation chain nobody can follow. The mechanics of why the other two pull ahead are in Polars vs pandas: the three differences that matter.

If you take one thing from this

Ask what the code is, not how fast it is. A question about data is SQL, and SQL belongs in a database engine. Logic that changes every week is Python, and Python belongs in expressions you can test. When a pipeline holds both — and most do — Arrow means you do not have to pick.

The long version, with runnable code across DuckDB, Parquet, Arrow and Polars on real datasets, is my book Local-First Analytics. If you would rather have someone look at your pipeline and say which parts belong in which tool — and whether the single-node design holds at all — a Data Platform Audit is a week and a written roadmap you keep. The scoping call below is free.