SQL Table Partitioning — Function on Key Disables Pruning

At some point in every database engineer's career, a query that used to run in 200ms starts taking 45 seconds. The table hasn't changed structurally — it's just grown from 2 million rows to 800 million. Indexes help, but even a perfectly tuned B-tree index on 800 million rows requires significant I/O just to traverse the tree and resolve heap pages. You start questioning your index design, your query shape, your statistics configuration — and most of the time the real answer is that you've hit the ceiling of what single-segment storage can do efficiently.

This is where table partitioning stops being an academic concept and becomes an operational lifeline.

The core mechanism partitioning exploits is called partition pruning: instead of scanning or even index-seeking across the full dataset, the query planner eliminates entire physical segments it knows cannot contain rows matching your WHERE clause. A query filtering on the last 30 days never touches the 7 years of historical data sitting in other partitions — those segments are excluded at plan time, before a single page is read from disk. Beyond query performance, partitioning unlocks fast bulk operations. Dropping a year's worth of old data becomes a near-instant metadata operation (DROP PARTITION) instead of a DELETE that locks the table for hours, generates hundreds of gigabytes of WAL, and leaves you running VACUUM for days.

I've seen teams partition a table and wonder why nothing improved — usually because they partitioned on the wrong column, or because a single function call in the WHERE clause was silently disabling pruning on every query. I've also seen partitioning done right turn a 45-second dashboard query into a 180ms one without touching a single index or rewriting application logic.

By the end of this article you'll understand how each partitioning strategy works at the storage level, write production-ready DDL for range, list, hash, and composite schemes, diagnose when partition pruning is silently failing, manage partition maintenance without downtime, and avoid the three mistakes that turn partitioning from a performance win into a support nightmare.

Why Partition Pruning Fails When the Partition Key Is Wrapped in a Function

SQL table partitioning splits a large table into smaller physical segments (partitions) based on a partition key — typically a date column or an ID range. The core mechanic is partition pruning: the query planner eliminates irrelevant partitions at plan time, scanning only the subset that could contain matching rows. This is not an optimization hint; it's a structural property of the storage layout.

Pruning works because the planner compares query predicates directly against the partition boundary metadata. When you write WHERE created_at >= '2024-01-01', the planner knows which partitions hold January data. But if you wrap the partition key in a function — WHERE DATE(created_at) >= '2024-01-01' — the planner sees an opaque expression, not a direct column comparison. It cannot infer the partition range, so it falls back to a full scan of all partitions. This is not a planner bug; it's a fundamental limitation of how partition metadata is stored and matched.

Use partitioning when you have time-series data exceeding 100M rows, or when you need to drop old data via DROP PARTITION instead of slow DELETE. But never assume the planner will be smart about function-wrapped keys. The performance cost of a missed prune is linear in the number of partitions — if you have 365 daily partitions, a missed prune means scanning 365× more data than necessary.

Partitioning Strategies — Range, List, Hash, and Composite

SQL offers four partitioning strategies, each designed for a different access pattern. Choosing the wrong strategy is the most common partitioning mistake — it adds storage and planning overhead with zero pruning benefit on your actual queries.

Range partitioning splits data by a continuous value — typically a date or timestamp. Each partition covers a defined interval (e.g., January 2026, February 2026). This is the most common strategy for time-series data, event logs, and financial transactions. Queries filtering on date ranges prune efficiently because the planner can compare your WHERE clause bounds against partition boundaries directly at plan time — no row evaluation needed.

List partitioning splits data by discrete values — a region code, status, or category. Each partition contains rows matching a specific set of known values (e.g., region IN ('US', 'CA')). This works well when your queries filter on a fixed enumeration of categories and you want each category group to live in its own physical segment for both pruning and operational isolation.

Hash partitioning distributes rows evenly across N partitions using a hash function applied to the partition key. There is no semantic meaning to which partition a row lands in — the goal is purely even distribution. Hash partitioning is the right choice when you have no natural range or list dimension but need to reduce per-partition size for maintenance operations like index rebuilds and vacuum. It provides no pruning benefit for range queries.

