BrevFeed
Sign in
← All blogs

CSV vs JSON vs Avro vs Parquet vs ORC: One Table, Five Formats, 2000x

BrevFeed Jul 25, 2026 20 min read 2 reads
Data EngineeringFile FormatsParquetPythonDatabases

"What's the average session duration?"

Same question as last time — one column, one number, five million rows. Last time the variable was the layout: row store versus column store, one file each. This time the layout is fixed at "whatever the format does" and the variable is the format itself. I wrote the exact same table five ways — CSV, JSON Lines, Avro, Parquet, ORC — and asked each the same one-column question.

The file that holds this table is anywhere from 304 MiB to 2.20 GiB, and answering the question costs between 26 MiB and 2.2 GiB of reading, or between 0.045 seconds and 91 seconds of wall clock. Same rows, same query, same machine. The only thing that changed is which container I wrote the bytes into.

That's the whole subject: a file format is a container choice, and it's three independent decisions stacked on top of each other.

The three axes every format is a point on

Forget the brand names for a second. Every one of these five formats is just a position on three axes:

  1. Text or binary? Can a human cat the file, or does it need a decoder?
  2. Schema or no schema? Do the field names and types travel with the data, or does every reader have to already know them?
  3. Row-oriented or columnar? Are a record's fields stored together, or is each column stored contiguously?

Lay the five out on that grid and the numbers stop being trivia:

Format Text/binary Schema Layout
CSV text none row
JSON Lines text none (self-describing per row) row
Avro binary yes, travels with the file row
Parquet binary yes column
ORC binary yes column

Each axis costs something and buys something. Here's what the measurements say each one is worth.

The rig

A synthetic events table: 5,000,000 rows × 20 columns — the same dataset, same fixed seed, as the row-vs-column post, so you can reuse that mental model. Twenty columns of the usual telemetry: ids, a 32-char hex session_id, timestamps, country/city/device/os enums, two floats for lat/long, and the query target, duration_ms. The question is SELECT avg(duration_ms) FROM events — touch one column, ignore nineteen. Every format returns the identical mean, 300,009.9226, which is the check that no reader quietly dropped or mangled a row. Warm cache, one machine, best-of-three runs.

Here's everything, then the walk through it:

Format File size Bytes to answer Read Write vs CSV size
CSV 1.04 GiB 1.04 GiB 17.8 s 68 s 1.0×
JSON Lines 2.20 GiB 2.20 GiB 91.0 s 55 s 0.47×
Avro (deflate) 420 MiB 420 MiB 43.2 s 254 s 2.5×
Parquet (snappy) 458 MiB 25.8 MiB 0.045 s 12.5 s 2.3×
ORC (zlib) 304 MiB 304 MiB* 0.31 s 22.6 s 3.5×

