What Partitioning and Clustering Actually Do Inside BigQuery

The last post was about a dashboard that got expensive because its BigQuery table wasn't partitioned or clustered. That post assumed you already knew what those two words mean. This one doesn't.

Storage and compute are two different systems

The thing that makes BigQuery behave differently from a normal database is that storage and compute aren't the same machine, or even the same cluster. Your table sits in Colossus, Google's distributed storage layer, in a columnar format called Capacitor. Compute is a separate fleet of workers that gets assigned to your query, reads whatever data it needs off Colossus over Google's internal network, and gets handed back once the query finishes. Storage doesn't wait around for you between queries, and workers don't sit around waiting for you between clicks. That's why you don't provision a "server size" for BigQuery the way you would for Postgres. You're renting compute by the query (or by the slot, if you're on a reservation), not by the hour.

The part that matters for cost: because storage is columnar, a query only reads the columns it actually references. SELECT max_temp FROM readings never touches the humidity column on disk. That's the first, free optimization BigQuery gives you just for using it. Partitioning and clustering are the two levers you control on top of that, and they both do the same basic thing: they stop your query from having to look at data it doesn't need, at a level below "which columns," down at "which rows."

Partitioning: skip whole partitions before scanning anything

A partitioned table is physically split into segments, and each segment maps to one value of a partitioning key. Time-series data usually gets partitioned by day, using either a DATE/TIMESTAMP column you already have, an ingestion-time pseudo-column BigQuery maintains for you, or an integer range if your data splits naturally along numeric buckets instead of dates.

CREATE TABLE mydataset.readings
PARTITION BY DATE(logged_time)
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM mydataset.readings_raw

Once that table exists, a query with a filter on the partitioning column never even opens the partitions outside that range:

SELECT MAX(temp) FROM mydataset.readings
WHERE DATE(logged_time) = '2026-07-15'

BigQuery reads the metadata, sees the query only touches one day, and skips every other day's data before a single byte is scanned. That's the entire optimization: not a smarter scan, no scan at all for the partitions that don't match. require_partition_filter = TRUE is worth turning on for exactly this reason — it stops anyone from writing a query that forgets the date filter and accidentally pays for a full scan anyway.

Clustering: sort what's left so filtering is cheap too

Partitioning gets you down to "the right day." Clustering gets you further, down to "the right rows within that day." A clustered table is physically sorted by up to four columns you pick, and BigQuery keeps track of the min/max range of those columns in each storage block. When your query filters on a clustered column, BigQuery checks those block ranges and skips any block whose range can't contain a match, the same pruning idea as partitioning, just one layer deeper and working on blocks instead of whole partitions.

CREATE TABLE mydataset.readings
PARTITION BY DATE(logged_time)
CLUSTER BY device_id
AS SELECT * FROM mydataset.readings_raw

SELECT MAX(temp) FROM mydataset.readings
WHERE DATE(logged_time) = '2026-07-15'
  AND device_id = 4821

The date filter prunes down to one partition. The device_id filter then prunes down to the handful of blocks inside that partition that could possibly contain rows for device 4821. Column order matters here: BigQuery sorts by the first clustering column first, so put whatever you filter on most often (or with the most selectivity) first in the CLUSTER BY list.

The one-line version

Partitioning decides which whole segments of the table your query is allowed to skip. Clustering decides which blocks inside the segments you didn't skip can also be skipped. Neither one changes what your SQL looks like. Both change what "run this query" actually costs, which is why they're the first thing worth checking before you touch a single line of application code, exactly what the previous post's fix turned out to be.