Choosing Between Partitioning and Clustering in Google BigQuery for Cost and Performance

The Cost Problem in BigQuery

When a table grows into the terabyte range, a simple SELECT * can scan far more data than necessary, driving up query costs. BigQuery charges based on the bytes processed, so reducing the amount of data read is the primary way to control spend.

Partitioning: A Coarse‑Grained Filter

Partitioning splits a table into separate segments based on a column—usually a DATE, TIMESTAMP, or INTEGER range. When a query includes a filter on that column, BigQuery performs partition pruning and skips whole partitions that do not match the predicate.

However, each table is limited to 4,000 partitions. Partitioning by the hour for a multi‑year dataset will exceed that limit in under six months, so the technique works best for daily, monthly, or other relatively low‑granularity buckets.

Clustering: Fine‑Grained Ordering Inside Partitions

Clustering does not create new physical boundaries. Instead, BigQuery sorts the data within each partition by one or more columns, placing rows with similar values close together. The service automatically reclusters as new data is loaded, so no manual index maintenance is required.

This layout lets the engine skip irrelevant blocks inside a partition when a query filters on the clustered column, which is especially useful for high‑cardinality fields such as user_id or product_sku. Clustering does not guarantee sorted output; you still need ORDER BY if order matters.

Worked Example: Web‑Events Table

Suppose you collect click‑stream events and frequently query by date range and a specific user.

-- Run in the BigQuery console
CREATE TABLE `your_project.your_dataset.web_events` (
  event_id STRING,
  user_id INT64,
  event_type STRING,
  event_timestamp TIMESTAMP
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type;

A typical query that benefits from both mechanisms looks like this:

SELECT COUNT(*)
FROM `your_project.your_dataset.web_events`
WHERE DATE(event_timestamp) BETWEEN '2023-10-01' AND '2023-10-07'
  AND user_id = 54321
GROUP BY event_type;

BigQuery first prunes to the seven daily partitions matching the date filter, then within those partitions it reads only the blocks where user_id = 54321 thanks to clustering.

Trade‑offs and Limitations

• Partition filter required: Omitting the partition column in the WHERE clause forces a full table scan, negating any cost savings.
• Partition limit: The 4,000‑partition ceiling means very high‑resolution time partitioning (e.g., per minute) is not feasible for long histories.
• Clustering is not a sort guarantee: Results are returned in arbitrary order unless you add ORDER BY.
• Background reclustering: While automatic, it consumes slots and may lag during heavy ingest periods, temporarily reducing clustering effectiveness.

Verifying the Design

Use the Dry Run feature in the BigQuery UI to see the estimated bytes processed before executing a query. Compare a query with the date filter to one without it; a significant drop indicates partition pruning is working.

You can also inspect the partition metadata directly:

SELECT partition_id, row_count
FROM `your_project.your_dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'web_events'
ORDER BY partition_id;

If the row counts are evenly distributed across the expected date partitions, the partitioning scheme is sound. For clustering, run two similar queries—one filtering on a clustered column and one on a non‑clustered column—and observe the bytes processed; the clustered version should read less data.