Home › Learn › Row vs column storage

Data foundations · storage

Row-oriented vs column-oriented: file formats and databases, explained

What row-oriented and column-oriented storage mean, drawn on one tiny table: how CSV, Avro, Parquet and ORC lay out bytes, how PostgreSQL and ClickHouse store tables, why analytics reads fewer bytes from columns, why single-row updates are awkward, with a measured demo, a verdict for each scenario and how it ties to Delta and Iceberg.

General knowledge of how these systems work, not re-checked against each vendor's docs today. The measured numbers in s3 and s10 come from a short script on this page, run once on a laptop. Check the "Not confirmed" list at the bottom.

The idea

Same values, two orders

A table is a grid of values. A file or a database must still write them in a line, one after another, and there are two natural ways to do it. Row-oriented storage writes one whole record, then the next. Column-oriented storage writes one whole column, then the next.

The tableidregionamount1North502South203North70Row layout on disk: one whole record, then the next1North502South203North70Neighbours on disk are the values of one record.Column layout on disk: one whole column, then the next123NorthSouthNorth502070Neighbours on disk are the values of one column.Same nine values. Only the order they are stored in changes.

Same nine values, two orders.

The one-line rule. Row storage keeps a record's values together, so it is quick to fetch or change one record. Column storage keeps a column's values together, so it is quick to read a few columns across millions of records.

How a column store rebuilds rows

A row is not stored. It is rebuilt by position.

If every column sits in its own block, where does the "row" go? Nowhere. Every column is written in the same order, so the value at position 2 of every column belongs to row 2. The engine rebuilds a row by picking position 2 from each column it needs.

position0123id1234regionNorthSouthNorthEastamount50207010row 0row 2North, 50North, 70Question: region and amount where amount > 401 Scan only the amount column. Positions 0 and 2 pass (50 and 70).2 Read region at positions 0 and 2. The id column is never touched.3 Stitch by position: the value at position p in each column belongs to the same row.A row is not stored. It is rebuilt from the same position in every column.

Dashed boxes are the rows rebuilt from positions 0 and 2.

StepWhat happensWhy it is cheap
1. FilterScan only the column in the WHERE and note the positions that passOne column read, not the whole table
2. FetchRead the other wanted columns, only at those positionsUnused columns (here id) stay on disk
3. StitchPut the values with the same position together as a rowPositions are simple numbers

Delaying the stitch until the last moment is often called late materialization: carry around row numbers, and build real rows only for the few that survive the filter.

When the stitching hurts

  • SELECT * for one record. To rebuild all columns of row 2, the engine must read position 2 from every column block. A table of 50 columns means 50 reads for one row. A row store does it in one.
  • Changing one row. Its values are in many column blocks, and the blocks are not edited in place. Systems write new data or mark rows as deleted and tidy up later (see s8).
  • Order must stay aligned. Every column of a block must keep the same order, or the positions stop matching. That is why engines sort or reorder a whole block together, never one column alone.

How real systems keep the positions lined up

SystemHow position works (in general terms)
Parquet fileThe file is split into row groups. In each group every column is a separate chunk with the same number of values. Row n of the group is value n of each chunk.
ORC fileSimilar idea with stripes instead of row groups.
ClickHouseEach data part keeps one file per column, aligned by row number, with a sparse index that points into blocks of rows (called granules).
In-memory (Arrow, DuckDB)Columns are arrays; index n in each array is the same row.

Reading less

What an analytic query actually reads

Row store: reads every cellColumn store: reads only the columns it needsidregionchannelqtyamount............................................................idregionchannelqtyamount............................................................Question: SUM(amount) grouped by regionIt needs 2 of 5 columns. A column store can leave the other 3 on disk.

A column store skips the columns the query does not use.

Try it on 200,000 rows with five columns (id, region, channel, qty, amount), stored as text, and ask for SUM(amount) grouped by region. The code is in s10. One run on a laptop:

Row layoutColumn layout
Bytes the query has to read5,437,347 (everything)2,458,232 (only region and amount), 45% of the row layout
Time to compute it in Python0.142 s0.083 s
Same answer?Yes: both give identical totals

