Modern systems often deal with continuous data streams: logs, metrics, transactions, or sensor readings. Over time, these tables grow huge and slow down queries and backups. To keep performance high and storage clean, we need automation both in how data is stored (partitioning) and maintained (scheduling).

That’s where pg_partman and pg_cron come in. Together, they turn PostgreSQL into a self-managing time-series database, automatically creating partitions, cleaning up old data, and keeping everything in order.

Why Partitioning Matters

Partitioning splits a large table into smaller chunks (partitions) based on time or another key. Instead of one massive table, you get multiple smaller tables like:

• sensor_readings_p2025_01_01
• sensor_readings_p2025_01_02
• sensor_readings_p2025_01_03

Queries become faster because PostgreSQL can skip partitions outside the time range.

Example:

SELECT * FROM sensor_readings
WHERE reading_time >= now() - interval '1 day';

Now PostgreSQL only scans the recent partition, not the whole dataset.

Automating Partition Management with pg_partman

pg_partman is a PostgreSQL extension that automates partition creation and cleanup. It supports both time-based and serial-based partitioning.

With pg_partman, you can:

• Automatically create new partitions in advance (premake)
• Drop old partitions based on retention
• Define your own template for partitions (structure + indexes)
• Run maintenance easily via SQL or cron jobs

Practical Template: Daily Time-Based Partitioning

Here’s a complete Goose migration template to automate daily partitions for a simple sensor_readings table.

-- +goose Up
-- +goose StatementBegin

-- Create schemas for organization
CREATE SCHEMA IF NOT EXISTS partman;
CREATE SCHEMA IF NOT EXISTS iot;

-- Install pg_partman in partman schema (safe for all environments)
DO $do$
BEGIN
    CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
    RAISE NOTICE 'pg_partman installed successfully';
EXCEPTION 
    WHEN undefined_file THEN
        RAISE NOTICE 'pg_partman not available - skipping (development mode)';
END
$do$;

-- Grant privileges to main user
GRANT ALL ON SCHEMA partman TO postgres;

-- ============================================================
-- 1. Create a TEMPLATE table for partitions
-- ============================================================
-- The template defines structure & indexes that each partition will copy.
CREATE TABLE iot.sensor_readings_template (
    id bigserial PRIMARY KEY,
    device_id text NOT NULL,
    reading_value numeric(10,2) NOT NULL,
    reading_time timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Indexes to improve query performance
CREATE INDEX idx_sensor_template_device_id ON iot.sensor_readings_template(device_id);
CREATE INDEX idx_sensor_template_reading_time ON iot.sensor_readings_template(reading_time);

-- ============================================================
-- 2. Create the MAIN partitioned table
-- ============================================================
CREATE TABLE iot.sensor_readings (
    id bigserial PRIMARY KEY,
    device_id text NOT NULL,
    reading_value numeric(10,2) NOT NULL,
    reading_time timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (reading_time);

-- ============================================================
-- 3. Register with pg_partman and configure
-- ============================================================
DO $do$
BEGIN
    PERFORM partman.create_parent(
        p_parent_table => 'iot.sensor_readings',
        p_control => 'reading_time',
        p_type => 'range',
        p_interval => '1 day',
        p_premake => 3,
        p_template_table => 'iot.sensor_readings_template'
    );

    UPDATE partman.part_config
    SET retention = '30 days',
        retention_keep_table = false,
        retention_keep_index = false,
        infinite_time_partitions = true,
        datetime_string = 'YYYY-MM-DD'
    WHERE parent_table = 'iot.sensor_readings';

    RAISE NOTICE 'sensor_readings registered with pg_partman successfully';
END
$do$;

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin

DROP TABLE IF EXISTS iot.sensor_readings CASCADE;
DROP TABLE IF EXISTS iot.sensor_readings_template CASCADE;

DO $do$
BEGIN
    DROP EXTENSION IF EXISTS pg_partman CASCADE;
    DROP SCHEMA IF EXISTS partman CASCADE;
EXCEPTION 
    WHEN OTHERS THEN
        RAISE NOTICE 'Cleanup failed (may not exist): %', SQLERRM;
END
$do$;

-- +goose StatementEnd

Why Use a Template Table?

The template table is the base model for all partitions that pg_partman creates later. Every new partition is cloned from this template, including columns, indexes, constraints, and defaults.

Without a template, each new partition would be empty and missing indexes, which could hurt query performance badly.

Benefits of Using a Template

Purpose	Explanation
Consistency	Every partition has the same structure and indexes
Performance	Indexes are pre-created automatically
Automation	You don’t need to manually modify partitions
Safety	Avoids schema drift between partitions

The template table ensures every new partition behaves like the original table.