Pandas Performance Optimization Guide: When to Switch to Polars or DuckDB in 2024

{</* resource-info */>}

Pandas has been the dominant DataFrame library in Python for over 15 years. With 40,000+ stars on GitHub and millions of monthly downloads, it remains the first tool data scientists reach for when manipulating tabular data. But Pandas was designed in 2008 for datasets that fit comfortably in a single machine’s RAM — typically a few gigabytes. In 2024, analysts routinely work with 10 GB, 100 GB, or even terabyte-scale datasets, and Pandas’ architectural limitations become painful at that scale.

This guide examines why Pandas slows down on large data, explores optimization techniques you can apply before switching tools, and provides a detailed comparison of Polars and DuckDB — the two leading alternatives that have gained serious traction since 2022. We include reproducible benchmarks, migration strategies, and a scenario-based decision matrix.

Why Pandas Struggles with Large Datasets #

Pandas’ performance limitations are architectural, not superficial. Understanding them helps you decide when optimization is worthwhile and when a tool switch becomes necessary.

Single-threaded execution. Pandas uses only one CPU core for most operations. A groupby on a 10 GB dataset will peg a single core at 100% while leaving seven others idle on an 8-core machine. This design decision simplified Pandas’ internals but creates a hard throughput ceiling.

Eager evaluation. Every Pandas operation executes immediately and materializes a full copy of the result in memory. Chaining five DataFrame operations means creating five intermediate copies. On a 2 GB DataFrame, this can exhaust 16 GB of RAM before the final result is computed.

Memory overhead. Pandas stores data in NumPy arrays with object dtype for strings, which uses 8 bytes per pointer plus the actual string storage. A CSV file that is 1 GB on disk often expands to 4-8 GB in Pandas memory. The lack of a compact string representation (until the recent StringDtype backend) hurts efficiency.

No query optimizer. When you write df[df['col'] > 0].groupby('key').agg('mean'), Pandas executes each operation in sequence exactly as written. It cannot reorder operations, push filters down, or eliminate redundant computation — optimizations that databases have performed for decades.

Real-world benchmarks confirm these limitations. On the h2oai db-benchmark, a standard industry benchmark for DataFrame operations, Pandas 2.1 takes over 300 seconds to perform a groupby-aggregation on a 5 GB dataset. Polars completes the same operation in under 10 seconds, and DuckDB in approximately 15 seconds.

Pandas Optimization Techniques #

Before abandoning Pandas, exhaust these optimization strategies. Many production workflows can remain in Pandas with modest tuning.

Code-Level Optimizations #

1. Use categorical dtypes. Converting low-cardinality string columns to category dtype reduces memory usage by 50-90% and speeds up groupby operations by 5-10x. Apply with df['col'] = df['col'].astype('category').

2. Prefer vectorized operations over apply(). apply() iterates row-by-row in Python, bypassing NumPy’s C-optimized loops. Replace df['col'].apply(lambda x: x * 2) with df['col'] * 2 for 100x speedups.

3. Use eval() and query() for complex expressions. Pandas’ eval() uses the NumExpr engine to evaluate compound expressions element-wise without creating intermediate arrays. df.query('A > 0 and B < 5') is faster than boolean indexing for large DataFrames.

4. Avoid chained indexing. df[df.A > 0]['B'] = 1 triggers a SettingWithCopyWarning and may not modify the original DataFrame. Use .loc[df.A > 0, 'B'] = 1 instead — it is faster and correct.

5. Read efficient file formats. Parquet and Feather are 10-50x faster to read than CSV and use significantly less memory. Convert source data once with df.to_parquet('data.parquet') and read with pd.read_parquet(). Parquet also preserves dtypes and supports column pruning.

6. Profile memory usage. Use the memory_profiler package to identify which operations consume the most RAM. Memory pressure often causes slowdowns through excessive swapping before CPU saturation.

Chunk Processing for Memory-Bound Workflows #

When a dataset exceeds available RAM, process it in chunks:

chunk_size = 100_000
results = []
for chunk in pd.read_csv('large_file.csv', chunksize=chunk_size):
    processed = chunk.groupby('key').sum()
    results.append(processed)
final = pd.concat(results).groupby('key').sum()

This pattern keeps memory usage bounded by chunk_size rather than the full dataset size. For aggregation operations, process each chunk and combine results in a final step. For stateful operations like sorting, this approach does not work — external sorting or a database becomes necessary.

