Itroduction

Managing large tables in PostgreSQL becomes increasingly difficult as data grows. While PostgreSQL native partitioning solves many scalability and performance problems, maintaining partitions manually can quickly become an operational challenge.

Creating future partitions, dropping old partitions, monitoring partition health, and handling retention policies manually can consume significant DBA effort and increase the risk of operational mistakes.

This is where pg_partman becomes extremely useful. In this blog, we will explore how pg_partman automates PostgreSQL partition management and makes large-scale partitioned environments easier to maintain.

Why Manual Partition Management Becomes Difficult

Modern applications continuously generate large amounts of data such as logs, transactions, audit records, monitoring data and historical reports. To manage growing tables efficiently, organizations commonly use PostgreSQL partitioning with daily, monthly or yearly partitions.

Although PostgreSQL native partitioning is powerful, managing partitions manually can become difficult over time. DBAs must create future partitions, clean up old partitions, manage retention policies, and maintain scripts manually. Missing partitions or operational mistakes can also lead to INSERT failures or data routing issues.

Without proper automation, partition management itself can become a major operational challenge in large PostgreSQL environments.

What is pg_partman

pg_partman is an open-source PostgreSQL extension designed to automate partition management.

It simplifies the lifecycle management of partitioned tables by automatically:

• Creating future partitions
• Managing retention policies
• Dropping old partitions
• Running maintenance operations
• Handling partition automation

pg_partman supports:

• Time-based partitioning
• Serial / ID-based partitioning
• Native PostgreSQL partitioning

Instead of manually managing partitions, DBAs can rely on automated maintenance workflows.

pg_partman architecture showing parent partitioned table, child partitions, background worker automation, metadata management, and maintenance workflow.

This architecture diagram explains how pg_partman automates partition lifecycle management inside PostgreSQL environments.

• Application queries interact with the parent partitioned table
• Data is automatically routed into child partitions
• pg_partman metadata tracks partition information
• Background workers execute maintenance operations
• Future partitions are automatically created
• Old partitions are cleaned up using retention policies

Why Use pg_partman

1. Automatic Partition Creation

New partitions are automatically created before they are required.

Example:

If the current partition is for April 2025, pg_partman can automatically create:

• May 2025
• June 2025
• July 2025

This ensures that incoming inserts always have valid target partitions.

2. Automatic Retention Management

Older partitions can be automatically dropped based on retention settings.

Instead of running expensive DELETE operations like:

DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '6 months';

We can simply drop old partitions.

This approach:

• Reduces VACUUM overhead
• Improves cleanup performance
• Simplifies maintenance
• Reduces storage usage

3. Reduced Operational Complexity

Without pg_partman:

• Manual scripts
• Cron jobs
• Continuous DBA monitoring
• Risk of missing future partitions

With pg_partman:

• Automated partition maintenance
• Simplified administration
• Predictable partition lifecycle
• Lower operational risk

Installing pg_partman

Install Extension Package

RHEL / CentOS

yum install pg_partman

Ubuntu

apt install postgresql-17-partman

Create Dedicated Schema

Creating a dedicated schema for pg_partman is recommended.

This helps:

• Organize extension objects
• Simplify maintenance
• Improve backup management
• Separate extension metadata

CREATE SCHEMA partman;

Create Extension

CREATE EXTENSION pg_partman SCHEMA partman;

Creating a Native Partitioned Table

Let us create an orders table partitioned by date.

CREATE TABLE orders (
    order_id      BIGINT,
    customer_id   BIGINT,
    order_date    DATE NOT NULL,
    amount        NUMERIC
) PARTITION BY RANGE(order_date);

This creates a parent partitioned table. The actual data will be stored in child partitions.

Template Table Concept

One important feature in pg_partman is the use of template tables.

Template tables help propagate:

• Indexes
• Constraints
• Storage settings
• Additional table properties

across all child partitions automatically.

Create Template Table

CREATE TABLE partman.orders_template (
    LIKE public.orders
);

Add Primary Key

ALTER TABLE partman.orders_template
ADD PRIMARY KEY(order_id);

This ensures all future partitions inherit the same structure.

Creating Automated Partitions Using pg_partman

Now configure pg_partman to manage the partition lifecycle.

SELECT partman.create_parent(
    p_parent_table := 'public.orders',
    p_control := 'order_date',
    p_type := 'native',
    p_interval := 'monthly',
    p_template_table := 'partman.orders_template',
    p_premake := 3
);

Understanding Important Parameters

Checking Created Partitions

You can verify generated partitions using:

SELECT inhrelid::regclass
FROM pg_inherits
WHERE inhparent = 'public.orders'::regclass;

Example output:

orders_p2025_01
orders_p2025_02
orders_p2025_03
orders_p2025_04

Understanding the Premake Feature

The premake feature is one of the most important capabilities of pg_partman.

It automatically creates future partitions ahead of time.

This prevents:

• INSERT failures
• Missing partition issues
• Data routing problems

For example:

If the current partition is April 2025 and premake is set to 3, pg_partman automatically creates:

• May 2025
• June 2025
• July 2025

before inserts arrive.

pg_partman Premake Workflow

This workflow demonstrating how pg_partman proactively creates future partitions before incoming inserts arrive.

The premake feature is one of the most important operational capabilities of pg_partman.

In this example:

• Current partition represents the active partition
• Future partitions are automatically pre-created
• Missing partition scenarios are avoided
• Inserts continue smoothly without interruption

