Partitioning large tables in PostgreSQL can significantly improve query performance and manageability by dividing data into smaller, more manageable chunks. This post will guide you through setting up partitioning using the pg_partman extension.

Why Partitioning?

Partitioning is useful when dealing with large datasets, such as logging, analytics, or audit records. By breaking a table into partitions based on a specific column (e.g., created_at), PostgreSQL can speed up queries by scanning only the relevant partitions.

Installing pg_partman

First, ensure the pg_partman extension is installed in your PostgreSQL instance:

CREATE EXTENSION pg_partman;

Creating the Audit Logs Table

We will partition an audit_logs table based on the created_at column. Before creating the table, we set up a sequence for the id column:

CREATE SEQUENCE audit_logs_id_seq;

Now, create the parent table with partitioning enabled:

CREATE TABLE public.audit_logs ( "id" int8 NOT NULL DEFAULT nextval('audit_logs_id_seq'::regclass), "action" varchar NOT NULL, "actor_id" int8, "actor_type" varchar, "record_id" int8, "record_type" varchar, "payload" text, "request" text, "created_at" timestamp NOT NULL, "updated_at" timestamp, "is_admin_event" bool DEFAULT false, "uuid" varchar, "history" hstore, PRIMARY KEY ("id", "created_at") ) PARTITION BY RANGE (created_at);

Enabling Partitioning with pg_partman

We now configure pg_partman to automatically create partitions for the audit_logs table based on the created_at timestamp.

Creating the Parent Partition

To enable partitioning, run:

SELECT partman.create_parent(p_parent_table := 'public.audit_logs', p_control := 'created_at', p_type := 'native', p_interval := '1 mon',  p_premake := 12);

How It Works

• p_parent_table → The name of the table to partition (audit_logs).
• p_control → The column used for partitioning (created_at).
• p_type → The partitioning method (native, meaning built-in PostgreSQL partitioning).
• p_interval → The partitioning interval (1 month in this case).
• p_premake → Number of future partitions to create in advance (12 months).

Differences Across pg_partman Versions

During implementation, I noticed that different versions of pg_partman have slightly varying parameters for creating partitions. Some older versions used different argument names or required additional parameters. If you encounter issues while following this guide, check your installed pg_partman version and refer to its official documentation for the correct parameter list.

Automating Partition Maintenance

To ensure new partitions are created automatically and old ones are cleaned up, we need to run the pg_partman maintenance task regularly. The easiest way is by adding a cron job:

psql DATABASE_URL -c 'SELECT partman.run_maintenance();'

This command should be scheduled to run periodically (e.g., daily) to maintain partition management.

Conclusion

Partitioning with pg_partman makes managing large tables in PostgreSQL much easier by automatically creating and maintaining partitions. This approach significantly improves query performance and simplifies data retention management.