Managing PostgreSQL partitions with the pg_partman extension

PostgreSQL table partitioning provides a framework for high-performance handling of data input and reporting. Use partitioning for databases that require very fast input of large amounts of data. Partitioning also provides for faster queries of large tables. Partitioning helps maintain data without impacting the database instance because it requires less I/O resources.

By using partitioning, you can split data into custom-sized chunks for processing. For example, you can partition time-series data for ranges such as hourly, daily, weekly, monthly, quarterly, yearly, custom, or any combination of these. For a time-series data example, if you partition the table by hour, each partition contains one hour of data. If you partition the time-series table by day, the partitions holds one day's worth of data, and so on. The partition key controls the size of a partition.

When you use an INSERT or UPDATE SQL command on a partitioned table, the database engine routes the data to the appropriate partition. PostgreSQL table partitions that store the data are child tables of the main table.

During database query reads, the PostgreSQL optimizer examines the WHERE clause of the query and, if possible, directs the database scan to only the relevant partitions.

Starting with version 10, PostgreSQL uses declarative partitioning to implement table partitioning. This is also known as native PostgreSQL partitioning. Before PostgreSQL version 10, you used triggers to implement partitions.

PostgreSQL table partitioning provides the following features:

You can't alter the schema for an individual partition. However, you can alter the parent table (such as adding a new column), which propagates to partitions.

Overview of the PostgreSQL pg_partman extension

You can use the PostgreSQL pg_partman extension to automate the creation and maintenance of table partitions. For more general information, see PG Partition Manager in the pg_partman documentation.

Instead of having to manually create each partition, you configure pg_partman with the following settings:

After you create a PostgreSQL partitioned table, you register it with pg_partman by calling the create_parent function. Doing this creates the necessary partitions based on the parameters you pass to the function.

The pg_partman extension also provides the run_maintenance_proc function, which you can call on a scheduled basis to automatically manage partitions. To ensure that the proper partitions are created as needed, schedule this function to run periodically (such as hourly). You can also ensure that partitions are automatically dropped.

Enabling the pg_partman extension

If you have multiple databases inside the same PostgreSQL DB instance for which you want to manage partitions, enable the pg_partman extension separately for each database. To enable the pg_partman extension for a specific database, create the partition maintenance schema and then create the pg_partman extension as follows.

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

If you receive an error such as the following, grant the rds_superuser privileges to the account or use your superuser account.

ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.

To grant rds_superuser privileges, connect with your superuser account and run the following command.

GRANT rds_superuser TO user-or-role;

For the examples that show using the pg_partman extension, we use the following sample database table and partition. This database uses a partitioned table based on a timestamp. A schema data_mart contains a table named events with a column named created_at. The following settings are included in the events table:

The following DDL statements create these objects, which are automatically included on each partition.

CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
        org_name TEXT,
        CONSTRAINT pk_organization PRIMARY KEY (org_id)  
    );

CREATE TABLE data_mart.events(
        event_id        BIGSERIAL, 
        operation       CHAR(1), 
        value           FLOAT(24), 
        parent_event_id BIGINT, 
        event_type      VARCHAR(25), 
        org_id          BIGSERIAL, 
        created_at      timestamp, 
        CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), 
        CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), 
        CONSTRAINT fk_orga_membership 
            FOREIGN KEY(org_id) 
            REFERENCES data_mart.organization (org_id),
        CONSTRAINT fk_parent_event_id 
            FOREIGN KEY(parent_event_id, created_at) 
            REFERENCES data_mart.events (event_id,created_at)
    ) PARTITION BY RANGE (created_at);

CREATE INDEX idx_org_id     ON  data_mart.events(org_id);
CREATE INDEX idx_event_type ON  data_mart.events(event_type);

Configuring partitions using the create_parent function

After you enable the pg_partman extension, use the create_parent function to configure partitions inside the partition maintenance schema. The following example uses the events table example created in Enabling the pg_partman extension. Call the create_parent function as follows.

SELECT partman.create_parent( p_parent_table => 'data_mart.events',
 p_control => 'created_at',
 p_type => 'native',
 p_interval=> 'daily',
 p_premake => 30);

The parameters are as follows:

For a complete description of the create_parent function, see Creation Functions in the pg_partman documentation.

Configuring partition maintenance using the run_maintenance_proc function

You can run partition maintenance operations to automatically create new partitions, detach partitions, or remove old partitions. Partition maintenance relies on the run_maintenance_proc function of the pg_partman extension and the pg_cron extension, which initiates an internal scheduler. The pg_cron scheduler automatically executes SQL statements, functions, and procedures defined in your databases.

The following example uses the events table example created in Enabling the pg_partman extension to set partition maintenance operations to run automatically. As a prerequisite, add pg_cron to the shared_preload_libraries parameter in the DB instance's parameter group.

CREATE EXTENSION pg_cron;

UPDATE partman.part_config 
SET infinite_time_partitions = true,
    retention = '3 months', 
    retention_keep_table=true 
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

Following, you can find a step-by-step explanation of the preceding example:

For a complete description of the run_maintenance_proc function, see Maintenance Functions in the pg_partman documentation.