Partitioning Strategies in SQL: Using Table Partitioning and Partition Pruning to Accelerate Queries on Large Datasets

Introduction

You have a table with 800 million rows of e-commerce transaction data. A product manager asks you for last month's revenue by category. The query runs for 11 minutes. Your manager asks why the dashboard is slow. You add an index. It gets slightly better — still 4 minutes. You add more indexes. The query planner starts ignoring them because the statistics are stale. Sound familiar?

This is the wall that indexing alone can't break through. Once your tables grow beyond a certain size — typically hundreds of millions of rows or hundreds of gigabytes — you need a fundamentally different approach. Table partitioning doesn't just speed up queries by helping the database find rows faster; it physically reorganizes how data is stored so that entire chunks of irrelevant data are never touched in the first place. That's partition pruning, and it's one of the most powerful performance tools available to SQL practitioners working at scale.

By the end of this lesson, you'll be able to design and implement partitioning strategies for real-world production tables, write queries that reliably trigger partition pruning, and diagnose cases where pruning silently fails. We'll work through concrete examples in PostgreSQL (with notes on differences in BigQuery, Snowflake, and SQL Server where they matter).

What you'll learn:

• How range, list, and hash partitioning work — and how to choose between them
• How to implement partitioned tables from scratch and migrate existing data into them
• How partition pruning works under the hood and how to verify it's actually happening
• How to write queries that exploit partition elimination effectively
• Common pitfalls that silently disable pruning and how to avoid them

---

Prerequisites

You should be comfortable with:

• Writing multi-table SQL queries with joins, aggregations, and subqueries
• Reading basic query execution plans (EXPLAIN / EXPLAIN ANALYZE)
• Understanding indexes and how they differ from full table scans
• Basic DDL: CREATE TABLE, ALTER TABLE, foreign keys, constraints

---

What Partitioning Actually Is

Before we design anything, let's be precise about what partitioning does — because it's easy to confuse it with indexing, sharding, or clustering.

A partitioned table is a logical table that is physically stored as multiple smaller sub-tables called partitions. From the application's perspective, there's one table. Under the hood, the database is managing several distinct storage segments, each holding a specific subset of the data defined by a partition key.

When you query a partitioned table, the query planner examines your WHERE clause. If your filter references the partition key, the planner can eliminate entire partitions that cannot possibly contain matching rows. This is partition pruning (sometimes called partition elimination). Instead of scanning 800 million rows, the planner scans the 22 million rows in the relevant partition.

This is categorically different from an index. An index is a separate data structure that points to rows. Pruning skips entire physical storage segments — the data is simply never read from disk.

Partitioning also brings secondary benefits:

• Maintenance operations (like VACUUM, index rebuilds, archiving) can run on individual partitions without locking the entire table.
• Data retention is trivially fast — dropping an old partition is nearly instantaneous compared to a DELETE on 100 million rows.
• Parallel query execution engines can scan multiple partitions simultaneously across different workers.

---

The Three Partitioning Strategies

Range Partitioning

Range partitioning divides data based on a continuous range of values in the partition key. It's by far the most common strategy and almost always the right choice when your data has a natural time dimension.

-- PostgreSQL: Creating a range-partitioned orders table
CREATE TABLE orders (
    order_id        BIGINT NOT NULL,
    customer_id     INT NOT NULL,
    order_date      DATE NOT NULL,
    total_amount    NUMERIC(12, 2) NOT NULL,
    status          VARCHAR(20) NOT NULL,
    region          VARCHAR(50) NOT NULL
) PARTITION BY RANGE (order_date);

-- Create one partition per year
CREATE TABLE orders_2022
    PARTITION OF orders
    FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

CREATE TABLE orders_2023
    PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

CREATE TABLE orders_2024
    PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

-- A catch-all for overflow (good defensive practice)
CREATE TABLE orders_2025
    PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-12-31');

Notice that the range bounds are exclusive on the upper end in PostgreSQL. TO ('2022-12-31') means up to but not including December 31st — you'd miss the last day. The correct way to define a full year is:

CREATE TABLE orders_2022
    PARTITION OF orders
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

This is one of the most common implementation bugs. The TO value should be the first value of the next partition.

