Row vs Column Storage: How Databases Store Data on Disk

A row store like PostgreSQL keeps all the values of one record next to each other on disk. A column store like BigQuery, Snowflake, Redshift, ClickHouse, or a Parquet file keeps all the values of one column next to each other. That one choice decides how many bytes a query has to read, how well the data compresses, and how cheap it is to change a single record.

This guide is about that physical layout, the order of bytes on disk. If you came here for spreadsheets: a row runs left to right, a column runs top to bottom, and the rest of this page is about databases. To make the differences concrete, we built the same 3,000,000-row orders table three ways (a PostgreSQL heap table, a CSV file, meaning comma-separated text, and a Parquet file), ran the same queries against all three, and put the numbers in the tables below.

What does "row vs column" mean in database storage?

Row-oriented storage writes a record's values one after the other, then the next record. Column-oriented storage writes all values of the first column, then all values of the second column, and so on. Microsoft's SQL Server docs define it the same way: a rowstore stores data row by row, a columnstore stores it column by column.

Here is a tiny orders table in both layouts. The logical table is the same. Only the order of bytes on disk changes.

ROW STORE (one page holds whole rows)
[101 | 7 | paid | 20.00] [102 | 7 | paid | 35.00] [103 | 9 | refunded | 40.00]

COLUMN STORE (one row group holds one chunk per column)
order_id: [101, 102, 103]
customer_id: [7, 7, 9]
status: [paid, paid, refunded]
total: [20.00, 35.00, 40.00]

A query like the one below needs two of the four columns. In the column layout it reads the customer_id chunk and the total chunk and skips the rest. In the row layout, every row it reads carries order_id and status along for the ride.

SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id;

Real engines add page headers, null bitmaps, metadata, indexes, and compression on top of this picture. The rest of this article looks at those details with real files.

How do row stores and column stores lay bytes on disk?

PostgreSQL stores a table as an array of fixed-size pages, usually 8 kB, and each item on a page is one row. Parquet stores a table as row groups, and each row group holds one contiguous chunk per column.

On the PostgreSQL side, every table is an array of 8 kB pages. A page starts with a 24-byte header, then a 4-byte pointer per item, then the rows themselves. Each row has its own 23-byte header before the column values. To read the amount column of row 2,000,000, PostgreSQL reads the whole 8 kB page that row is on.

On the Parquet side, the file format splits the table into row groups, and each row group holds one column chunk per column. Readers first read the metadata in the footer to find the chunks they need, then fetch only those. The Parquet spec recommends row groups of 512 MB to 1 GB and data pages of about 8 KB.

This is how a Parquet reader answers a query on three of our seven columns:

Our 3,000,000-row orders table (seven columns: order_id, customer_id, order_date, status, amount, category, and a 40-character notes string) came out like this:

The Parquet file is 3.4 times smaller than the PostgreSQL table for the same rows (316.21 / 91.85 = 3.44). The reason is in the next two sections: a column store reads fewer bytes, and it compresses each column on its own.

Why does a column store read less data for an analytics query?

A column store only reads the columns a query names, while a row store reads whole pages that carry every column. In our test, PostgreSQL read all 40,475 pages (316 MB) to answer a three-column aggregate, while the same three columns take up 20.7 MB of the Parquet file.

The query we timed sums one month of orders per customer:

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= DATE '2024-06-01'
AND order_date < DATE '2024-07-01'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 5;

It touches three of the seven columns. PostgreSQL's plan shows what it had to read to answer it:

Parallel Seq Scan on orders
Filter: ((order_date >= '2024-06-01'::date) AND (order_date < '2024-07-01'::date))
Rows Removed by Filter: 918042
Buffers: shared hit=3233 read=37242
Execution Time: 250.521 ms

Shared hit plus read is 3,233 + 37,242 = 40,475 buffers. That is every page of the table, because the notes column and the other columns the query ignores share pages with the ones it needs. In the Parquet file, the three needed column chunks add up to 20,678,569 bytes out of 91,850,195 (20,678,569 / 91,850,195 = 22.5%). The rest of the file, mostly the notes column, never gets read.

Here are the median timings for the same query on the same machine (64 CPUs, 247 GiB RAM). DuckDB 1.5.5 ran the CSV and Parquet queries; PostgreSQL 15.19 ran the heap table with two parallel workers. The results matched on all three.

