Module A-14·19 min read

The operational difference between designing a partition and maintaining 500 of them at scale.

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

Q: A Senior DBA is investigating a performance regression in a PostgreSQL 13 application. A query against a PARTITION BY RANGE (timestamp) table, executed via a prepared statement with a timestamp parameter (e.g., PREPARE q AS SELECT * FROM transactions WHERE timestamp = $1;), is unexpectedly performing full table scans across all partitions. Which of the following is the MOST likely architectural reason for this behavior?

PostgreSQL 13 does not support runtime partition pruning for parameterized queries, causing the planner to prune only at plan time when the parameter value is unknown. — The lesson explicitly states that in PostgreSQL versions prior to 14, partition pruning only happens at plan time, not execution time. For parameterized queries, if the parameter value is not known at plan time, the planner cannot prune partitions, leading to full table scans. Option A describes a type mismatch, which can break pruning, but parameterized queries typically handle types correctly. Option B is incorrect because constraint_exclusion is irrelevant for declarative partitioning. Option D describes function wrapping, which breaks pruning, but is not specific to parameterized queries or PG13's runtime pruning limitation.

Q: A DevOps engineer configures pg_partman for a mission-critical transactions table, partitioned monthly by timestamp. The requirement is to keep 12 months of active data online, but also retain older data for compliance audits in a separate, queryable archive without it being part of the active partitioned set. Which pg_partman configuration, combined with its maintenance process, achieves this goal most effectively?

Set p_interval = 'monthly', p_premake = 4, and retention = '12 months' with retention_keep_table = true. — Option B correctly uses `retention = '12 months'` to define the active data window and `retention_keep_table = true` to ensure that older partitions are detached (becoming standalone tables) rather than dropped. This allows them to be queried for archival purposes without being part of the active partitioned set. `p_premake = 4` provides a reasonable buffer for future partition creation. Option A and D are incorrect because `retention_keep_table = false` would drop the old partitions, making them unrecoverable. Option C is less effective because `p_premake = 1` is a very small buffer, and manually detaching defeats the automation provided by `pg_partman`'s retention policy.

Q: A team needs to integrate a large, pre-existing transactions_legacy_2023 table (containing 2023 data) into a live, declaratively partitioned transactions table with minimal disruption to ongoing read and write operations. The transactions table is partitioned by timestamp. Which sequence of operations will achieve this with the least impact on the live system?

First, add a CHECK constraint to transactions_legacy_2023 NOT VALID, then VALIDATE CONSTRAINT, and finally ALTER TABLE transactions ATTACH PARTITION transactions_legacy_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'); — Option C describes the recommended method for near-instant ATTACH PARTITION. By first adding a `CHECK` constraint `NOT VALID` to the child table, then `VALIDATE`ing that constraint (which can take time but only locks the child table with `ShareUpdateExclusiveLock`, allowing reads/writes on the parent), the final `ATTACH PARTITION` command becomes instant because the system already knows the data satisfies the partition bounds. Option A would perform a full table scan on the `transactions_legacy_2023` table during the `ATTACH` operation, which can be time-consuming and acquire a `ShareUpdateExclusiveLock` on the parent table for the duration of the scan. Option B is incorrect as the table already exists and the `ATTACH` step still incurs the validation cost. Option D would cause significant downtime by detaching all existing partitions and is an entirely unnecessary and disruptive approach.

What this module covers: Module 7 introduced partitioning strategies. This module goes deeper into the mechanics that make partitioning actually work at scale: how the query planner prunes partitions (and what silently breaks it), declarative partitioning vs inheritance-based partitioning and when each applies, constraint exclusion, and the operational reality of maintaining hundreds of partitions over months and years using 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:

text

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:

Creates new future partitions up to p_premake ahead
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:

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

Summary

Concept	Key Takeaway
Declarative vs inheritance	Use declarative (PG10+) for all new work. Inheritance is legacy.
Partition pruning	Works automatically with declarative partitioning. Breaks with function wrapping, type mismatch, or OR conditions on partition key.
Verifying pruning	`EXPLAIN (VERBOSE)` and look for "Partitions excluded". Always verify after partitioning.
`pg_partman`	Automate partition creation and retention. Run via `pg_cron` hourly.
`ATTACH PARTITION`	Use NOT VALID + VALIDATE + ATTACH sequence for instant attachment of pre-loaded partitions.
`DETACH CONCURRENTLY`	PG14+ — detach without blocking reads/writes.
Global unique constraints	Must include partition key. For true global uniqueness, use a separate lookup table.
Partition-local indexes	New partitions do not inherit parent's indexes automatically. pg_partman handles this.

Knowledge Check

A Senior DBA is investigating a performance regression in a PostgreSQL 13 application. A query against a PARTITION BY RANGE (timestamp) table, executed via a prepared statement with a timestamp parameter (e.g., PREPARE q AS SELECT * FROM transactions WHERE timestamp = $1;), is unexpectedly performing full table scans across all partitions. Which of the following is the MOST likely architectural reason for this behavior?

A DevOps engineer configures pg_partman for a mission-critical transactions table, partitioned monthly by timestamp. The requirement is to keep 12 months of active data online, but also retain older data for compliance audits in a separate, queryable archive without it being part of the active partitioned set. Which pg_partman configuration, combined with its maintenance process, achieves this goal most effectively?

A team needs to integrate a large, pre-existing transactions_legacy_2023 table (containing 2023 data) into a live, declaratively partitioned transactions table with minimal disruption to ongoing read and write operations. The transactions table is partitioned by timestamp. Which sequence of operations will achieve this with the least impact on the live system?

Test your knowledge with more question sets

PreviousModule A-13: Locking Internals: Row Locks, Table Locks, and Advisory Locks Next Module A-15: The Extensions Ecosystem: pg_cron, TimescaleDB, Citus, and pgvector

Discussion

Join the discussion

Loading comments...