Skip to content
All articles

Partitioning vs Clustering in BigQuery (and When to Combine)

Dean Jain

Dean Jain

Senior Staff Software Engineer · Enterprise AI, Data & Cloud Architect

· 5 min read

BigQueryData ModelingPerformance
---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart TB
    subgraph P["🗂️ Partitioning coarse: prune whole segments"]
        direction LR
        F1["WHERE date = '2024-10-22'"]:::gate --> PP["scan 1 partition,<br/>skip the rest"]:::good
    end
    subgraph C["🧲 Clustering fine: sorted storage blocks"]
        direction LR
        F2["WHERE customer_id = 42"]:::gate --> CC["scan only matching<br/>blocks"]:::good
    end
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A

Figure 1: Two ways to scan less partitioning skips whole segments; clustering reads only the relevant sorted blocks.

BigQuery bills you by the bytes your query scans, so the entire performance-and-cost game is reading less data. Partitioning and clustering are the two levers, and they’re often confused because they sound similar and both “speed up queries.” They don’t work the same way, they have different limits, and the best tables use both. Here’s how each cuts bytes, and how to decide.

Summary

  • Both reduce bytes scanned that’s the whole point. Fewer bytes = faster queries and a smaller bill.
  • Partitioning is coarse and segment-based. Split a table by one column (date, integer range, ingestion time); a qualifying filter lets BigQuery prune entire partitions. Bonus: you get a cost estimate before the query runs.
  • Clustering is fine and sort-based. BigQuery sorts storage blocks by up to 4 columns; filters and aggregations on those columns scan only the matching blocks. Great for high-cardinality columns.
  • Combine them for finely-grained optimization partition by date, cluster within each partition by your common filter columns.
  • Always prefer partitioning over date-sharded tables sharding multiplies schema/metadata overhead and hurts performance.

1. The billing model, then partitioning

Internalize one fact and everything follows: you pay for bytes read, not rows returned. A SELECT that scans a 1 TB table costs the same whether it returns one row or a million. So optimization means arranging storage so BigQuery can avoid reading data your query doesn’t need.

Partitioning is the coarse-grained tool. You divide a table into segments partitions by specifying a partition column. When a query has a qualifying filter on that column, BigQuery scans only the matching partitions and skips the rest. This is pruning, and it’s the single biggest lever for time-series data.

BigQuery supports three partition types:

  • Time-unit column partition by a DATE/TIMESTAMP column (the most common).
  • Ingestion time partition automatically by when rows landed.
  • Integer range partition by buckets of an integer column.

Partitioning carries a unique perk: because each partitioned table keeps metadata about its sort properties, BigQuery can give you a cost estimate before you run the query (via a dry run). You also unlock partition-level management set a partition expiration to auto-delete old data, write to or delete a single partition without touching the rest.

2. Clustering: sorted blocks, finer control

Clustering sorts a table’s underlying storage blocks by a user-defined column order (up to 4 clustering columns). When a query filters or aggregates on those columns, BigQuery reads only the blocks whose values match not the whole table or partition.

---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart LR
    T["📊 Clustered table<br/>sorted by (status, customer_id)"]:::server --> B1["block: status=OPEN"]:::good
    T --> B2["block: status=OPEN"]:::good
    T --> B3["block: status=CLOSED"]:::neutral
    Q["WHERE status = 'OPEN'"]:::gate -->|"reads"| B1
    Q -->|"reads"| B2
    Q -.->|"skips"| B3
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A
    classDef neutral fill:#ECECEC,stroke:#8A8A8A,stroke-width:2px,color:#0F172A
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A

Figure 2: Clustering sorts blocks by your chosen columns, so a filter reads only the matching blocks.

A few rules that shape how you use it:

  • Column order matters. With multiple clustering columns, the order sets precedence in how BigQuery sorts and groups blocks put the column you filter on most first.
  • Columns must be top-level and non-repeated.
  • You can change clustering columns, but it only applies to new data.
  • No pre-run cost estimate. Because block sorting is adaptive, BigQuery often can’t tell you exact bytes before execution it just reduces them at runtime. (This is the trade vs partitioning’s upfront estimate.)

Clustering is “a good first option” precisely because it’s low-commitment and shines where partitioning struggles especially high-cardinality columns (think customer_id, url) that would create far too many partitions.

3. Side by side

PartitioningClustering
GranularityCoarse whole segmentsFine sorted blocks
ColumnsOne partition columnUp to 4, order matters
Best forDate/time ranges, low-ish cardinalityHigh-cardinality filters & aggregations
Cost estimate before run?✅ Yes❌ No (reduced at execution)
Management perksExpiration, per-partition write/deleteAdaptive block sizing
Change later?Fixed at designEditable (new data only)

Figure 3: The decision table partitioning for predictable date ranges, clustering for granular, high-cardinality filters.

4. Combine them the best tables use both

These aren’t either/or. The canonical high-performance pattern is partition by date, then cluster within each partition by your most common filter columns:

---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart LR
    Q["WHERE date = '2024-10-22'<br/>AND status = 'OPEN'"]:::gate --> PRUNE["🗂️ Prune to 1 partition"]:::server
    PRUNE --> BLOCKS["🧲 Within it, read only<br/>OPEN-status blocks"]:::good
    BLOCKS --> R["⚡ Minimal bytes scanned"]:::good
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A

Figure 4: Combined partition pruning narrows to one day, then clustering reads only the matching blocks inside it.

A query filtering on both columns first prunes to the right partition, then reads only the clustered blocks inside it the smallest possible scan.

When should you reach for clustering instead of partitioning? When partitioning would backfire:

  • You need more granularity than a single partition column allows.
  • Partitioning would create tiny partitions (< ~10 GB each) many small partitions bloat metadata and slow queries.
  • You’d exceed partition limits (too many partitions).
  • Your DML frequently modifies most partitions (e.g. updates every few minutes).

5. One rule: never date-shard

If you’re tempted to create date-named tables (events_20241022, events_20241023, …), don’t. Table sharding forces BigQuery to keep a separate schema and metadata copy per table and re-check permissions per table adding overhead and hurting performance. Partitioning is strictly better, and you can convert existing date-sharded tables into an ingestion-time partitioned table.

Why it matters: in BigQuery, storage layout is cost control, because you pay for bytes scanned. Reach for partitioning to prune by date and get predictable, pre-run cost estimates; reach for clustering to slice by high-cardinality columns at fine granularity; and combine them on big tables for the smallest scans. Get the layout right once and every query on that table is faster and cheaper forever.

References