This automation significantly reduces operational risk in high-volume PostgreSQL systems.

What Happens When Future Partitions Are Missing

If a required partition does not exist, PostgreSQL routes incoming rows into the DEFAULT partition. Although this prevents immediate INSERT failures, it introduces several operational issues.

Problems Caused by DEFAULT Partition Usage

• Data is stored in incorrect partitions
• Partition pruning may not work efficiently
• Reports can become inaccurate
• DEFAULT partition size may grow rapidly
• Maintenance becomes complicated

This is one of the major reasons why premake settings are important.

Default Partition Problem

Workflow showing how missing future partitions can cause incoming rows to enter the DEFAULT partition.

This diagram demonstrates one of the most common operational issues in partitioned PostgreSQL environments.

When future partitions are not available:

• PostgreSQL redirects incoming rows into the DEFAULT partition
• Partition pruning efficiency may decrease
• Maintenance complexity increases
• Reporting accuracy may be affected

After pg_partman maintenance runs:

• Missing partitions are created automatically
• Data is moved into the correct partition
• DEFAULT partition size is reduced
• Query performance improves

Moving Data from DEFAULT Partition

pg_partman provides maintenance procedures to move misplaced rows into correct partitions.

Example:

CALL partman.partition_data_proc('public.orders');

This procedure:

• Creates missing partitions
• Moves data into proper child partitions
• Cleans up the DEFAULT partition

After running maintenance:

VACUUM ANALYZE orders;

This updates planner statistics and improves query optimization.

Running Maintenance Manually

Maintenance can be executed manually using:

SELECT partman.run_maintenance();

This operation:

• Creates future partitions
• Drops expired partitions
• Updates metadata
• Handles retention policies

Background Worker Automation

pg_partman includes a background worker process:

pg_partman_bgw

This automatically executes maintenance periodically.

Enable Background Worker

Update postgresql.conf:

shared_preload_libraries = 'pg_partman_bgw'

Restart PostgreSQL afterward.

Common Background Worker Parameters

Maintenance Interval

pg_partman_bgw.interval = 3600

Runs maintenance every hour.

Database Name

pg_partman_bgw.dbname = 'postgres'

Defines which database runs maintenance.

Role Name

pg_partman_bgw.role = 'postgres'

Defines which role executes maintenance.

Using Cron Scheduler Instead

Some environments prefer cron-based scheduling.

Example:

0 * * * * psql -c "SELECT partman.run_maintenance();"

This executes maintenance every hour.

Retention Management

Retention policies help automatically remove old partitions.

Example:

UPDATE partman.part_config
SET retention = '6 months',
    retention_keep_table = false
WHERE parent_table = 'public.orders';

This configuration:

• Keeps only recent data
• Drops older partitions automatically
• Reduces storage usage
• Simplifies cleanup operations

Inserting Sample Data

INSERT INTO orders VALUES
(1,101,'2025-01-10',1000),
(2,102,'2025-02-15',2500),
(3,103,'2025-03-20',3200);

PostgreSQL automatically routes rows into appropriate partitions.

Partition Pruning Performance

One of the biggest advantages of partitioning is Partition Pruning.

When queries include partition filters, PostgreSQL scans only matching partitions instead of scanning the entire dataset.

Example:

EXPLAIN
SELECT *
FROM orders
WHERE order_date = '2025-02-15';

Instead of scanning all partitions, PostgreSQL scans only the matching partition.

This significantly improves:

• Query performance
• Index efficiency
• I/O utilization

Best Practices

Choose Proper Partition Intervals

Avoid creating unnecessary partitions.

Monitor DEFAULT Partition

DEFAULT partitions should remain minimal.

Rapid growth usually indicates:

• Missing future partitions
• Premake issues
• Maintenance failures

Configure Proper Premake Count

Always keep sufficient future partitions ready.

Example:

premake = 3

This keeps three future partitions prepared in advance.

Schedule Regular Maintenance

Whether using:

• Background workers
• Cron jobs

maintenance must execute regularly.

Common Mistakes

1. Too Many Partitions

Excessive partitions increase planner overhead.

2. Incorrect Partition Key

Partition key selection should match query patterns.

Example:

If queries filter by date, partition using date columns.

3. Ignoring DEFAULT Partition

DEFAULT partitions should never be ignored.

Unchecked DEFAULT growth may reduce partition efficiency.

4. Forgetting VACUUM ANALYZE

After maintenance operations:

VACUUM ANALYZE orders;

should be executed to refresh planner statistics.

Advantages of pg_partman

• Automated partition management
• Improved scalability
• Simplified retention handling
• Reduced DBA effort
• Better operational reliability
• Faster maintenance operations
• Improved lifecycle management

Limitations of pg_partman

• Supports primarily RANGE-style automation
• Requires proper maintenance scheduling
• Poor partition design can still affect performance
• Large numbers of partitions may increase planning overhead

Conclusion

PostgreSQL native partitioning is extremely powerful for handling large datasets, but manual partition maintenance can quickly become operationally complex.

pg_partman simplifies partition lifecycle management by automating:

• Future partition creation
• Retention cleanup
• Maintenance execution
• DEFAULT partition handling
• Partition automation workflows

For large-scale PostgreSQL environments, pg_partman transforms partitioning into a practical and production-ready solution. Partitioning improves scalability but automation is what makes it sustainable in real-world systems.