Composite partitioning combines two strategies — typically range at the first level and hash (or list) at the second level. For example: partition by month (range), then sub-partition each month into 4 hash buckets. This gives you time-based pruning at the top level and even distribution within each time window at the second level. It's the right call when pure range partitioning creates hot partitions because recent months concentrate all writes.

-- ============================================================
-- RANGE PARTITIONING: split by date — most common for time-series
-- Each partition holds one month of data
-- ============================================================
CREATE TABLE orders (
    order_id     BIGINT NOT NULL,
    customer_id  INT NOT NULL,
    total_amount DECIMAL(10, 2),
    created_at   TIMESTAMP NOT NULL,
    status       VARCHAR(20)
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_2026_02 PARTITION OF orders
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE orders_2026_03 PARTITION OF orders
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
-- ... continue for each month

-- Catch-all partition for values outside defined ranges
-- Without this, out-of-range inserts fail with a hard error
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- ============================================================
-- LIST PARTITIONING: split by discrete category values
-- Each partition holds a specific region's data
-- ============================================================
CREATE TABLE transactions (
    txn_id      BIGINT NOT NULL,
    amount      DECIMAL(12, 2),
    region      VARCHAR(10) NOT NULL,
    txn_date    DATE
) PARTITION BY LIST (region);

CREATE TABLE txn_north_america PARTITION OF transactions
    FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE txn_europe PARTITION OF transactions
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES', 'IT');
CREATE TABLE txn_asia_pacific PARTITION OF transactions
    FOR VALUES IN ('JP', 'KR', 'AU', 'IN', 'SG');
CREATE TABLE txn_default PARTITION OF transactions DEFAULT;

-- ============================================================
-- HASH PARTITIONING: even distribution — no semantic meaning
-- Use when you have no natural range or list dimension
-- ============================================================
CREATE TABLE user_events (
    event_id    BIGINT NOT NULL,
    user_id     INT NOT NULL,
    event_type  VARCHAR(50),
    event_time  TIMESTAMP
) PARTITION BY HASH (user_id);

CREATE TABLE user_events_p0 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_events_p1 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_events_p2 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_events_p3 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- ============================================================
-- COMPOSITE PARTITIONING: range (monthly) + hash sub-partitions
-- Time-based pruning at level 1, even distribution at level 2
-- Solves hot-partition problem when recent months are write-heavy
-- ============================================================
CREATE TABLE audit_log (
    log_id      BIGINT NOT NULL,
    actor_id    INT NOT NULL,
    action      VARCHAR(100),
    log_time    TIMESTAMP NOT NULL
) PARTITION BY RANGE (log_time);

-- Monthly partition, itself partitioned by hash on actor_id
CREATE TABLE audit_log_2026_01 PARTITION OF audit_log
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
    PARTITION BY HASH (actor_id);

CREATE TABLE audit_log_2026_01_p0 PARTITION OF audit_log_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE audit_log_2026_01_p1 PARTITION OF audit_log_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE audit_log_2026_01_p2 PARTITION OF audit_log_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE audit_log_2026_01_p3 PARTITION OF audit_log_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partition Pruning — The Silent Performance Killer When It Fails

Partition pruning is the entire reason partitioning exists. When it works, queries touch only the relevant physical segment — 1 month out of 48, 1 region out of 10. When it fails silently, every query scans all partitions, and partitioning actually degrades performance compared to the unpartitioned table because of the per-partition planning overhead.

The most common pruning killers, in order of how often I see them in the wild:

1. Function calls on the partition key: WHERE EXTRACT(month FROM created_at) = 1 or WHERE DATE_TRUNC('month', created_at) = '2026-01-01'. The planner cannot compare the function's output against partition boundaries without evaluating the function for every possible input value — which it cannot do at plan time. So it gives up and scans everything.
2. Implicit type casting: WHERE created_at = '2026-01-15' where created_at is a TIMESTAMP and the literal is a VARCHAR. Some databases resolve this gracefully; others disable pruning rather than risk a type mismatch. Always cast explicitly: WHERE created_at = TIMESTAMP '2026-01-15 00:00:00'.
3. OR conditions that include a non-partition-key column: WHERE created_at >= '2026-01-01' OR updated_at >= '2026-01-01' when the table is partitioned by created_at. The OR with updated_at forces the planner to consider every partition because it cannot prune based on a column that isn't the partition key.
4. Predicates buried inside subqueries or CTEs: Some planners cannot push WHERE clause predicates down through a CTE boundary, which means the partition scan at the inner level sees no filter and scans everything. Materializing CTEs in older PostgreSQL versions (pre-12) is a common cause of this.

The diagnostic command is always the same: EXPLAIN (ANALYZE, BUFFERS). Look for 'Partitions scanned: N' in the output. If N equals your total partition count, pruning is not working and you need to find why before the query touches production at scale.

-- ============================================================
-- DIAGNOSIS: verify partition pruning is active
-- Run EXPLAIN before and after any WHERE clause change
-- ============================================================

-- GOOD: bare column with direct range comparison — pruning fires
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at < '2026-02-01';
-- Expected output: Partitions scanned: 1
-- Execution time: ~200ms on 48-partition table

-- BAD: function call on partition key — pruning disabled
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount
FROM orders
WHERE DATE_TRUNC('month', created_at) = '2026-01-01';
-- Expected output: Partitions scanned: 48
-- Execution time: ~45s — 225x regression

-- BAD: EXTRACT on partition key — same problem, different syntax
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount
FROM orders
WHERE EXTRACT(year FROM created_at) = 2026
  AND EXTRACT(month FROM created_at) = 1;
-- Expected output: Partitions scanned: 48

-- BAD: OR with non-partition-key column — forces full scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount
FROM orders
WHERE created_at >= '2026-01-01'
   OR updated_at >= '2026-01-01';
-- Expected output: Partitions scanned: 48
-- Fix: split into two queries with UNION ALL if the OR is unavoidable

-- ============================================================
-- PARTITION SIZE AUDIT: identify hot or imbalanced partitions
-- Run monthly to catch retention and growth anomalies
-- ============================================================
SELECT
    c.relname AS partition_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_stat_user_tables.n_live_tup AS live_rows
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
JOIN pg_class p ON p.oid = i.inhparent
JOIN pg_stat_user_tables ON pg_stat_user_tables.relname = c.relname
WHERE p.relname = 'orders'
ORDER BY pg_total_relation_size(c.oid) DESC;

Partition Maintenance — Adding, Dropping, and Managing at Scale

Partitioning shifts database maintenance from row-level operations to metadata-level operations. This is the operational payoff that makes partitioning worth the schema complexity — but only if you manage it correctly. Get maintenance wrong and you end up with the worst of both worlds: the overhead of a partitioned schema and the pain of row-level operations on large datasets.

Dropping old data: Without partitioning, deleting 1 billion rows requires a DELETE statement that generates one WAL entry per deleted row, bloats the table with dead tuples, holds row-level locks during execution, and leaves VACUUM with an enormous cleanup job that takes hours and competes with production reads. With partitioning, ALTER TABLE ... DETACH PARTITION followed by DROP TABLE is a catalog metadata operation — it removes the partition's file pointers from the system catalog in milliseconds. No row-level locks on the parent table, no WAL bloat, no vacuum pressure afterward.

Adding new partitions: For range-partitioned tables, partitions must exist before data arrives. If a row's partition key value falls outside all defined partition ranges and no DEFAULT partition exists, the INSERT fails with a hard error and the transaction rolls back. The standard pattern is a scheduled job (cron or an internal scheduler) that creates partitions 3–6 months ahead using CREATE TABLE IF NOT EXISTS so the job is idempotent.

Sliding window pattern: The production standard for time-series retention is a sliding window: in one monthly maintenance job, create the partition for the month 3 months out and drop the partition from 13 months ago. The table stays at a constant size, the job is fast, and there's no manual intervention needed as long as the job runs.

Partition count limits: Each partition adds overhead to the planner's boundary evaluation step. PostgreSQL handles up to ~500 partitions well; beyond that, query planning time becomes measurable and can exceed execution time for fast queries. If you need more coverage than 500 monthly partitions (roughly 40 years), use composite partitioning — range at the top level with hash sub-partitions — instead of pushing the partition count higher.

-- ============================================================
-- SLIDING WINDOW: automated partition management
-- Create future partition, drop expired one — zero downtime
-- Run monthly via cron or internal scheduler
-- ============================================================

-- Step 1: Create next month's partition before data arrives
CREATE TABLE IF NOT EXISTS orders_2026_04 PARTITION OF orders
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- Step 2: Detach the partition from 13 months ago from the parent
-- DETACH removes catalog entry without locking the parent table
ALTER TABLE orders DETACH PARTITION orders_2025_03;

-- Step 3: Drop the physical files — instant once detached
DROP TABLE orders_2025_03;

-- PostgreSQL 14+: use CONCURRENTLY to avoid any parent table lock during detach
-- ALTER TABLE orders DETACH PARTITION orders_2025_03 CONCURRENTLY;

-- ============================================================
-- AUTOMATED PARTITION CREATION: stored procedure
-- Call monthly to stay 6 months ahead of data arrival
-- Schema: io.thecodeforge.maint for operational procedures
-- ============================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.maint.create_monthly_partitions(
    p_table_name TEXT,
    p_months_ahead INT DEFAULT 6
)
LANGUAGE plpgsql AS $$
DECLARE
    v_start_date DATE;
    v_end_date   DATE;
    v_partition_name TEXT;
    v_current    DATE := DATE_TRUNC('month', CURRENT_DATE);
BEGIN
    FOR i IN 0..p_months_ahead LOOP
        v_start_date     := v_current + (i || ' months')::INTERVAL;
        v_end_date       := v_start_date + INTERVAL '1 month';
        v_partition_name := p_table_name || '_' || TO_CHAR(v_start_date, 'YYYY_MM');

        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
             FOR VALUES FROM (%L) TO (%L)',
            v_partition_name,
            p_table_name,
            v_start_date,
            v_end_date
        );

        RAISE NOTICE 'Ensured partition exists: % (% to %)',
            v_partition_name, v_start_date, v_end_date;
    END LOOP;
END;
$$;

-- Usage: call at the start of each month to stay ahead
CALL io.thecodeforge.maint.create_monthly_partitions('orders', 6);

-- ============================================================
-- PARTITION HEALTH CHECK: detect gaps and size anomalies
-- Run weekly — catches maintenance job failures before inserts fail
-- ============================================================
SELECT
    c.relname                                                    AS partition_name,
    pg_size_pretty(pg_total_relation_size(c.oid))               AS total_size,
    pg_stat_user_tables.n_live_tup                              AS live_rows,
    pg_stat_user_tables.last_autoanalyze                        AS last_analyzed
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
JOIN pg_class p ON p.oid = i.inhparent
JOIN pg_stat_user_tables ON pg_stat_user_tables.relname = c.relname
WHERE p.relname = 'orders'
ORDER BY c.relname;

-- Monitor DEFAULT partition for unexpected row accumulation
-- Rows landing here mean a future partition is missing
SELECT COUNT(*) AS default_partition_rows
FROM orders_default;

Composite Partitioning and Cross-Partition Query Patterns

When a single partitioning dimension doesn't fully address your access pattern or your write distribution, composite partitioning (also called sub-partitioning) combines two strategies. The most common production pattern is range at the first level for time-based pruning and hash at the second level for even distribution within each time window.

