You read that correctly, Pandas should go extinct. Not the cute fluffy things used for international diplomacy, but the Python DataFrame library.

Why? Because Pandas’ inefficiencies force you to adopt distributed querying systems before your workloads justify the added complexity. I posit that most workloads will never justify those systems, they are just well marketed “silver bullets”.

To understand what I’m talking about we first must understand the typical adoption pathway for Pandas.

Why do we use Pandas?

The diagram below shows a rough guide of when you typically would consider adopting a given DataFrame library based on the data size you are working with. Following it from left to right, you also see the typical adoption pathway for data analysis tools, and the cliff that Pandas’ users experience beyond a certain data size.

People typically start with Excel and graduate to Pandas somewhere in the GB range. Pandas serves them well into the 10s of GBs range, and then they start hitting memory issues, slow computation, or become frustrated with Pandas’ baroque API. The traditional answer at this point is to graduate to a “real” (read: expensive) tool like Spark, DataBricks, Snowflake, or Dask designed for Big Data ™️

Here’s the thing: there’s a growing gap between the “Pandas cliff” and the scale where distributed systems are genuinely necessary. This gap, sits somewhere around the 100GB mark, and can be effectively filled by modern, high-performance, single-machine tools. I’m primarily talking about Polars and DuckDB.

Why do we care so much about this ~100GB threshold? The answer lies in understanding how much “Big Data” exists in the wild.

I have Big Data, right?

In 2024, Amazon published a paper entitled “Why TPC is not enough: An analysis of the Amazon Redshift fleet”. The aim of this paper was to compare telemetry data from Amazon’s own distributed analytics database, Redshift, with the query patterns used in industry standard database benchmarks. As part of their analysis Amazon published fleet statistics on query run times and table sizes.

If we’re willing to make a couple of assumptions we draw some interesting conclusions about how Amazon’s customers are using analytics databases. Let’s assume that:

• The average size of a row in a Redshift table is 1KB
• Every RedShift cluster is comprised of 10 machines that are each capable of guzzling data at 8GB/s from S3, and do nothing but this

We find that:

• 94.68% of tables in the Redshift fleet contain fewer than 100GB of data
• 86.9% of queries operate on 80GB of data or less

If you are interested in another, deeper look at this dataset, Jordan Tigani of MotherDuck did a deep dive here. Note: MotherDuck is a SaaS business selling DuckDB hosting, so some scepticism is perhaps warranted.

    10 machines * 8GB * 1 second = 80GB of data

    10^8 rows in a table * 1KB = 100GB

But what does this all mean?

You likely do not have Big Data, and probably never will. You have Medium Data problems, and need Medium Data solutions.

Meet the alternatives

The alternatives I propose, as alluded to earlier are DuckDB and Polars. In broad strokes, Polars is a Rust-based DataFrame library that feels familiar to Pandas, but differs in several important ways we will explore. DuckDB is an in-memory analytics DB - essentially SQLite for analytics. To get a feel for these tools and how they differ from Pandas let’s look at an example.

The 1 Billion Row Challenge was a challenge to write the fastest Java program which could compute the min, mean and max of a 1 billion row CSV containing weather station data. The fastest implementation accepted for the competition ran in 1.5 seconds.

The original challenge used a bare metal Hetzner AX161 server with 32 cores and 128GB of RAM running Debian 12. Because the author is a serial procrastinator, a skinflint and Hetzner requires you to develop a “reputation” in order to rent large boxes, a m7a.8xlarge from AWS was instead used for these tests, also running Debian 12.

This fundamental configuration is the same as the original challenge: 32 cores and 128GB of RAM on an AMD CPU. However not using bare metal dedicated hardware may affect reproducibility somewhat^(sorry).

Shut up and show me the code

Without further ado, let’s look at some implementations.

Pandas

This should look very familiar to anyone who has touched Pandas before. We read the data in from the CSV, group by the weather station and then compute the aggregate min, mean and max figures.