Warning: In PostgreSQL, if you insert a row whose partition key value doesn't fall into any partition, the insert fails with an error. Always create a DEFAULT partition to catch unexpected values, or ensure your partition coverage is complete.

-- Defensive default partition
CREATE TABLE orders_default
    PARTITION OF orders DEFAULT;

List Partitioning

List partitioning divides data based on discrete, enumerated values. It's ideal when you have a low-cardinality categorical column and your queries frequently filter on it.

-- Partitioning an events table by region
CREATE TABLE events (
    event_id        BIGINT NOT NULL,
    event_timestamp TIMESTAMPTZ NOT NULL,
    event_type      VARCHAR(50) NOT NULL,
    user_id         INT NOT NULL,
    region          VARCHAR(20) NOT NULL,
    properties      JSONB
) PARTITION BY LIST (region);

CREATE TABLE events_north_america
    PARTITION OF events
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE events_europe
    PARTITION OF events
    FOR VALUES IN ('GB', 'DE', 'FR', 'IT', 'NL', 'ES');

CREATE TABLE events_apac
    PARTITION OF events
    FOR VALUES IN ('AU', 'JP', 'SG', 'IN', 'KR');

CREATE TABLE events_other
    PARTITION OF events DEFAULT;

List partitioning only makes sense if your application queries are genuinely filtered by that column. If your analysts almost always query globally and rarely by region, you've added overhead without the pruning benefit.

Tip: List partitioning is particularly powerful in multi-tenant SaaS systems where you partition by tenant_id or client_id. Each tenant's data lives in its own partition, and queries scoped to a single tenant get automatic pruning.

Hash Partitioning

Hash partitioning distributes rows evenly across a fixed number of partitions by computing a hash of the partition key. It doesn't enable range-based pruning, but it's extremely useful for spreading write load and enabling parallel operations when you have no natural range dimension.

-- Hash partitioning a user_activity table
CREATE TABLE user_activity (
    activity_id     BIGINT NOT NULL,
    user_id         INT NOT NULL,
    activity_type   VARCHAR(50) NOT NULL,
    occurred_at     TIMESTAMPTZ NOT NULL,
    metadata        JSONB
) PARTITION BY HASH (user_id);

CREATE TABLE user_activity_p0
    PARTITION OF user_activity
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);

CREATE TABLE user_activity_p1
    PARTITION OF user_activity
    FOR VALUES WITH (MODULUS 8, REMAINDER 1);

-- ... continue for p2 through p7
CREATE TABLE user_activity_p7
    PARTITION OF user_activity
    FOR VALUES WITH (MODULUS 8, REMAINDER 7);

Hash partitioning is honestly the least exciting from a query acceleration standpoint. You only get pruning when you filter on an exact user_id value, which means the database can compute the hash and go directly to the right partition. For range queries or aggregations across many users, all partitions are still scanned.

Use hash partitioning primarily for write scalability and parallel maintenance, not for query optimization.

---

Choosing the Right Partitioning Strategy

Here's a decision framework based on your access patterns:

Use range partitioning when:

• Your dominant query filter is on a timestamp, date, or sequential integer
• You have data retention requirements (drop old partitions)
• Your data grows continuously over time
• You run time-series analytics

Use list partitioning when:

• You have a low-cardinality categorical column (region, status, product_line)
• Queries are almost always scoped to one or a few categories
• You want physical isolation between tenants or business units

Use hash partitioning when:

• You need even data distribution for parallel writes
• No natural range or categorical dimension exists
• You're optimizing for maintenance parallelism, not query pruning

Use composite (sub-partitioning) when:

• Your queries filter on two dimensions regularly
• Example: First partition by year (range), then by region (list)