The count on Parquet takes under a millisecond because the row count is in the file's metadata. PostgreSQL has to scan the table to count live rows. These are two different engines, so the gap mixes the layout with the engine design, but the buffer numbers above show the layout part: one side read 316 MB, the other read about 21 MB.

Why does columnar data compress better?

Columnar data compresses better because the values next to each other on disk come from the same column, so they share a type and often repeat. A status column with four possible values shrinks far more than a page that mixes ids, dates, text, and money.

The per-column numbers from our Parquet file make this visible. The "encoded" size is after Parquet's own encoding step (dictionary and run-length style encodings) and before ZSTD compression; the Parquet spec treats encoding and compression as separate steps and lets you pick the compression per column.

Two things stand out. First, the status column was already down to 1.1 MB before compression ran, because encoding replaced 3,000,000 strings with small dictionary codes. Second, the random notes column barely shrinks, and it ends up as most of the file (68,016,030 / 91,850,195 = 74%). Columnar storage helps most on columns with repeats or patterns, and least on random text.

Warehouses use the same ideas. BigQuery's Capacitor format uses run-length encoding, dictionary encoding, bit-vector and frame-of-reference techniques, and collects statistics on every column while it encodes. Citus reported an 8.02x compression ratio for its columnar PostgreSQL table on its own test dataset, and Microsoft quotes up to 7x compression for SQL Server columnstore indexes over rowstore. Those are vendor numbers on vendor datasets, so the number on your own data can land anywhere in that range.

Why are single-row reads and updates cheap in a row store and expensive in a column store?

A row store finds one record through an index and reads one page that already holds every column of that record. A column store has to touch one chunk per column to rebuild the same record, and it stores data in compressed, sorted units that cannot be edited in place.

Our test shows the read side. Looking up one order by its primary key:

SELECT * FROM orders WHERE order_id = 1234567;

PostgreSQL's plan for this query reads four 8 kB pages in total: three index pages and one table page. A single-row update on the same table took 1.06 ms in our run.

Column stores handle writes by staging them, then rewriting units in the background:

• ClickHouse turns every insert into a sorted, immutable data part. Background merges decompress the parts, merge the column files into bigger ones, build a new index, and recompress. Old parts are deleted after 8 minutes by default.
• SQL Server sends small writes to a row-based deltastore and only moves rows into compressed columnstore rowgroups once 1,048,576 of them have piled up.
• Snowflake reclustering deletes and reinserts the affected records into new micro-partitions, and the old ones stay around for Time Travel and Fail-safe.
• BigQuery limits DML (data manipulation language, meaning inserts, updates, and deletes) to 2 concurrent mutating statements per table and 25 DML statements every 10 seconds (quotas as of this writing). Its docs say BigQuery doesn't perform well when used as a relational database and recommend batching instead of frequent single-row appends.

This is why it makes sense to keep the application on a row store and the reporting on a column store, with something moving data between them in batches.

How do partitioning and clustering cut a warehouse scan?

Partitioning splits a table into big chunks by one column (usually a date), and a filter on that column lets the warehouse skip whole partitions. Clustering sorts the data inside each partition by up to a few more columns, so the warehouse can also skip blocks whose min and max values fall outside the filter.

In BigQuery, a partitioned table can be split by ingestion time, by a date or timestamp column, or by an integer range, and a table can only have one partitioning column. Each table can have up to 10,000 partitions. When a query filters on the partitioning column, BigQuery reads only the matching partitions.

A clustered table sorts the storage blocks inside each partition by up to four columns, and the order you list them matters. The docs give this example on a sales table partitioned by day and clustered by customer_id:

SELECT SUM(totalSale)
FROM `mydataset.ClusteredSalesData`
WHERE customer_id BETWEEN 20000 AND 23000
AND DATE(timestamp) = "2016-05-01";

BigQuery prunes block B3 because its date does not match, prunes block B1 because its customer_id range does not overlap, and scans only the timestamp, customer_id, and totalSale columns in blocks B2 and B4. Two levels of skipping happen before a single column chunk is read:

Snowflake and Redshift do the same thing with different names and units:

Partitioning by day and clustering by the column you filter on most is the setup BigQuery's docs suggest when a table gets frequent updates across a wide date range. The cost of clustering shows up on the write side: Snowflake's docs say reclustering a table with heavy DML costs more credits, because each recluster rewrites micro-partitions.

Can one database be both row-oriented and column-oriented?

