Managing Large PostgreSQL Tables with Native Partitioning and pg_partman

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

Over time, this leads to several issues:

• 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.

However, while partitioning improves performance and data management, operating partitioned tables manually can introduce 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.

PostgreSQL currently supports three partitioning methods:

Range
List
Hash

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

For example, imagine storing application events with a timestamp.

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

Here, events becomes the parent table.

Actual data is stored in child tables called partitions.

Example 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 example, the row would be stored inside:

events_2026_03_21

Queries still run against the parent table.

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

PostgreSQL internally performs partition pruning, meaning only the relevant partitions are scanned.

---

Why Partitioning Improves Performance

Partitioning helps in several ways.

Instead of scanning a very large table, PostgreSQL only scans the partitions that match the query conditions.

Example:

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

Partitioning also simplifies operations like:

• archiving historical data
• dropping old data quickly
• managing index sizes

For example, dropping old data can be done instantly by dropping a partition.

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 native partitioning is powerful, managing partitions manually introduces operational challenges.

For example:

• new partitions must be created ahead of time
• old partitions must be removed manually
• retention policies must be implemented manually
• missing partitions can cause insert failures

Example scenario:

                  application inserts event at midnight
                                   ↓
                        new partition does not exist
                                   ↓
                              insert fails

As systems scale and tables grow, manually managing partitions becomes increasingly difficult.

This is where automation becomes valuable.

---

Introducing pg_partman

pg_partman is a PostgreSQL extension designed to automate partition management.

It builds on top of PostgreSQL’s native partitioning and helps manage partitioned tables more efficiently.

pg_partman can automatically handle:

• creation of future partitions
• retention and removal of old partitions
• partition maintenance operations

This reduces the operational overhead of managing partitioned tables manually.

---

How pg_partman Works

pg_partman works by managing a parent partitioned table and automatically maintaining its partitions.

A simplified workflow looks like this:

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

For example, if a table is partitioned by day, pg_partman can automatically create upcoming partitions.

events_p2026_03_22
events_p2026_03_23
events_p2026_03_24

This ensures that new inserts always have a valid partition.

---

Benefits of Using pg_partman

Compared to managing partitions manually, pg_partman provides several advantages.

                    Automatic partition creation
                                   ↓
                    Reduced operational overhead
                                   ↓
                   Safer data retention management
                                   ↓
                   Less risk of missing partitions

Instead of maintaining partition logic manually in application code or scripts, pg_partman handles this inside the database.

---

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.

---