2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Haroon's PlanetPostgreSQL3 / PostgreSQL Time-based Partitioning for IoT Data using pg_partman
Muhammad Haroon

PostgreSQL Time-based Partitioning for IoT Data using pg_partman

July 24, 2018/0 Comments/in Haroon's PlanetPostgreSQL, Internet of Things /by Muhammad Haroon

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!

Tags: BDR, Internet of things, IOT, pg_partman, Postgres-BDR, PostgreSQL
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Postgres Installer – A Step by Step Guide to install PostgreSQL Talk slides: Partitioning Improvements in PostgreSQL 11
Scroll to top
×