PostgreSQL Table Partitioning with pg_partman: Complete Guide

A SaaS platform we worked with had an audit_logs table that grew to 800GB over three years of unbounded inserts. Every night, autovacuum ran for six hours straight — and still couldn't finish before the next wave of writes began. Queries against the last 30 days of logs routinely timed out at the 30-second mark because the planner had no choice but to scan the entire heap. The fix was straightforward: range-partition the table by month using PostgreSQL's native declarative partitioning, then hand maintenance over to pg_partman. Within a week of migration, VACUUM dropped to 8 minutes per partition, and time-range queries returned in under 200 milliseconds.

PostgreSQL Native Partitioning: RANGE, LIST, HASH

PostgreSQL's declarative partitioning, introduced in version 10 and significantly improved through versions 11–14, splits one logical table into physical child tables called partitions. The database routes rows automatically based on the partition key you define at table creation time. Three strategies are available.

RANGE partitioning splits rows by a continuous range of values — timestamps and integer IDs are the most common keys. LIST partitioning assigns specific discrete values to named partitions, useful for multi-tenant schemas keyed on a tenant_id or a country code. HASH partitioning distributes rows by a hash of the partition key, useful for spreading write load evenly when no natural time or list boundary exists.

-- RANGE partition by month
CREATE TABLE audit_logs (
    id          bigserial,
    tenant_id   int         NOT NULL,
    event_type  text        NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- LIST partition by region
CREATE TABLE orders (
    id        bigserial,
    region    text NOT NULL,
    amount    numeric(12,2),
    ordered_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY LIST (region);

-- HASH partition across 8 buckets
CREATE TABLE events (
    id         bigserial,
    account_id int NOT NULL,
    data       jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY HASH (account_id);

Native partitioning handles query routing, constraint enforcement, and — since PostgreSQL 11 — index creation and default partitions. What it does not handle is the operational lifecycle: pre-creating next month's partition, dropping partitions older than your retention window, and keeping everything running without manual cron jobs. That is where pg_partman earns its place.

Why pg_partman?

pg_partman is a PostgreSQL extension that manages the lifecycle of partitioned tables. It pre-creates future partitions based on a configurable premake count (default: 4 partitions ahead), enforces a retention policy by detaching or dropping old partitions, and integrates directly with pg_cron for fully automated maintenance. It supports both time-based and serial-integer partitioning, and it works on top of standard PostgreSQL declarative partitioning — it does not replace the underlying mechanism, it automates it.

Without pg_partman, a common failure mode is a table that runs out of partitions at midnight on the first day of a new month because no one remembered to run the CREATE TABLE statement for the new child. pg_partman eliminates that class of outage entirely.

Installing pg_partman

From the PGDG Repository (apt)

On Debian or Ubuntu systems with the PostgreSQL Global Development Group repository configured, pg_partman is available as a standard package. Replace 16 with your installed major version.

sudo apt-get update
sudo apt-get install postgresql-16-partman

# Verify the shared library is available
ls /usr/share/postgresql/16/extension/pg_partman*

On RHEL/Rocky Linux systems using the PGDG DNF repository:

sudo dnf install pg_partman_16

Enabling the Extension in PostgreSQL

pg_partman must be loaded as a shared library if you plan to use the background worker approach. Add it to postgresql.conf before creating the extension.

# In postgresql.conf
shared_preload_libraries = 'pg_partman_bgw'   # add to existing list if needed
pg_partman_bgw.interval = 3600                # run maintenance every hour
pg_partman_bgw.role = 'partman'               # role that owns the managed tables
pg_partman_bgw.dbname = 'myapp'               # target database

Then create a dedicated schema and enable the extension. A superuser or a role with the pg_partman_basic role (PostgreSQL 14+) is required.

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- Create a dedicated role for pg_partman operations (best practice)
CREATE ROLE partman WITH LOGIN PASSWORD 'strongpassword';
GRANT ALL ON SCHEMA partman TO partman;
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman;
GRANT ALL ON SCHEMA public TO partman;  -- adjust to your table schema

Setting Up Range Partitioning by Time

Create the Parent Table

The parent table defines the schema and the partition key. It holds no data itself — all rows live in child partitions. Create it with PARTITION BY RANGE on the timestamp column you will use as the partition key.

CREATE TABLE public.audit_logs (
    id          bigserial,
    tenant_id   int         NOT NULL,
    event_type  text        NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)   -- partition key must be part of PK
) PARTITION BY RANGE (created_at);

-- Create a default partition to catch rows outside defined ranges
-- (PostgreSQL 11+; useful during migration or if a partition is ever missing)
CREATE TABLE public.audit_logs_default
    PARTITION OF public.audit_logs DEFAULT;

Initialize pg_partman with create_parent()

The create_parent() function registers the table with pg_partman, creates the initial set of partitions (past and future, based on premake), and populates the partman.part_config table with management metadata.

SELECT partman.create_parent(
    p_parent_table  => 'public.audit_logs',
    p_control       => 'created_at',
    p_type          => 'range',
    p_interval      => 'monthly',
    p_premake       => 3,       -- pre-create 3 future monthly partitions
    p_start_partition => (now() - interval '3 months')::text  -- backfill 3 past months
);

After this call, PostgreSQL will have child tables named audit_logs_p2025_10, audit_logs_p2025_11, and so on, each covering exactly one calendar month. pg_partman records this in partman.part_config.

Configure partition_config for Retention

Update the partman.part_config row for your table to set the retention window. Setting retention to '12 months' tells pg_partman to detach partitions older than 12 months during maintenance. Setting retention_keep_table to false tells it to DROP the detached partition entirely.

UPDATE partman.part_config
SET
    premake              = 3,
    optimize_trigger     = 4,
    retention            = '12 months',
    retention_keep_table = false,   -- DROP old partitions (true = detach only)
    retention_keep_index = false,
    infinite_time_partitions = true
WHERE parent_table = 'public.audit_logs';

-- Verify the configuration
SELECT parent_table, partition_interval, premake, retention, retention_keep_table
FROM partman.part_config
WHERE parent_table = 'public.audit_logs';

Automating Partition Maintenance

Using pg_cron to Call run_maintenance_proc()

The most common automation approach pairs pg_partman with pg_cron. Install pg_cron in the same database and schedule a nightly maintenance run.

-- Requires pg_cron extension
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule run_maintenance_proc() to run at 01:00 every night
SELECT cron.schedule(
    'partman-maintenance',
    '0 1 * * *',
    $$CALL partman.run_maintenance_proc()$$
);

-- To run maintenance for a single parent table only:
SELECT cron.schedule(
    'partman-audit-logs',
    '0 2 * * *',
    $$SELECT partman.run_maintenance('public.audit_logs')$$
);

Background Worker Approach

If you loaded pg_partman_bgw in shared_preload_libraries, pg_partman runs its own background worker process on the interval you configured in postgresql.conf. This is useful when you cannot install pg_cron or prefer not to run a separate scheduler.

# Check that the background worker started
grep "pg_partman" /var/log/postgresql/postgresql-16-main.log
# Expected: LOG:  pg_partman master background worker master process initialized

What Happens During Maintenance

When run_maintenance() executes, pg_partman inspects each managed parent table and performs three actions in order: it creates new future partitions up to the premake count ahead of the current timestamp; it applies any new indexes defined on the parent to child partitions that are missing them; and it evaluates each old partition against the retention policy, detaching or dropping those that fall outside the window.

-- Run maintenance manually and return status
SELECT partman.run_maintenance('public.audit_logs');

-- Check partition creation results
SELECT partition_tablename, partition_range
FROM partman.show_partitions('public.audit_logs')
ORDER BY partition_range DESC
LIMIT 10;

Partition Pruning: How PostgreSQL Skips Partitions

Partition pruning is the mechanism by which the PostgreSQL query planner eliminates child tables that cannot contain rows matching a WHERE clause predicate on the partition key. It is controlled by the enable_partition_pruning GUC, which defaults to on.

-- Confirm partition pruning is enabled (it is by default)
SHOW enable_partition_pruning;

-- A query with a partition key predicate will prune irrelevant partitions
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, event_type, payload
FROM public.audit_logs
WHERE created_at >= '2025-11-01'
  AND created_at  < '2025-12-01'
  AND tenant_id = 42;

The EXPLAIN output will include a line such as Partitions selected: 1 of 14, confirming that 13 child tables were eliminated at plan time without any I/O. If you see Partitions selected: 14 of 14, your WHERE clause is not on the partition key, or the value is not known at plan time (e.g., a non-immutable function).

Note the distinction between partition pruning and the older constraint_exclusion mechanism. constraint_exclusion applies to table inheritance and checks CHECK constraints on child tables; it is slower because it runs during execution rather than planning. With declarative partitioning and enable_partition_pruning = on, pruning happens at plan time and is significantly more efficient. Leave constraint_exclusion at its default of partition (not on) to avoid redundant work.

Migrating an Existing Table to Partitioned

Moving a live 800GB table to a partitioned schema without downtime requires a careful sequence. The approach uses a new partitioned parent table alongside the original, a batched backfill, and an atomic rename swap.

Create the New Partitioned Table Alongside the Old

-- Step 1: Create the new partitioned table with a temporary name
CREATE TABLE public.audit_logs_partitioned (
    LIKE public.audit_logs INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- Step 2: Initialize pg_partman on the new table
SELECT partman.create_parent(
    p_parent_table => 'public.audit_logs_partitioned',
    p_control      => 'created_at',
    p_type         => 'range',
    p_interval     => 'monthly',
    p_premake      => 3
);

-- Step 3: Add a trigger on the old table to dual-write new rows into the new table
--         (keep this trigger lightweight — just a BEFORE INSERT redirect)
CREATE OR REPLACE FUNCTION public.redirect_audit_insert()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO public.audit_logs_partitioned VALUES (NEW.*);
    RETURN NULL;  -- prevent insert into old table once backfill is complete
END;
$$;

Backfill with partition_data_proc()

pg_partman provides partition_data_proc() to move rows from the default partition (or an unpartitioned table attached as default) into the correct child partitions in batches, avoiding a single massive transaction lock.

-- Attach old table as the default partition temporarily
-- (only feasible if schemas are identical; otherwise use batched INSERT...SELECT)
ALTER TABLE public.audit_logs_partitioned
    ATTACH PARTITION public.audit_logs DEFAULT;

-- Run the backfill procedure — processes p_batch_count rows per call
CALL partman.partition_data_proc(
    p_parent_table  => 'public.audit_logs_partitioned',
    p_batch_count   => 1000,
    p_wait          => 1,   -- wait 1 second between batches to reduce I/O pressure
    p_source_table  => 'public.audit_logs'
);

-- Monitor progress
SELECT count(*) FROM public.audit_logs;                   -- rows remaining in old
SELECT count(*) FROM public.audit_logs_partitioned;       -- rows moved

Swap with Minimal Lock

-- Once backfill is complete, swap names atomically
-- This requires an ACCESS EXCLUSIVE lock but holds it for milliseconds
BEGIN;
ALTER TABLE public.audit_logs          RENAME TO audit_logs_old;
ALTER TABLE public.audit_logs_partitioned RENAME TO audit_logs;
COMMIT;

-- Validate row counts match, then drop the old table
SELECT count(*) FROM public.audit_logs_old;
-- DROP TABLE public.audit_logs_old;  -- run after validation

Common Pitfalls

-- Create index on all current and future partitions
-- pg_partman will apply this to future partitions automatically
CREATE INDEX ON public.audit_logs (tenant_id, created_at);

-- Verify all partition indexes exist
SELECT tablename, indexname
FROM pg_indexes
WHERE tablename LIKE 'audit_logs%'
ORDER BY tablename;

-- PostgreSQL 12+: valid FK referencing a partitioned table
CREATE TABLE public.audit_log_comments (
    id          bigserial PRIMARY KEY,
    log_id      bigint      NOT NULL,
    log_created timestamptz NOT NULL,
    comment     text,
    FOREIGN KEY (log_id, log_created)
        REFERENCES public.audit_logs (id, created_at)
);

Working with JusDB on PostgreSQL Partitioning

JusDB designs and implements PostgreSQL partitioning strategies for tables that have outgrown single-table performance. We handle schema design, pg_partman configuration, maintenance automation, and zero-downtime migration from unpartitioned tables.

Explore JusDB PostgreSQL Services →  |  Talk to a DBA

Related reading:

• PostgreSQL Explained
• PostgreSQL PITR and Backup
• PgBouncer Connection Pooling