Time-based table partitioning using pg partman

Table partitioning is splitting of a large table in smaller tables based on values of some attribute in the table. This post is about partitioning tables using pg_partman. It provides the following two types of table partitioning,

  • Time-based table partitioning
  • Serial id based partitioning. Follow below sections to do time-based table partitioning. I will be using the postgres database to describe the example

Installing and setting up pg_partman

git clone https://github.com/keithf4/pg_partman
cd pg_partman
make install
make NO_BGW=1 install

After running above commands successfully, pg_partman should be installed on your machine. running the command in step 4 will provide plain PL/PGSQL functions only. These functions can be used to implement partitioning logic.

  • create schema and extension for partman by logging into the postgreSQL database. I am using PostgreSQL interactive terminal to run following commands
create database test-partman;
\c test-partman;
create schema partman;
create extension pg_partman schema partman;

Creating a database table

create table users(
id bigserial primary key,
first_name varchar not null,
last_name varchar,
email varchar not null,
created_at timestamp without time zone not null,
updated_at timestamp without time zone not null
);

Creating a partition set

Let me demystify arguments passed to the above function.

  • p_parent_table is the name of the parent table, here users table is under the public schema. This argument is of type text.
  • p_control is the name of the column in the parent table. Partitioning is based on this column. As partman provides time/serial id based partitioning so data type of this column in the table must of time or integer type. This argument is of type text.
  • p_type is about the type of partitioning. Type of partitioning can of native or partman type. In this example, I have used the partman type. This argument is of type text.
  • p_interval is the time interval of each partition. In this example, I used ‘daily’ interval. so the child table will be created for each date. This argument is of type text.
  • p_automatic_maintenance is used to set the automatic maintenance when run_maintenance() is called. Maintenance is about the creation of new child tables and deleting older child tables based on retention period config. This argument is of type text.
  • p_debug turns on the printing the additional debugging information in the console. This argument is of type boolean

Setting the retention period

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

Running maintenance

select partman.run_maintenance(
p_parent_table := 'public.users',
p_debug := TRUE);

After following above sections successfully you should have implemented time-based partitioning. If you have any suggestions please let me know.

Product Engineer at Gojek.