Module 14 — Advanced Partitioning: Pruning, Maintenance, and pg_partman

Declarative Partitioning vs Inheritance-Based Partitioning

Postgres has two partitioning systems with very different operational profiles.

Inheritance-Based Partitioning (Pre-PG10)

Before PostgreSQL 10, partitioning was implemented via table inheritance and CHECK constraints:

sql
-- Parent table (no data stored here)
CREATE TABLE transactions (
  id          BIGSERIAL,
  block_height BIGINT,
  timestamp   TIMESTAMPTZ,
  amount      NUMERIC(38,8)
);

-- Child table inherits parent's columns
CREATE TABLE transactions_2025 (
  CHECK (timestamp >= '2025-01-01' AND timestamp < '2026-01-01')
) INHERITS (transactions);

CREATE TABLE transactions_2026 (
  CHECK (timestamp >= '2026-01-01' AND timestamp < '2027-01-01')
) INHERITS (transactions);

-- Route inserts via trigger
CREATE OR REPLACE FUNCTION insert_transaction_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.timestamp >= '2026-01-01' THEN
    INSERT INTO transactions_2026 VALUES (NEW.*);
  ELSIF NEW.timestamp >= '2025-01-01' THEN
    INSERT INTO transactions_2025 VALUES (NEW.*);
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_transaction
BEFORE INSERT ON transactions
FOR EACH ROW EXECUTE FUNCTION insert_transaction_trigger();

Problems with inheritance partitioning:

• INSERT routing requires a trigger on every insert (trigger overhead on every write)
• The trigger must be manually updated every time a new partition is added
• Unique constraints cannot span partitions
• Foreign keys to/from partitioned tables are not enforced
• No automatic partition pruning in the query planner (requires constraint_exclusion = on)

Declarative Partitioning (PG10+)

Declarative partitioning is a first-class feature. The database handles routing, pruning, and constraint enforcement natively:

sql
-- Declarative range partitioning by timestamp
CREATE TABLE transactions (
  id          BIGSERIAL,
  block_height BIGINT NOT NULL,
  timestamp   TIMESTAMPTZ NOT NULL,
  amount      NUMERIC(38,8)
) PARTITION BY RANGE (timestamp);

-- Partition definition — no triggers needed
CREATE TABLE transactions_2025
  PARTITION OF transactions
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE transactions_2026
  PARTITION OF transactions
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Inserts route automatically based on timestamp value
INSERT INTO transactions (block_height, timestamp, amount)
VALUES (18500000, '2026-05-17 10:00:00+00', 1.5);
-- Goes to transactions_2026 automatically

Advantages of declarative over inheritance:

• No trigger required for INSERT routing (handled by the executor natively)
• Primary key and unique constraints work (must include partition key)
• Foreign keys to partitioned tables are supported (PG12+)
• Native partition pruning — no constraint_exclusion needed
• ATTACH PARTITION / DETACH PARTITION for online maintenance

When to still use inheritance: very old databases still on PG9.x (rare), or when you need a child table to have additional columns beyond the parent (inheritance allows this, declarative does not).

Partition Pruning: How It Works and What Breaks It

Partition pruning is the planner optimization that skips scanning irrelevant partitions. It is the primary reason to partition: a query on timestamp > '2026-05-01' should only scan transactions_2026, not all historical partitions.

How Pruning Works

The planner examines the partition key predicate and eliminates partitions whose bounds cannot contain matching rows.

sql
EXPLAIN SELECT * FROM transactions
WHERE timestamp >= '2026-05-01' AND timestamp < '2026-06-01';

-- With pruning:
-- Append
--   ->  Seq Scan on transactions_2026
--         Filter: (timestamp >= '2026-05-01' AND timestamp < '2026-06-01')

-- Without pruning (all partitions scanned):
-- Append
--   ->  Seq Scan on transactions_2025
--   ->  Seq Scan on transactions_2026
--   ...

What Silently Breaks Pruning

1. Type mismatch between predicate and partition key:

sql
-- Partition key is TIMESTAMPTZ
-- Predicate uses a TEXT literal — implicit cast prevents pruning
WHERE timestamp >= '2026-05-01'::text

-- Correct: use the matching type
WHERE timestamp >= '2026-05-01'::timestamptz
-- or just let Postgres infer the correct type:
WHERE timestamp >= '2026-05-01 00:00:00+00'

2. Function wrapping the partition key:

sql
-- date_trunc wraps the partition key — pruning cannot see through the function
WHERE date_trunc('month', timestamp) = '2026-05-01'

-- Correct: range predicate on the raw column
WHERE timestamp >= '2026-05-01' AND timestamp < '2026-06-01'

3. OR conditions across partition key:

sql
-- Both conditions needed for pruning; OR prevents elimination
WHERE timestamp > '2026-05-01' OR block_height = 18500000
-- Planner cannot prune — block_height condition could match any partition

4. Partition key in a subquery:

sql
-- The outer query's partition key is hidden from the planner
SELECT * FROM transactions
WHERE id IN (SELECT id FROM other_table WHERE timestamp > '2026-05-01')
-- Planner does not prune — it doesn't know which partitions id values fall in

5. Runtime parameter (Postgres < 14):

sql
-- In PG < 14, pruning only happens at plan time, not execution time
-- Parameterized queries may not prune at plan time
PREPARE q AS SELECT * FROM transactions WHERE timestamp = $1;
EXECUTE q('2026-05-17');  -- may not prune in PG < 14

Postgres 14+ supports runtime partition pruning — even parameterized queries prune at execution time.

Verifying Pruning

sql
-- Always verify pruning is working after adding partitioning
EXPLAIN (ANALYZE, VERBOSE)
SELECT count(*) FROM transactions
WHERE timestamp >= '2026-05-01' AND timestamp < '2026-06-01';

-- Look for:
-- "Partitions excluded: 23 out of 24"
-- or check that only one child table appears in the plan

-- Disable pruning to see the unoptimized plan (diagnostic only):
SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM transactions WHERE timestamp >= '2026-05-01';
-- Shows all partitions being scanned
SET enable_partition_pruning = on;

Constraint Exclusion (Legacy)

For inheritance-based partitioning, constraint_exclusion enables the planner to use CHECK constraints for pruning:

ini
# postgresql.conf
constraint_exclusion = partition   # only apply to partitioned tables (default)
# constraint_exclusion = on        # apply to all tables (expensive)
# constraint_exclusion = off        # disable completely

With declarative partitioning, constraint_exclusion is irrelevant — native partition pruning handles it. Only enable constraint_exclusion = on if you are still using inheritance-based partitioning.

Partition Maintenance at Scale

The Problem: Partition Explosion

A table partitioned by week over 5 years has 260 partitions. By month, 60. Manually creating and managing these partitions is error-prone and operationally expensive.

The worst case: the table has no partition for the current date range. An INSERT fails with:

ERROR: no partition of relation "transactions" found for row
DETAIL: Partition key of the failing row contains (timestamp) = (2027-01-05 00:00:00+00).

This is a write failure — all inserts are rejected until a partition covering the current time range is created.

pg_partman: Automated Partition Management

pg_partman is the standard extension for automated partition management. It:

• Creates future partitions proactively on a schedule
• Optionally detaches or drops old partitions based on retention policy
• Maintains a premake buffer (creates N future partitions ahead of current time)
• Works with pg_cron or external schedulers

sql
-- Install pg_partman
CREATE EXTENSION pg_partman SCHEMA partman;

-- Register the partitioned table with pg_partman
SELECT partman.create_parent(
  p_parent_table   => 'public.transactions',
  p_control        => 'timestamp',       -- partition key column
  p_type           => 'range',
  p_interval       => 'monthly',         -- partition size
  p_premake        => 4,                 -- create 4 future partitions in advance
  p_start_partition => '2024-01-01'      -- start of partition range
);

This creates:

• All monthly partitions from 2024-01-01 to 4 months in the future
• The partman.part_config entry tracking this table