-- Composite partitioning: range by year, then list by region
CREATE TABLE sales (
    sale_id     BIGINT NOT NULL,
    sale_date   DATE NOT NULL,
    region      VARCHAR(20) NOT NULL,
    amount      NUMERIC(12,2) NOT NULL
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024
    PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    PARTITION BY LIST (region);

CREATE TABLE sales_2024_us
    PARTITION OF sales_2024
    FOR VALUES IN ('US');

CREATE TABLE sales_2024_eu
    PARTITION OF sales_2024
    FOR VALUES IN ('EU');

CREATE TABLE sales_2024_other
    PARTITION OF sales_2024 DEFAULT;

Sub-partitioning multiplies your maintenance burden — you have more partitions to manage — so use it only when you have clear evidence of dual-dimension query patterns.

---

Partition Granularity: How Fine Is Too Fine?

A question that doesn't get enough attention: how many partitions should you create? Monthly? Weekly? Daily?

The answer depends on three factors:

1. Query selectivity. If most queries span 30-60 days of data, monthly partitions work well — typical queries touch 1-2 partitions. Daily partitions would give you the same result (still touching 30-60 partitions) with far more overhead.

2. Data volume per partition. A partition should be large enough that it benefits from its own indexes and statistics but small enough that it provides meaningful pruning. A good rule of thumb: aim for partitions in the range of 10-100 GB in production. Partitions under 1 GB often don't justify the overhead.

3. Planner overhead. The PostgreSQL query planner has to consider every partition when building an execution plan. With thousands of partitions, planning time itself becomes noticeable. In PostgreSQL, enable_partition_pruning = on (the default) helps, but there's still a planning cost per partition. Staying under 500 partitions is a reasonable target for most workloads.

-- Checking partition counts
SELECT 
    parent.relname AS parent_table,
    COUNT(child.relname) AS partition_count,
    SUM(pg_relation_size(child.oid)) / 1024 / 1024 AS total_mb
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
GROUP BY parent.relname;

---

How Partition Pruning Works (And How to Verify It)

Understanding the mechanism of pruning helps you write queries that exploit it and diagnose queries that don't.

When the query planner receives a query, it evaluates the WHERE clause against each partition's constraint. If the constraint provably excludes all matching rows — based on the partition's defined range or list values — that partition is pruned from the execution plan before any data is read.

This happens at plan time (for static values) or execution time (for parameterized queries and some dynamic cases).

Let's see this in action:

-- This query should prune all partitions except orders_2024
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS revenue,
    COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date < '2025-01-01'
GROUP BY 1
ORDER BY 1;

The output you want to see:

Aggregate  (cost=...)
  ->  Seq Scan on orders_2024  (cost=...)
        Filter: (order_date >= '2024-01-01' AND order_date < '2025-01-01')

Notice only orders_2024 appears — the other partitions are absent. That's pruning working correctly.

Now compare to a query where pruning fails:

-- This might NOT prune effectively
EXPLAIN (ANALYZE, BUFFERS)
SELECT SUM(total_amount)
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024;

You'll likely see all partitions scanned:

Aggregate
  ->  Append
        ->  Seq Scan on orders_2022
        ->  Seq Scan on orders_2023
        ->  Seq Scan on orders_2024
        ->  Seq Scan on orders_2025

Why? Because wrapping the partition key in a function (EXTRACT()) prevents the planner from directly comparing the filter against partition bounds. The planner doesn't know how to evaluate EXTRACT(YEAR FROM order_date) = 2024 against partition bounds defined in terms of raw order_date ranges.

Rule: Never wrap the partition key in a function in your WHERE clause if you want pruning. Write predicates that compare the partition key directly to literal values.

---

Indexing Partitioned Tables

Partitioning and indexing work together. You should still create indexes on partitioned tables — but with partitioned tables, indexes are defined per partition (or globally via CREATE INDEX ON the_parent_table).

In PostgreSQL, creating an index on the parent table automatically creates matching indexes on all existing and future partitions:

-- Create a global index on the partitioned orders table
-- PostgreSQL will propagate this to all partitions
CREATE INDEX idx_orders_customer_id 
    ON orders (customer_id);

CREATE INDEX idx_orders_status_date 
    ON orders (status, order_date);

This is powerful because when a query touches only one partition (via pruning), the partition-level index kicks in and further narrows the scan within that partition.

-- This query benefits from both pruning AND index scan
SELECT *
FROM orders
WHERE order_date >= '2024-06-01'
  AND order_date < '2024-07-01'
  AND customer_id = 88421;

The planner: 1) prunes to orders_2024, then 2) uses the index on customer_id to find exactly the right rows within that partition. Two levels of elimination.

Tip: In very large production systems, you can create indexes on individual partitions rather than globally. This gives you fine-grained control — for example, an old historical partition that's rarely queried might not need a full composite index.

