In modern PostgreSQL deployments, especially those dealing with time-series, event logs, or high-ingest analytical workloads, managing large tables efficiently is essential for maintaining performance and scalability. While PostgreSQL offers native support for table partitioning, manually creating and maintaining partitions can quickly become tedious and error-prone, especially as your data grows.
This is where pg_partman (PostgreSQL Partition Manager) steps in.
pg_partman is a powerful, open-source PostgreSQL extension designed to automate and simplify the creation, maintenance, and management of both time-based and ID-based partitioned tables. It significantly reduces operational overhead by handling partition creation, constraint management, data migration, and retention policies, all without needing to write complex custom scripts.
In this comprehensive blog post, we’ll dive deep into the core capabilities of the pg_partman extension. You’ll learn how to:
- Set up pg_partman in your PostgreSQL environment.
- Automatically create and manage partitioned tables.
- Use the wide range of built-in functions to monitor, maintain, and optimize your partitions.
- Enhance query performance while reducing manual effort.
We’ll also walk through real-world SQL examples and provide practical usage tips for integrating pg_partman into your PostgreSQL workflow effectively.
Installing pg_partman
Check if the extension is available in your PostgreSQL
select * from pg_available_extensions where name = 'pg_partman';
If it is not listed, then install it using the command below.
sudo apt-get install postgresql-17-partman
-- Install the extension (as superuser)
CREATE EXTENSION pg_partman;
Verify the installation. I it is created, you can see the result like this
postgres=# \dx pg_partman
List of installed extensions
Name | Version | Schema | Description
------------+---------+--------+------------------------------------------------------
pg_partman | 5.2.4 | public | Extension to manage partitioned tables by time or ID
(1 row)
To see the available functions provided by the extension named pg_partman:
SELECT p.proname AS function_name,
pg_catalog.pg_get_function_identity_arguments(p.oid) AS arguments,
n.nspname AS schema_name
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_depend d ON d.objid = p.oid
JOIN pg_extension e ON e.oid = d.refobjid
WHERE d.deptype = 'e'
AND e.extname = 'pg_partman'
ORDER BY p.proname;
Creating Partitioned Tables with pg_partman
Step 1: Create a new schema for better identification
CREATE SCHEMA IF NOT EXISTS sales_partman;
Purpose: Create a schema to organize partitioning-related objects.
Why Create This?: Schemas provide a namespace to group tables and avoid clutter in the public schema. The GitHub example uses 'partman_test' for organization, and we use 'sales_partman' to keep your partitioning objects separate.
-- Step 2: Create Partitioned Parent Table
CREATE TABLE sales_partman.sales_data (
id INTEGER NOT NULL,
sale_date DATE NOT NULL,
region_id INTEGER NOT NULL,
customer_name TEXT,
amount NUMERIC,
PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);
Purpose: Create the parent table for partitioning, which will route data to child partitions based on sale_date.
Why Create This?: The parent table is the main interface for queries (e.g., INSERT, SELECT). It must be partitioned with 'PARTITION BY RANGE' to define how data is split (by sale_date ranges). This addresses your earlier error where a non-partitioned table caused 'create_parent' to fail.
-- Step 3: Create Index on Parent Table
CREATE INDEX sales_data_sale_date_idx ON sales_partman.sales_data (sale_date);
Purpose: Add an index on sale_date to improve query performance.
-- Step 4: Create Template Table
CREATE TABLE sales_partman.sales_data_template (
LIKE sales_partman.sales_data
);
ALTER TABLE sales_partman.sales_data_template ADD PRIMARY KEY (id, sale_date);
Purpose: Create a template table to define the structure, constraints, and indexes for child partitions.
Why Create This?: The template table ensures child partitions (e.g., sales_data_p20250807) inherit the correct structure, primary key, and any additional constraints or indexes. The GitHub example uses a template to apply a primary key, and we do the same to maintain consistency.
-- Step 5: Create Sequence for ID
CREATE SEQUENCE IF NOT EXISTS sales_partman.sales_data_id_seq
OWNED BY sales_partman.sales_data_template.id;
ALTER TABLE sales_partman.sales_data_template
ALTER COLUMN id SET DEFAULT nextval('sales_partman.sales_data_id_seq'::regclass);
Purpose: Create a sequence for generating unique ID values, as partitioned tables don’t support SERIAL.
Why Create This?: Your original table used SERIAL for ID, but partitioned tables require a manual sequence. The sequence is linked to the template table, so child partitions inherit the default for auto-incrementing IDs.
-- Step 6: Configure Partitioning with pg_partman
SELECT partman.create_parent(
p_parent_table := 'sales_partman.sales_data',
p_control := 'sale_date',
p_type := 'range',
p_interval := '1 day',
p_template_table := 'sales_partman.sales_data_template',
p_premake := 100
);
Purpose: Set up daily partitions for sales_data using pg_partman.
Why Create This?: This creates child partitions (e.g., sales_data_p20250807) for each day, sets up triggers to route data, and configures maintenance. It addresses your earlier errors (e.g., 'daily' not supported, no partitions found) by using '1 day' and creating partitions before inserting data.
Why This Function?: 'partman.create_parent' is a pg_partman function that automates partition creation and management. Parameters:
- - p_parent_table: The pre-partitioned table (must have PARTITION BY RANGE).
- - p_control: The partitioning column (sale_date).
- - p_type: 'range' for range-based partitioning.
- - p_interval: '1 day' for daily partitions (corrected from your earlier 'daily').
- - p_template_table: Specifies the template for child partition structure.
- - p_premake: Creates 100 days of partitions before and after the current date to cover your ~100-day data range.
-- Step 7: Insert Sample Data
INSERT INTO sales_partman.sales_data (id, sale_date, region_id, customer_name, amount)
SELECT
nextval('sales_partman.sales_data_id_seq'::regclass) AS id,
CURRENT_DATE - (random() * 100)::int AS sale_date,
(random() * 10)::int + 1 AS region_id,
md5(random()::text) AS customer_name,
round((random() * 1000)::numeric, 2) AS amount
FROM generate_series(1, 100000);
This tests the partitioning setup by inserting realistic data, ensuring rows are routed to the correct child partitions based on sale_date. It must run after create_parent to avoid the 'no partition found' error you encountered.
-- Step 8: Verify Partitioning
\dt sales_partman.*
SELECT tableoid::regclass, count(*)
FROM sales_partman.sales_data
GROUP BY tableoid::regclass
ORDER BY tableoid::regclass;
Purpose: Confirm that partitions were created and data is distributed correctly.
Below is a categorized list of essential pg_partman functions along with their purposes.
Partition Creation & Configuration
Converts a regular table into a partitioned parent table based on time or ID.
Converts a child partition into a sub-parent for nested partitioning.
Manually creates a time-based partition for a specific date.
Manually creates an ID-based partition up to a certain value
Data Migration into Partitions
Migrates existing time-based data into their corresponding partitions.
Migrates existing ID-based data into appropriate ID partitions.
Detects and fills any gaps in partition ranges (useful after downtime).
Reverting Partitioning
Reverses partitioning, converting a partitioned table back into a normal table.
Partition Cleanup & Lifecycle Management
Drops a specific time-based partition.
Performs automatic maintenance: creates new future partitions and drops expired ones.
Constraint & Privilege Management
- reapply_constraints_proc()
Reapplies constraints (e.g., primary keys, uniqueness) across all child partitions.
Applies inherited constraints to all partitions manually.
Removes constraints from partitions, typically for performance or bulk operations.
Applies the parent table's privileges to all child partitions.
Monitoring & Metadata
Lists all child partitions under a parent table.
Displays the current configuration and status of a partition set.
Predicts the name of a future partition based on a given value.
Utility Functions
- dump_partitioned_table_definition()
Generates the SQL definition for a partitioned table, useful for migrations or backups.
- inherit_template_properties()
Ensures that all child partitions inherit properties from the template table.
- uuid7_time_encoder() / uuid7_time_decoder()
Converts between timestamps and UUIDv7 format for use in modern, time-ordered UUID scenarios.pg_partman is an indispensable tool for any PostgreSQL DBA or developer handling large datasets. It not only simplifies partitioning setup but also automates day-to-day maintenance, enforces best practices, and provides tools for visibility and control.
Whether you're dealing with log data, time-series metrics, or event streams, pg_partman ensures PostgreSQL scales reliably without compromising performance. Start integrating it into your database infrastructure today to enjoy the benefits of efficient partition management with minimal overhead.