Polars: The Rust-Powered DataFrame Revolution #

Polars, created by Ritchie Vink and first released in 2020, is a DataFrame library written in Rust with Python bindings. It leverages the Apache Arrow columnar memory format and supports both eager and lazy execution modes. As of late 2024, Polars has surpassed 30,000 GitHub stars and is the fastest-growing DataFrame library in the Python ecosystem.

Polars Lazy API and Streaming #

Polars’ defining feature is its lazy evaluation engine. When you write:

import polars as pl

lf = pl.scan_parquet('data.parquet')
result = (lf.filter(pl.col('amount') > 100)
            .groupby('category')
            .agg(pl.col('amount').sum())
            .collect())

Polars builds a query plan but does not execute immediately. The .collect() call triggers optimization — the query optimizer reorders filters before joins, eliminates unused columns, and selects optimal execution strategies. This often produces 5-50x speedups over equivalent eager code.

For datasets larger than RAM, Polars offers streaming mode:

result = (lf.filter(pl.col('amount') > 100)
            .groupby('category')
            .agg(pl.col('amount').sum())
            .collect(streaming=True))

Streaming mode processes data in batches, keeping memory usage constant regardless of dataset size. It supports filters, projections, groupby aggregations, and joins — covering the majority of ETL workloads.

Key Polars advantages:

• Multithreaded execution on all CPU cores by default
• Arrow-native throughout — zero-copy interoperability with PyArrow and Pandas
• Consistent API for eager (pl.DataFrame) and lazy (pl.LazyFrame) modes
• No index — simpler mental model than Pandas’ row/column duality
• Zero external dependencies — single pip install

Polars limitations:

• Smaller ecosystem than Pandas (though growing rapidly)
• Different API requires learning curve for experienced Pandas users
• Some operations (rolling window joins, certain reshapes) are less mature
• Debugging lazy queries can be harder than stepping through eager code

DuckDB: The In-Process OLAP Database #

DuckDB, developed at the Dutch CWI research institute and first released in 2019, is an embedded analytical database that runs inside your Python process. It speaks SQL, not a Python DataFrame API, but integrates seamlessly with Pandas, Polars, and Arrow.

DuckDB’s architecture borrows from high-performance analytical databases:

• Cost-based optimizer. DuckDB’s query optimizer uses table statistics to choose join orders, predicate pushdown strategies, and parallelization plans — the same technology that powers Snowflake and BigQuery.
• Vectorized execution. Operations process data in compressed vectors (batches of 1,024-2,048 rows), maximizing CPU cache efficiency and SIMD instruction usage.
• Zero external dependencies. Like Polars, DuckDB is a single pip install with no server to configure.

DuckDB + Pandas Integration Patterns #

The most powerful DuckDB pattern is querying Pandas DataFrames directly with SQL:

import duckdb
import pandas as pd

df = pd.read_parquet('sales.parquet')

result = duckdb.sql("""
    SELECT region, SUM(revenue) as total_revenue
    FROM df
    WHERE date >= '2024-01-01'
    GROUP BY region
    ORDER BY total_revenue DESC
""").to_df()

DuckDB reads the Pandas DataFrame via zero-copy Arrow conversion, pushes the WHERE clause filter down to avoid materializing intermediate results, and parallelizes the groupby across all CPU cores. The result converts back to a Pandas DataFrame via .to_df().

Other powerful patterns include:

• Direct Parquet queries. duckdb.read_parquet('*.parquet') queries Parquet files without loading them into memory — DuckDB scans only the required row groups and columns.
• Window functions. ROW_NUMBER(), LEAD(), LAG(), and custom frame specifications are fully supported and significantly faster than Pandas’ rolling() and expanding().
• CTE and subquery support. Complex analytical queries with multiple CTEs execute efficiently without materializing intermediate DataFrames.

Head-to-Head Benchmark: Pandas vs Polars vs DuckDB #

We benchmarked three libraries on a 5 GB synthetic dataset (100 million rows) of sales transactions stored in Parquet format. Tests ran on a c5.4xlarge AWS instance (16 vCPU, 32 GB RAM) with warm caches.

These results are directionally consistent with the h2oai db-benchmark and independent benchmarks published by the Polars and DuckDB teams. Your exact results will vary based on data characteristics, hardware, and query patterns.