The problem composite partitioning solves: pure range partitioning on a timestamp creates structurally uneven partitions as traffic grows. Your January 2023 partition has 2M rows. Your January 2026 partition has 40M rows because traffic has grown 20x over three years. Writes concentrate on the current month's partition — that single segment becomes a hotspot for concurrent inserts, autovacuum, and index updates. Adding hash sub-partitions distributes writes across N buckets per time period, keeping per-leaf-partition size roughly constant regardless of when the data was written.

The trade-off is total partition count. Partitioning by month over 4 years gives you 48 partitions. Adding 4 hash sub-partitions per month gives you 192. Adding 8 hash sub-partitions gives you 384. That's still under the 500-partition threshold where planner overhead becomes noticeable. But if you go to 16 sub-partitions over 4 years, you're at 768 — and you'll start seeing planning time increase on fast point-lookup queries.

Cross-partition queries deserve specific attention. When a query cannot prune at either level — because it doesn't filter on the partition key or because the predicate is in a form the planner can't evaluate at plan time — the database must probe every leaf partition. On a composite-partitioned table with 192 partitions, that's 192 separate segment scans. That is materially slower than scanning one unpartitioned table with a good index because of the per-partition coordination overhead. Analytics workloads that routinely scan the entire dataset are not good candidates for row-level partitioning — columnar storage or pre-aggregated materialized views are better tools for that access pattern.

