PostgreSQL Table Partitioning at Scale: Range, Hash, Pruning Mechanics & Zero-Downtime Migration
This article is available in English. Browse translated guides in the language menu.
When a relational database table surpasses tens of millions of rows—scaling into hundreds of gigabytes or terabytes of storage—standard optimization techniques begin to break down. Even with perfectly chosen B-Tree indexes, high-throughput systems start exhibiting erratic latency spikes, degraded write throughput, ballooning buffer cache evictions, and catastrophic autovacuum stalls.
The instinctive attempt to index every access path leads to write amplification and index sizes that exceed system memory (shared_buffers and OS page cache). Once the index working set spills to disk, random read I/O dominates query execution, turning sub-millisecond point lookups into multi-second disk crawls.
The architectural solution to this physical scaling ceiling is declarative table partitioning.
Partitioning splits a logically monolithic table into physically distinct, smaller sub-tables while presenting a single relational abstraction to client applications. When implemented correctly, query execution engines skip unneeded partitions through partition pruning, index sizes remain compact enough to fit comfortably in RAM, and maintenance operations like data purging execute instantaneously with DROP TABLE rather than slow, bloat-inducing DELETE queries.
This engineering guide breaks down PostgreSQL table partitioning from low-level storage engine mechanics to production zero-downtime migration architecture.
1. The Scaling Wall: Why Monolithic Tables Break at 100M+ Rows
Relational engines are designed around the assumption that the working set (frequently accessed data and its associated indexes) resides in memory. When a table scales beyond 100 million rows, three physical architectural bottlenecks emerge.
+-------------------------------------------------------------------------------+
| The Monolithic Table Bottleneck |
+-------------------------------------------------------------------------------+
| |
| 1. Working Set Spills RAM: |
| Index Size (e.g. 48GB) > shared_buffers (16GB) |
| -> B-Tree traversal requires random disk block reads |
| |
| 2. Autovacuum Starvation: |
| A single 200GB heap requires hours to scan for dead tuples |
| -> High transaction ID progression risks TXID wraparound shutdown |
| |
| 3. Purging Dead Data Causes Severe Table Bloat: |
| DELETE FROM orders WHERE created_at < NOW() - INTERVAL '90 days'; |
| -> Generates massive WAL volume, dead item pointers, and fragmented pages|
| |
+-------------------------------------------------------------------------------+A. The Buffer Pool Working Set Eviction
Consider an e-commerce orders table storing 250 million rows:
- The base heap table occupies 180 GB.
- A composite index on
(customer_id, created_at)occupies 28 GB. - A primary key index on
(order_id)occupies 16 GB.
If your database server has 64 GB of system RAM with shared_buffers = 16GB, the indexes alone (44 GB) exceed the database buffer pool. When queries access orders randomly across history, leaf and internal B-Tree pages are constantly evicted from memory to make room for new lookups. The cache hit ratio drops below 90%, and query latency becomes bounded by storage I/O operations per second (IOPS).
By contrast, if the table is partitioned monthly by created_at, only the current month’s partitions and indexes (e.g., 2.5 GB index) are actively read and modified. The entire active working set stays resident in shared_buffers, achieving a 99.9% cache hit ratio.
B. Autovacuum Starvation and Bloat Isolation
In PostgreSQL’s Multi-Version Concurrency Control (MVCC) architecture, an UPDATE or DELETE leaves behind dead tuples on 8KB heap pages. VACUUM must scan table heap pages and every index on the table to reclaim dead line pointers.
On a monolithic 200 GB table, an autovacuum worker may run for 6 to 12 hours. If write traffic is sustained, autovacuum cannot keep pace with tuple churn, driving table bloat. If an autovacuum run fails or is cancelled by a lock conflict, the process must restart from the beginning.
Partitioning isolates MVCC churn: autovacuum processes each 2 GB partition independently in minutes. If a write burst occurs in Partition A, it does not force autovacuum to rescan the untouched data in Partitions B, C, or D.
C. The Cost of Data Retention (DELETE vs DROP)
Purging expired historical data from a monolithic table using DELETE is an operational anti-pattern:
-- DANGEROUS AT SCALE: Generates massive WAL, page fragmentation, and table bloat
DELETE FROM audit_logs
WHERE timestamp < NOW() - INTERVAL '1 year';This statement:
- Locks millions of tuples.
- Writes hundreds of gigabytes to the Write-Ahead Log (WAL), flooding replication streams.
- Leaves millions of empty slots across table pages that can only be reclaimed via aggressive vacuuming or
VACUUM FULL(which requires an exclusive table lock).
With partitioning, historical data retention is an O(1) catalog operation:
-- ZERO-OVERHEAD PURGE: Instantaneous metadata drop, zero WAL churn, no heap bloat
ALTER TABLE audit_logs DETACH PARTITION audit_logs_2025_01;
DROP TABLE audit_logs_2025_01;2. PostgreSQL Declarative Partitioning Architecture
PostgreSQL (version 10+) implements declarative partitioning. Rather than using heritage inheritance tables and custom procedural trigger routing, declarative partitioning delegates routing and optimization directly to the core query parser, planner, and executor.
Client Application Query
SELECT * FROM orders WHERE order_date = '2026-04-15';
|
v
+---------------------------------+
| Root Partitioned Table |
| "orders" |
| (Routing & Schema Catalog) |
+---------------------------------+
|
[ Partition Pruning Engine Checks ]
[ Boundary: 2026-04-01 to 2026-05-01 ]
|
+---------------------------+---------------------------+
| | |
v (PRUNED / SKIPPED) v (SCANNED ONLY) v (PRUNED / SKIPPED)
+---------------------------+ +---------------------------+ +---------------------------+
| orders_2026_03 | | orders_2026_04 | | orders_2026_05 |
| Range: 2026-03-01..04-01 | | Range: 2026-04-01..05-01 | | Range: 2026-05-01..06-01 |
| Heap Storage: 8.2 GB | | Heap Storage: 7.9 GB | | Heap Storage: 0.1 GB |
| Local Indexes: 1.1 GB | | Local Indexes: 1.0 GB | | Local Indexes: 0.02 GB |
+---------------------------+ +---------------------------+ +---------------------------+Core Architecture Components:
- The Partitioned Table (Root): A virtual table that does not hold physical storage pages. It acts as the canonical schema definition and routing entry point.
- The Partitions (Leaves): Standard physical tables (
relkind = 'r') attached to the root table with explicit boundary constraints. - Partition Bounds: Metadata constraints registered in
pg_classandpg_inheritsdefining the exact value space mapped to each partition. - Tuple Routing: When an
INSERThits the root table, PostgreSQL evaluates the partition key against partition boundaries using binary search or hash calculation, directly dispatching the tuple to the target leaf heap page.
3. Partitioning Strategies: Range, List, Hash & Composite
PostgreSQL provides three core declarative partitioning strategies, which can be composed hierarchically.
+-----------+-----------------------------------+------------------------------------------+
| Strategy | Partition Key Type | Ideal Workloads |
+-----------+-----------------------------------+------------------------------------------+
| RANGE | Timestamps, Dates, Monotonic IDs | Time-series, Audit logs, Ledgers, Orders |
| LIST | Statuses, Country Codes, Tenants | Multi-region, Discrete tenant tiers |
| HASH | UUIDs, User IDs, Account Hashes | Write scaling, Eliminating hotspots |
| COMPOSITE | Range (Date) + Hash (Tenant/User) | Massive SaaS, Multi-tenant event logs |
+-----------+-----------------------------------+------------------------------------------+Strategy 1: Range Partitioning
Range partitioning assigns tuples based on numeric or date intervals. Partition bounds are defined using FROM (inclusive) TO (exclusive).
-- 1. Create the root partitioned table
CREATE TABLE customer_orders (
order_id BIGINT NOT NULL,
customer_id BIGINT NOT NULL,
order_date DATE NOT NULL,
amount NUMERIC(12, 2) NOT NULL,
status VARCHAR(32) NOT NULL,
PRIMARY KEY (order_date, order_id)
) PARTITION BY RANGE (order_date);
-- 2. Create discrete monthly partitions
CREATE TABLE customer_orders_2026_01 PARTITION OF customer_orders
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE customer_orders_2026_02 PARTITION OF customer_orders
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE customer_orders_2026_03 PARTITION OF customer_orders
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
-- 3. Create a default partition to catch out-of-bounds anomalies safely
CREATE TABLE customer_orders_default PARTITION OF customer_orders DEFAULT;Critical Range Rule: Boundaries are half-open intervals:
[start, end). The value matchingFROMis included; the value matchingTObelongs to the subsequent partition.
Strategy 2: Hash Partitioning
When data lacks a natural temporal axis but write concurrency creates lock contention on a single heap, Hash Partitioning distributes tuples evenly across $N$ partitions using an internal hash function (hash_bytes).
-- 1. Create root table partitioned by hash of customer_id
CREATE TABLE user_sessions (
session_token UUID NOT NULL,
customer_id BIGINT NOT NULL,
ip_address INET NOT NULL,
last_active_at TIMESTAMPTZ NOT NULL,
payload JSONB,
PRIMARY KEY (customer_id, session_token)
) PARTITION BY HASH (customer_id);
-- 2. Create 4 balanced hash partitions (MODULUS 4, REMAINDER 0..3)
CREATE TABLE user_sessions_p0 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_p1 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_p2 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_p3 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 3);Architectural Tip on Modulus: Always select a modulus that is a power of 2 ($2^k$: 4, 8, 16, 32, 64). If you ever need to split partitions later, a power-of-two modulus allows splitting a single partition into two halves without reshuffling the entire table.
Strategy 3: List Partitioning
List partitioning routes rows based on explicit sets of discrete key values, ideal for legal data residency or tenant regional isolation.
CREATE TABLE global_compliance_events (
event_id BIGINT GENERATED ALWAYS AS IDENTITY,
jurisdiction VARCHAR(8) NOT NULL,
organization_id UUID NOT NULL,
event_payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY (jurisdiction, event_id)
) PARTITION BY LIST (jurisdiction);
CREATE TABLE compliance_eu PARTITION OF global_compliance_events
FOR VALUES IN ('EU', 'DE', 'FR', 'ES', 'IT');
CREATE TABLE compliance_us PARTITION OF global_compliance_events
FOR VALUES IN ('US', 'CA');
CREATE TABLE compliance_apac PARTITION OF global_compliance_events
FOR VALUES IN ('SG', 'JP', 'AU', 'IN');Strategy 4: Composite (Subpartitioning) Architecture
High-scale multi-tenant applications often require two-dimensional scaling: historical data must be purged by date, while active queries filter by tenant_id. This is achieved by subpartitioning.
-- Level 1: Range Partition by Month
CREATE TABLE telemetry_metrics (
recorded_at TIMESTAMPTZ NOT NULL,
tenant_id INTEGER NOT NULL,
metric_name VARCHAR(64) NOT NULL,
metric_value DOUBLE PRECISION NOT NULL,
PRIMARY KEY (recorded_at, tenant_id, metric_name)
) PARTITION BY RANGE (recorded_at);
-- Level 2 Root: Month of April 2026 partitioned by Hash of tenant_id
CREATE TABLE telemetry_2026_04 PARTITION OF telemetry_metrics
FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-05-01 00:00:00+00')
PARTITION BY HASH (tenant_id);
-- Leaf Partitions for April 2026
CREATE TABLE telemetry_2026_04_h0 PARTITION OF telemetry_2026_04
FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE telemetry_2026_04_h1 PARTITION OF telemetry_2026_04
FOR VALUES WITH (MODULUS 2, REMAINDER 1);4. Partition Pruning: Internal Mechanics & Diagnostics
The primary read performance advantage of partitioning is Partition Pruning. The query planner and executor inspect query WHERE predicates and prove mathematically that specific partitions cannot contain matching rows, excluding them from the execution plan.
PostgreSQL applies pruning at two distinct phases:
- Compile-Time (Planner) Pruning: Occurs during query planning when literal constants are supplied in the query string.
- Run-Time (Executor) Pruning: Occurs during query execution when predicates depend on parameters, prepared statement arguments, or inner loop variables of a join.
+-----------------------------------------------------------------------------------------+
| Compile-Time vs Run-Time Pruning |
+-----------------------------------------------------------------------------------------+
| |
| 1. Compile-Time (Planner): |
| Query: SELECT * FROM customer_orders WHERE order_date = '2026-02-15'; |
| Planner checks partition bounds at parse/plan time. |
| Plan: Scans ONLY customer_orders_2026_02. Other partitions never enter the plan. |
| |
| 2. Run-Time (Executor): |
| Query: PREPARE order_lookup(date) AS SELECT * FROM customer_orders WHERE order_date=$1|
| At planning time, $1 is unknown. Planner creates generic plan covering all partitions|
| During EXECUTE order_lookup('2026-02-15'), executor evaluates bound at startup |
| EXPLAIN displays: "Subplans Removed: 11" |
| |
+-----------------------------------------------------------------------------------------+Inspecting Pruning with EXPLAIN (ANALYZE, BUFFERS)
Let’s test query execution against an 8-partition time-series table.
Scenario A: Compile-Time Pruning with Literals
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT order_id, customer_id, amount
FROM customer_orders
WHERE order_date BETWEEN '2026-02-10' AND '2026-02-20';Plan Output:
Append (actual time=0.042..1.120 rows=4150 loops=1)
Buffers: shared hit=48
-> Bitmap Heap Scan on customer_orders_2026_02 (actual time=0.041..0.850 rows=4150 loops=1)
Recheck Cond: ((order_date >= '2026-02-10'::date) AND (order_date <= '2026-02-20'::date))
Buffers: shared hit=48
-> Bitmap Index Scan on customer_orders_2026_02_pkey (actual time=0.028..0.028 rows=4150 loops=1)
Index Cond: ((order_date >= '2026-02-10'::date) AND (order_date <= '2026-02-20'::date))
Buffers: shared hit=3
Planning Time: 0.185 ms
Execution Time: 1.340 msNotice that customer_orders_2026_01 and customer_orders_2026_03 were completely pruned before execution.
Scenario B: Run-Time Pruning with Prepared Statements
PREPARE query_orders(date) AS
SELECT * FROM customer_orders WHERE order_date = $1;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
EXECUTE query_orders('2026-03-14');Plan Output:
Append (actual time=0.038..0.040 rows=1 loops=1)
Subplans Removed: 3
Buffers: shared hit=4
-> Index Scan using customer_orders_2026_03_pkey on customer_orders_2026_03 (actual time=0.036..0.037 rows=1 loops=1)
Index Cond: (order_date = '2026-03-14'::date)
Buffers: shared hit=4
Planning Time: 0.210 ms
Execution Time: 0.065 msThe line Subplans Removed: 3 proves that the executor dynamically skipped 3 partitions the instant the parameter was bound at runtime.
The Fatal Anti-Pattern: Function Wrapped Predicates
A common mistake that completely disables partition pruning is wrapping the partition key in a non-immutable function or expression:
-- PRUNING BROKEN: Scans EVERY partition in the table!
SELECT * FROM customer_orders
WHERE date_trunc('month', order_date) = '2026-02-01'::date;Because date_trunc transforms the column value, the optimizer cannot prove boundary constraints against the raw order_date partition boundaries. It falls back to scanning every partition.
Corrected Formulation:
-- PRUNING PRESERVED: Optimizer directly evaluates boundary bounds
SELECT * FROM customer_orders
WHERE order_date >= '2026-02-01' AND order_date < '2026-03-01';5. Local Indexes, Primary Keys & Constraint Rules
Partitioning alters fundamental assumptions regarding relational constraints and indexing.
The Golden Rule of Primary Keys and Unique Constraints
PostgreSQL Architecture Constraint: Every unique constraint or primary key on a partitioned table MUST include all columns of the partition key.
-- ILLEGAL: Fails with ERROR: unique constraint on partitioned table must include all partitioning columns
CREATE TABLE accounts (
account_id UUID NOT NULL,
country_code VARCHAR(2) NOT NULL,
balance NUMERIC(14, 2),
PRIMARY KEY (account_id)
) PARTITION BY LIST (country_code);Why Does This Restriction Exist? PostgreSQL enforces uniqueness via B-Tree indexes. In PostgreSQL, all indexes on partitioned tables are local—meaning each physical partition owns its own independent B-Tree index. There is no global cross-partition index structure in PostgreSQL.
If account_id were unique without country_code, inserting account_id = 'A1' into accounts_us and account_id = 'A1' into accounts_eu would require accounts_us to cross-examine the disk pages of accounts_eu during an insert. This would destroy write scalability.
The Solution:
-- CORRECT: Composite Primary Key containing partition key
ALTER TABLE accounts ADD PRIMARY KEY (country_code, account_id);Automatic Index Inheritance
When you define an index on the root partitioned table, PostgreSQL automatically creates a corresponding local index on every existing partition, as well as any partition created in the future:
-- Index created on root table:
CREATE INDEX idx_orders_customer_lookup ON customer_orders (customer_id, order_date);
-- PostgreSQL automatically creates:
-- customer_orders_2026_01_customer_id_order_date_idx on customer_orders_2026_01
-- customer_orders_2026_02_customer_id_order_date_idx on customer_orders_2026_026. Zero-Downtime Migration: From 200M Monolith to Partitioned Table
Migrating a high-traffic production table containing hundreds of millions of rows into a partitioned architecture cannot be done with a simple ALTER TABLE. Taking an exclusive table lock (ACCESS EXCLUSIVE) for hours while copying data would trigger extended application outages.
The following zero-downtime architectural pattern migrates active production tables using a shadow table, trigger sync, chunked backfill, and atomic rename.
+-----------------------------------------------------------------------------------------+
| Zero-Downtime Partition Migration Pipeline |
+-----------------------------------------------------------------------------------------+
| |
| Phase 1: Deploy Shadow Partitioned Table |
| CREATE TABLE orders_partitioned (...) PARTITION BY RANGE (created_at); |
| |
| Phase 2: Dual-Write In-Flight Mutations (Trigger or Dual-ORM) |
| CREATE TRIGGER sync_orders_to_shadow |
| BEFORE INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW ... |
| |
| Phase 3: Background Chunked Historical Backfill |
| Copies historical slices in batches of 25,000 rows with WHERE id BETWEEN ... |
| ON CONFLICT (created_at, id) DO NOTHING |
| |
| Phase 4: Atomic Table Swap (Microsecond Exclusive Lock) |
| BEGIN; |
| LOCK TABLE orders IN ACCESS EXCLUSIVE MODE; |
| ALTER TABLE orders RENAME TO orders_old; |
| ALTER TABLE orders_partitioned RENAME TO orders; |
| COMMIT; |
| |
+-----------------------------------------------------------------------------------------+Complete Production Migration Script
Step 1: Create the Target Partitioned Schema
-- 1. Create target partitioned table with identical types
CREATE TABLE orders_v2 (
id BIGINT NOT NULL,
customer_id BIGINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
total_amount NUMERIC(12, 2) NOT NULL,
status VARCHAR(32) NOT NULL,
PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);
-- 2. Pre-create partitions spanning the historical and future boundary
CREATE TABLE orders_v2_2025 PARTITION OF orders_v2
FOR VALUES FROM ('2025-01-01 00:00:00+00') TO ('2026-01-01 00:00:00+00');
CREATE TABLE orders_v2_2026_q1 PARTITION OF orders_v2
FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');
CREATE TABLE orders_v2_2026_q2 PARTITION OF orders_v2
FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-07-01 00:00:00+00');
CREATE TABLE orders_v2_default PARTITION OF orders_v2 DEFAULT;Step 2: Establish the Dual-Write Synchronization Trigger
To ensure that any INSERT, UPDATE, or DELETE executed by active applications during backfill is captured in the new structure:
CREATE OR REPLACE FUNCTION trg_sync_orders_v2()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO orders_v2 (id, customer_id, created_at, total_amount, status)
VALUES (NEW.id, NEW.customer_id, NEW.created_at, NEW.total_amount, NEW.status)
ON CONFLICT (created_at, id) DO UPDATE
SET customer_id = EXCLUDED.customer_id,
total_amount = EXCLUDED.total_amount,
status = EXCLUDED.status;
ELSIF (TG_OP = 'UPDATE') THEN
-- If partition key changed, delete old tuple and insert new
IF (OLD.created_at != NEW.created_at) THEN
DELETE FROM orders_v2 WHERE created_at = OLD.created_at AND id = OLD.id;
INSERT INTO orders_v2 (id, customer_id, created_at, total_amount, status)
VALUES (NEW.id, NEW.customer_id, NEW.created_at, NEW.total_amount, NEW.status);
ELSE
UPDATE orders_v2
SET customer_id = NEW.customer_id,
total_amount = NEW.total_amount,
status = NEW.status
WHERE created_at = NEW.created_at AND id = NEW.id;
END IF;
ELSIF (TG_OP = 'DELETE') THEN
DELETE FROM orders_v2 WHERE created_at = OLD.created_at AND id = OLD.id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER sync_orders_to_v2_trg
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION trg_sync_orders_v2();Step 3: Chunked Background Backfill Loop (Python / Shell / SQL)
Execute the backfill in discrete transactions iterating over primary key ranges to prevent lock escalation and excessive WAL buildup.
-- Run in an automated looping worker script
DO $$
DECLARE
batch_size INT := 25000;
min_id BIGINT;
max_id BIGINT;
current_cursor BIGINT;
BEGIN
SELECT MIN(id), MAX(id) INTO min_id, max_id FROM orders;
current_cursor := min_id;
WHILE current_cursor <= max_id LOOP
INSERT INTO orders_v2 (id, customer_id, created_at, total_amount, status)
SELECT id, customer_id, created_at, total_amount, status
FROM orders
WHERE id >= current_cursor AND id < current_cursor + batch_size
ON CONFLICT (created_at, id) DO NOTHING;
current_cursor := current_cursor + batch_size;
-- Prevent overwhelming replication slots
COMMIT;
PERFORM pg_sleep(0.05);
END LOOP;
END $$;Step 4: Atomic Table Swap
Once the row counts between orders and orders_v2 match, swap the tables within a strict lock timeout boundary:
BEGIN;
-- Set a 2-second lock timeout to avoid queuing queries behind a blocked lock
SET LOCAL lock_timeout = '2000ms';
-- Drop the sync trigger
DROP TRIGGER sync_orders_to_v2_trg ON orders;
-- Swap table names atomically
ALTER TABLE orders RENAME TO orders_legacy_backup;
ALTER TABLE orders_v2 RENAME TO orders;
-- Reset sequence ownership if applicable
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;
COMMIT;If a long-running reporting query blocks the lock, the transaction safely aborts after 2,000ms without disrupting traffic, allowing you to retry seconds later.
7. Lifecycle Automation: pg_partman and pg_cron
Manual partition management in production is error-prone. If traffic rolls over into a new month without a matching partition, and no default partition exists, inserts will crash with a constraint violation.
The industry standard for declarative partition automation is the pg_partman extension paired with pg_cron.
+-------------------------------------------------------------------------------+
| Automated Maintenance Architecture |
+-------------------------------------------------------------------------------+
| |
| pg_cron (Scheduler) ---> Triggers pg_partman.run_maintenance_proc() |
| | |
| v |
| 1. Pre-creates N future partitions (e.g. Next 3 months in advance) |
| 2. Detaches expired partitions: |
| ALTER TABLE telemetry DETACH PARTITION telemetry_p2024_01 CONCURRENTLY; |
| 3. Archives detached partitions to AWS S3 / Glacier or drops table |
| |
+-------------------------------------------------------------------------------+Production Setup with pg_partman
-- 1. Install extension
CREATE EXTENSION IF NOT EXISTS pg_partman;
-- 2. Register partitioned table with pg_partman
SELECT create_parent(
p_parent_table => 'public.customer_orders',
p_control => 'order_date',
p_interval => '1 month',
p_type => 'native',
p_premake => 3 -- Always ensure 3 months of future partitions exist
);
-- 3. Configure automatic retention (detach partitions older than 12 months)
UPDATE part_config
SET retention = '12 months',
retention_keep_table = false -- Set to true if archiving to S3 before dropping
WHERE parent_table = 'public.customer_orders';Scheduling with pg_cron
Schedule the maintenance routine to run nightly at 02:00 UTC:
SELECT cron.schedule(
'partman-nightly-maintenance',
'0 2 * * *',
'CALL run_maintenance_proc()'
);Zero-Downtime Partition Detachment
When retiring historical partitions in PostgreSQL 14+, always use DETACH PARTITION ... CONCURRENTLY:
-- Standard DETACH takes exclusive lock across the entire parent table
-- CONCURRENTLY takes only a lightweight ShareUpdateExclusiveLock
ALTER TABLE customer_orders
DETACH PARTITION customer_orders_2024_01 CONCURRENTLY;
-- Now drop or export safely without holding locks on the active parent table
DROP TABLE customer_orders_2024_01;8. Production Antipatterns & Diagnostic Toolkit
Antipattern 1: Partition Explosion (The 10,000 Partition Trap)
A common design flaw is partitioning too granularly (e.g., partitioning by day across 10 years = 3,650 partitions).
- The Bottleneck: Each partition consumes file descriptors, memory locks, and catalog entries. During query planning, PostgreSQL must acquire locks on every partition before pruning occurs. If you have 5,000 partitions, a simple query spends 40ms planning and only 0.2ms executing.
- Rule of Thumb: Keep active partition counts per table between 20 and 300 partitions.
Antipattern 2: Cross-Partition Updates
If a transaction updates the partition key such that the row must move across partition boundaries:
UPDATE customer_orders
SET order_date = '2026-03-01'
WHERE order_id = 9102 AND order_date = '2026-02-28';PostgreSQL executes this as an atomic DELETE from customer_orders_2026_02 followed by an INSERT into customer_orders_2026_03. This increases WAL generation, breaks HOT optimizations, and can introduce lock deadlock risks under high concurrency. Keep partition keys immutable whenever possible.
Diagnostic Script: Inspecting Partition Sizes & Bloat
Run this diagnostic query to inspect real physical storage, index overhead, and row distributions across all attached partitions:
SELECT
parent.relname AS parent_table,
child.relname AS partition_name,
pg_size_pretty(pg_total_relation_size(child.oid)) AS total_size,
pg_size_pretty(pg_relation_size(child.oid)) AS data_size,
pg_size_pretty(pg_indexes_size(child.oid)) AS index_size,
child.reltuples::bigint AS approximate_rows
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace n ON child.relnamespace = n.oid
WHERE parent.relname = 'customer_orders'
ORDER BY child.relname;9. Architectural Decision Matrix
+------------------------------+---------------------------+-----------------------------------+
| System Dimension | Monolithic Table | Declaratively Partitioned Table |
+------------------------------+---------------------------+-----------------------------------+
| Max Scalable Rows | 20M - 50M rows | 1B+ rows |
| Index Working Set in RAM | Degrades as table grows | Constant (current partitions fit) |
| Data Retention Prune Cost | O(N) slow DELETE + bloat | O(1) instant DROP TABLE |
| Autovacuum Lock Overhead | High (entire heap locked) | Isolated (per-partition workers) |
| Primary Key Constraint Model | Global single column PK | Must include partition key |
| Cross-Partition Update Cost | Standard single-row MVCC | Cross-table DELETE + INSERT |
| Maintenance Tooling | Manual VACUUM/REINDEX | Automated via pg_partman |
+------------------------------+---------------------------+-----------------------------------+Table partitioning is not a universal performance cure for poorly indexed queries. However, when applied to high-volume time-series, financial ledgers, audit trails, and multi-tenant platforms, declarative partitioning establishes the physical foundation required to scale PostgreSQL seamlessly to terabyte scale.
Ready to Level Up Your SQL?
Don't just read about databases. Write queries, build schemas, and practice real-world interview questions interactively.
Start Learning for Free