Key observations:

• Polars dominates filter and groupby operations — its lazy optimizer excels at predicate pushdown and vectorized aggregation.
• DuckDB wins on joins and sorts — its cost-based query optimizer finds better execution plans for complex multi-table operations.
• Both alternatives use roughly 30-40% of Pandas’ peak memory, primarily due to Arrow’s efficient columnar encoding.

Decision Matrix: Which Tool for Which Scenario? #

Migration Strategies and Interoperability #

Switching DataFrame libraries does not have to be an all-or-nothing decision. A gradual migration minimizes risk:

Phase 1: Use DuckDB to accelerate Pandas workflows. Start by replacing complex Pandas queries with DuckDB SQL that reads from Pandas DataFrames. This requires zero changes to your data ingestion or output code — DuckDB slots into the middle of existing pipelines.

Phase 2: Adopt Polars for new ETL pipelines. Write new data processing pipelines in Polars. The Arrow-native format allows zero-copy handoff to Pandas for downstream libraries (scikit-learn, XGBoost) that expect Pandas input via pyarrow compatibility.

Phase 3: Convert legacy Pandas code selectively. Profile your codebase to identify the slowest 20% of operations. Convert only these hot paths to Polars or DuckDB. The Narwhals library provides a compatibility layer that lets you write Polars-style code that executes on either Polars or Pandas backends.

Interoperability patterns:

• Polars → Pandas: df.to_pandas() (zero-copy when possible)
• Pandas → Polars: pl.from_pandas(df)
• DuckDB → Pandas: .to_df()
• DuckDB → Polars: .pl()
• All three read and write Parquet as a common interchange format

Frequently Asked Questions #

Is Polars a drop-in replacement for Pandas? #

No, Polars is not a drop-in replacement. While many operations have direct equivalents (df.filter() instead of df[df.col > 0], pl.col() instead of string column names), the API differs meaningfully. Polars has no index, uses method chaining over bracket indexing, and requires explicit lazy/eager mode selection. However, the conceptual model is similar enough that experienced Pandas users become productive in Polars within a few days. The Polars user guide includes a comprehensive migration cheat sheet.

When should I use DuckDB instead of Polars? #

Choose DuckDB when your team prefers SQL over Python DataFrame APIs, when queries involve complex joins with multiple tables, or when you need direct Parquet querying without loading data into memory. DuckDB’s cost-based optimizer often outperforms Polars on multi-table analytical queries. Choose Polars when you want a Python-native API, need streaming execution for out-of-core processing, or are building data pipelines where Python control flow (loops, conditionals) interleaves with data operations.

Can Polars and DuckDB work together? #

Yes. Both are Arrow-native and interoperate seamlessly. A typical hybrid workflow reads data with DuckDB’s efficient Parquet scanner, performs SQL-based filtering and windowing, converts to Polars via .pl(), then continues with Polars’ Python-native API for ML preprocessing. This combines DuckDB’s query optimizer with Polars’ streaming and ergonomic DataFrame operations.

How much faster is Polars than Pandas? #

Speedups vary by operation but typically range from 5x to 50x on single-node workloads. Filter operations see 10-30x speedups due to vectorized execution and predicate pushdown. Groupby aggregations improve 5-20x through multithreading and hash-optimized aggregation. Memory usage drops by 50-70% due to Arrow’s compact columnar format. On the h2oai db-benchmark suite, Polars ranks consistently in the top three across all tested operations.

Should beginners learn Pandas or Polars first? #

In 2024, beginners should still start with Pandas. The vast majority of tutorials, courses, Stack Overflow answers, and production codebases use Pandas. Understanding Pandas is essential for reading existing code and contributing to most data science teams. After achieving Pandas proficiency (typically 2-3 months of regular use), learning Polars adds a powerful tool for performance-critical workloads. For complete beginners who know SQL already, DuckDB offers an alternative entry point that leverages existing SQL knowledge.

Recommended Infrastructure #

To run any of the tools above reliably 24/7, infrastructure matters:

• DigitalOcean — $200 free credit, 14+ global regions, one-click droplets for AI/dev workloads.
• HTStack — Hong Kong VPS with low latency for mainland China access. This is the same IDC hosting dibi8.com — production-proven.

Affiliate links — no extra cost to you, helps keep dibi8.com running.