Mastering pg_partman: Automate PostgreSQL Partitioning for Billion-Row Datasets

Table of Contents

The Headache of Massive Tables and the Need for Automation

Managing a database that grows by millions of rows every day eventually leads to a performance wall. I remember a project where we had a single ‘logs’ table that hit 500GB. Simple SELECT queries started taking minutes, and maintenance tasks like VACUUM were basically impossible to finish. Having worked with MySQL, PostgreSQL, and MongoDB across different projects, each has its own strengths, but PostgreSQL’s native partitioning is where it really shines for structured data at scale.

PostgreSQL introduced native declarative partitioning in version 10, which was a huge leap forward. However, there is a catch: it doesn’t manage the partitions for you. You have to manually create the next month’s table, manage the constraints, and drop old data yourself.

If you forget to create the next partition, your application starts throwing errors because there is no place for the data to land. This is exactly where pg_partman becomes a lifesaver. It acts as the automation engine that handles the lifecycle of your partitions so you can sleep at night.

Getting pg_partman Into Your Environment

Before we can automate anything, we need to get the extension installed on the server. If you are using a managed service like AWS RDS or Azure Database for PostgreSQL, pg_partman is likely already available, and you just need to enable it. For those managing their own Linux servers, you’ll need to install the package that matches your Postgres version.

On a Debian/Ubuntu system for PostgreSQL 16, the process looks like this:

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

Once the package is installed, you need to tell PostgreSQL to load it. This requires a change to your postgresql.conf file. Locate the shared_preload_libraries setting and add pg_partman_bgw (the Background Worker). This allows the extension to run maintenance tasks automatically in the background.

# Edit postgresql.conf
shared_preload_libraries = 'pg_partman_bgw'

Restart your PostgreSQL service to apply the changes. After the restart, log into your database and create the extension and a dedicated schema for it. I highly recommend keeping pg_partman in its own schema to keep your public schema clean.

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

Configuring Your First Automated Partition

Let’s look at a practical example. Suppose we have an iot_sensor_data table that receives thousands of entries per second. We want to partition this table by the created_at timestamp, creating a new partition for every day.

First, create the “template” or parent table. Note that in declarative partitioning, the parent table defines the structure and the partition key.

CREATE TABLE public.iot_sensor_data (
    id bigint NOT NULL,
    sensor_id int NOT NULL,
    data_value numeric,
    created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

Now, instead of manually running CREATE TABLE ... PARTITION OF, we let pg_partman take over. We call the create_parent function. This tells the extension to start managing our table, using ‘daily’ intervals and pre-creating 4 partitions ahead of time so we never run out of space.

SELECT partman.create_parent(
    p_parent_table := 'public.iot_sensor_data',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := 'daily',
    p_premake := 4
);

The p_premake parameter is vital. It ensures that if today is Monday, partitions for Tuesday, Wednesday, Thursday, and Friday already exist. This buffer protects you against temporary failures in the maintenance job.

Automating the Maintenance Task

Even though we told pg_partman how we want our partitions, it still needs a trigger to actually create new ones as time passes. There are two main ways to do this: using the Background Worker (BGW) or using a cron job.

Method 1: The Background Worker (Recommended)

Since we added pg_partman_bgw to our shared_preload_libraries earlier, we just need to configure it in postgresql.conf. This is the most robust method because it doesn’t rely on external tools.

# Add these to postgresql.conf
pg_partman_bgw.interval = 3600 # Run every hour
pg_partman_bgw.role = 'postgres'
pg_partman_bgw.dbname = 'your_database_name'

Method 2: Using pg_cron

If you prefer more granular control or are already using pg_cron for other tasks, you can schedule the maintenance function manually:

SELECT cron.schedule('0 * * * *', $$SELECT partman.run_maintenance()$$);

Every time run_maintenance() executes, it checks all tables managed by pg_partman, creates new partitions if needed based on the p_premake value, and handles data retention.

Data Retention and Cleanup

One of the biggest advantages of partitioning is the ability to delete old data instantly. Deleting 100 million rows with a DELETE command generates massive amounts of WAL logs and causes table bloat. With partitioning, you just drop the entire table (the partition), which is an O(1) operation.

To automate this, update the part_config table for your specific parent table. Let’s say we only want to keep 30 days of sensor data.

UPDATE partman.part_config 
SET retention = '30 days', 
    retention_keep_table = false
WHERE parent_table = 'public.iot_sensor_data';

With retention_keep_table = false, pg_partman will physically drop the old partition tables. If you set it to true, it will simply unbind them from the parent table but leave the data on disk—useful if you want to archive it to cold storage like S3 before deleting.

Verification and Health Checks

Once everything is running, you shouldn’t just assume it’s working. I always check the part_config and the actual list of tables to ensure the automation is healthy.

To see all tables currently managed by the extension:

SELECT parent_table, partition_type, partition_interval, retention 
FROM partman.part_config;

To verify that the partitions are actually being created in the file system/database, use the \d+ command in psql on the parent table. You should see a list of child partitions mapped to specific time ranges.

If you ever run into a situation where maintenance hasn’t run and you are missing partitions, you can force a manual run for a specific table to catch up:

SELECT partman.run_maintenance('public.iot_sensor_data');

Final Thoughts

Moving from a single monolithic table to an automated partitioned setup is one of the most impactful changes you can make for a high-growth database. It solves the performance degradation issue and provides a clean way to manage data retention without the overhead of heavy DELETE operations. By combining PostgreSQL’s native partitioning with pg_partman, you get a system that scales to billions of rows while requiring almost zero manual intervention once the initial pipes are laid.