Check the Doorway: CSV vs Parquet on Five Million Rows

Our pandas vs Polars vs DuckDB race ended with a piece of advice: check the doorway first. If loading the data is most of your wait, a better file format may beat a new library.

It’s good advice. It’s also far easier to give than to measure. So we went back to the same five million orders, on the same 2-core machine, and measured it.

What’s actually behind the door

Start with what’s in the files. A CSV is text. Every number in it is spelled out in characters, every date is a string, and nothing in the file says which is which, so every tool that opens it has to read all 210 MB, split it on commas and work out the types again. Every time.

Parquet is built the other way round. It is a columnar format: it stores each column separately, compressed, with its type written into the file (ours records order_date as timestamp[us]). Our file of orders shrank from 209.5 MB as CSV to 68.2 MB as Parquet with pandas’ default settings.

That much is well known. What’s less often shown is whether it matters on a modest machine, and by how much. And there’s an awkward possibility to rule out first: that the slow part was never the format, just pandas’ own CSV reader.

The one-argument fix

First, the cheap option. pandas has a second CSV engine, PyArrow’s, and switching to it takes one argument. If that closes the gap, the whole format question is a red herring. So we tried it first.

It made things worse. With the same parse_dates as before, the PyArrow engine took 5.88 s to read the file, against 3.47 s for the default engine. We didn’t believe it either, so we took it apart in a second session (which is why the default engine shows a slightly different time below; more on that drift at the end):

pd.read_csv(f, parse_dates=['order_date'])
# 4.19 s
pd.read_csv(f, engine='pyarrow', parse_dates=['order_date'])
# 6.56 s
pd.read_csv(f, engine='pyarrow')
# 0.97 s, and order_date comes back as object
pd.read_csv(f, engine='pyarrow', dtype_backend='pyarrow')
# 0.87 s, and order_date comes back as date32[day][pyarrow]

Here’s what happens. PyArrow reads the dates as proper dates. pandas has no plain date type of its own, so they arrive as five million Python date objects, and parse_dates then has to turn all of those back into a date column. That’s where the time goes. Drop parse_dates and the read takes 0.97 s, but the dates arrive as a column of Python objects that no date method will touch. Add dtype_backend='pyarrow' and it’s quick with real dates, at the price of Arrow-backed types in every column, which some code won’t expect. So the one-argument fix is a two-argument fix with a new set of types. It works. It also isn’t a free lunch.

Changing the door

Then we did the thing the advice actually said. We wrote the orders to Parquet once with each library’s defaults, and timed every library reading the same file, the one pandas wrote:

df = pd.read_csv('orders.csv', parse_dates=['order_date'])
df.to_parquet('orders.parquet')                 # pandas defaults: snappy

orders = pd.read_parquet('orders.parquet')     # types come back with it
four   = pd.read_parquet('orders.parquet',
                         columns=['region', 'order_date', 'qty', 'unit_price'])

Here’s the number that sets the tone. pandas read the CSV in 3.47 s and the Parquet file in 252 ms, about 14 times faster. Polars went from 978 ms to 112 ms, and DuckDB from 1.20 s to 311 ms. The slowest Parquet read, 311 ms, beat the fastest CSV read, 978 ms. Every answer was checked: the same 5,000,000 rows, the same revenue total of 1,067,372,417.25, the same date range and the same customer ids, in every case.

Most questions don’t need every column, though. Our revenue question uses four of the seven, and this is where the two formats really part company. Asked for four columns, pandas took 3.58 s from the CSV, no quicker than reading all seven, because a CSV reader still has to read every byte to find the commas. From Parquet it took 124 ms, and Polars 38 ms. Those four columns are 19.2 MB of the 68.2 MB file; the other three were simply never read.

The whole job

Reading is only the doorway, though. The question that matters is the whole job: open the file, then total revenue by region and month. In the race, most of pandas’ time on a job like that went on the opening. So we ran the full job six ways, each library from each file, and checked that all six produced identical 192 region-by-month totals, compared to two decimal places.

pandas on Parquet beat Polars and DuckDB on CSV.

Read that again: the slowest library, on the better file, finished ahead of both faster libraries on the worse file. Polars and DuckDB on Parquet were faster still, so this is no case for pandas over the other two. It’s a case for looking at the door before you move house. On this job, on this machine, the file format was worth more than the library.

And the conversion pays for itself almost at once. Reading the CSV and writing Parquet took pandas about 4.39 s, Polars about 1.63 s, and DuckDB 1.69 s for one COPY statement that does both. For all three, the time saved on later reads had covered the conversion by the second read.

One wrinkle we didn’t expect: the three libraries write different Parquet files from the same data. Polars’ default compression, zstd, made the smallest file, 48.6 MB, against 68.2 MB for pandas and 69.3 MB for DuckDB, which both default to snappy. Which file read fastest depended on the reader, so we timed everything on the pandas-written file and kept the rest in the results file.

As for the caveats: after the warm-up run the files sit in the operating system’s memory, so these numbers time parsing and decoding, not the disk; on a slow disk the smaller file should help even more. The data is synthetic and random, which gives compression little to work with. And this cloud machine drifts: the same pandas CSV read took 4.19 s in a later session, 21% slower than in the main run. The medians are steady within a run, but treat the third digit with suspicion. We used pandas 3.0.5, Polars 2.0.0 and DuckDB 2.0.0 with PyArrow 26.0.0 on Python 3.13.16, each read in a fresh process, 7 timed runs after one warm-up.

The door you haven’t checked

Which leaves an awkward thought. The cheapest speed-up in this whole series didn’t involve learning a new library. It was one line that writes a different kind of file, run once. Meanwhile our own datasets library, the one in the box above, holds 399 files without a single Parquet among them. With none bigger than 230 KB, that is still the right call.

At what size does it stop being one?