-- ============================================================
-- COMPOSITE: range (monthly) + hash (4 buckets per month)
-- Prevents hot-partition problem when traffic grows over time
-- Total partitions: months × hash_modulus (e.g., 48 × 4 = 192)
-- ============================================================
CREATE TABLE event_stream (
    event_id    BIGINT NOT NULL,
    user_id     INT NOT NULL,
    event_type  VARCHAR(50),
    event_time  TIMESTAMP NOT NULL,
    payload     JSONB
) PARTITION BY RANGE (event_time);

-- Monthly range partition, itself partitioned by hash on user_id
CREATE TABLE event_stream_2026_01 PARTITION OF event_stream
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
    PARTITION BY HASH (user_id);

CREATE TABLE event_stream_2026_01_p0 PARTITION OF event_stream_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE event_stream_2026_01_p1 PARTITION OF event_stream_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE event_stream_2026_01_p2 PARTITION OF event_stream_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE event_stream_2026_01_p3 PARTITION OF event_stream_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- ============================================================
-- PRUNING BEHAVIOR: which queries prune and at which level
-- Understanding this is essential for query design on composite tables
-- ============================================================

-- FULL PRUNING: both range key and hash key in WHERE
-- Planner prunes level 1 (month) then level 2 (hash bucket)
-- Touches exactly 1 leaf partition
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_stream
WHERE event_time >= '2026-01-01' AND event_time < '2026-02-01'
  AND user_id = 12345;
-- Partitions scanned: 1 — the hash bucket for user 12345 within January

-- PARTIAL PRUNING: range key only
-- Level 1 prunes to one month; level 2 scans all 4 hash buckets
-- Still useful — touches 4 partitions instead of 192
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_stream
WHERE event_time >= '2026-01-01' AND event_time < '2026-02-01';
-- Partitions scanned: 4 (all hash sub-partitions of 2026_01)

-- NO PRUNING: hash key only, no range key
-- Level 1 cannot prune — hash key alone doesn't establish a time boundary
-- Scans every month's hash bucket for the matching remainder
-- Often slower than the unpartitioned equivalent due to per-partition overhead
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_stream
WHERE user_id = 12345;
-- Partitions scanned: all leaf partitions across all months

-- ============================================================
-- COMPOSITE PARTITION HEALTH CHECK
-- Verify all sub-partitions exist for each range parent
-- Missing sub-partitions silently drop inserts to the DEFAULT bucket
-- ============================================================
SELECT
    parent.relname  AS range_partition,
    child.relname   AS hash_sub_partition,
    pg_size_pretty(pg_total_relation_size(child.oid)) AS size
FROM pg_inherits i1
JOIN pg_class parent ON parent.oid = i1.inhrelid
JOIN pg_inherits i2  ON i2.inhparent = parent.oid
JOIN pg_class child  ON child.oid = i2.inhrelid
JOIN pg_class root   ON root.oid = i1.inhparent
WHERE root.relname = 'event_stream'
ORDER BY parent.relname, child.relname;

Querying Partition Metadata — Stop Guessing, Start Verifying

You think your table is partitioned? The optimizer might disagree. After a 'quick' ALTER TABLE someone forgot to check, you'll be scanning every partition like it's 1999. SQL Server exposes partition metadata through system views — use them before a production fire drill.

The three views you need: sys.partitions, sys.partition_schemes, and sys.partition_functions. They tell you exactly which partitions exist, their boundary values, and how data is mapped. No guesswork.