---

Migrating an Existing Table to a Partitioned Table

This is the real-world challenge. You rarely get to design partitioned tables from scratch — more often, you have an enormous existing table that needs to be partitioned. This is a delicate operation on a live production system.

The general strategy: create the new partitioned structure, load data in, swap atomically.

-- Step 1: Create the new partitioned table (different name for now)
CREATE TABLE orders_partitioned (
    order_id        BIGINT NOT NULL,
    customer_id     INT NOT NULL,
    order_date      DATE NOT NULL,
    total_amount    NUMERIC(12, 2) NOT NULL,
    status          VARCHAR(20) NOT NULL,
    region          VARCHAR(50) NOT NULL
) PARTITION BY RANGE (order_date);

-- Step 2: Create partitions covering all historical data
CREATE TABLE orders_partitioned_2020
    PARTITION OF orders_partitioned
    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

CREATE TABLE orders_partitioned_2021
    PARTITION OF orders_partitioned
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE orders_partitioned_2022
    PARTITION OF orders_partitioned
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE orders_partitioned_2023
    PARTITION OF orders_partitioned
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_partitioned_2024
    PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_partitioned_default
    PARTITION OF orders_partitioned DEFAULT;

-- Step 3: Backfill historical data (can be done in batches)
INSERT INTO orders_partitioned
SELECT * FROM orders
WHERE order_date >= '2020-01-01' AND order_date < '2021-01-01';

INSERT INTO orders_partitioned
SELECT * FROM orders
WHERE order_date >= '2021-01-01' AND order_date < '2022-01-01';

-- ... repeat for other years

-- Step 4: Recreate indexes on the new table
CREATE INDEX idx_op_customer_id ON orders_partitioned (customer_id);
CREATE INDEX idx_op_status_date ON orders_partitioned (status, order_date);

-- Step 5: Atomic rename (requires brief lock)
BEGIN;
ALTER TABLE orders RENAME TO orders_legacy;
ALTER TABLE orders_partitioned RENAME TO orders;
COMMIT;

-- Step 6: Validate, then drop legacy table after confidence period
-- DROP TABLE orders_legacy;

For very large tables, Step 3 should be done in chunks with pg_sleep() or time-based batching to avoid overwhelming the write-ahead log. In some environments, tools like pg_partman automate this process entirely.

Warning: The ALTER TABLE ... RENAME step takes a brief ACCESS EXCLUSIVE lock on the table. On a busy production system, plan this during low-traffic hours and ensure your connection pooler handles the brief disruption.

---

Partition Pruning in Cloud Data Warehouses

If you work in Snowflake, BigQuery, or Redshift, partitioning works differently — and in some ways, more automatically.

BigQuery

BigQuery uses partitioned tables (by date/timestamp or integer range) and clustered tables (analogous to composite indexes). You define partitioning at table creation:

-- BigQuery: Create a partitioned and clustered table
CREATE TABLE `project.dataset.orders`
(
    order_id        INT64,
    customer_id     INT64,
    order_date      DATE,
    total_amount    NUMERIC,
    status          STRING,
    region          STRING
)
PARTITION BY order_date
CLUSTER BY region, customer_id
OPTIONS (
    partition_expiration_days = 1095  -- Auto-expire partitions after 3 years
);

BigQuery automatically prunes partitions when your WHERE clause filters on the partition column. You can check how many bytes will be processed before running:

-- BigQuery query with partition filter
SELECT region, SUM(total_amount)
FROM `project.dataset.orders`
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY region;

BigQuery will show you the estimated bytes scanned in the query validator — a direct indicator of partition pruning effectiveness.

Snowflake

Snowflake uses micro-partitioning automatically — every table is automatically divided into compressed micro-partitions of 50-500MB. You influence which micro-partitions are pruned through clustering keys:

-- Snowflake: Define a clustering key on an existing table
ALTER TABLE orders CLUSTER BY (order_date, region);

Snowflake's automatic clustering is one of its most powerful features. After defining a clustering key, Snowflake continuously reorganizes micro-partitions in the background. Queries that filter on order_date benefit from pruning without you manually managing partitions.

The tradeoff: automatic re-clustering costs credits. Monitor the AUTOMATIC_CLUSTERING_HISTORY view to understand the cost.