(ORC's answer-cost is an upper bound — see the caveat at the end.)

Axis 1: text costs you, and JSON costs you twice

CSV is the baseline everyone starts from: 1.04 GiB, readable in any editor, loadable by anything. Its virtue is universality and its price is everything else. There's no schema, so every reader re-guesses that duration_ms is an integer and not the string "300009"; the numbers are stored as their decimal digits, so a four-byte integer becomes up to six ASCII bytes; and it's row-oriented, so answering a one-column query means parsing every one of the twenty fields on every line just to reach the one you want. Seventeen seconds to average a single column.

Now look at JSON Lines, and look closely, because it's the surprise in the table. It's 2.20 GiB — more than double CSV — the largest file here by far. The reason is structural, not incidental: JSON repeats every key name on every row. Five million times, the file spells out "duration_ms":, "session_id":, "country":, all twenty of them. CSV writes the column names once, in a header line, and never again. That repetition is the entire "self-describing" property people like about JSON, and it is not free — you are paying for the schema inline, per record, forever.

Takeaway #1: human-readable is a runtime cost, charged on every row. CSV pays it in loose typing and digit-encoding; JSON pays it again in repeated keys. Both are fine for interchange, config, and data you'll eyeball. Neither is a serious way to store five million flat records you'll query more than once.

Axis 2: a schema baked into the file — Avro

Avro is the first binary format here, and the first that stores its schema inside the file. To see why that changes the size, look at how an Avro file (technically an object container file) is actually laid out:

==============================================
 HEADER
   magic bytes  "Obj\x01"
   metadata: the full SCHEMA, written as JSON
   a 16-byte sync marker (one random token)
----------------------------------------------
 DATA BLOCK   row-count / byte-size / records...
              ...then the sync marker again
----------------------------------------------
 DATA BLOCK   row-count / byte-size / records...
              ...then the sync marker again
==============================================

Two choices in that picture do all the work.

The schema lives once in the header, and the records carry no field names. Where a JSON line spells out "duration_ms": on every row, Avro writes the field names exactly once — in the header schema — then stores each record as its field values concatenated back to back, in the order the schema declares. The reader knows the thirteenth value is duration_ms because the schema says so, not because a key is sitting next to it. That's the whole reason Avro lands at 420 MiB, 2.5× smaller than CSV and a fifth of JSON: the field names are paid for once, not five million times.

The sync markers make the file splittable. That same random 16-byte token is repeated between every block, so a reader handed the middle of a huge file can scan forward to the next sync marker and resume cleanly at a block boundary. That's exactly what lets Hadoop or Spark cut one big Avro file into pieces and give a piece to each worker — row-at-a-time stream and batch processing is Avro's home turf, and this is the mechanic underneath it. Compression (deflate here) is applied per block, so every block stays independently decodable.

But notice what the layout does not give you: any way to read one column without the others. A record's fields are stored together, so all of duration_ms is scattered one-value-per-record across every block. Averaging that one column means decoding all twenty fields of all five million records — 43 seconds, worse than CSV's 17 because deflate is now doing work too. And there is nowhere in the format to store per-column min/max, so there's no metadata that could let you skip anything. Avro is row-oriented to the core; if your only question is "average one column," it's not the answer.

Avro's real payoff is the one no scan benchmark can show: schema evolution. A schema can change — add a field — and old files and new readers keep working, because Avro resolves the writer's schema (the one in the file header) against the reader's. Add is_bot with a default of false:

# A file written months ago, under the OLD schema (no is_bot).
# A reader running TODAY opens it with the NEW schema:
for r in fastavro.reader(old_file, reader_schema=schema_v2):
    print(r)   # -> {'event_id': 1, ..., 'is_bot': False}   # default filled in

The reverse works too: an old reader still on v1 opens a file a new writer already stamped with is_bot, and simply never asks for the field it doesn't know about. New and old coexist, no coordinated migration, no crash. CSV and JSON don't break when you add a column either — but nothing enforces that is_bot is a bool, or that every producer spells the header the same way. With Avro the contract lives in one schema file everyone resolves against; with CSV/JSON the default lives copy-pasted in every reader's code. That, plus being splittable for row-at-a-time streaming (think Kafka, log pipelines), is why Avro exists — not scan speed.

One more number worth staring at: Avro took 254 seconds to write, twenty times Parquet's 12.5. Deflate plus per-record encoding is expensive. If you write once and read rarely, you are paying that bill for nothing.

Takeaway #2: Avro trades scan speed for a schema that evolves and a stream you can split. It's a write-and-flow format, not a scan format. Reach for it in streaming and append-log pipelines where the schema will change under you — not to answer analytics queries.

Axis 3: columnar with a metadata footer — Parquet

Flip the last axis and everything about the query changes, because the file is now organized by column and it carries metadata that describes itself. Start with the mental model. Take that same Person table and write it to Parquet, and what lands on disk is closer to this than to a grid of rows:

example1.parquet
  schema   : { name: string, age: int32, location: string }
  name     : [ankit, sachin, rahul, amit]
  age      : [32, 25, 28, 35]              -> stats:  min 25   max 35
  location : [delhi, mumbai, jaipur, chennai]

Two things are stored here that CSV, JSON, and Avro never had. The columns are physically separated, so you can read one and skip the rest. And each column ships with statistics — min, max, null count — so you can decide whether to open it at all. Write a second file and it carries its own stats (age -> min 27, max 32); that per-file, per-column min/max is exactly the footer the 117-GB Parquet post used to skip whole files without reading a row.

The real on-disk structure is that idea nested three levels deep:

Parquet file
|
+- Row group 1   (250,000 rows, in this rig)
|    +- Column chunk: name        -> pages (encoded, then compressed)
|    +- Column chunk: age         -> pages ...
|    +- Column chunk: duration..  -> pages ...
+- Row group 2   ...
|    +- ...
+- FOOTER   <-- read FIRST, from the end of the file
     +- the schema
     +- per column chunk: byte offset, size, encoding,
        and statistics (min, max, null count)

That footer is the whole trick, and it buys two independent skips:

  1. Projection — you want duration_ms; the reader looks up its byte offset in the footer and seeks straight to those bytes. The other nineteen columns are never read, decompressed, or allocated. That's the 25.8 MiB.
  2. Predicate pushdown — with a filter like duration_ms > 1000, the reader checks each row group's stored min/max first and skips whole row groups that can't contain a match, without decoding them.
pq.read_table("events.snappy.parquet", columns=["duration_ms"])["duration_ms"].mean()

25.8 MiB read. 0.045 seconds. Against JSON's 91 seconds that's a ~2,000× gap on the same question — not decoder cleverness, just the format letting the reader not read 95% of the file. Same lever as the row-vs-column post, one level up: there it was one column out of a column store; here it's the format even having separable columns and a footer to point at them.

You can see the columnar shape in how you build one with pyarrow — you hand it data already organized as columns, not rows:

import pyarrow as pa
import pyarrow.parquet as pq

schema = pa.schema([("name", pa.string()),
                    ("age", pa.int32()),
                    ("location", pa.string())])

# three columns, not four rows — you feed a column store columns
data = [["ankit", "sachin", "rahul", "amit"],
        [32, 25, 28, 35],
        ["delhi", "mumbai", "jaipur", "chennai"]]

table = pa.Table.from_arrays([pa.array(c) for c in data], schema=schema)
pq.write_table(table, "example1.parquet")   # footer + column stats written for you

# read back just one column chunk — the footer offset is what makes this cheap
ages = pq.read_table("example1.parquet", columns=["age"])["age"].to_pylist()

pq.write_table computes and writes the footer statistics for you; read_table(..., columns=[...]) is the projection that uses those offsets. Point read_table at a directory of .parquet files and it stitches them into one table — which is all a "dataset" of thousands of files really is (the 117-GB post's 26,757 of them): this structure, many times over, each file pre-labeled with the stats that say whether it's worth opening.

Takeaway #3: columnar isn't just "columns stored together" — it's columns stored together plus a footer that indexes them. The layout lets you skip columns; the footer statistics let you skip rows. Every fast number in this post is one or both of those skips.

ORC — the same idea, indexed more finely

ORC is Parquet's columnar sibling from the Hive/Hadoop lineage. Same principle — columns stored together, self-describing metadata — but the structure is sliced differently, and the interesting difference is in the metadata:

ORC file
|
+- Stripe 1   (~64 MiB, in this rig)
|    +- Index data     <-- min/max per column, every 10,000 rows
|    +- Row data       <-- the column value streams
|    +- Stripe footer  <-- stream locations + encodings
+- Stripe 2 ...
+- File footer         <-- schema + per-column, file-level statistics
+- Postscript          <-- compression codec + footer length

Parquet's row group is ORC's stripe (~64 MiB here). What sets ORC apart is that it keeps statistics at three levels — the whole file, each stripe, and every 10,000 rows within a stripe (its "row index"). So a filter can skip not just a whole stripe but a 10,000-row run inside a stripe it did open — finer-grained row-skipping than Parquet's row-group granularity. ORC can also carry optional bloom filters per column, which make equality predicates (country = 'JP') skippable even when min/max can't help (a min of AU and max of US tells you nothing about whether JP is present; a bloom filter does). That finer indexing, plus a zlib default that compresses harder than Parquet's snappy, is why ORC produced the smallest file here — 304 MiB, 3.5× under CSV — and answered the query in 0.31 second by the same column-skipping route.

Parquet and ORC are close relatives; in practice the ecosystem picks for you — Parquet is the broad default across Spark, DuckDB, BigQuery, and Snowflake; ORC is native to the Hive world — not a benchmark.

The through-line for both: the columnar win is half layout, half metadata. Separating columns lets you skip the ones you don't want; the min/max/bloom-filter metadata lets you skip the rows you don't want. Parquet and ORC make the same bet and just draw the metadata boundaries in slightly different places.

Reading each one in Python

The asymmetry above shows up directly in the code. For the row formats there's no way to ask for one column — you read records and reach into them. For the columnar formats, naming the column is the API, and it's the whole reason they're fast. Same query — avg(duration_ms) — five readers:

import pandas as pd
import fastavro
import pyarrow.orc as orc
import pyarrow.parquet as pq

# CSV — text, row-oriented. usecols still parses every line to find the
# column; chunksize keeps 5M rows off the heap. No way to skip the rest.
def read_csv():
    total = count = 0
    for chunk in pd.read_csv("events.csv", usecols=["duration_ms"], chunksize=1_000_000):
        total += chunk["duration_ms"].sum(); count += len(chunk)
    return total / count

# JSON Lines — one JSON object per line, same chunked pattern. Every key
# on every row is parsed whether you want the field or not.
def read_jsonl():
    total = count = 0
    for chunk in pd.read_json("events.jsonl", lines=True, chunksize=500_000):
        total += chunk["duration_ms"].sum(); count += len(chunk)
    return total / count

# Avro — binary + schema, but row-oriented: each record decodes all 20
# fields. You can't ask for one column; you iterate whole records.
def read_avro():
    total = count = 0
    with open("events.avro", "rb") as fh:
        for record in fastavro.reader(fh):
            total += record["duration_ms"]; count += 1
    return total / count

# Parquet — columnar. columns=[...] is the point: only that column's
# bytes leave the disk. This is the 25.8 MiB / 0.045 s path.
def read_parquet():
    return pq.read_table("events.snappy.parquet",
                         columns=["duration_ms"])["duration_ms"].to_pandas().mean()

# ORC — same idea, same columns=[...] pruning under the hood.
def read_orc():
    return orc.ORCFile("events.orc").read(columns=["duration_ms"])["duration_ms"].to_pandas().mean()

Notice the shape of the code mirrors the shape of the numbers: the two row formats (read_avro, and the usecols/chunked readers) have no column-selection argument that actually avoids touching the other fields, while Parquet and ORC take a columns= list that decides how many bytes ever leave the disk.

And the one that reads all five — worth knowing about — is DuckDB, which pushes the column projection down for you without a pandas step at all:

import duckdb
con = duckdb.connect()
con.execute("SELECT avg(duration_ms) FROM read_csv('events.csv')").fetchone()[0]
con.execute("SELECT avg(duration_ms) FROM read_json('events.jsonl')").fetchone()[0]
con.execute("SELECT avg(duration_ms) FROM read_parquet('events.snappy.parquet')").fetchone()[0]
# (DuckDB has no built-in Avro/ORC reader as of this writing — those two
#  still go through the fastavro / pyarrow readers above.)

Against Parquet, that SELECT avg(duration_ms) reads only the one column — DuckDB sees the footer and prunes without being told to. It's the least code and, for the columnar formats, the same fast path by a shorter route.

So which one do you actually use?

The table doesn't crown a winner because the question is under-specified. Match the format to the access pattern and the lifecycle, not to a single benchmark row:

Honest caveats

Takeaways

The mundane version, same as last time: the cheapest bytes are the ones you never read — and the format is what decides whether skipping them is even an option.

Rig: a synthetic 5-million-row, 20-column events table, generated once with a fixed seed (the same table as the row-vs-column post) and written out in all five formats. Sizes and write times are measured; read times are best-of-three with a warm page cache. Every path returned the same mean (300,009.9226), the check that no format's reader dropped or corrupted a row. Bytes-to-answer for Parquet is the exact compressed size of the one column's chunks, read from the footer without touching a row; for the row formats it's the file size, since no column subset is possible; for ORC it's an upper bound, as noted above.

0 Comments