Start with sys.partitions to verify partition counts. Then check sys.partition_range_values for boundary clarity. If a partition has zero rows, that's a red flag — maybe your maintenance script dropped something it shouldn't have. Always query metadata before and after schema changes. It's the difference between a calm 'yes' in a meeting and a panicked rollback.

// io.thecodeforge — database tutorial

-- Is your SalesOrderHeader actually partitioned?
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    p.partition_number,
    p.rows,
    prv.boundary_id,
    prv.value AS boundary_value
FROM sys.partitions p
LEFT JOIN sys.partition_range_values prv
    ON prv.function_id = (
        SELECT function_id 
        FROM sys.partition_schemes ps
        WHERE ps.data_space_id = p.data_space_id
    )
    AND prv.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'SalesOrderHeader'
ORDER BY p.partition_number;

Limitations That Will Bite You — Partitioning Isn't a Silver Bullet

Partitioning is a scalpel, not a chainsaw. Most devs learn this after their first failed ALTER TABLE SWITCH or a query that went parallel-psycho. Here's the reality: not all operations play nicely with partitions.

First, you cannot use ALTER TABLE SWITCH to move data between partitions with different columns, collations, or computed columns unless they're persisted. The metadata checks are strict — one mismatch and you're stuck with a manual data move.

Second, only SQL Server Enterprise Edition supports full partition-level operations like TRUNCATE on a single partition. Standard Edition treats partitioned tables as regular tables for many operations — you'll lose the performance benefit.

Third, IDENTITY columns don't reset per partition. If you truncate partition 2, the identity seed won't reset unless the entire table is truncated. That catches teams doing rolling window maintenance.

Fourth, indexes on partitioned tables have their own rules. Non-aligned indexes can kill partition pruning and make queries cross partitions unnecessarily.

// io.thecodeforge — database tutorial

-- This SWITCH will fail silently if indexes don't align
-- Stage table must have same schema, same clustered index
CREATE TABLE SalesOrder_Stage (
    OrderDate datetime,
    OrderID int,
    Amount decimal(10,2)
) ON [PRIMARY];

-- SWITCH to partition 3
ALTER TABLE SalesOrderHeader
SWITCH PARTITION 3 TO SalesOrder_Stage;
-- Fails: 'The index ... is not aligned with the switch partition'

-- Workaround: ensure stage table has identical partitioned index
-- Or use a non-partitioned swap table with no indexes
-- Then rebuild indexes on target

Partition Permissions — Who Can Alter the Schema Without Breaking Queries

Partitioning adds schema-level objects that demand specific permissions beyond basic DML. The partition function and scheme are owned by the schema owner, not the table owner. Granting ALTER on a table does not automatically grant rights to modify partitions. To split or merge a partition boundary, a user needs ALTER ANY DATASPACE or ownership of the partition function. Without it, ADD PARTITION or SWITCH PARTITION fails with permission errors. Even viewing partition metadata via sys.partition_functions requires VIEW DEFINITION. The silent failure occurs when a DBA adds a new filegroup but forgets to grant the partition function owner execute rights on the filegroup’s database. Production trap: granting db_ddladmin to an application service account exposes the entire partition structure to accidental boundary shifts. Lock down partition function changes to a separate role and audit their usage.

// io.thecodeforge — database tutorial

-- Grant rights to view partition scheme metadata
GRANT VIEW DEFINITION ON PARTITION SCHEME: MonthlyScheme TO ReportingUser;

-- Grant ability to alter partition structure
GRANT ALTER ANY DATASPACE TO PartitionAdmin;

-- Error: missing permission causes silent failure
-- User must own partition function or have ALTER ANY DATASPACE
ALTER PARTITION FUNCTION MonthlyPF()
    SPLIT RANGE ('2025-04-01');

-- Verify effective permissions
SELECT dp.name, p.permission_name
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE p.major_id = OBJECT_ID('PartitionFunction: MonthlyPF');

New Filegroups for Partitions — When and Why to Create Them