def do_1brc_pandas(file_path: str):
    df = (
        pd.read_csv(file_path, sep=";", names=["station", "measurement"])
        .groupby("station")
        .agg({"measurement": ["min", "mean", "max"]})
        .round(2)
    )

The key part of this example to remember is that Pandas executes each step of this computation sequentially and eagerly. It reads in the entire dataset, groups it and then performs aggregation.

Polars

The Polars code looks similar to Pandas, but it works very differently at runtime as we will see.

def do_1brc_polars(file_path: str):
    df = (
        pl.scan_csv(
            file_path,
            separator=";",
            new_columns=["station", "measurement"],
            has_header=False,
        )
        .group_by("station")
        .agg(
            pl.col("measurement").min().round(2).alias("min"),
            pl.col("measurement").mean().round(2).alias("mean"),
            pl.col("measurement").max().round(2).alias("max"),
        )
        .collect(new_streaming=True) # Stream the input data and perform computations in chunks
    ) 

The data is scanned in chunks, grouped and aggregated. The key detail here is that scan_csv is lazily evaluated and the call to .collect executes the query pipeline. If this sounds like database terminology it should. This lazy evaluation allows Polars to construct an optimised query graph, similar to a database, and leverage 40 years worth of database optimisations to read the data in a chunk-wise fashion and parallelise the work across threads as necessary.

Much like a database, we can visualise the optimised and unoptimised query plan by replacing our call to .collect with a call to .explain(streaming=True) and .explain(streaming=True, optimized=False) respectively.

AGGREGATE
        [col("measurement").min().round().alias("min"), col("measurement").mean().round().alias("mean"), col("measurement").max().round().alias("max")] BY [col("station")] FROM
    STREAMING:
    simple π 2/2 ["measurement", "station"]
        Csv SCAN [/Users/eddie/Documents/code/pandas-should-go-extinct/data/measurements.csv]
        PROJECT 2/2 COLUMNS

DuckDB

The DuckDB code reads like vanilla SQL - columns are selected with an aggregation function applied and a group by criteria.

def do_1brc_duckdb(file_path: str):
    df = duckdb.read_csv(file_path, names=["station", "measurements"])

    src = duckdb.sql("""
          create table src as 
          select 
            station, 
            min(measurements) min, 
            max(measurements) max, 
            cast(avg(measurements) as decimal(8, 1)) avg 
          from df 
          group by station
        """
    )

The key takeaways from this code sample is that DuckDB provides an SQL interface over your data, however and wherever it is stored. It also has the ability to query Python objects in memory, in the listing above the object df is created by reading the CSV and is queried using SQL.

DuckDB, much like Polars, constructs and executes query plans which are applied in a lazy, multi-threaded, chunk-wise manner depending on if the query engine deems it appropriate. By tacking on a call to .explain() we can also view the query plan that DuckDB generates for the query.

┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
explain analyze create or replace table src as select station, min(measurements) min, max(measurements) max, cast(avg(measurements) as decimal(8, 1)) avg from df group by station
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 56.08s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      CREATE_TABLE_AS      │
│    ────────────────────   │
│           1 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│          station          │
│            min            │
│            max            │
│            avg            │
│                           │
│         8888 Rows         │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│    ────────────────────   │
│         Groups: #0        │
│                           │
│        Aggregates:        │
│          min(#1)          │
│          max(#2)          │
│          avg(#3)          │
│                           │
│         8888 Rows         │
│         (121.01s)         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│          station          │
│        measurements       │
│        measurements       │
│        measurements       │
│                           │
│      1000000000 Rows      │
│          (0.29s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│         Function:         │
│       READ_CSV_AUTO       │
│                           │
│        Projections:       │
│          station          │
│        measurements       │
│                           │
│      1000000000 Rows      │
│         (316.38s)         │
└───────────────────────────┘

Performance Results

Performance was measured by writing a stand-alone script for each library and pointing it at a 1 billion row CSV on disk.

The script was executed from a lightweight hand-rolled benchmark tool which spawns a fresh Python interpreter to run the script and polls memory and CPU metrics on a 50ms interval using psutil until the child process exits. Two warmup iterations were run for each benchmark followed by thirty repetitions of the script.

This approach is by no means perfect, but struck a suitable balance between accuracy and overhead for the purposes of comparison.

The results here truly speak for themselves: Polars and DuckDB are significantly faster than Pandas, using 2x and 19x less memory respectively. They are within striking distance of a hand-tooled Java implementation.

The memory usage of the Polars implementation still seems a little high, I suspect not all the computations were streamed. DuckDB was flawless, giving phenomenal performance with very little code and no tuning.

Local Dev Performance

However, better production performance is only part of the equation. DuckDB and Polars also shine in speeding up your local dev loop.

Let’s repeat the performance tests on powerful, slightly dated laptop hardware. For this test I used a Framework 13 with an Intel i5-1135G7 with 8 cores and 16GB of RAM.

Once again we see great performance from Polars and DuckDB, albeit with Polars consuming significant memory and slightly dipping into swap. Pandas by comparison, runs like molasses and guzzles memory.

What do I get for free?

This benchmark highlights several key advantages over traditional Pandas workflows:

• Painless Multi-threading: Polars and DuckDB automatically use all your CPU cores without you needing to manage threads or processes. You paid for those cores; use them!
• Efficient Memory Use & Streaming: Both DuckDB and Polars can process data chunk-wise, which helps reduce memory usage.
• Lazy Evaluation: By defining the whole computation upfront, both libraries can optimize the execution plan, applying techniques like predicate pushdown (filtering data early) - just like “real” databases.
• Potential Spill-to-Disk: When optimized operations exceed RAM, these tools have built-in mechanisms to intelligently spill intermediate results to disk, which is usually more efficient than relying on the OS’s generic swapping.

If you’re interested in the more standard TPC-H benchmark results, you can find them here.

Painless adoption with Apache Arrow

We’ve all been burned by shiny tools before, some of us are even being burnt by them as we speak. The key to testing out these tools without rewriting everything is Apache Arrow. Arrow is becoming the de facto in-memory representation of columnar data, and is the brain child of the original creator of Pandas, Wes McKinney. Pandas has supported Arrow since its 2.0 release in April 2023.

Polars and DuckDB both support Arrow natively. This means that you can move DataFrames between Polars, Pandas and DuckDB without copying memory, making switching between the frameworks nearly “free”. The one gotcha here is that Pandas doesn’t create Arrow-backed DataFrames by default, you have to specify when you create the DataFrame that you want the dtype_backend to be pyarrow.

Motivating example: NYC taxi data

Let’s analyze the NYC Taxi dataset (trips from 2009-present, stored in monthly Parquet files) to see if cash payments became less common during the pandemic (2019-2022). This involves processing about 3GB of Parquet data.

For this example we’ll implement functions for reading the parquet files and performing the computation in both DuckDB and Pandas. The Polars version of these functions is left as an exercise to the reader 😉.

The Pandas code:

COLUMNS = ["tpep_pickup_datetime", "payment_type"]
MIN_DATETIME = "2019-01-01"
MAX_DATETIME = "2023-01-01"
# Enum value for cash as the payment type
CASH = 2

def read_data_pandas(folder: Path) -> pd.DataFrame:
    df = None
    for entry in folder.iterdir():
        if not entry.name.endswith("parquet"):
            continue
        temp_df = pd.read_parquet(entry, columns=COLUMNS, dtype_backend="pyarrow")
        if df is not None:
            df = pd.concat([df, temp_df])
        else:
            df = temp_df
    # Clamp the data to the range we're interested in
    df = df[df["tpep_pickup_datetime"] > pd.to_datetime(MIN_DATETIME)]
    df = df[df["tpep_pickup_datetime"] < pd.to_datetime(MAX_DATETIME)]

    df["month"] = df["tpep_pickup_datetime"].dt.month
    df["year"] = df["tpep_pickup_datetime"].dt.year
    df = df.drop(["tpep_pickup_datetime"], axis="columns")

    return df

def calculate_cash_pandas(df: pd.DataFrame):
    df = (
        df.groupby(["year", "month", "payment_type"])
        .agg({"payment_type": "count"})
        .unstack(fill_value=0, level=2)["payment_type"]
        .reset_index()
    )

    df["total_payments"] = df.iloc[:, 2:8].sum(axis=1)
    df["cash_pct"] = (df[CASH] / df["total_payments"]) * 100

The DuckDB code:

def read_data_duck(folder: str):
    return duckdb.sql(f"""
        select 
          datepart('year', tpep_pickup_datetime) year, 
          datepart('month', tpep_pickup_datetime) month, 
          payment_type 
        from '{folder}/*.parquet'
        where 
          tpep_pickup_datetime > '{MIN_DATE}'
          and tpep_pickup_datetime < '{MAX_DATE}'"""
      )

def calculate_cash_duck(data):
    return duckdb.sql(f"""
        with total as (
            select year, month, count(payment_type) payments from df 
            group by year, month
        ),
        total_cash as (
            select year, month, count(payment_type) cash from df 
            where payment_type={CASH} 
            group by year, month
        )
        select total.*, cash, (cash / total) * 100 cash_pct from total 
        join total_cash 
          on total.year=total_cash.year and total.month=total_cash.month
        order by total.year, total.month
    """).df() # Force evaluation by materialising to a df otherwise the execution is lazy

We can then matrix these calls to read data and run the calculations together to understand the bang for buck you can get from picking either tool for reading, writing or both.

def pure_pandas(folder: Path):
  data = read_data_pandas(folder)
  df = calculate_cash_pandas(data)

def duck_reads_panda_thinks(folder: Path):
  data = read_data_duck(folder).df()
  df = calculate_cash_pandas(data)

def panda_reads_duck_thinks(folder: Path):
  data = read_data_pandas(folder)
  df = calculate_cash_duck(data)

def pure_duck(folder: Path):
    data = read_data_duck(folder)
    df = do_taxi_duck_compute(data)

Using the same benchmark script as before with the same laptop specs we get:

Again, we see a similar pattern. DuckDB is able to fully utilise the machine’s CPU cores whilst sipping at memory. Where Pandas becomes involved we are slowed to a crawl and memory usage precipitiously climbs.

If you were interested in whether cash usage declined during the Pandemic, it sure did. However, correlation !== causality, so please don’t draw any meaningful conclusions from this.

Why shouldn’t I listen to you?

Skepticism is a healthy thing in the technology industry, so I’ve compiled a list of reasons why you shouldn’t listen to me:

1. I’m just a person running benchmarks. All the code is open source so you can read it for yourself and decide if it’s flawed. I implore you to do so
2. If you’re super integrated into the Pandas ecosystem, perhaps the switching cost is too high. That being said the bar for “too high switching cost” has changed pretty dramatically since I gave this talk
3. Let Pandas cook. Pandas is improving, albeit slowly given its pivotal position in the ecosystem. However, there are benefits of both DuckDB and Polars beyond pure performance. Both offer less confusing APIs in my experience, and in the case of DuckDB, SQL is a highly transferrable skill set.

Polars vs DuckDB, which one is better?

This totally depends on your workload, experience and preference. Data engineers tend to love SQL, software engineers tend to love Polars. Have a ping at them both and find out which one you prefer.

Really the only thing you shouldn’t do is blindly pick up a distributed querying system and all its attendant complexities just because Pandas suffers from poor performance. The chances of you actually needing one in the long run are pretty small.

Performance has never been more accessible, shop around!