-- Snowflake: Check clustering effectiveness
SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date, region)');

---

Real-World Project: Building a High-Performance Analytics Table

Let's put this together in a realistic project. You're building a web analytics table for a high-traffic platform — roughly 50 million events per month, growing. Analysts query it by date range and sometimes by event type. Here's the full implementation.

Design the Schema

-- Partitioned web_events table
CREATE TABLE web_events (
    event_id        BIGSERIAL,
    occurred_at     TIMESTAMPTZ NOT NULL,
    event_date      DATE NOT NULL GENERATED ALWAYS AS (occurred_at::DATE) STORED,
    session_id      UUID NOT NULL,
    user_id         INT,
    event_type      VARCHAR(50) NOT NULL,
    page_path       TEXT NOT NULL,
    referrer        TEXT,
    device_type     VARCHAR(20),
    country_code    CHAR(2),
    properties      JSONB
) PARTITION BY RANGE (event_date);

Note the use of a generated column event_date derived from occurred_at. This gives us a clean DATE value to partition on — important because range comparisons on DATE are simpler and cleaner than on TIMESTAMPTZ.

Create Partitions and Automate Future Ones

-- Create monthly partitions for 2024
CREATE TABLE web_events_2024_01
    PARTITION OF web_events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE web_events_2024_02
    PARTITION OF web_events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- ... through 2024-12

CREATE TABLE web_events_2024_12
    PARTITION OF web_events
    FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

CREATE TABLE web_events_default
    PARTITION OF web_events DEFAULT;

In production, use pg_partman to automate monthly partition creation. It monitors your table and pre-creates upcoming partitions.

Create Targeted Indexes

-- Each index propagates to all partitions
CREATE INDEX idx_we_user_id 
    ON web_events (user_id) 
    WHERE user_id IS NOT NULL;

CREATE INDEX idx_we_event_type_date 
    ON web_events (event_type, event_date);

CREATE INDEX idx_we_session 
    ON web_events (session_id);

-- BRIN index for sequential timestamp data (very low overhead)
CREATE INDEX idx_we_occurred_brin 
    ON web_events USING BRIN (occurred_at);

Write Queries That Exploit Pruning

-- Analyst query: daily active users for a specific month
-- Correctly written to trigger pruning
SELECT
    event_date,
    COUNT(DISTINCT user_id) AS dau,
    COUNT(DISTINCT session_id) AS sessions,
    COUNT(*) AS events
FROM web_events
WHERE event_date >= '2024-11-01'
  AND event_date < '2024-12-01'
GROUP BY event_date
ORDER BY event_date;

-- Funnel analysis: conversion from landing to signup in one week
SELECT
    DATE_TRUNC('day', occurred_at) AS day,
    COUNT(DISTINCT CASE WHEN event_type = 'page_view' AND page_path = '/' 
                        THEN session_id END) AS landing_sessions,
    COUNT(DISTINCT CASE WHEN event_type = 'signup_complete' 
                        THEN session_id END) AS signups
FROM web_events
WHERE event_date >= '2024-11-18'
  AND event_date < '2024-11-25'
GROUP BY 1
ORDER BY 1;

Verify Pruning Is Working

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(DISTINCT user_id)
FROM web_events
WHERE event_date >= '2024-11-01'
  AND event_date < '2024-12-01';

You should see only web_events_2024_11 in the plan. If other partitions appear, check the column types — a mismatch between the literal type and the partition column type can silently prevent pruning.

Implement Data Retention

-- Drop partitions older than 18 months (nearly instantaneous)
DROP TABLE IF EXISTS web_events_2023_01;
DROP TABLE IF EXISTS web_events_2023_02;
DROP TABLE IF EXISTS web_events_2023_03;

-- Or: detach and archive before dropping
ALTER TABLE web_events DETACH PARTITION web_events_2023_01;
-- Now web_events_2023_01 is a standalone table
-- You can pg_dump it to cold storage, then drop
DROP TABLE web_events_2023_01;

DROP TABLE on a partition is O(1) — metadata operations only. The equivalent DELETE FROM web_events WHERE event_date < '2023-04-01' would take hours and generate massive WAL traffic.

---

Hands-On Exercise

