Mastering Time-Series Retention in PostgreSQL with pg_partman

The Inevitable Crisis of Unpartitioned Time-Series Tables

Any system ingesting high-volume, time-series data—be it metrics, logs, IoT events, or financial transactions—will eventually face a critical performance bottleneck with a monolithic, unpartitioned table. The problem isn't just the table's size; it's the operational cost of managing data retention. A seemingly simple DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'; becomes a production incident waiting to happen.

Senior engineers who have managed such systems recognize the symptoms:

Let's quantify this. Consider a DELETE on a large, unpartitioned table:

-- This is a simplified example. In production, this table would have more columns and indexes.
CREATE TABLE events_unpartitioned ( 
    id BIGSERIAL PRIMARY KEY,
    device_id UUID NOT NULL,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Imagine this table has 500 million rows...

-- The query that brings your database to its knees:
EXPLAIN ANALYZE DELETE FROM events_unpartitioned WHERE created_at < '2023-01-01T00:00:00Z';

/*
-- Hypothetical but realistic EXPLAIN ANALYZE output

                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Delete on events_unpartitioned  (cost=0.43..8654321.98 rows=166543210 width=6)
   ->  Index Scan using events_unpartitioned_created_at_idx on events_unpartitioned (cost=0.43..8654321.98 rows=166543210 width=6)
         Index Cond: (created_at < '2023-01-01 00:00:00+00'::timestamp with time zone)
 Planning Time: 0.15 ms
 Execution Time: 7200000.00 ms  -- (2 hours!)
*/

The solution is not to optimize the DELETE query, but to eliminate it entirely. This is achieved by architecting the data around PostgreSQL's native partitioning, where data retention becomes a metadata-only DROP TABLE operation on an old partition—an action that is instantaneous and non-blocking.

However, native partitioning alone requires manual, ongoing maintenance to create new partitions and drop old ones. This is where pg_partman becomes essential. It provides the automation layer to manage this lifecycle declaratively, turning a manual chore into a reliable, hands-off process.

Architecting the Partitioned Solution with `pg_partman`

We will design a partitioned table structure for an events table, partitioned by day. pg_partman will manage the creation of future partitions and the deletion of old ones.

1. Schema Design

The parent table looks almost identical to the unpartitioned table, but with the critical PARTITION BY RANGE clause. All writes (INSERT) will target this parent table, and PostgreSQL will route them to the correct child partition.

-- First, ensure the extension is available
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- The parent table definition
CREATE TABLE events (
    id BIGSERIAL NOT NULL,
    device_id UUID NOT NULL,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- IMPORTANT: Create an index on the partition key. This is crucial for performance.
CREATE INDEX idx_events_created_at ON events (created_at);

-- Other indexes can be created here as well. pg_partman will propagate them to new partitions.
CREATE INDEX idx_events_device_id ON events (device_id);

Key Architectural Point: The primary key or any unique constraint on a partitioned table must include the partition key (created_at in this case). Since id is a BIGSERIAL, it's not unique across partitions. A common pattern is to use a composite primary key:

-- Drop the old table and recreate with a composite primary key
DROP TABLE events;

CREATE TABLE events (
    id BIGSERIAL NOT NULL,
    device_id UUID NOT NULL,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at) -- Composite PK
) PARTITION BY RANGE (created_at);

CREATE INDEX idx_events_created_at ON events (created_at);
CREATE INDEX idx_events_device_id ON events (device_id);

This ensures uniqueness within the context of the partition key, satisfying PostgreSQL's constraint requirements.

Deep Dive: `pg_partman` Configuration and Initial Setup

Configuration is done via the partman.create_parent() function. Its parameters are critical for production behavior.

-- Configure pg_partman to manage our 'events' table
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',          -- The column to partition by
    p_type := 'native',                  -- Use native partitioning (the only option for PG11+)
    p_interval := '1 day',               -- Create a new partition daily
    p_premake := 4,                      -- Create 4 future partitions in advance
    p_start_partition := '2023-10-01',   -- Optional: If you want to start partitioning from a specific date
    p_automatic_maintenance := 'on'      -- Ensure partman's maintenance procedure manages this table
);

Let's break down the most important parameters for senior engineers:

* p_parent_table: The table to manage. Schema-qualify it.

* p_control: The partition key column.

* p_interval: The time range for each partition. This is a critical design decision. '1 day' is common for high-volume data. '1 week' or '1 month' might be suitable for lower volumes. Too granular (e.g., '1 hour') can lead to too many partitions, increasing planning overhead.

* p_premake: This is a crucial performance and reliability parameter. It defines how many future partitions pg_partman should maintain. If an INSERT arrives for a timestamp that doesn't have a corresponding partition, the insert will fail. p_premake := 4 means pg_partman will always ensure partitions for the next 4 days exist. This provides a buffer in case the maintenance job fails to run for a day or two, preventing write failures.

After running create_parent(), you can inspect the configuration and the newly created partitions:

-- Check the configuration
SELECT * FROM partman.part_config WHERE parent_table = 'public.events';

-- List the child partitions that were created
SELECT parent.relname AS parent_table,
       child.relname AS child_partition
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'events';

/*
-- Expected output (assuming today is Oct 27, 2023)
 parent_table |   child_partition
--------------+----------------------
 events       | events_p2023_10_27
 events       | events_p2023_10_28
 events       | events_p2023_10_29
 events       | events_p2023_10_30
 events       | events_p2023_10_31
*/

Automating the Lifecycle: The Maintenance Run and Retention Policy

pg_partman does its work through a maintenance function, partman.run_maintenance_proc(). This single procedure handles both creating new partitions and dropping old ones for all configured tables.

Scheduling the Maintenance Job

The most robust way to schedule this is with pg_cron, an in-database scheduler. This avoids reliance on external cron systems and keeps the entire configuration within PostgreSQL.

-- First, ensure pg_cron is enabled in postgresql.conf and restart the DB
-- shared_preload_libraries = 'pg_cron'

CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule the maintenance to run every hour at the 5-minute mark.
-- Running it hourly ensures that if one run fails, it will be retried soon.
SELECT cron.schedule('partman-maintenance', '5 * * * *', 'CALL partman.run_maintenance_proc()');

Implementing the Retention Policy

Data retention is configured directly in partman.part_config. Let's set a 90-day retention policy for our events table.

UPDATE partman.part_config
SET retention = '90 days',
    retention_keep_table = true,  -- Set to 'false' to DROP partitions
    retention_keep_index = true
WHERE parent_table = 'public.events';

* retention: An interval defining how long to keep partitions. pg_partman checks the partition's date range against this value.

* retention_keep_table: When false, partitions older than the retention period are DROPped. When true, they are kept but detached from the parent table. This is useful if you need to archive the data before deletion.

Let's simulate a maintenance run and see it in action. Imagine we have partitions from August 2023. With a 90-day retention policy (from late October), these should be dropped.

-- Manually run the maintenance procedure to see the effect immediately
CALL partman.run_maintenance_proc();

-- Check the partitions again. The old August partitions should be gone.

The performance difference is staggering. A DELETE took hours and locked the table. DROP TABLE is a metadata change that completes in milliseconds, regardless of the partition's size, with no locking contention on the parent table.

Advanced Patterns and Edge Case Management

This is where we move from basic setup to production-hardening.

1. Migrating a Massive Existing Table (Backfilling)

You often decide to partition a table that already contains billions of rows. You cannot simply ALTER TABLE to partition it. The process requires a careful migration with minimal downtime.

The Strategy: Create, Migrate, Swap

a. Rename events_unpartitioned to events_old.

b. Rename events_partitioned to events.

c. Drop the trigger.

This locking period is very short, typically lasting only milliseconds.

Implementation Snippets:

-- Step 2: The Trigger Function
CREATE OR REPLACE FUNCTION redirect_events_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO events_partitioned (id, device_id, event_type, payload, created_at)
    VALUES (NEW.id, NEW.device_id, NEW.event_type, NEW.payload, NEW.created_at);
    RETURN NULL; -- Prevent the insert on the old table
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER events_insert_trigger
    BEFORE INSERT ON events_unpartitioned
    FOR EACH ROW EXECUTE FUNCTION redirect_events_insert();

-- Step 3: Batch Migration (example logic in a DO block)
DO $$
DECLARE
    batch_size INT := 50000;
    rows_affected INT;
