Partition Pruning & Elimination

Advanced SQL

Partition Your Data

Partitioning splits a large table into smaller, physically separate pieces based on a key. When you query with a filter on the partition key, the database eliminates partitions that don't match — scanning only relevant data.

Range Partitioning — By date ranges (most common)
Hash Partitioning — Even distribution by hash value
List Partitioning — By categorical values (region, status)
Partition Pruning — WHERE clause eliminates irrelevant partitions

Partitioning is essential for tables with billions of rows — it turns a 10-minute full table scan into a 1-second partition scan.

Partition Types Visualized

Range Partitioning

-- PostgreSQL declarative partitioning
CREATE TABLE orders (
  order_id BIGINT,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- Create quarterly partitions
CREATE TABLE orders_2023_q1 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE orders_2023_q2 PARTITION OF orders
  FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
CREATE TABLE orders_2023_q3 PARTITION OF orders
  FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
CREATE TABLE orders_2023_q4 PARTITION OF orders
  FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

ℹ️

Key Insight: Partition pruning automatically eliminates partitions that don't match the query's WHERE clause. This dramatically reduces I/O by only scanning relevant partitions — from scanning 100GB to scanning 25GB for a quarterly query.

Hash Partitioning

-- Hash partitioning for even distribution
CREATE TABLE users (
  user_id BIGINT,
  username VARCHAR(100),
  email VARCHAR(255)
) PARTITION BY HASH (user_id);

CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

List Partitioning

-- List partitioning for categorical data
CREATE TABLE sales (
  sale_id BIGINT,
  region VARCHAR(50),
  amount DECIMAL(10,2)
) PARTITION BY LIST (region);

CREATE TABLE sales_us PARTITION OF sales FOR VALUES IN ('US_EAST', 'US_WEST');
CREATE TABLE sales_eu PARTITION OF sales FOR VALUES IN ('EU_WEST', 'EU_EAST');
CREATE TABLE sales_apac PARTITION OF sales FOR VALUES IN ('APAC_EAST', 'APAC_WEST');

BigQuery Partitioning

-- BigQuery time-unit partitioning
CREATE TABLE `project.dataset.events` (
  event_id STRING, event_time TIMESTAMP,
  user_id STRING, event_type STRING
)
PARTITION BY DATE(event_time)
OPTIONS (require_partition_filter = true);

-- Query with partition filter (required!)
SELECT * FROM `project.dataset.events`
WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01';

⚠️

Important: BigQuery's require_partition_filter = true forces queries to include a partition filter, preventing full-table scans and reducing costs. Without it, you scan the entire table and pay for all data.

Partition Pruning Patterns

-- Good: Pruning works
SELECT * FROM orders WHERE order_date = '2023-06-15';

-- Good: Range pruning
SELECT * FROM orders
WHERE order_date BETWEEN '2023-04-01' AND '2023-06-30';

-- Bad: Pruning fails (function on partition column)
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;

-- Good: Rewrite for pruning
SELECT * FROM orders
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

Composite Partitioning (Subpartitioning)

-- First level: by date
CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
  PARTITION BY LIST (region);

-- Second level: by region within year
CREATE TABLE orders_2023_us PARTITION OF orders_2023
  FOR VALUES IN ('US_EAST', 'US_WEST');

Partition Maintenance

-- Create new partition in advance
CREATE TABLE orders_2024_q1 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- Detach old partition
ALTER TABLE orders DETACH PARTITION orders_2022_q1;

-- Drop old partition
DROP TABLE orders_2022_q1;

-- Default partition catches unmatched rows
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

Quiz: Test Your Knowledge

Follow-Up Questions

When would you choose hash partitioning over range partitioning?
How does partition pruning interact with index usage?
What's the impact of partitioning on INSERT performance?
How do you handle cross-partition queries efficiently?
Explain the concept of partition elimination in distributed databases.

Key Takeaways

Range partitioning — Best for date/time columns (quarterly, monthly)
Hash partitioning — Even distribution for point lookups (no range queries)
List partitioning — Categorical columns (region, status)
Partition pruning — WHERE clause must reference partition column directly (no functions!)
Default partition — Catches unmatched rows to prevent INSERT errors
BigQuery — Use require_partition_filter = true to prevent full-table scans

Partition Pruning & Elimination — Range, Hash, List & BigQuery Partitioning