Yes. Several engines keep a row layout for transactions and a column layout for analytics at the same time, either as a second copy of the data or as a per-table choice.

The Citus pattern is a good picture of how these get used: the current month's partition stays a heap table so the app can update it, and older partitions get converted to columnar for cheap storage and fast scans.

Where do ELT and CDC fit between PostgreSQL or MySQL and a columnar warehouse?

An ELT tool (extract, load, then transform inside the warehouse) reads changes from the row store's write-ahead log, collects them into files, and loads those files into the warehouse in batches. That matches the column store's write path: few, large loads instead of many single-row writes.

Change data capture (CDC) means reading the database's own change log instead of querying tables. For PostgreSQL, Erathos reads the WAL (write-ahead log), which needs wal_level set to logical and REPLICA IDENTITY FULL on tables where you want the full before and after state of updates and deletes. For MySQL, it reads the binlog in ROW format with FULL row images. Both connectors offer two snapshot modes: initial (copy the table once, then stream changes) and no_data (stream changes only).

The load side works in batches by design. Erathos extracts the data, writes it to temporary files in a cloud bucket, loads the warehouse with a COPY command or the destination's equivalent, and deletes the temporary files when the job finishes. A COPY of a batch file is the kind of write BigQuery and Redshift are built for, and it stays clear of the per-statement DML quotas above.

Here is the path from a single-row change to a warehouse load:

For the destination, Erathos loads into BigQuery and Redshift, with pipelines that are incremental by default and an update type per table: batch, cursor-based incremental, or CDC. There is a step-by-step MySQL CDC to BigQuery guide if you want to see the setup end to end.

Which is faster: row or column storage?

Neither is faster in general; it depends on the shape of the query. Row storage wins when a query needs one record or a small range and all of its columns. Column storage wins when a query scans many rows and needs a few columns.

Our numbers from one machine sum it up:

Microsoft's guidance for SQL Server draws the same line: rowstore for seeks and small ranges in transactional work, columnstore for large scans in warehouse work. For a wider set of numbers across engines, ClickBench publishes analytical query timings for PostgreSQL, DuckDB, ClickHouse, BigQuery, Snowflake, Redshift, and Parquet setups. Compare only rows with the same machine type, because the board mixes hardware and dates.

FAQ

Is PostgreSQL row-based or column-based?

Row-based. PostgreSQL's standard heap tables store rows as items on fixed-size pages, usually 8 kB. Extensions like Citus Columnar and pg_duckdb add a columnar option, but the default table stays row-oriented.

Is BigQuery columnar?

Yes. BigQuery converts loaded data into its own columnar format, Capacitor, which encodes each column separately and stores definition and repetition levels so it can rebuild nested records from only the columns a query asks for.

Is Parquet row-based or column-based?

Column-based. The spec describes Parquet as a column-oriented data file format: a file holds row groups, a row group holds exactly one chunk per column, and a chunk holds pages. In our 3,000,000-row file, a query on three of seven columns needed 20.7 MB of the 91.9 MB file.

Is CSV row-based?

Yes, in the plain sense that a CSV file is one line of text per record with all its fields in order. It stores everything as plain text, without Parquet's per-column chunks, footer metadata, or compression. In our test the same rows took 265.64 MB as CSV and 91.85 MB as Parquet, and a count(*) took 254.80 ms on the CSV versus 0.49 ms on Parquet.

Is SQL a columnar database?

No. SQL is a query language. The same SQL runs on row stores (PostgreSQL, MySQL) and column stores (BigQuery, Snowflake, Redshift, ClickHouse). The storage layout is a choice made by the engine, and you cannot tell it from the query.

Which is faster, a row database or a columnar database?

It depends on the query. In our test a primary-key lookup was 144 times faster on the PostgreSQL heap table (0.06 ms vs 8.64 ms), and a one-month aggregate over three columns was 6.4 times faster on Parquet (31.82 ms vs 204.01 ms).

Can a database be both row and column oriented?

Yes. SQL Server can add a columnstore index to a rowstore table, Oracle Database In-Memory keeps both formats at the same time, and PostgreSQL gets a columnar option through Citus Columnar or pg_duckdb.

Move your row-store data into a column store

If your application runs on PostgreSQL or MySQL and your reports run on BigQuery or Redshift, the job in between is capturing row-level changes and loading them in batches. Try Erathos free for 14 days and set up a CDC pipeline from your database to your warehouse.