Pandas vs Polars vs DuckDB

Polars is written in Rust and built on Apache Arrow's columnar memory format, with a query optimizer that compiles expressions to multi-threaded execution plans. Pandas uses NumPy's row-major memory layout, executes operations eagerly in a single thread (by default), and has significant Python overhead per operation. The performance gap widens with larger datasets: on a 1 GB CSV with a groupby-aggregate, Polars is typically 5–20x faster and uses 3–5x less memory than Pandas. Polars also has a lazy evaluation mode (LazyFrame) that automatically optimizes query plans — pushing predicates down and eliminating unnecessary column scans before any data is read.

DuckDB is an in-process OLAP database — think SQLite but columnar and optimized for analytical queries. You can query Pandas DataFrames, Polars DataFrames, Parquet files, CSV files, and JSON directly from DuckDB with standard SQL. DuckDB's vectorized engine often outperforms both Pandas and Polars for aggregation-heavy SQL-style queries, especially on multi-core machines. The key advantage: SQL syntax is familiar to data analysts who don't want to learn DataFrame APIs. DuckDB works in-process (no server), has a free Python package, and handles files larger than RAM via streaming. Ideal for ad-hoc analytics, replacing Spark for local workloads, and as the engine behind dbt for local development.

It depends on your workload. For datasets under 100k rows, Pandas is fast enough and has the richest ecosystem (sklearn, seaborn, statsmodels all accept Pandas DataFrames natively). For datasets between 100k and 10M rows where performance matters, Polars is a significant upgrade worth the API learning curve. For production ETL pipelines processing millions of rows regularly, the memory and speed savings of Polars typically justify the migration. The Polars API is more explicit than Pandas — operations like filtering use expressions rather than boolean indexing, and the lack of an index forces cleaner column-based thinking. Migration is easiest when you have good test coverage of expected outputs.

For single-machine workloads, yes — Polars and DuckDB can handle datasets up to the available RAM plus disk (via streaming mode). Polars' streaming engine and DuckDB's out-of-core processing can handle datasets of several hundred GB on a single 32-core machine. Apache Spark is still necessary when your data genuinely requires distributed processing across multiple machines (multiple TB or PB scale), or when you need Spark's ecosystem: MLlib, Structured Streaming, Delta Lake connectors, or existing PySpark infrastructure. The trend in 2025–2026 is to replace Spark with DuckDB or Polars for datasets that fit on a large single machine (256 GB RAM), dramatically reducing infrastructure cost and pipeline complexity.