# Row vs Column Storage: How Databases Store Data on Disk > Row vs column storage tested on 3M rows in PostgreSQL, CSV and Parquet: bytes read, compression, and which queries run faster. Source: https://www.erathos.com/en/blog/row-vs-column-storage Em português: https://www.erathos.com/blog/row-vs-column-storage Published: 2026-10-26 Category: Data Engineering ![Row vs column](https://cms-media.erathos.com/Row vs column.png) 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver17). 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](https://www.postgresql.org/docs/current/storage-page-layout.html). 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](https://parquet.apache.org/docs/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](https://github.com/apache/parquet-format/) 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: ![How Parquet reads only needed columns](https://cms-media.erathos.com/How Parquet reads only needed 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: Storage Size on disk Unit of storage PostgreSQL 15 heap table 316.21 MB (40,475 pages of 8,192 bytes) 8 kB page holding whole rows PostgreSQL primary key index 64.28 MB extra B-tree pages CSV file 265.64 MB one text line per row Parquet file (ZSTD) 91.85 MB 25 row groups, one chunk per column each 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. Query CSV via DuckDB PostgreSQL heap table Parquet via DuckDB June aggregate (3 of 7 columns) 352.45 ms 204.01 ms 31.82 ms count(\*) over 3,000,000 rows 254.80 ms 101.43 ms 0.49 ms 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](https://github.com/apache/parquet-format/) treats encoding and compression as separate steps and lets you pick the compression per column. Column Raw values Encoded size After ZSTD Shrink status (4 distinct values) 3,000,000 strings 1,138,943 bytes 15,431 bytes 74x from encoded category (8 distinct values) 3,000,000 strings 1,514,951 bytes 20,944 bytes 72x from encoded order\_id (sequential) 24,000,000 bytes 24,000,775 bytes 3,084,597 bytes 7.8x amount (random cents) 24,000,000 bytes 24,000,775 bytes 9,947,683 bytes 2.4x notes (random 40-char text) 132,000,000 bytes 132,000,825 bytes 68,016,030 bytes 1.9x 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](https://cloud.google.com/blog/products/bigquery/inside-capacitor-bigquerys-next-generation-columnar-storage-format), and collects statistics on every column while it encodes. Citus reported an [8.02x compression ratio](https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-compression-for-postgres/) for its columnar PostgreSQL table on its own test dataset, and Microsoft quotes [up to 7x compression](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/indexes?view=sql-server-ver17) 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;` Storage Median time What happened PostgreSQL heap + primary key 0.06 ms Index scan, 4 buffers touched Parquet via DuckDB 8.64 ms No index; reader locates the row group, then pulls the row from all 7 column chunks CSV via DuckDB 615.29 ms Full parse of the file 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](https://clickhouse.com/docs/concepts/core-concepts/merges). 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver17) 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](https://docs.snowflake.com/en/user-guide/tables-clustering-keys) 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](https://docs.cloud.google.com/bigquery/quotas) (quotas as of this writing). Its docs say [BigQuery doesn't perform well when used as a relational database](https://docs.cloud.google.com/bigquery/docs/partitioned-tables) 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](https://docs.cloud.google.com/bigquery/docs/partitioned-tables) 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](https://docs.cloud.google.com/bigquery/quotas). When a query filters on the partitioning column, BigQuery reads only the matching partitions. A [clustered table](https://docs.cloud.google.com/bigquery/docs/clustered-tables) 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: ![How BigQuery skips days and blocks](https://cms-media.erathos.com/How BigQuery skips days and blocks) Snowflake and Redshift do the same thing with different names and units: Warehouse Coarse unit Fine unit and skipping rule Limits in the docs BigQuery Partition (one column) Clustered storage blocks, sorted by up to 4 columns [10,000 partitions per table](https://docs.cloud.google.com/bigquery/quotas), [4 clustering columns](https://docs.cloud.google.com/bigquery/docs/clustered-tables) Snowflake Micro-partitions, 50 to 500 MB uncompressed, created automatically Metadata holds the value range per column; pruning [drops micro-partitions first, then columns](https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions) Clustering key of [3 or 4 columns recommended](https://docs.snowflake.com/en/user-guide/tables-clustering-keys) Redshift Sort key ordering [1 MB disk blocks](https://docs.aws.amazon.com/redshift/latest/dg/t_Sorting_data.html) with min and max per block; a one-month filter on five years of date-sorted data can skip up to 98% of blocks in AWS's example Interleaved sort key of up to 8 columns ClickHouse Partition (optional) Sorted parts with a sparse index, one row per [8,192-row granule](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree) by default Docs recommend against fine-grained partitioning; ORDER BY is the main lever Parquet file Row group Column chunk, located through footer metadata Spec recommends [512 MB to 1 GB row groups](https://github.com/apache/parquet-format/) 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. System How it mixes the two Detail from the docs Citus Columnar (PostgreSQL) Create a table with USING columnar next to normal heap tables Their test: [444 s on heap vs 18 s on columnar](https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-compression-for-postgres/) for one aggregate; columnar tables did not support UPDATE or DELETE at the time pg\_duckdb (PostgreSQL) Extension that runs analytics SQL on [DuckDB's columnar engine](https://github.com/duckdb/pg_duckdb) inside PostgreSQL Supports PostgreSQL 14 through 18; the heap tables stay as they are SQL Server [Nonclustered columnstore index](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver17) on a rowstore table Rowgroups of 1,048,576 rows; small writes go to the deltastore first Oracle Database In-Memory [Row and column formats maintained at once](https://docs.oracle.com/en/database/oracle/oracle-database/19/inmem/in-memory-column-store-architecture.html) Column units are compressed and read-only; DML goes through the row-based buffer cache SingleStore [Per-table choice](https://docs.singlestore.com/cloud/create-a-database/choosing-a-table-storage-type/) between in-memory rowstore and on-disk columnstore Docs point high-update tables at rowstore and batch-loaded tables at columnstore MySQL HeatWave [In-memory query accelerator](https://www.oracle.com/mysql/) attached to a MySQL database Oracle's pitch is analytics on transactional data without a separate ETL (extract, transform, load) copy 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](https://docs.erathos.com/connectors/databases/postgresql) 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](https://docs.erathos.com/connectors/databases/mysql). 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](https://docs.erathos.com/platform/how-we-move-data) 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: ![How CDC batches row changes](https://cms-media.erathos.com/How CDC batches row changes) For the destination, Erathos loads into [BigQuery](https://www.erathos.com/en/connectors/bigquery?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=row-vs-column) and [Redshift](https://www.erathos.com/en/connectors/redshift?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=row-vs-column), 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](https://www.erathos.com/en/blog/mysql-cdc-to-bigquery?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=row-vs-column) 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: Query shape PostgreSQL heap Parquet via DuckDB Winner One record by primary key 0.06 ms 8.64 ms Row store, 144x Single-row update 1.06 ms Rewrite of a compressed unit Row store Sum of one month, 3 of 7 columns 204.01 ms 31.82 ms Column store, 6.4x count(\*) 101.43 ms 0.49 ms Column store, from metadata 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver17). For a wider set of numbers across engines, [ClickBench](https://benchmark.clickhouse.com/) 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](https://www.postgresql.org/docs/current/storage-page-layout.html). 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](https://cloud.google.com/blog/products/bigquery/inside-capacitor-bigquerys-next-generation-columnar-storage-format) 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](https://github.com/apache/parquet-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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver17), Oracle Database In-Memory keeps [both formats at the same time](https://docs.oracle.com/en/database/oracle/oracle-database/19/inmem/in-memory-column-store-architecture.html), 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](https://app.erathos.com/signup?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=row-vs-column) and set up a CDC pipeline from your database to your warehouse.