BEGIN
    LOOP
        WITH moved_rows AS (
            DELETE FROM events_unpartitioned
            WHERE ctid IN (
                SELECT ctid FROM events_unpartitioned LIMIT batch_size
            )
            RETURNING *
        )
        INSERT INTO events_partitioned SELECT * FROM moved_rows;

        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        RAISE NOTICE 'Moved % rows', rows_affected;
        COMMIT; -- Commit each batch
        EXIT WHEN rows_affected < batch_size;
    END LOOP;
END;
$$;

-- Step 4: The Swap (perform this during a low-traffic window)
BEGIN;
LOCK TABLE events_unpartitioned IN ACCESS EXCLUSIVE MODE;
ALTER TABLE events_unpartitioned RENAME TO events_old;
ALTER TABLE events_partitioned RENAME TO events;
-- Drop the trigger from the old table
DROP TRIGGER events_insert_trigger ON events_old;
COMMIT;

2. Managing Indexes and Constraints

By default, pg_partman automatically creates indexes on new partitions by copying them from the parent table. For complex scenarios, such as unique constraints that don't include the partition key, you can use a template table.

pg_partman will use the structure of the table specified in p_template_table (in part_config) to create new partitions, giving you full control over indexes, constraints, and other table properties.

3. Handling Out-of-Order Data

What happens if a delayed event with a timestamp from three days ago arrives? If p_premake is set high enough, the partition will still exist. But what if the event is from a month ago, and that partition has already been dropped?

By default, the INSERT will fail. This is often the desired behavior, as it signals a problem in the data pipeline. However, pg_partman creates a default partition (events_default) to catch such data. You should have a monitoring process that periodically checks this default partition. If data lands there, it indicates a problem and the data can be manually moved to a correctly dated partition if one is created for it.

-- Monitor for data in the default partition
SELECT COUNT(*) FROM events_default;

4. Transaction Safety and Failure Modes

run_maintenance_proc() is transactional. If it fails while creating a set of new partitions, the entire operation is rolled back, leaving the database in a consistent state. The p_premake buffer is your safety net, allowing the system to continue accepting writes even if the maintenance job fails for a few cycles.

For monitoring, you can check PostgreSQL logs for errors from pg_cron or query partman.part_error_log for issues specific to pg_partman operations.

Performance Analysis and Query Optimization

The primary benefit of partitioning for read queries is partition pruning. When a query's WHERE clause includes a filter on the partition key (created_at), the PostgreSQL query planner is smart enough to scan only the relevant partitions.

Consider this query:

EXPLAIN ANALYZE SELECT AVG(CAST(payload->>'value' AS numeric)) 
FROM events 
WHERE device_id = 'a1b2c3d4-...' AND created_at BETWEEN '2023-10-26 00:00:00' AND '2023-10-26 23:59:59';

On an Unpartitioned Table: The planner would have to scan the massive idx_events_created_at index for the date range and then fetch the corresponding rows from the heap.

On a Partitioned Table: The planner will identify that the date range falls entirely within the events_p2023_10_26 partition.

-- Simplified EXPLAIN output showing partition pruning
->  Append  (cost=...)
    ->  Bitmap Heap Scan on events_p2023_10_26  (cost=...)
          ->  Bitmap Index Scan on events_p2023_10_26_device_id_idx
                Index Cond: (device_id = 'a1b2c3d4-...')

Notice that only one partition is scanned. The planner completely ignores the other hundreds of partitions, dramatically reducing the amount of data to be processed.

This makes queries on recent data incredibly fast, as they only operate on a small, targeted subset of the total data volume.

Conclusion: From Liability to Asset

For systems with time-series data, a large, monolithic table is a technical liability that guarantees future operational pain. By implementing a robust partitioning strategy automated with pg_partman, you transform this liability into a strategic asset.

This approach provides:

* Instantaneous Data Retention: DROP TABLE is a non-blocking, metadata-only operation that replaces resource-intensive DELETEs.

* Sustained Performance: Queries remain fast by leveraging partition pruning, and INSERT performance is consistent as indexes remain small and unbloated.

* Operational Resilience: Automation via pg_cron and the safety buffer from p_premake create a hands-off, reliable system that can withstand temporary maintenance failures.

Adopting pg_partman is not a minor optimization; it is a fundamental architectural shift for managing large-scale time-series data in PostgreSQL. It is a production-proven solution that addresses the entire data lifecycle, ensuring your database remains scalable, manageable, and performant as your data volume grows indefinitely.