Storage Layers: Intermediate

Every storage format decides which bytes sit next to each other on disk. That choice sets how many bytes a query must read and how well they compress. It also sets how fast a CPU can work through them. Take a page_views table with 40 columns. They include country, session_id, page_url and user_agent, plus a JSON blob in properties. Its rows average 290 bytes, and country is a 2-letter code.

A row layout writes each row's values together, then the next row's. A columnar layout writes each column's values together, then the next column's. Here are 4 rows of page_views, with 3 of its 40 columns, in the order each layout puts them on disk.

For this query the row layout leaves the engine no shortcut. The 2 bytes of country sit inside every 290-byte row, and storage returns data in blocks of kilobytes or megabytes, so the engine reads every row in full to extract a single field. In the columnar layout the country values form one contiguous run: the engine reads that run and skips the other 39 columns.

Neighbouring values in a column share a type and often repeat. Millions of country values hold around 200 distinct strings, while a single row puts a timestamp beside a URL beside a boolean. Columnar writers turn that sameness into savings with encodings, applied per column before any general-purpose codec runs.

Sort order decides how much run-length encoding saves. Written in arrival order, country goes US, DE, US, BR, so each run is only 1 or 2 values long. Sorted by country, each country collapses into a single long run.

A columnar file cuts its rows into row groups, commonly roughly 128MB each; some formats call them stripes. Within a row group each column has its own chunk, so a reader can fetch country for a row group without touching page_url. The footer at the end of the file records the minimum and maximum value of each column in each row group, along with how many nulls it holds. The pushdown section puts those statistics to work, letting the engine skip row groups without reading them.

page_views holds 450 days of history. An hourly load writes each day as 24 columnar files of roughly 230MB, 5.5GB in all. A query filtering on view_date = DATE '2026-10-21' reads 1 directory. The other 449 are never even listed, and skipping them that way is partition pruning.

A partition key pays off only when queries filter on it. Partition page_views by load_hour, the hour the pipeline wrote each row, while analysts filter on view_date, and every query still opens every partition, because no filter mentions load_hour. Choosing well starts from the query log: count which columns appear in WHERE clauses and how many distinct values each holds.

view_date wins on both counts. Almost every query filters on it, and a single value per day gives each partition 5.5GB, enough for files in the hundreds of MB. session_id is the opposite case: 9 million partitions a day would hold a few hundred bytes each. country sits in between, and the third of queries filtering on it tempts teams to add it as a second level.

Add country under view_date, keep the hourly load, and each run now writes 1 file per country it saw. With 200 countries across 24 runs, that is up to 4,800 files a day. The 5.5GB splits unevenly: the biggest countries get hourly files of tens of MB, and most get files well under 1MB.

Round figures on general text, in line with the zstd project's published benchmark. Speed counts the uncompressed bytes produced. Your data will give different numbers, and the ordering usually holds.

fct_orders is partitioned by event_date and country. 3 days times 2 countries gives 6 partitions. Each partition holds 1 file of 5 row groups of 128MB, and the footer records min and max order_ts for each row group. The query asks for orders placed on 2026-04-22 at or after 19:00. Switch between the 2 spellings and watch which row groups the engine opens.

The first spelling names event_date, so the engine keeps the 2 partitions for 2026-04-22 and drops the other 4. Inside each file it compares 19:00 to each row group's max order_ts. The first 3 row groups end at 18:00:00 or earlier and are skipped; the last 2 are read. That is 4 row groups and 512MB of file, out of 30 row groups and 3,840MB. The second spelling filters on hour(order_ts), a value no footer stores, and never mentions event_date, so unless the engine rewrites it, the scan reads all 30 row groups and filters the rows afterwards.

Some engines rewrite a few common wrappers, such as a cast from timestamp to date, back into a range on the bare column. Whether yours did shows up in the plan, covered below.

Min and max help when values are clustered: a row group spanning 06:00 to 12:00 rules itself out for 19:00. They fail on a column like customer_id, whose values are scattered, so every row group's min and max span almost the whole id range. A bloom filter fills that gap. At write time the writer builds a small bit array per row group from every customer_id in it. At read time the engine asks whether c_48213 could be present. A no is certain, and the row group is skipped. A yes means possibly, with a false-positive rate set at write time, often about 1%.

Say the customer ordered once on 2026-04-22, so the order sits in 1 of the 10 row groups across that date's 2 files. The bloom filters rule out the other 9, barring a false positive. They only help with equality and short IN lists. Neither customer_id LIKE 'c_48%' nor a range filter gets anything from them. They also exist only for columns the writer was configured to build them on.

Pushdown is the engine's decision, so check it instead of assuming it. EXPLAIN prints the plan without running the query. EXPLAIN ANALYZE runs it and adds what each step actually read.

To check the first spelling, put EXPLAIN ANALYZE on the line above its SELECT and run it. The plan lists the scan of fct_orders with the constraint the engine pushed into it, and the input size that scan read.

Each technique so far cut bytes on its own. Applied to a single table, the cuts multiply. Here is page_views as it first landed: 450 days of uncompressed, row-oriented CSV, 10TB in all. It has 40 columns, and its rows average 290 bytes. The quarterly report counts sessions per country and page type over the last 90 days, excluding bots.

Each factor comes from an earlier section. Partitioning removes the days the report does not ask for, and it works only because the WHERE clause compares view_date directly, the pushdown habit. Columnar layout removes 36 of the 40 columns. Encodings and zstd shrink what remains 4x, helped by the country sort that lengthens runs. The order of the steps changes nothing, because each factor divides the bytes that are left.

A table without partitions can still skip row groups by the min and max of a date column if its files were written in date order. To find out which ones to skip, though, the engine first reads the footer of every file across all 450 days.

The quarterly report reads every country, so the country sort helped only compression. A Germany-only version adds country = 'DE', and row-group min and max on country can now prune, but only a whole row group at a time. Each hourly file of about 230MB holds 2 row groups, so German rows share row groups with other countries and the scan falls at best to about half, near 50GB. A daily job that rewrites each day sorted by country makes about 43 row groups per day; if German views are 6% of traffic, they fill about 3 of them, and the scan approaches 6GB.