DWH Interview Question 7 - What is the difference between a row store and a column store?

Data Warehouse Interview Q&A · Question 7

In this post we are going to cover the difference between a row store and a column store. This is the storage idea behind the OLTP vs OLAP split, and it explains why data warehouses are so fast at big scans.

A row store keeps all the values of one row together on disk. A column store keeps all the values of one column together. Same table, same SQL. The only difference is how the bytes are laid out, and that changes how much each query has to read.

We will take one small orders table, picture it stored both ways, and then run two very different queries against it: one analytics query and one single-row lookup.

Question 7 of the Real DWH Question and Answers series on TechBrothersIT.

On this page

1. The interview answer
2. Let's create some sample data
3. Step 1: An analytics query - total amount by country
4. Step 2: A transactional query - fetch one whole order
5. Why column stores compress so well
6. Row store vs column store side by side
7. Follow-up questions interviewers ask
8. The full script

The interview answer

Say this first

A row store keeps all the values of a row together on disk, which is ideal for reading or writing whole rows (OLTP). A column store keeps all the values of a column together, so analytics queries read only the columns they need and data compresses very well (OLAP).

Let's create some sample data

We need one small table: orders, with an order ID, customer, country and amount.

Sample data — run this first

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer VARCHAR(50),
  country  VARCHAR(50),
  amount   DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (1001, 'Sara', 'Canada', 53.00),
  (1002, 'Omar', 'USA',    25.00),
  (1003, 'Lina', 'USA',    95.00),
  (1004, 'Mei',  'Canada', 27.00),
  (1005, 'Ravi', 'USA',    40.00),
  (1006, 'Ana',  'USA',    18.00);

Here is the table.

Query

SELECT * FROM orders ORDER BY 1;

Result6 rows · 4 columns

order_id	customer	country	amount
1001	Sara	Canada	53.00
1002	Omar	USA	25.00
1003	Lina	USA	95.00
1004	Mei	Canada	27.00
1005	Ravi	USA	40.00
1006	Ana	USA	18.00

Six orders, four columns. Now let's picture how this same table sits on disk in each layout.

• Row store - the database writes one complete row, then the next: 1001, Sara, Canada, 53.00, then 1002, Omar, USA, 25.00, and so on.
• Column store - it writes all the order IDs together (1001 to 1006), then all the customers, then all the countries, then all the amounts.

Both hold exactly the same data. Now let's run two different queries and think about what each layout has to read.

Step 1: An analytics query - total amount by country

Query one is a typical analytics question: total amount by country. It only needs two of the four columns, country and amount.

Query

SELECT country, SUM(amount) AS total
FROM orders
GROUP BY country
ORDER BY total DESC;

Result2 rows · 2 columns

country	total
USA	178.00
Canada	80.00

USA has 178.00 and Canada has 80.00. The answer is the same in both layouts, but the work is not.

• In the row store, the country and amount values are scattered inside every row. The engine has to read every row in full, including order IDs and customer names it does not need.
• In the column store, it reads just the country block and the amount block, and skips the rest. Here that is half the data.

With a hundred columns in a real fact table, a query like this might read only two or three percent of the data. Less data read means less disk, less memory and less network. That is the main reason warehouses are column stores.

Step 2: A transactional query - fetch one whole order

Query two is the opposite: show me everything about order 1003. This query needs every column of a single row.

Query

SELECT *
FROM orders
WHERE order_id = 1003;

Result1 row · 4 columns

order_id	customer	country	amount
1003	Lina	USA	95.00

One row comes back: Lina, USA, 95.00.

• In the row store, the whole row sits in one place. One small read, done.
• In the column store, the four values live in four different column blocks, so the engine has to visit each one and stitch the row back together.

Do that thousands of times per second, with inserts and updates too, and the row store wins clearly. This is why OLTP databases are row stores.

Why column stores compress so well

There is a second big advantage of column stores: compression. Look at the country column on its own: Canada, USA, USA, Canada, USA, USA. In a column store these values sit next to each other, and they repeat a lot.

• Dictionary encoding - store a short dictionary, 1 = Canada and 2 = USA, and then just the small codes: 1 2 2 1 2 2.
• Run-length encoding - if the column is sorted, store each value once with a count: Canada × 2, USA × 4.

Smaller data means fewer bytes to read, and more of it fits in memory. In a row store, each country value is surrounded by IDs, names and amounts, so it compresses much less.

Row store vs column store side by side

	Row store	Column store
LAYOUT	Values of a row stored together	Values of a column stored together
BEST AT	Single-row reads, inserts, updates	Scans and aggregates over few columns
COMPRESSION	Modest	High (dictionary, run-length)
EXAMPLES	PostgreSQL, MySQL, Oracle, SQL Server	Snowflake, BigQuery, Redshift, Parquet
BOTH?	SQL Server columnstore indexes, in-memory column options	Choose the layout per workload

A good point to add in the interview: many databases now offer both. SQL Server has columnstore indexes, and Oracle and others have in-memory column options. You pick the layout per workload.

Follow-up questions interviewers ask

Why are warehouses column stores?

Analytics queries touch a few columns of many rows; columnar layout reads less data and compresses better.

Why not use a column store for OLTP?

Single-row reads and frequent inserts/updates must touch every column file; row stores handle that far better.

Is Parquet a column store?

Yes, Parquet is a columnar file format, which is why it is the default in data lakes.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom

-- ========================================================================
-- Data Warehouse Interview Q&A #7 - Row store vs column store
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA dwh_q07;  SET search_path TO dwh_q07;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer VARCHAR(50),
  country  VARCHAR(50),
  amount   DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (1001, 'Sara', 'Canada', 53.00),
  (1002, 'Omar', 'USA',    25.00),
  (1003, 'Lina', 'USA',    95.00),
  (1004, 'Mei',  'Canada', 27.00),
  (1005, 'Ravi', 'USA',    40.00),
  (1006, 'Ana',  'USA',    18.00);


-- Step 1: An analytics query: total amount by country
SELECT country, SUM(amount) AS total
FROM orders
GROUP BY country
ORDER BY total DESC;

-- Step 2: A transactional query: fetch one whole order
SELECT *
FROM orders
WHERE order_id = 1003;

Key points

• Row store: a row's values together; best for whole-row reads and writes (OLTP)
• Column store: a column's values together; reads only needed columns (OLAP)
• Analytics queries read far less data from a column store
• Similar values side by side compress very well
• Many databases offer both; pick the layout per workload

Next in this series

Question 8 — What is data modeling? What is data modeling?

Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.