Two of five columns is 40% by count. The query read 45% of the bytes, because the columns differ in width. With 50 columns instead of five, the same query would read a few percent. Treat the figures as an illustration of the idea, not a benchmark of any product.

Compression and skipping

Why columns also shrink and skip

  • Similar values sit together. A region column holds only a handful of repeating words, so tricks like dictionary encoding and run-length encoding work well. In the same run, compressing the region column on its own shrank it from 1,100,402 to 90,344 bytes (about 12 times). channel shrank about 13 times.
  • Mixed columns shrink less. id, qty and amount are close to random numbers, so they compressed far less. Over the whole table, the column layout compressed to 1,444,777 bytes against 1,722,204 for the row layout, only about 1.19 times smaller. The demo uses plain zlib on text. Real column formats use better encodings, so expect larger gains there; this run does not measure them.
  • Statistics let the reader skip. Parquet keeps the minimum and maximum of each column chunk in the file's footer. A query for amount > 800 can skip any chunk whose maximum is below 800 without reading it. Row stores can skip too, with indexes, but a plain row file has no such per-column summary.

File formats

Row and column file formats

FormatLayoutGood forVerdict
CSVRow, plain textHand-off to people and simple toolsDrop for analytics at scale
JSON LinesRow, plain text, nestedLogs and API payloadsDrop for analytics at scale
AvroRow, binary, schema includedWriting one event at a time, for example on KafkaPursue for streams and landing
ParquetColumn chunks inside row groups, with statisticsAnalytics, Delta and Iceberg tablesPursue for analytics
ORCColumn chunks inside stripesAnalytics, mostly in the Hive worldPursue where you already use it
ArrowColumn, in memory (and a file form)Passing data between tools without copyingPursue for in-process speed

Databases

Row and column databases

DatabaseMain layoutTypical job
PostgreSQL, MySQL, SQLiteRowApplications: users, orders, payments (OLTP)
Lakebase (managed Postgres on Databricks)RowThe same, next to your lakehouse
SQL Server, OracleRow by default, with optional column indexes or in-memory column copiesMixed workloads on one system
ClickHouseColumnFast analytics and event or cost data over many rows
Redshift, BigQuery, SnowflakeColumnCloud data warehouses
DuckDBColumnAnalytics on a laptop or inside a program
Databricks SQL, Spark, TrinoEngines that read column files (Parquet, Delta, Iceberg) from storageLakehouse analytics

The classification is the main design, not a law: several systems add features of the other kind.

Format vs database

They are different layers

File formatTable formatDatabase or engine
What it isHow bytes sit in one fileRules that make many files behave like one tableSoftware that stores, indexes, plans and runs queries
ExamplesParquet, Avro, CSVDelta, IcebergPostgreSQL, ClickHouse, Spark, Databricks SQL
Is it row or column?Yes, the layout inside the fileInherits it: Delta and Iceberg usually hold Parquet, so they are column-orientedIt depends on the engine's own storage

So when a Delta table is slow to update one row, the reason is the column files underneath. See Delta vs Iceberg and who maintains your tables.

Writes and updates

Why single-row changes are awkward in column stores

  • Files are written once. A Parquet file has a footer with its statistics, so you cannot append one row in place. The engine writes a new file.
  • A row is in many places. Updating it means touching every column block it appears in.
  • So systems batch. ClickHouse writes each insert as a new part and merges parts in the background, so it wants inserts in batches, not one row at a time. Delta and Iceberg write new files and can mark old rows deleted (for example with deletion vectors), then compact later.
  • Row stores are built for this. They update a record where it sits and use an index to find it.

The usual design is both: a row database takes the live writes, and a pipeline copies the data into column files for analysis. See medallion architecture.

Choosing

Common situations, with a verdict

