Partitioning vs clustering in BigQuery

Partition a BigQuery table when queries filter on a date and you need a firm cost estimate. Cluster it when they filter on several columns or on columns with many values. Use both on large tables. How to choose, with numbers.

Partition a table when your queries nearly always filter on one date, timestamp or integer column, and each partition is large. Cluster it when queries filter on other columns, on several columns, or on a column with a lot of distinct values. On a large table that has both kinds of filter, do both: partition by date, then cluster inside each partition.

Both features make BigQuery read less of the table, and both only help when the query filters on the right column. The differences are in how precise they are, how many values they can handle and what else they give you.

What each one does

A partitioned table is stored in separate pieces, one per value of the partition column (a day, a month, an integer range). A filter on that column picks pieces. Before the query runs, BigQuery knows which pieces are chosen and so how many bytes it will read.

A clustered table is sorted by up to four columns, in blocks that record the minimum and maximum of each clustered column. A filter on the leading columns skips the blocks that can’t match. BigQuery learns which blocks match only while it works through them, so the estimate before the run is a ceiling.

You can see the difference in the wording of a dry run: for a clustered table the bq command line says “will process upper bound of N bytes”, because the clustered blocks that match are only known at run time. The numbers on this page come from dry runs.

Side by side

Neither costs anything to maintain. BigQuery re-sorts clustered data in the background and doesn’t charge for it.

What the savings look like

On wikipedia.pageviews_2020, partitioned by day and clustered by wiki and title, a one-day filter reads 2.4 GB of the views column out of 833 GB:

SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour >= '2020-03-01' AND datehour < '2020-03-02'

Adding a filter on wiki, the first clustered column, reads 622 MB of that:

SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour >= '2020-03-01' AND datehour < '2020-03-02'
  AND wiki = 'en'

On a table clustered only, covid19_genome_sequence.metadata_clustered, a filter on the first clustered column cuts the acc column from 363 MB to 395 KB:

SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE acc = 'SRR11454611'

The two reductions are different in kind. Partitioning excludes 365 of 366 days regardless of anything else in the query. Clustering depends on the shape of the filter: a function around the column or a filter on the third clustered column alone gives nothing, as the clustering article shows.

Choose partitioning when

• Queries nearly always have a time range, and you want to see the price before running. This is the common case for event, log and order tables.
• You need to drop old data on a schedule. partition_expiration_days deletes whole partitions.
• You want to force a filter. require_partition_filter rejects a query without one, which stops an accidental full scan of a large table.
• You reload or overwrite data by day. A load job can write to one partition without touching the others.
• Old data is rarely changed and you want long-term storage pricing. Each partition is judged separately, so only the partitions untouched for 90 days get the lower rate.

Choose clustering when

• Queries filter on columns other than a date, or on several columns.
• The filter column has many distinct values, such as a customer ID, a URL or a user ID. A table can’t have more than 10,000 partitions, so a column with millions of values can’t be partitioned by value. It can be clustered.
• The table is too small for partitions to be worth it. Google suggests partitions of at least about 10 GB. Many small partitions make the table’s metadata large and slow down planning.
• Your changes touch most partitions every few minutes. Google lists this as a reason to cluster instead of partition.
• You don’t need an exact estimate in advance.

Check what partitions you would get

Before partitioning a table, look at how big each partition would be. For a table that is already partitioned, INFORMATION_SCHEMA.PARTITIONS has the sizes:

SELECT partition_id, total_rows, total_logical_bytes
FROM `bigquery-public-data.wikipedia.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'pageviews_2020'
ORDER BY partition_id;

For pageviews_2020 this returns 366 daily partitions of 5.3 to 7.9 GiB, an average of 6.3 GiB, which is under Google’s 10 GB guideline, and the table still prunes well, so treat the guideline as a rule of thumb. For a table with a wide date range and little data per day, daily partitions mean hundreds of tiny pieces. Google recommends monthly or yearly partitions together with clustering on the partitioning column. In Querylab.io a table’s Details tab shows its partitioning and clustering (table information), and the Missing Partition Filter, Missing Cluster Filter and Clustering Order Suboptimal checks flag queries that don’t use them (code quality).

Do both

Partition by date, then cluster by the columns your queries filter on besides the date. The date filter chooses partitions and gives a firm upper bound, and the clustering filter shrinks it inside each partition. This is the layout of the pageviews table.

CREATE TABLE shop.orders (
  order_id INT64,
  customer_id INT64,
  country STRING,
  placed_at TIMESTAMP
)
PARTITION BY DATE(placed_at)
CLUSTER BY customer_id, country
OPTIONS (require_partition_filter = TRUE);

Google says clustering gains are negligible for tables or partitions under 64 MB, so clustering inside very small partitions does little.

You can’t change your mind in place

A table’s partitioning is fixed at creation, and you can’t convert an unpartitioned table to a partitioned one. Create a new table with CREATE TABLE ... PARTITION BY ... CLUSTER BY ... AS SELECT and move the data. Clustering can be added to an existing table, but only data written afterward is sorted. Plan the layout from the filters your queries use today, and check with a dry run that the filters prune.