Partitioning and clustering are the two physical-layout decisions that determine what BigQuery scans, and therefore what you pay and how long you wait. They are also the two settings most often set once, by guess, and never revisited. This guide gives you a decision procedure, the queries to measure the result, and the cases where neither helps and you need a search index instead.
The one-paragraph mental model
A partitioned table is split into physical segments by a single column — a date, a timestamp, an integer range, or ingestion time. When a query filters on that column, BigQuery skips every partition that cannot match. The pruning happens before the query runs, so the bytes you are billed for shrink immediately.
A clustered table sorts data inside each partition (or the whole table, if unpartitioned) by up to four columns. Blocks of storage carry min/max statistics for the clustering columns, so a filter on a clustering column lets BigQuery skip blocks. Pruning happens during execution, the win shows up as fewer bytes processed, and — importantly — the on-demand cost estimate in the console does not reflect it until the query actually runs.
Partitioning is coarse and predictable; clustering is fine-grained and statistical. Use both when you can.
Decision table
| Filter pattern | Column cardinality | Recommendation |
|---|---|---|
| Date or timestamp range on almost every query | Any | Partition by that column (DAY by default; HOUR or MONTH if partition count would be far outside 100–4,000) |
| Equality on an ID or category after a date filter | High (thousands+) | Partition by date, cluster by the ID |
| Equality on a low-cardinality column (region, status) | Low (under ~100) | Cluster, never partition — integer-range partitions on low-cardinality keys create tiny, inefficient partitions |
| Multi-column equality in a consistent order | Mixed | Cluster by those columns in filter-frequency order (first column matters most) |
| Point lookup on a string or JSON value, no date filter | Very high | Search index (see below), clustering as a secondary measure |
| Table under ~10 GB | Any | Usually nothing — pruning overhead exceeds the saving |
Two rules of thumb: never partition on a column with more than about 4,000 distinct values per table (the hard limit is 10,000 partitions), and put the clustering column you filter on most in the first position, because clustering order is hierarchical.
Creating the table
CREATE TABLE `acme_analytics.events`
(
event_ts TIMESTAMP NOT NULL,
user_id STRING,
event_name STRING,
country STRING,
payload JSON
)
PARTITION BY DATE(event_ts)
CLUSTER BY user_id, event_name
OPTIONS (
partition_expiration_days = 400,
require_partition_filter = TRUE
);
require_partition_filter = TRUE rejects any query that does not constrain event_ts. It is the single most effective cost guardrail on a large event table, and it is the setting we add first on every audit. Existing tables can be altered:
ALTER TABLE `acme_analytics.events`
SET OPTIONS (require_partition_filter = TRUE);
Note that a filter must be on the partition column itself, not a derived expression; WHERE DATE(event_ts) = '2026-08-01' prunes, WHERE FORMAT_TIMESTAMP('%Y-%m', event_ts) = '2026-08' does not.
Measuring the win
Do not trust the console estimate for clustering. Run the query, then read bytes billed from the job metadata:
SELECT
job_id,
creation_time,
total_bytes_billed / POW(1024, 3) AS gib_billed,
total_slot_ms / 1000 AS slot_seconds,
query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
AND job_type = 'QUERY'
ORDER BY creation_time DESC
LIMIT 20;
The before/after workflow we use on client work:
- Copy the table as-is into a scratch dataset (
CREATE TABLE scratch.events_flat COPY acme_analytics.eventsloses the layout, which is what you want for a baseline). - Create the candidate layout with
CREATE TABLE ... PARTITION BY ... CLUSTER BY ... AS SELECT * FROM scratch.events_flat. - Run the five most frequent production queries (pulled from
INFORMATION_SCHEMA.JOBSsorted by count) against both tables. - Compare
total_bytes_billedandtotal_slot_ms. A good clustering choice on a well-filtered query typically cuts bytes billed by 60–95 percent; a partition-only change on a date-filtered query is closer to proportional with the date range.
Also check INFORMATION_SCHEMA.PARTITIONS for your table. Partitions of a few megabytes each mean the partition grain is too fine; thousands of partitions each under 1 GB is the usual sign.
Re-clustering is automatic — but not instant
Streaming inserts and frequent small loads land in unclustered blocks. BigQuery re-clusters in the background at no charge, so a table loaded every minute will show degraded block pruning for newly arrived data and full pruning for older data. If a freshness-sensitive dashboard reads only the last few minutes, clustering will not help that query much; a partition grain of HOUR plus a tight timestamp filter will.
When neither helps: search indexes
Clustering prunes blocks, which works when the rows you want are co-located. It does not help when you need one row out of a billion by an arbitrary string — a request ID, an email address, a value inside a JSON column — because those rows are scattered across every block. That is what a search index is for:
CREATE SEARCH INDEX events_search
ON `acme_analytics.events` (user_id, payload);
SELECT event_ts, event_name
FROM `acme_analytics.events`
WHERE SEARCH(payload, 'order-7f3a9c');
With the index in place, SEARCH() and — for indexed columns — plain = and IN predicates use the index, and a point lookup that previously scanned terabytes reads megabytes. The index is maintained automatically; check INFORMATION_SCHEMA.SEARCH_INDEXES for coverage_percentage before assuming a query is using it. Search indexes are a complement to partitioning and clustering, not a replacement: analytical scans over a date range still want the partition, and the index adds storage cost proportional to the indexed columns.
Checklist
- Every table over ~10 GB with a natural time column is partitioned on it, with
require_partition_filteron - Clustering columns match the most frequent equality filters, in frequency order
- No partition column with more than a few thousand distinct values
- Bytes billed measured before and after, from
INFORMATION_SCHEMA.JOBS, not the estimator - Point-lookup workloads on strings or JSON use a search index
If you would like a second opinion on a specific table, contact us — a layout review is usually a one-day engagement.