SituationUseVerdictWhy
An app reads and writes one order at a timeRow databasePursueWhole records, quick changes
Reports over millions of rows and a few columnsColumn store or ParquetPursueReads only the needed columns
Landing a Kafka stream of eventsAvro (or JSON) firstPursueOne record at a time, schema attached
Cost or log analysis over many rows (your CostSherlock data)ClickHouse or similarPursueGroup-bys over a few columns
Analytics straight from a row database used by the appRow databaseDrop at scaleHeavy scans compete with live traffic; copy to a column store
Frequent one-row updates in a column fileParquet filesDropEach change rewrites or marks data
CSV as the main analytics storeCSVDropReads everything, no types, weak compression
Fetch one whole record by id from a big analytics tableA row store or an indexDrop the column store for thisThe row must be rebuilt from every column

Honest limits

The demo code, and what is not confirmed

The numbers above come from this script, standard library only. Your run will differ in the timings; the sizes are fixed by the seed.

"""Same data stored by rows and by columns, using only the Python standard library.
Run:  python3 demo.py
The page shows the numbers from one run (N = 200,000). Text columns and zlib are a crude stand-in for Parquet."""
import random
import time
import zlib


def run(n=200_000, seed=7):
    random.seed(seed)
    regions, channels = ["North", "South", "East", "West"], ["web", "app", "store"]
    rows = [(i, random.choice(regions), random.choice(channels), random.randint(1, 500), round(random.uniform(5, 900), 2)) for i in range(n)]
    row_bytes = "\n".join(",".join(map(str, r)) for r in rows).encode()          # row layout: one line per row
    col_bytes = ["\n".join(map(str, c)).encode() for c in zip(*rows)]            # column layout: one block per column
    names = ["id", "region", "channel", "qty", "amount"]
    out = {"raw_row": len(row_bytes), "raw_col": sum(map(len, col_bytes))}
    out["zlib_row"] = len(zlib.compress(row_bytes, 6))
    out["zlib_col_each"] = {nm: len(zlib.compress(b, 6)) for nm, b in zip(names, col_bytes)}
    out["zlib_col"] = sum(out["zlib_col_each"].values())
    # query: SUM(amount) GROUP BY region
    t = time.perf_counter()
    tot = {}
    for line in row_bytes.decode().split("\n"):
        f = line.split(",")
        tot[f[1]] = tot.get(f[1], 0) + float(f[4])
    out["t_row"] = time.perf_counter() - t
    t = time.perf_counter()
    tot2 = {}
    for r, a in zip(col_bytes[1].decode().split("\n"), col_bytes[4].decode().split("\n")):
        tot2[r] = tot2.get(r, 0) + float(a)
    out["t_col"] = time.perf_counter() - t
    out["same_answer"] = all(abs(tot[k] - tot2[k]) < 1e-6 for k in tot)
    out["bytes_read_row"] = len(row_bytes)
    out["bytes_read_col"] = len(col_bytes[1]) + len(col_bytes[4])
    return out


if __name__ == "__main__":
    print(run())
  • The demo is not Parquet. It stores text and compresses with zlib to illustrate the idea. Real column formats use binary encodings, dictionaries, run-length encoding and statistics, so their gains differ.
  • The timings are one run of Python loops on one laptop, not a database benchmark.
  • Product details (ClickHouse granules, Parquet row group sizes, SQL Server and Oracle column options, which Databricks and cloud warehouse internals are columnar) are from general knowledge, not checked against current docs today.

FAQ

Questions people ask

Is Parquet row-oriented or column-oriented?

Column-oriented. A Parquet file is split into row groups, and inside each group every column is stored as its own chunk, so a reader can load only the columns a query needs.

Is a column database the same as a column file format?

No. A file format only says how bytes are laid out in a file. A database is an engine with storage, indexes, transactions and a query planner. ClickHouse is a column database with its own storage layout; Databricks SQL is an engine that reads column files such as Parquet.

Why are updates slow in column stores?

A row's values are scattered across column chunks, and the files are written once and not edited in place. Changing one row means writing new data or marking old rows deleted, then merging later. Column systems prefer batched inserts.

Which should I use for an application with users and orders?

A row-oriented database such as PostgreSQL, because the application reads and writes whole records one at a time. Copy the data to a column store for reporting.

Next

Where to go next

Related on this site: How reads and writes work in Delta and Spark · Delta vs Iceberg · Who maintains your tables · Table, view, materialized view or metric view · Databricks materialized views · Medallion architecture · Streaming architecture

All data engineering