PostgreSQL Declarative Partitioning at 50M Rows/Day: Automating Partition Pruning, Maintenance & Retention with pg_partman

The Monolith Breakdown: When B-Trees and Autovacuum Surrender

In high-throughput logging, telemetry, and transactional systems processing 50 million rows per day, monolithic PostgreSQL tables hit an inevitable wall. As table sizes cross 200 million rows (100GB+ on disk), three severe production failure modes manifest:

1. RAM Cache Eviction: B-Tree indexes on timestamps and UUIDs exceed PostgreSQL's shared_buffers and the operating system's file system cache. Every write requires random disk I/O to update index leaves, causing write IOPS to spike.
2. Autovacuum Freezes: Autovacuum workers take hours or days to scan the multi-hundred-gigabyte table to clean dead tuples, consuming disk bandwidth and risking transaction ID wraparound.
3. Expensive Data Purges: Executing DELETE FROM audit_logs WHERE created_at < NOW() - INTERVAL '30 days' generates massive write-ahead log (WAL) churn, bloats tables, and frequently locks active transactions.

Native Declarative Partitioning: Anatomy of Partition Pruning

PostgreSQL's native declarative partitioning divides a logical master table into physical child tables organized by key ranges. The query planner leverages Partition Pruning to eliminate non-relevant partitions from execution plans entirely:

• Static Pruning: Resolved at query parse/plan time when query predicates contain constant values (e.g., WHERE created_at >= '2026-09-01').
• Run-Time Pruning: Resolved dynamically during execution when predicates contain parameterized values, subqueries, or joins.

Configuring Automated Partitions with pg_partman

Managing partition creation manually via cron scripts risks race conditions where writes fail if a new partition is missing. The gold standard for production management is the pg_partman extension, which orchestrates partition pre-creation and data retention natively within PostgreSQL.

-- 1. Create parent partitioned table
CREATE TABLE telemetry_events (
    event_id UUID DEFAULT gen_random_uuid(),
    device_id INT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (created_at, event_id)
) PARTITION BY RANGE (created_at);

-- 2. Register table with pg_partman for daily partitioning
-- Creates 4 future daily partitions in advance and retains 30 days of data
SELECT partman.create_parent(
    p_parent_table => 'public.telemetry_events',
    p_control => 'created_at',
    p_type => 'native',
    p_interval => 'daily',
    p_premake => 4
);

-- 3. Configure automated retention policy (Drop partitions older than 30 days)
UPDATE partman.part_config
SET retention = '30 days',
    retention_keep_table = false
WHERE parent_table = 'public.telemetry_events';

Automating Maintenance via Native Background Workers

Rather than relying on external system cron jobs, pg_partman can be run via PostgreSQL's native background worker process or scheduled via pg_cron every hour:

-- Schedule partition maintenance every hour at minute 05
SELECT cron.schedule('partman-maintenance', '5 * * * *', $$
    SELECT partman.run_maintenance_proc();
$$);

When the retention period expires, pg_partman executes a DROP TABLE on the oldest child partition. Unlike a DELETE query, dropping an entire child table executes in less than 2 milliseconds, reclaims 100% of disk space instantly, and produces zero WAL bloat or dead tuples.

To inspect index designs for append-only datasets, see our related analysis on Change Data Capture (CDC) at Scale with Debezium.