pg_partman

What pg_partman does

PostgreSQL has supported native declarative partitioning since version 10, but creating and maintaining partitions is manual work. You need to create each child partition by hand, ensure future partitions exist before data arrives, and manage old partitions when they outlive their usefulness. For a table partitioned by day, that means creating 365 partitions per year — indefinitely. I have seen teams begin this task with admirable discipline and abandon it within a quarter.

pg_partman automates all of this. You create a partitioned parent table, call partman.create_parent() to register it, and the extension takes over. It creates an initial set of child partitions, pre-creates future partitions ahead of time (configurable via the premake setting), and provides a maintenance procedure that keeps the partition set current. A background worker or external scheduler calls this procedure on a regular interval.

The extension supports two partitioning strategies: time-based (partition by timestamp column — daily, hourly, weekly, monthly, or any custom interval) and serial/ID-based (partition by integer column — every N rows). Both use PostgreSQL's built-in PARTITION BY RANGE under the hood. Since version 5.0, pg_partman exclusively uses native declarative partitioning and requires PostgreSQL 14 or later.

When to use pg_partman

Partitioning is not always the right tool — it adds complexity, and for many tables, proper indexing is entirely sufficient. I would not recommend reorganizing the household archives if a better label on the existing drawer would do. But when the table has genuinely outgrown a single drawer, pg_partman removes the operational burden of managing the expansion by hand.

• Time-series data — event logs, metrics, IoT readings, audit trails. Partition by day or month so queries that filter on time only scan relevant partitions.
• Retention policies — when old data should be dropped or archived on a schedule. Dropping a partition is instant compared to deleting millions of rows.
• Large tables with a natural range key — order tables partitioned by month, user activity tables partitioned by week. Partition pruning speeds up queries that filter on the partition key.
• Maintenance operations on large tables — VACUUM, ANALYZE, and index rebuilds operate per-partition, which means smaller, faster operations that hold locks for less time.
• Archival workflows — detach old partitions from the parent table and move them to cheaper storage or a separate archive database.

Installation and setup

pg_partman is a third-party extension maintained by pgexperts. It is not included in the PostgreSQL contrib modules, so it must be installed separately — either from your distribution's package manager, compiled from source, or enabled through your cloud provider's extension catalog.

-- 1. Create the partman schema and extension
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- 2. (Optional) Enable the background worker for automatic maintenance
-- Add to postgresql.conf and restart:
-- shared_preload_libraries = 'pg_partman_bgw'
-- pg_partman_bgw.dbname = 'your_database'
-- pg_partman_bgw.interval = 3600

The background worker (pg_partman_bgw) requires adding it to shared_preload_libraries, which means a server restart. However, basic pg_partman functionality — creating partitions, running maintenance manually — works without the background worker. Many users prefer to schedule maintenance with pg_cron instead, and on managed cloud platforms where shared_preload_libraries is not yours to edit, that preference becomes a necessity.

Key functions and usage

Time-based partitioning

The most common pattern: a table partitioned by a timestamp column, with new partitions created automatically as time progresses.

-- Create a partitioned table for time-series events
CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL DEFAULT now(),
  event_type text NOT NULL,
  payload    jsonb
) PARTITION BY RANGE (created_at);

-- Register it with pg_partman (daily partitions, 4 premade)
SELECT partman.create_parent(
  p_parent_table  => 'public.events',
  p_control       => 'created_at',
  p_interval      => '1 day',
  p_premake       => 4
);

Serial/ID-based partitioning

For tables where the natural partition key is an integer — useful when time is not the primary access pattern but the table grows large enough to benefit from range pruning.

-- Create a partitioned table using an integer ID range
CREATE TABLE logs (
  id      bigint GENERATED ALWAYS AS IDENTITY,
  message text,
  level   int
) PARTITION BY RANGE (id);

-- Register with pg_partman (1 million rows per partition)
SELECT partman.create_parent(
  p_parent_table  => 'public.logs',
  p_control       => 'id',
  p_interval      => '1000000',
  p_premake       => 4
);

Running maintenance

Maintenance creates new partitions as needed and enforces retention policies. It must run on a regular schedule — either via the background worker, pg_cron, or an external scheduler like cron or Cloud Scheduler.

-- Run maintenance manually (creates new partitions, applies retention)
CALL partman.run_maintenance_proc();

-- Or schedule it with pg_cron (every hour)
SELECT cron.schedule(
  'partman-maintenance',
  '0 * * * *',
  $$CALL partman.run_maintenance_proc()$$
);

Retention policies

A household that never discards old records eventually finds itself unable to move through its own corridors. pg_partman can automatically drop or detach partitions that fall outside a retention window, configured per-table in the partman.part_config table.

-- Set a 90-day retention policy on the events table
UPDATE partman.part_config
SET retention         = '90 days',
    retention_keep_table = false
WHERE parent_table = 'public.events';

-- Next run_maintenance_proc() call will drop partitions older than 90 days

Inspecting partition configuration

The partman.part_config table is the central registry. Query it to see all managed tables, their intervals, premake settings, and retention policies.

-- See all tables managed by pg_partman
SELECT parent_table, control, partition_interval, premake, retention
FROM partman.part_config;

-- List child partitions for a specific parent
SELECT partition_schemaname, partition_tablename
FROM partman.show_partitions('public.events')
ORDER BY partition_tablename;

Cloud availability

How Gold Lapel relates

Allow me to be direct about where these responsibilities divide. Partitioning is a data management strategy — it organizes large tables into smaller physical pieces to improve maintenance operations and enable partition pruning. Gold Lapel is a query optimization layer — it analyzes your workload and automatically creates materialized views, recommends indexes, and rewrites queries to serve results faster. Overlapping concerns, but distinct duties.

When the goal is query performance, I have seen Gold Lapel's materialized views eliminate the need for partitioning entirely. A query that scans a 500-million-row table filtering on a timestamp range may benefit from partitioning — but if Gold Lapel has already pre-joined and materialized the result set that query needs, it serves the answer from a compact materialized view without touching the large table at all. The partition never needed to exist; the question simply needed a better answer.

When the goal is data lifecycle management — dropping old data efficiently, archiving partitions, keeping VACUUM operations small — partitioning with pg_partman is the right tool and I would not pretend otherwise. Gold Lapel does not replace it. The two work well together: pg_partman manages the physical layout of your data, and Gold Lapel optimizes how queries access it. Proper housekeeping and proper service are not competing interests.