# 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.

Source: https://docs.querylab.io/bigquery/partitioning-vs-clustering/

Updated: 2026-10-14

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](https://docs.querylab.io/bigquery/partitioning/) 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](https://docs.querylab.io/bigquery/clustering/) 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

|                              | Partitioning                               | Clustering                                                                                                |
| ---------------------------- | ------------------------------------------ | --------------------------------------------------------------------------------------------------------- |
| Columns                      | One                                        | Up to four, order matters                                                                                 |
| Column types                 | `DATE`, `TIMESTAMP`, `DATETIME` or `INT64` | `BOOL`, `DATE`, `DATETIME`, `TIMESTAMP`, `INT64`, `NUMERIC`, `BIGNUMERIC`, `STRING`, `GEOGRAPHY`, `RANGE` |
| Distinct values              | At most 10,000 partitions                  | No limit                                                                                                  |
| Cost estimate before the run | Firm for the partitions chosen             | An upper bound                                                                                            |
| Pruning unit                 | A whole partition                          | A storage block                                                                                           |
| Good table size              | Partitions of about 10 GB or more          | Tables above about 64 MB                                                                                  |
| Expiration                   | Per partition                              | Whole table only                                                                                          |
| Can require a filter         | Yes (`require_partition_filter`)           | No                                                                                                        |

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:

**partitioning: one day**

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

BigQuery dry run: this query processes 2.4 GB.

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

**clustering on top: one day, one wiki**

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

BigQuery dry run: this query processes 621.6 MB.

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:

**clustering alone: one acc**

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

BigQuery dry run: this query processes 395.3 KB.

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](https://docs.querylab.io/bigquery/clustering/) 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:

```sql
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](https://docs.querylab.io/schema-browser/table-information/#what-the-details-tab-reports)), and the Missing Partition Filter, Missing Cluster Filter and Clustering Order Suboptimal checks flag queries that don't use them ([code quality](https://docs.querylab.io/writing-sql/code-quality/#performance)).

## 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.

```sql
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.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to partitioned tables](https://cloud.google.com/bigquery/docs/partitioned-tables)

- [Introduction to clustered tables](https://cloud.google.com/bigquery/docs/clustered-tables)

- [Quotas and limits](https://cloud.google.com/bigquery/quotas)