Scrolls
4 min read
On this page

Partition Management

Creating partitions is the easy part. Managing them over time — creating new ones, archiving old ones, handling indexes, and keeping operations smooth — is the ongoing work that determines whether partitioning helps or becomes a burden.

Creating & Attaching Partitions

Creating Partitions Ahead of Time

Always create partitions before they are needed. If an INSERT arrives and no matching partition exists (and there is no default partition), it fails.

-- Create next month's partition
CREATE TABLE orders_2025_05 PARTITION OF orders
    FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');

Attaching an Existing Table as a Partition

If you have data in a standalone table that you want to bring into a partitioned structure:

-- The table must have a compatible schema and a check constraint
-- that matches the partition bounds
ALTER TABLE legacy_orders_2023
ADD CONSTRAINT chk_partition_range
CHECK (created_at >= '2023-01-01' AND created_at < '2024-01-01');

-- Attach it — the check constraint lets PostgreSQL skip a full table scan
ALTER TABLE orders ATTACH PARTITION legacy_orders_2023
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Without the check constraint, ATTACH PARTITION scans the entire table to verify all rows fall within the partition bounds. For large tables, add the constraint first.

Attaching with Minimal Locking

ATTACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent table briefly. On busy systems, minimize the duration:

  1. Add the check constraint (does not lock the parent).
  2. Run ATTACH PARTITION (fast, since the constraint proves validity).
-- Step 1: add constraint (only locks the child table)
ALTER TABLE new_partition
ADD CONSTRAINT chk_range
CHECK (created_at >= '2025-06-01' AND created_at < '2025-07-01')
NOT VALID;

-- Validate separately (scans the child, no parent lock)
ALTER TABLE new_partition VALIDATE CONSTRAINT chk_range;

-- Step 2: attach (fast, no scan needed)
ALTER TABLE orders ATTACH PARTITION new_partition
    FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

Automatic Partition Creation with pg_partman

pg_partman is an extension that automates partition creation and retention.

Installation & Setup

CREATE EXTENSION pg_partman;

-- Create the partitioned parent table first
CREATE TABLE events (
    id bigserial,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

-- Configure pg_partman to manage it
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := 'monthly',
    p_premake := 3  -- create 3 future partitions
);

Maintenance

pg_partman requires a maintenance function to be called regularly:

-- Run this via cron or pg_cron
SELECT partman.run_maintenance();

This creates upcoming partitions and optionally drops or detaches old ones.

Retention Policy

-- Configure retention: keep 12 months, detach older partitions
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = true,  -- detach but don't drop
    infinite_time_partitions = true
WHERE parent_table = 'public.events';

With retention_keep_table = true, old partitions are detached but not dropped. You can then archive or drop them manually.

Using pg_cron for Automation

-- Schedule pg_partman maintenance every hour
SELECT cron.schedule('partman-maintenance', '0 * * * *',
    $$SELECT partman.run_maintenance()$$);

Detaching Partitions for Archival

Detaching removes a partition from the partitioned table without dropping the data. The partition becomes a standalone table.

-- Detach an old partition
ALTER TABLE orders DETACH PARTITION orders_2023_q1;

-- Now orders_2023_q1 is a standalone table
-- You can dump it, move it to cheap storage, or drop it
pg_dump -t orders_2023_q1 mydb > orders_2023_q1.sql

-- Drop after archival
DROP TABLE orders_2023_q1;

Concurrent Detach (PostgreSQL 14+)

-- CONCURRENTLY avoids blocking queries on the parent table
ALTER TABLE orders DETACH PARTITION orders_2023_q1 CONCURRENTLY;

Without CONCURRENTLY, DETACH takes an ACCESS EXCLUSIVE lock on the parent table. The concurrent variant uses a weaker lock but requires two transactions internally and cannot run inside a transaction block.

Dropping Old Data Efficiently

This is one of the biggest operational advantages of partitioning. Dropping a partition is nearly instant compared to DELETE.

-- Instant: drops the partition and its data
DROP TABLE orders_2023_q1;

-- Compare to the non-partitioned approach:
-- DELETE FROM orders WHERE created_at < '2023-04-01';
-- This scans the table, generates WAL, creates dead tuples, needs VACUUM.
-- On a table with billions of rows, this takes hours.

Partition-Wise Joins & Aggregation

When joining two tables partitioned on the same key, PostgreSQL can join matching partitions directly instead of joining the full tables.

-- Both tables partitioned by created_at with matching bounds
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
CREATE TABLE shipments (...) PARTITION BY RANGE (created_at);

-- Partition-wise join: each partition of orders joins
-- only with the corresponding partition of shipments
SELECT o.id, s.tracking_number
FROM orders o
JOIN shipments s ON s.order_id = o.id
  AND s.created_at = o.created_at
WHERE o.created_at >= '2024-01-01' AND o.created_at < '2024-04-01';