Partitioning across a single filegroup negates I/O isolation, the primary performance benefit. When each partition resides on its own filegroup, you can place filegroups on separate physical drives or storage tiers. Create filegroups before the partition scheme references them. Use ALTER DATABASE ADD FILEGROUP, then add files. A common mistake is creating one filegroup for the entire table instead of per partition range. Hot partitions — like current month data — benefit from fast SSD filegroups; cold partitions can live on slower HDD filegroups. When you split a partition boundary, the new partition inherits the default filegroup unless you specify NEXT USED. Production trap: adding a filegroup after the scheme is built requires dropping and recreating the scheme, which forces a full data movement. Plan all filegroups upfront. For historical data, rotate filegroups quarterly and set them to READ_ONLY after the partition is no longer written to.

// io.thecodeforge — database tutorial

-- Create filegroups for each partition range
ALTER DATABASE SalesDB ADD FILEGROUP FG_Month2025_01;
ALTER DATABASE SalesDB ADD FILEGROUP FG_Month2025_02;

-- Add physical files to each filegroup
ALTER DATABASE SalesDB ADD FILE (
    NAME = 'Data_2025_01',
    FILENAME = 'D:\SSD\Data_2025_01.ndf'
) TO FILEGROUP FG_Month2025_01;

ALTER DATABASE SalesDB ADD FILE (
    NAME = 'Data_2025_02',
    FILENAME = 'E:\HDD\Data_2025_02.ndf'
) TO FILEGROUP FG_Month2025_02;

-- Reference filegroups in partition scheme
CREATE PARTITION SCHEME MonthlyScheme
AS PARTITION MonthlyPF
TO (FG_Month2025_01, FG_Month2025_02);

Declarative Partitioning in PostgreSQL: Range, List, Hash

PostgreSQL 10 introduced declarative partitioning, which simplifies partition management compared to traditional inheritance-based partitioning. With declarative partitioning, you define a parent table and specify the partition method (range, list, or hash) using the PARTITION BY clause. PostgreSQL automatically routes rows to the appropriate child partitions based on the partition key. Range partitioning divides data into intervals (e.g., by date), list partitioning assigns rows to partitions based on a list of values (e.g., region codes), and hash partitioning distributes rows across a fixed number of partitions using a hash function (e.g., for load balancing). For example, to create a range-partitioned table by date: CREATE TABLE sales (id int, sale_date date, amount numeric) PARTITION BY RANGE (sale_date); Then create partitions: CREATE TABLE sales_2024_q1 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'); This approach eliminates the need for triggers or manual routing, reduces maintenance overhead, and improves query performance through partition pruning. However, ensure the partition key is not wrapped in a function (e.g., DATE_TRUNC('month', sale_date)) as that disables pruning. Hash partitioning is useful for evenly distributing data when no natural range or list exists, but it complicates range-based queries. Always choose the partition method based on your query patterns and data distribution.

-- Range partitioning by date
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- List partitioning by region
CREATE TABLE customers (
    id INT,
    region TEXT,
    name TEXT
) PARTITION BY LIST (region);

CREATE TABLE customers_na PARTITION OF customers
    FOR VALUES IN ('NA', 'US', 'CA');

CREATE TABLE customers_eu PARTITION OF customers
    FOR VALUES IN ('EU', 'UK', 'DE');

-- Hash partitioning with 4 partitions
CREATE TABLE logs (
    id INT,
    log_date DATE,
    message TEXT
) PARTITION BY HASH (id);

CREATE TABLE logs_p0 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE logs_p1 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE logs_p2 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE logs_p3 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partition Pruning: How It Works and How to Verify

