PostgreSQL Time-based Partitioning for IoT Data using pg_partman

This blog continues the discussion from my previous post on IoT Solution‘s scalability for IoT workloads where I discussed how declarative partitioning in PostgreSQL 10 can help achieve scalability. While native declarative partitioning is a good start, the experience of creating and maintaining the same partitions I did in my last post becomes much more fun with pg_partman.

pg_partman is an extension to create and manage both time-based and serial-based table partition sets. Native partitioning in PostgreSQL 10 is supported as of pg_partman v3.0.1. It is important to note that all the features of trigger-based partitioning are not yet supported in native, but performance in both reads and writes is significantly better. Since Postgres-BDR runs as an extension on PostgreSQL, we can enjoy all features of PostgreSQL 10 and pg_partman in Postgres-BDR as well.

pg_partman works as an extension, and we can install it directly on top of PostgreSQL and Postgres-BDR without having to change much. Let’s take a look to see how pg_partman improves overall partition creation and partition management experience.

CREATE SCHEMA iot;

Load pg_partman extension

CREATE EXTENSION pg_partman WITH SCHEMA iot;

Let’s partition iot_temperature_sensor_data table by creating monthly partitions

SELECT partman.create_parent('iot.iot_temperature_sensor_data','ts','native','monthly');

By default, pg_partman will create one time-partition for current month, four partitions of past four months and four more partitions for four months in the future. You can tweak around p_start_partition and p_premake if you need to override those settings for cases where you possibly more partitions for backfilling old data or need to create more partitions into the future respectively.

\d+ iot.iot_temperature_sensor_data
                                     Table "iot.iot_temperature_sensor_data"
  Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
-----------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 ts        | timestamp without time zone |           |          |         | plain    |              | 
 device_id | text                        |           |          |         | extended |              | 
 reading   | double precision            |           |          |         | plain    |              | 
Partition key: RANGE (ts)
Partitions: iot_temperature_sensor_data_p2018_03 FOR VALUES FROM ('2018-03-01 00:00:00') TO ('2018-04-01 00:00:00'),
            iot_temperature_sensor_data_p2018_04 FOR VALUES FROM ('2018-04-01 00:00:00') TO ('2018-05-01 00:00:00'),
            iot_temperature_sensor_data_p2018_05 FOR VALUES FROM ('2018-05-01 00:00:00') TO ('2018-06-01 00:00:00'),
            iot_temperature_sensor_data_p2018_06 FOR VALUES FROM ('2018-06-01 00:00:00') TO ('2018-07-01 00:00:00'),
            iot_temperature_sensor_data_p2018_07 FOR VALUES FROM ('2018-07-01 00:00:00') TO ('2018-08-01 00:00:00'),
            iot_temperature_sensor_data_p2018_08 FOR VALUES FROM ('2018-08-01 00:00:00') TO ('2018-09-01 00:00:00'),
            iot_temperature_sensor_data_p2018_09 FOR VALUES FROM ('2018-09-01 00:00:00') TO ('2018-10-01 00:00:00'),
            iot_temperature_sensor_data_p2018_10 FOR VALUES FROM ('2018-10-01 00:00:00') TO ('2018-11-01 00:00:00'),
            iot_temperature_sensor_data_p2018_11 FOR VALUES FROM ('2018-11-01 00:00:00') TO ('2018-12-01 00:00:00')

Time-based sub-partitions becomes easy too. Take a look:

SELECT partman.create_sub_parent('iot.iot_temperature_sensor_data','ts','native','daily',p_native_check :='yes',p_premake := 2);

This creates sub-partitions to store data on a single day basis.

SELECT tablename FROM pg_tables WHERE schemaname = 'iot' ORDER BY tablename;
                    tablename                     
--------------------------------------------------
 iot_temperature_sensor_data
 iot_temperature_sensor_data_p2018_03
 iot_temperature_sensor_data_p2018_03_p2018_03_01
 iot_temperature_sensor_data_p2018_04
 iot_temperature_sensor_data_p2018_04_p2018_04_01
 iot_temperature_sensor_data_p2018_05
 iot_temperature_sensor_data_p2018_05_p2018_05_01
 iot_temperature_sensor_data_p2018_06
 iot_temperature_sensor_data_p2018_06_p2018_06_01
 iot_temperature_sensor_data_p2018_07
 iot_temperature_sensor_data_p2018_07_p2018_07_15
 iot_temperature_sensor_data_p2018_07_p2018_07_16
 iot_temperature_sensor_data_p2018_07_p2018_07_17
 iot_temperature_sensor_data_p2018_07_p2018_07_18
 iot_temperature_sensor_data_p2018_07_p2018_07_19
 iot_temperature_sensor_data_p2018_08
 iot_temperature_sensor_data_p2018_08_p2018_08_01
 iot_temperature_sensor_data_p2018_09
 iot_temperature_sensor_data_p2018_09_p2018_09_01
 iot_temperature_sensor_data_p2018_10
 iot_temperature_sensor_data_p2018_10_p2018_10_01
 iot_temperature_sensor_data_p2018_11
 iot_temperature_sensor_data_p2018_11_p2018_11_01
(23 rows)

Let’s take a look at monthly partition to see how it is sub-partitioned.

\d+ iot_temperature_sensor_data_p2018_03
                                 Table "iot.iot_temperature_sensor_data_p2018_03"
  Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
-----------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 ts        | timestamp without time zone |           |          |         | plain    |              | 
 device_id | text                        |           |          |         | extended |              | 
 reading   | double precision            |           |          |         | plain    |              | 
Partition of: iot_temperature_sensor_data FOR VALUES FROM ('2018-03-01 00:00:00') TO ('2018-04-01 00:00:00')
Partition constraint: ((ts IS NOT NULL) AND (ts >= '2018-03-01 00:00:00'::timestamp without time zone) AND (ts < '2018-04-01 00:00:00'::timestamp without time zone))
Partition key: RANGE (ts)
Partitions: iot_temperature_sensor_data_p2018_03_p2018_03_01 FOR VALUES FROM ('2018-03-01 00:00:00') TO ('2018-03-02 00:00:00')

Partition maintenance: Creating future partitions

As discussed already in previous post, IoT time-based data is mostly append-only which means that more partitions are needed as data scales. PostgreSQL 10 does not provide any built in mechanism yet in terms of automatically creating future partitions. We need to do that manually.

One could do that by creating an SQL script to create more partitions and schedule it to run at appropriate interval and time. pg_partman makes it easy by providing maintenance functions. Again you can create a job to run at regular intervals to perform partition maintenance tasks but it looks much simpler with pg_partman.

SELECT run_maintenance(p_analyze := false);

Native partitioning can result in heavy locking and therefore it is recommended to set p_analyze to FALSE which will effectively disable analyze.

Partition maintenance: Dropping/expiring old partitions

We know that IoT data can grow at a quick pace and that very soon we need to archive old data. Again something that pg_partman makes very easy to do by explicitly setting retention period. For example, if we wanted old partitions to expire and drop after 6 months:

UPDATE partman.part_config SET retention_keep_table = false, retention = '6 month' 
WHERE parent_table = 'iot.iot_temperature_sensor_data';

Following this change, whenever:

SELECT run_maintenance(p_analyze := false);

runs again, it will take care of dropping partitions older than 6 months.

Doesn’t PostgreSQL’s native partitioning and pg_partman make it a fun experience to do partitioning especially if you compare it with partitioning exercise we did in my previous post? Discussion on IoT data and how PostgreSQL and Postgres-BDR can help continues. Stay tuned for more!