Automated Maintenance with pg_cron

sql
-- Install pg_cron
CREATE EXTENSION pg_cron;

-- Run partition maintenance every hour
SELECT cron.schedule(
  'partman-maintenance',
  '0 * * * *',     -- every hour at :00
  $$SELECT partman.run_maintenance_proc()$$
);

run_maintenance_proc() checks all registered partition sets and:

1. Creates new future partitions up to p_premake ahead
2. Detaches or drops partitions older than the retention policy

Setting a Retention Policy

sql
-- Keep 12 months of data; automatically detach older partitions
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = true   -- detach (keep as regular table), not drop
WHERE parent_table = 'public.transactions';

-- To drop instead of detach:
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.transactions';

retention_keep_table = true detaches old partitions — they become standalone tables, accessible for archival queries but no longer part of the partitioned set. This allows bulk exports to cold storage before dropping.

ATTACH PARTITION and DETACH PARTITION

Manual partition management for cases where pg_partman is not used:

sql
-- Create a new partition and attach it (near-instant with declarative partitioning)
CREATE TABLE transactions_2027
  PARTITION OF transactions
  FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');

-- Alternatively: create as a regular table, load data, then attach
CREATE TABLE transactions_archive_2023 (LIKE transactions INCLUDING ALL);
-- ... load data into transactions_archive_2023 ...

-- ATTACH acquires ShareUpdateExclusiveLock — reads and writes continue
-- It performs a full table scan to validate all rows satisfy the partition constraint
ALTER TABLE transactions
  ATTACH PARTITION transactions_archive_2023
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

To make ATTACH PARTITION instant (skip the validation scan):

sql
-- Step 1: add the CHECK constraint before attaching (instant, NOT VALID)
ALTER TABLE transactions_archive_2023
  ADD CONSTRAINT chk_2023_range
  CHECK (timestamp >= '2023-01-01' AND timestamp < '2024-01-01')
  NOT VALID;

-- Step 2: validate the constraint (ShareUpdateExclusiveLock, reads/writes continue)
ALTER TABLE transactions_archive_2023
  VALIDATE CONSTRAINT chk_2023_range;

-- Step 3: attach — now instant because constraint proves all rows are valid
ALTER TABLE transactions
  ATTACH PARTITION transactions_archive_2023
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

sql
-- Detach a partition (near-instant in PG14+ with CONCURRENTLY)
ALTER TABLE transactions
  DETACH PARTITION transactions_2023 CONCURRENTLY;
-- PG14+: uses a weaker lock, allows reads/writes during detach

Partition-Local Indexes vs Global Indexes

Each partition has its own indexes. There are no global indexes spanning all partitions.

sql
-- Create an index on the parent — it creates on all existing partitions
CREATE INDEX idx_transactions_block_height
  ON transactions (block_height);

-- This creates:
-- idx_transactions_2025_block_height on transactions_2025
-- idx_transactions_2026_block_height on transactions_2026
-- ... one per partition

-- New partitions created later do NOT automatically get the index
-- You must create it explicitly on new partitions, OR use pg_partman
-- which handles this automatically

Unique Constraints and Partitioning

Unique constraints on a partitioned table must include the partition key:

sql
-- This fails: hash uniqueness cannot be guaranteed globally
ALTER TABLE transactions ADD CONSTRAINT transactions_hash_unique UNIQUE (hash);
-- ERROR: unique constraint on partitioned tables must include all partitioning columns

-- This works: uniqueness within each (timestamp_month, hash) combination
ALTER TABLE transactions ADD CONSTRAINT transactions_hash_unique
  UNIQUE (timestamp, hash);
-- Each partition enforces uniqueness within its time range

For global uniqueness on a non-partition-key column (e.g., hash must be globally unique), the options are:

1. Maintain a separate unpartitioned lookup table: CREATE TABLE transaction_hashes (hash BYTEA PRIMARY KEY, transaction_id BIGINT)
2. Enforce uniqueness at the application layer
3. Use hash as part of the partition key (impractical for range-based partitioning)

Summary