Partition pruning is a query optimization technique where the database eliminates irrelevant partitions from the scan plan based on the query's WHERE clause. For example, if you query SELECT FROM sales WHERE sale_date = '2024-02-15', PostgreSQL can skip partitions that do not contain that date (e.g., sales_2024_q2). Pruning works only when the partition key is used directly in the WHERE clause without being wrapped in a function or expression. To verify whether pruning is happening, use EXPLAIN (ANALYZE, BUFFERS) and look for 'Subplans Removed' or check the output for 'never executed' partitions. For example: EXPLAIN (ANALYZE, BUFFERS) SELECT FROM sales WHERE sale_date = '2024-02-15'; In the output, you should see something like 'Subplans Removed: 3' if there are 4 partitions and only 1 is scanned. If pruning is not occurring, the query will scan all partitions, leading to poor performance. Common causes of pruning failure include: using functions on the partition key (e.g., WHERE DATE_TRUNC('month', sale_date) = '2024-02-01'), implicit type casting, or using OR conditions that span multiple partitions. To fix, avoid functions on the partition key and ensure data types match. You can also use pg_partman or query pg_inherits to monitor partition usage. Always test with EXPLAIN to confirm pruning.

-- Create a range-partitioned table
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE sales_2024_q3 PARTITION OF sales
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE sales_2024_q4 PARTITION OF sales
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

-- Insert sample data
INSERT INTO sales VALUES (1, '2024-02-15', 100);
INSERT INTO sales VALUES (2, '2024-05-20', 200);

-- Verify pruning with direct key usage (should prune)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM sales WHERE sale_date = '2024-02-15';
-- Look for: Subplans Removed: 3

-- Verify pruning failure with function on key (should not prune)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM sales WHERE DATE_TRUNC('month', sale_date) = '2024-02-01';
-- Look for: all partitions scanned

Partition Maintenance: Attach/Detach, Splitting, Archiving

Partition maintenance is critical for managing large datasets over time. PostgreSQL provides DDL commands to attach, detach, split, and archive partitions without downtime. Use ALTER TABLE ... ATTACH PARTITION to add a new partition to an existing partitioned table. For example, to add a new quarter: CREATE TABLE sales_2025_q1 (LIKE sales INCLUDING DEFAULTS); ALTER TABLE sales ATTACH PARTITION sales_2025_q1 FOR VALUES FROM ('2025-01-01') TO ('2025-04-01'); Detaching a partition is useful for archiving or dropping old data: ALTER TABLE sales DETACH PARTITION sales_2024_q1; This removes the partition from the parent but keeps the data intact as a standalone table. You can then archive or drop it later. Splitting a partition (e.g., splitting a large monthly partition into two weekly partitions) requires creating new partitions, moving data, and then detaching the old one. For example, to split sales_2024_q1 into January and February: CREATE TABLE sales_2024_jan (LIKE sales INCLUDING DEFAULTS); CREATE TABLE sales_2024_feb (LIKE sales INCLUDING DEFAULTS); ALTER TABLE sales ATTACH PARTITION sales_2024_jan FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); ALTER TABLE sales ATTACH PARTITION sales_2024_feb FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'); Then insert data from the old partition and drop it. Archiving can be automated using pg_partman or custom scripts. Always test maintenance operations in a staging environment first, and consider using transactions to ensure consistency. For large partitions, use CREATE TABLE ... AS or INSERT ... SELECT to move data efficiently.

-- Attach a new partition
CREATE TABLE sales_2025_q1 (LIKE sales INCLUDING DEFAULTS);
ALTER TABLE sales ATTACH PARTITION sales_2025_q1
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- Detach a partition for archiving
ALTER TABLE sales DETACH PARTITION sales_2024_q1;
-- Now sales_2024_q1 is a standalone table; can be archived or dropped

-- Split a partition (example: split sales_2024_q1 into two monthly partitions)
-- Step 1: Create new partitions
CREATE TABLE sales_2024_jan (LIKE sales INCLUDING DEFAULTS);
CREATE TABLE sales_2024_feb (LIKE sales INCLUDING DEFAULTS);
-- Step 2: Attach them
ALTER TABLE sales ATTACH PARTITION sales_2024_jan
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
ALTER TABLE sales ATTACH PARTITION sales_2024_feb
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Step 3: Move data from old partition (if any) and detach/drop it
INSERT INTO sales_2024_jan SELECT * FROM sales_2024_q1 WHERE sale_date < '2024-02-01';
INSERT INTO sales_2024_feb SELECT * FROM sales_2024_q1 WHERE sale_date >= '2024-02-01';
ALTER TABLE sales DETACH PARTITION sales_2024_q1;
DROP TABLE sales_2024_q1;