Managing Large PostgreSQL Tables with Native Partitioning and pg_partman

Source: Dev.to

The Problem

As databases grow, tables that store large volumes of time‑based data can quickly become difficult to manage.

Typical issues include:

• Queries become slower
• Indexes grow larger
• Maintenance operations like VACUUM take longer
• Managing old data becomes complicated

PostgreSQL provides native table partitioning to help address these problems, but manual partition management can add operational complexity.

In this article we’ll explore:

• How native PostgreSQL partitioning works
• The operational challenges of managing partitions manually
• How pg_partman automates partition management

Native PostgreSQL Partitioning

PostgreSQL supports table partitioning, allowing a large logical table to be split into multiple smaller physical tables called partitions.

Partitioning Methods

Range
List
Hash

For time‑based data, range partitioning is the most common approach.

Example: Creating a Partitioned Table

CREATE TABLE events (
    id         BIGSERIAL,
    created_at TIMESTAMP,
    data       JSONB
) PARTITION BY RANGE (created_at);

events becomes the parent table. Actual data is stored in child tables (partitions).

Example: Creating Partitions

CREATE TABLE events_2026_03_20
    PARTITION OF events
    FOR VALUES FROM ('2026-03-20') TO ('2026-03-21');

CREATE TABLE events_2026_03_21
    PARTITION OF events
    FOR VALUES FROM ('2026-03-21') TO ('2026-03-22');

Each partition is a physical table inside PostgreSQL.

How Data Is Inserted into Partitions

Applications still insert data into the parent table:

INSERT INTO events (created_at, data)
VALUES ('2026-03-21 10:15:00', '{"event":"login"}');

PostgreSQL automatically routes the row to the correct partition based on the partition key. In this case the row is stored in events_2026_03_21.

Queries continue to run against the parent table:

SELECT * FROM events
WHERE created_at >= now() - interval '7 days';

PostgreSQL performs partition pruning, scanning only the relevant partitions.

Why Partitioning Improves Performance

Partitioning reduces the amount of data that must be scanned:

Query for last 7 days
        ↓
PostgreSQL scans only recent partitions
        ↓
Older partitions are skipped

It also simplifies operations such as:

• Archiving historical data
• Dropping old data quickly
• Managing index sizes

Example: Dropping a whole month’s data is instantaneous:

DROP TABLE events_2024_03_01;

This is far faster than deleting millions of rows from a single large table.

The Operational Challenge with Native Partitioning

While powerful, manual partition management introduces several operational headaches:

• New partitions must be created ahead of time
• Old partitions must be removed manually
• Retention policies must be implemented manually
• Missing partitions cause insert failures

Scenario:

Application inserts event at midnight
        ↓
New partition does not exist
        ↓
Insert fails

As systems scale, manually handling partitions becomes increasingly difficult, making automation essential.

Introducing pg_partman

pg_partman is a PostgreSQL extension that automates partition management. It builds on native partitioning and handles:

• Automatic creation of future partitions
• Retention and removal of old partitions
• Routine partition maintenance

This reduces the operational overhead of managing partitioned tables manually.

How pg_partman Works

pg_partman manages a parent partitioned table and automatically maintains its partitions.

Simplified Workflow

Parent table
      ↓
pg_partman configuration
      ↓
Automatic creation of future partitions
      ↓
Optional retention policies for old partitions

Example: For a table partitioned by day, pg_partman can automatically create upcoming partitions such as:

events_p2026_03_22
events_p2026_03_23
events_p2026_03_24

Ensuring that new inserts always have a valid partition.

Benefits of Using pg_partman

Compared with manual management, pg_partman provides:

• Automatic partition creation – no more missing‑partition errors
• Reduced operational overhead – fewer manual scripts and checks
• Consistent retention policies – old partitions are dropped automatically
• Improved reliability – partitions are always present when needed

When pg_partman Is Useful

pg_partman is particularly useful for workloads involving large append‑only datasets, such as:

• Event logs
• Analytics data
• Application activity tracking
• Time‑series data

In these scenarios, new data continuously arrives while older data eventually becomes less important. Partition automation helps manage this lifecycle efficiently.

Final Thoughts

PostgreSQL’s native partitioning provides powerful capabilities for managing large datasets. However, operating partitioned tables manually can introduce additional operational complexity.

Extensions like pg_partman simplify this process by automating partition creation and maintenance.

By combining PostgreSQL’s native partitioning features with pg_partman’s automation, teams can manage large time‑based datasets more reliably and with less manual intervention.