Posted inDatabase

Automating PostgreSQL Partitioning with pg_partman: A Lifesaver for Terabyte-Scale Tables

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Handle Large Datasets in 5 Minutes with pg_partman

When a data table hits the 500GB mark or billions of records, simple SELECT statements start to slow down significantly. Deleting old data using DELETE also becomes a nightmare because it locks tables and causes Write-Ahead Log (WAL) bloat. pg_partman is a tool that helps you break these massive tables into smaller partitions based on time or ID, completely automatically.

Assuming you have installed the extension on Ubuntu, follow these steps for a quick deployment:

-- Group partman into a separate schema for cleanliness
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- Declare the parent table partitioned by time
CREATE TABLE public.user_logs (
    id bigserial,
    log_date timestamptz not null,
    data text,
    PRIMARY KEY (id, log_date)
) PARTITION BY RANGE (log_date);

-- Configure partman to automatically create daily partitions
SELECT partman.create_parent(
    p_parent_table := 'public.user_logs',
    p_control := 'log_date',
    p_type := 'native',
    p_interval := 'daily',
    p_premake := 4
);

Immediately, the system will pre-create 4 child tables for the next 4 days. You no longer have to worry about the application crashing because you forgot to create a new partition when a new day begins.

Why You Should Stop Manual Partitioning

In the past, I often had to use Python scripts or Cronjobs to ALTER TABLE and create new partitions on MySQL. PostgreSQL has had powerful Native Partitioning since version 10, but it only provides the framework. Calculating when to create child tables, naming them correctly, or cleaning up old data (Retention) still requires manual handling.

I’ve seen systems I managed hang at exactly midnight on the 1st of the month. The cause was simply a logic error in the partition creation script, leaving no place for new data to be stored. pg_partman eliminates these silly risks with its smart automation mechanism.

Most Practical Features:

  • Pre-prepare partitions: The p_premake parameter ensures child tables are always ready before actual data arrives.
  • Automatic cleanup: You can set the system to automatically drop log tables older than 3 months with just one configuration line.
  • Background operation: The Background Worker runs directly within the Postgres core, independent of external tools.

Practical Installation

1. Install from Repository

Here is the installation command for PostgreSQL 15 on Ubuntu:

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

2. Enable Background Worker

To let pg_partman run automatically without manual intervention, you need to modify the postgresql.conf file. Find and add the following lines:

shared_preload_libraries = 'pg_partman_bgw'
pg_partman_bgw.interval = 3600 -- Check once every hour
pg_partman_bgw.role = 'postgres'
pg_partman_bgw.dbname = 'your_database_name'

After saving, restart the PostgreSQL service to activate this automated engine.

Data Retention Management

Deleting 10 million rows using DELETE can take several minutes and clog the DB. But DROPing a partition containing 10 million rows takes less than a second. This is the biggest selling point of pg_partman.

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

With the above command, every hour, the system will scan child tables. Any table containing data older than 90 days will be dropped immediately, freeing up disk space without causing system overhead.

How to Convert Regular Tables to Partitioned Tables

Most of us only look into Partitioning when the current table has grown too large, sometimes reaching 50-100 million records. Don’t panic; pg_partman has a safe built-in migration process.

  1. Create a new table with the exact same PARTITION BY structure as the old one.
  2. Use the partition_data_proc function to move data in batches.
-- Move data in batches of 10,000 rows to avoid hanging the DB
CALL partman.partition_data_proc('public.user_logs', p_batch := 10000);

Moving data in segments is the best way to avoid hanging the DB.

Hard-earned Lessons from Implementation

After years of operating financial systems and centralized logs, I’ve gathered a few important notes:

Don’t Over-partition

Many people prefer hourly partitioning for detail. However, unless data reaches several billion rows per day, daily partitioning is usually optimal. Too many partitions will force the Postgres Query Planner to spend more time calculating, slowing down aggregate queries.

Indexes on Partition Columns are Mandatory

PostgreSQL features Partition Pruning to scan only the necessary child tables. But if you forget to index the log_date column, the system still has to perform a Full Scan on those child tables. In this case, query times can easily jump from 50ms to 5s.

Notes on Unique Constraints

This is a weakness of Partitioning. A Unique Index must contain the partition column. If you want the id column to be unique across all partitions, you must declare it as UNIQUE(id, log_date).

Instead of switching to complex NoSQL solutions, combining PostgreSQL with pg_partman is an extremely cost-effective choice. It ensures data integrity (ACID) while helping your system scale professionally.

Share:
Tags: