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.
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.
Dashed boxes are the rows rebuilt from positions 0 and 2.
| Step | What happens | Why it is cheap |
|---|---|---|
| 1. Filter | Scan only the column in the WHERE and note the positions that pass | One column read, not the whole table |
| 2. Fetch | Read the other wanted columns, only at those positions | Unused columns (here id) stay on disk |
| 3. Stitch | Put the values with the same position together as a row | Positions 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
| System | How position works (in general terms) |
|---|---|
| Parquet file | The 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 file | Similar idea with stripes instead of row groups. |
| ClickHouse | Each 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
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 layout | Column layout | |
|---|---|---|
| Bytes the query has to read | 5,437,347 (everything) | 2,458,232 (only region and amount), 45% of the row layout |
| Time to compute it in Python | 0.142 s | 0.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
regioncolumn holds only a handful of repeating words, so tricks like dictionary encoding and run-length encoding work well. In the same run, compressing theregioncolumn on its own shrank it from 1,100,402 to 90,344 bytes (about 12 times).channelshrank about 13 times. - Mixed columns shrink less.
id,qtyandamountare 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 > 800can 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
| Format | Layout | Good for | Verdict |
|---|---|---|---|
| CSV | Row, plain text | Hand-off to people and simple tools | Drop for analytics at scale |
| JSON Lines | Row, plain text, nested | Logs and API payloads | Drop for analytics at scale |
| Avro | Row, binary, schema included | Writing one event at a time, for example on Kafka | Pursue for streams and landing |
| Parquet | Column chunks inside row groups, with statistics | Analytics, Delta and Iceberg tables | Pursue for analytics |
| ORC | Column chunks inside stripes | Analytics, mostly in the Hive world | Pursue where you already use it |
| Arrow | Column, in memory (and a file form) | Passing data between tools without copying | Pursue for in-process speed |
Databases
Row and column databases
| Database | Main layout | Typical job |
|---|---|---|
| PostgreSQL, MySQL, SQLite | Row | Applications: users, orders, payments (OLTP) |
| Lakebase (managed Postgres on Databricks) | Row | The same, next to your lakehouse |
| SQL Server, Oracle | Row by default, with optional column indexes or in-memory column copies | Mixed workloads on one system |
| ClickHouse | Column | Fast analytics and event or cost data over many rows |
| Redshift, BigQuery, Snowflake | Column | Cloud data warehouses |
| DuckDB | Column | Analytics on a laptop or inside a program |
| Databricks SQL, Spark, Trino | Engines that read column files (Parquet, Delta, Iceberg) from storage | Lakehouse 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 format | Table format | Database or engine | |
|---|---|---|---|
| What it is | How bytes sit in one file | Rules that make many files behave like one table | Software that stores, indexes, plans and runs queries |
| Examples | Parquet, Avro, CSV | Delta, Iceberg | PostgreSQL, ClickHouse, Spark, Databricks SQL |
| Is it row or column? | Yes, the layout inside the file | Inherits it: Delta and Iceberg usually hold Parquet, so they are column-oriented | It 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
| Situation | Use | Verdict | Why |
|---|---|---|---|
| An app reads and writes one order at a time | Row database | Pursue | Whole records, quick changes |
| Reports over millions of rows and a few columns | Column store or Parquet | Pursue | Reads only the needed columns |
| Landing a Kafka stream of events | Avro (or JSON) first | Pursue | One record at a time, schema attached |
| Cost or log analysis over many rows (your CostSherlock data) | ClickHouse or similar | Pursue | Group-bys over a few columns |
| Analytics straight from a row database used by the app | Row database | Drop at scale | Heavy scans compete with live traffic; copy to a column store |
| Frequent one-row updates in a column file | Parquet files | Drop | Each change rewrites or marks data |
| CSV as the main analytics store | CSV | Drop | Reads everything, no types, weak compression |
| Fetch one whole record by id from a big analytics table | A row store or an index | Drop the column store for this | The 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