-- Enable partition-wise join (off by default due to planning overhead)
SET enable_partitionwise_join = on;

-- Enable partition-wise aggregation
SET enable_partitionwise_aggregate = on;

These settings increase planning time because the planner evaluates more join combinations. Enable them when the execution savings outweigh the planning cost.

Index Management

Each partition needs its own indexes. When you create an index on a partitioned table, PostgreSQL creates a matching index on each existing partition. New partitions automatically get the index.

-- Creates an index on every partition
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Each partition gets its own physical index:
-- idx_orders_customer_orders_2024_q1
-- idx_orders_customer_orders_2024_q2
-- etc.

Indexes on Individual Partitions

You can create indexes on specific partitions that do not exist on others:

-- Only the current quarter needs this index for OLTP queries
CREATE INDEX idx_orders_q2_status ON orders_2024_q2 (status);

CONCURRENTLY on Partitioned Tables

CREATE INDEX CONCURRENTLY does not work directly on partitioned tables. You must create the index on each partition individually:

-- Create on each partition concurrently
CREATE INDEX CONCURRENTLY idx_orders_q1_customer ON orders_2024_q1 (customer_id);
CREATE INDEX CONCURRENTLY idx_orders_q2_customer ON orders_2024_q2 (customer_id);
CREATE INDEX CONCURRENTLY idx_orders_q3_customer ON orders_2024_q3 (customer_id);
CREATE INDEX CONCURRENTLY idx_orders_q4_customer ON orders_2024_q4 (customer_id);

-- Then create the parent index using ONLY (does not build anything, just links)
CREATE INDEX idx_orders_customer ON ONLY orders (customer_id);

-- Attach each partition's index to the parent
ALTER INDEX idx_orders_customer ATTACH PARTITION idx_orders_q1_customer;
ALTER INDEX idx_orders_customer ATTACH PARTITION idx_orders_q2_customer;
ALTER INDEX idx_orders_customer ATTACH PARTITION idx_orders_q3_customer;
ALTER INDEX idx_orders_customer ATTACH PARTITION idx_orders_q4_customer;

This is verbose but avoids locking any partition for the duration of the index build.

Operational Overhead

Partitioning is not free. Be aware of these costs:

Planning Overhead

More partitions means more planning time. The planner must evaluate each partition. With hundreds of partitions, planning can take milliseconds that matter for OLTP queries.

-- Check planning time in EXPLAIN
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE created_at = '2024-06-15';
-- Planning Time: 2.345 ms  (increases with partition count)
-- Execution Time: 0.050 ms

Catalog Bloat

Each partition is a separate relation in pg_class. Thousands of partitions mean thousands of catalog entries, which slows down catalog-heavy operations.

Monitoring

-- Count partitions per parent
SELECT parent.relname AS parent_table,
       count(*) AS partition_count
FROM pg_inherits
JOIN pg_class parent ON parent.oid = pg_inherits.inhparent
JOIN pg_class child ON child.oid = pg_inherits.inhrelid
GROUP BY parent.relname
ORDER BY partition_count DESC;

-- Partition sizes
SELECT child.relname AS partition_name,
       pg_size_pretty(pg_relation_size(child.oid)) AS size
FROM pg_inherits
JOIN pg_class parent ON parent.oid = pg_inherits.inhparent
JOIN pg_class child ON child.oid = pg_inherits.inhrelid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

Common Pitfalls

  • Not automating partition creation. Relying on manual partition creation leads to insert failures when a new month or quarter starts. Use pg_partman or a cron job.
  • Detaching partitions without CONCURRENTLY on busy tables. The ACCESS EXCLUSIVE lock blocks all queries. Use CONCURRENTLY (PostgreSQL 14+) or schedule during maintenance windows.
  • Forgetting to create indexes on new partitions when managing them manually. If you create partitions outside of pg_partman and outside of a parent CREATE INDEX, the new partition will have no indexes.
  • Creating thousands of partitions. Daily partitions for 10 years = 3,650 partitions. This slows down planning. Use monthly or quarterly partitions and archive old ones.
  • Not monitoring the default partition. If rows land in the default, you have a gap in your partition scheme. Check regularly.
  • Attempting CREATE INDEX CONCURRENTLY on the partitioned parent. This is not supported. Create indexes on each partition individually and attach them.

Key Takeaways

  • Automate partition creation with pg_partman or pg_cron. Never rely on manual creation for time-based partitions.
  • Detaching partitions is the efficient way to archive or drop old data — instant compared to DELETE on billions of rows.
  • Partition-wise joins and aggregation can improve performance but increase planning time. Enable them selectively.
  • Every partition needs its own indexes. Use the CONCURRENTLY + ATTACH pattern for production index creation.
  • Monitor partition count, default partition contents, and planning time overhead.
  • The operational complexity of partitioning is the real cost. Keep the number of active partitions manageable.