Rows, pages and files

Before SQL, there is a file. Every table you will query in this track lives in one. The database’s job is to turn your question into as few reads of that file as it can.

So we start with the file.

A row is just bytes

Take one row from the orders table in the bitshop dataset:

order_id=1  customer_id=164  order_date=2025-09-22  status=delivered

On disk there are no column names and no equals signs. There are bytes. An INTEGER is 4 bytes. A DATE is also 4 bytes: a count of days since 1970-01-01. A short VARCHAR like delivered is its 9 characters plus a small length prefix.

So this row is about 4 + 4 + 4 + 9 = 21 bytes of data. The engine adds a little bookkeeping on top: which values are NULL, where each row starts. But the order of magnitude is set by the types you chose.

Let the database do the arithmetic for all 1,000 orders:

SELECT count(*) AS rows,
     4 + 4 + 4 + round(avg(strlen(status)), 1) AS approx_bytes_per_row,
     count(*) * (12 + round(avg(strlen(status)), 1)) AS approx_total_bytes
FROM orders;

About 20.6 bytes per row and about 20 KB for the whole table. That is smaller than one photo on your phone. Keep that number. It explains why every query in this track returns instantly, and why the same query on 2 billion rows does not.

Disks read pages, not rows

A disk, or an SSD, never hands the database a single row. It hands over a fixed-size block. Databases build on that and group rows into pages:

Reading one byte from a page costs the same as reading the whole page. The page is the unit of work.

Do the sum for orders in a PostgreSQL-style row store. 8,192 bytes per page divided by about 21 bytes per row is roughly 390 rows per page, before overhead. In practice Postgres adds about 24 bytes of header to each row, so it is closer to 160. Either way, 1,000 orders fit in a handful of pages.

page 0  [ 1 | 164 | 2025-09-22 | delivered ][ 2 | 82 | 2025-03-06 | delivered ][ 3 | 2 | ...
page 1  [ ... next ~160 rows ... ]
page 2  [ ... ]

Two ways to lay out the same table

There are two common ways to put rows into pages.

Row store. Each page holds complete rows, one after another. PostgreSQL, MySQL and SQLite work this way. It is great when you read or write one whole row at a time: “load order 412”, “insert this order”. One page read gets you everything about that row.

Column store. Each page holds many values from one column. All the order_id values sit together, then all the status values, and so on. DuckDB, the engine that runs the snippets on this site, works this way. So do Parquet files, BigQuery and Snowflake.

The column layout wins when you read a few columns from many rows, which is what analytics is. SELECT avg(quantity) FROM order_items needs one column out of four. A column store reads only the quantity pages. A row store reads every page, because quantity is mixed in with everything else.

It also compresses better. A page of status values only ever holds four distinct strings. Store each one once, then keep a 2-bit code per row, and that page shrinks by a lot.

Look at the real layout

DuckDB will show you how it stored a table. pragma_storage_info lists every column segment: the chunk of one column inside a row group. DuckDB puts up to 122,880 rows in a row group, so our 1,000 orders fit in one.

SELECT column_name, segment_type, count, stats
FROM pragma_storage_info('orders')
WHERE segment_type <> 'VALIDITY';

Read the output column by column. There is one segment per column, each holding 1,000 values. The VALIDITY segments we filtered out are the NULL bitmaps: one bit per row that says “is this value missing?”.

Look at the stats column. Every segment remembers its own minimum and maximum. For order_date that is 2024-02-06 to 2025-12-31. This matters in the next lesson. If you ask for orders from 2023, the engine can read those two dates, see that no row can match, and skip the segment without opening it. These min/max summaries are called zone maps, and they are the cheapest index there is.

Files hold pages

Last layer. A database file is a sequence of pages, plus a header that says where each table’s pages are. When people say “the table is 20 GB”, they mean that about 2.6 million 8 KB pages belong to it.

That gives you a way to estimate any query before you run it:

1. How many rows does it need to look at?
2. How many bytes per row, for the columns it touches?
3. Divide by the page size. That is how many pages it reads.

An SSD reads somewhere around 1 to 3 GB per second in sequence. So scanning a 20 GB table takes about 10 seconds, whatever the SQL looks like. If you need it faster, you need to read fewer pages. That one idea drives the rest of this track: indexes, partitions, column stores and good WHERE clauses all exist to cut the page count.

Ship it

Write a query that estimates the size of order_items, the biggest table in bitshop. Its columns are three INTEGERs (4 bytes each) and one DECIMAL(10,2) (8 bytes). Count the rows, multiply by the bytes per row, and divide by 8,192 to get pages.

Then answer one question in a comment above your query: if analysts mostly run sum(quantity * unit_price) over this table, should it live in a row store or a column store? One line, with the reason.

You should get 2,423 rows and about 4 to 5 pages. Small enough to read in one go. Multiply it by a million and the layout choice starts to matter.