Work through this exercise to solidify the concepts. You'll need a PostgreSQL 14+ instance (local or a free tier on Supabase/Neon works fine).

Scenario: You're given a flat transactions table with 5 years of financial data. Your task is to partition it by year, verify pruning works, and benchmark the improvement.

Step 1: Create and populate the source table.

CREATE TABLE transactions_flat (
    txn_id          BIGSERIAL PRIMARY KEY,
    account_id      INT NOT NULL,
    txn_date        DATE NOT NULL,
    amount          NUMERIC(14, 2) NOT NULL,
    txn_type        VARCHAR(30) NOT NULL,
    merchant        VARCHAR(100),
    category        VARCHAR(50)
);

-- Generate 5 years of synthetic data (~1M rows)
INSERT INTO transactions_flat (account_id, txn_date, amount, txn_type, merchant, category)
SELECT
    (RANDOM() * 100000)::INT + 1,
    '2020-01-01'::DATE + (RANDOM() * 1825)::INT,
    ROUND((RANDOM() * 5000 - 500)::NUMERIC, 2),
    (ARRAY['purchase', 'refund', 'transfer', 'payment'])[floor(RANDOM() * 4 + 1)],
    'Merchant_' || (RANDOM() * 500)::INT,
    (ARRAY['food', 'travel', 'utilities', 'entertainment', 'healthcare'])[floor(RANDOM() * 5 + 1)]
FROM generate_series(1, 1000000);

Step 2: Benchmark a typical query on the flat table.

\timing on

SELECT 
    category,
    SUM(amount) AS total,
    COUNT(*) AS txn_count
FROM transactions_flat
WHERE txn_date >= '2024-01-01'
  AND txn_date < '2025-01-01'
GROUP BY category
ORDER BY total DESC;

Note the execution time.

Step 3: Create the partitioned version and migrate the data.

CREATE TABLE transactions_partitioned (
    txn_id          BIGINT NOT NULL,
    account_id      INT NOT NULL,
    txn_date        DATE NOT NULL,
    amount          NUMERIC(14, 2) NOT NULL,
    txn_type        VARCHAR(30) NOT NULL,
    merchant        VARCHAR(100),
    category        VARCHAR(50)
) PARTITION BY RANGE (txn_date);

-- Create yearly partitions for 2020-2024
-- (Write the CREATE TABLE ... PARTITION OF statements yourself)
-- Then insert all data from transactions_flat
-- Then run the same query
-- Then run EXPLAIN ANALYZE to verify pruning

Step 4: Re-run the benchmark and compare execution times and buffer hits.

Step 5: Intentionally break pruning — rewrite the query using DATE_PART('year', txn_date) = 2024 instead of explicit date bounds. Run EXPLAIN and confirm all partitions are scanned.

Challenge: Add an index on (account_id, txn_date) and write a query that benefits from both pruning and index lookup. Verify both are happening in EXPLAIN ANALYZE.

---

Common Mistakes & Troubleshooting

Mistake 1: Pruning Not Triggering Due to Type Mismatch

Symptom: All partitions appear in the query plan despite a clear date filter.

Cause: Your literal is a different type than the partition key. For example, partitioning on DATE but filtering with a TIMESTAMP literal.

-- This may not prune correctly if event_date is DATE
WHERE event_date > '2024-01-01 00:00:00'  -- TIMESTAMP literal

-- Fix: Match the type
WHERE event_date >= '2024-01-01'::DATE

Always cast ambiguous literals explicitly.

Mistake 2: Pruning Disabled by Function on Partition Key

Already covered, but worth repeating because it's extremely common:

-- BAD: function on partition key
WHERE TO_CHAR(order_date, 'YYYY') = '2024'
WHERE DATE_TRUNC('month', order_date) = '2024-01-01'
WHERE EXTRACT(YEAR FROM order_date) = 2024

-- GOOD: direct comparison
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

If you receive queries from an ORM or BI tool that wraps dates in functions, this is worth a code review conversation with your team.

Mistake 3: Off-by-One in Partition Bounds Leading to Data Loss

Inserting a row with order_date = '2022-12-31' into a partition defined as FOR VALUES FROM ('2022-01-01') TO ('2022-12-31') will fail — that end date is exclusive. Always use the first day of the next period as the upper bound:

-- WRONG
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31')

-- CORRECT
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01')

Mistake 4: Missing DEFAULT Partition Causing Insert Failures

If a value doesn't match any partition and no DEFAULT partition exists, PostgreSQL raises an error. Always create a DEFAULT partition during development and in production tables accepting external data.

Mistake 5: Not Updating Partition Pruning Setting

-- Check that pruning is enabled
SHOW enable_partition_pruning;  -- Should be 'on'

-- If disabled, enable it
SET enable_partition_pruning = on;

This is rarely the problem in modern PostgreSQL (it's on by default), but check it when debugging in older environments or when another DBA has tuned the session parameters.

Mistake 6: Joining Across Partitioned Tables Without Filtering Both Sides

-- This prunes orders but scans ALL of customers
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_date < '2025-01-01';

If customers is also partitioned, make sure you're filtering it too — otherwise you'll get full scans on the unfiltered side.

Mistake 7: Creating Too Many Partitions

Daily partitions on a table growing at 1M rows/day sounds like precision, but at 5 years that's 1825 partitions. The PostgreSQL planner will spend measurable time just evaluating partition constraints. Monitor planning time with:

EXPLAIN (ANALYZE, TIMING)
SELECT ...;

If planning time exceeds 50-100ms, you likely have too many partitions. Consolidate to weekly or monthly.

---

Performance Considerations and When Not to Partition

Partitioning isn't free. Here's an honest accounting of the costs:

Planning overhead increases with partition count. Each query plan must evaluate all partition constraints. Prepare statements and connection poolers that cache plans help mitigate this.

Inserts have slightly higher overhead. The database must evaluate which partition each row belongs to before inserting. For bulk loads this is negligible; for high-frequency single-row inserts, the overhead is measurable but usually acceptable.

Cross-partition queries can be slower than non-partitioned equivalents. A query that spans all partitions does more work than the same query on a non-partitioned table of equivalent size, because it adds the Append/merge overhead of combining results across partitions.

Partitioning is not a substitute for a bad schema. If your query is slow because of a missing join condition or a fundamentally wrong aggregation strategy, partitioning won't save you.

When not to partition:

• Tables under ~50 million rows / 10 GB — the overhead rarely justifies the benefit
• Tables where the dominant queries don't filter on the partition key
• OLTP workloads with extremely high single-row insert rates where the routing overhead matters

---

Summary & Next Steps

You've covered a lot of ground. To summarize what you can now do:

• Design partitioning strategies based on actual query patterns, choosing between range, list, hash, and composite approaches
• Implement partitioned tables in PostgreSQL from scratch, with correct bound syntax and defensive DEFAULT partitions
• Write queries that reliably trigger partition pruning by filtering directly on the partition key with correct types
• Verify pruning is happening using EXPLAIN ANALYZE and diagnose cases where it fails
• Migrate existing flat tables to partitioned structures safely
• Implement near-instantaneous data retention by dropping or detaching old partitions
• Apply these concepts in cloud warehouses like BigQuery and Snowflake using their native mechanisms

What to explore next:

• Parallel query execution on partitioned tables — PostgreSQL can assign different partitions to different parallel workers, multiplying throughput further. Study max_parallel_workers_per_gather and how the planner assigns parallel workers to partitioned scans.
• pg_partman — The standard PostgreSQL extension for automated partition maintenance. Learn how to configure it for time-based partitioning with automatic pre-creation and retention policies.
• Partial indexes on partitions — You can create indexes on individual partitions with different predicates than the global index. This is useful for hot partitions (recent data) that need composite indexes while cold partitions only need simple ones.
• BRIN indexes — Block Range Indexes are specifically designed for naturally ordered data like timestamps in append-only tables. They're tiny and extremely fast for range scans, and they complement partitioning beautifully.
• Query rewriting for BI tool compatibility — Many BI tools generate SQL with date functions that break pruning. Learn how to configure tools like Metabase, Looker, and dbt to generate pruning-friendly SQL.

The combination of partitioning, targeted indexing, and statistics maintenance is the foundation of SQL performance engineering at scale. Master these, and you'll be the person on your team who can actually diagnose and fix the slow queries that everyone else just shrugs at.