Scaling Postgresql for managing over Α billion iot measurements
- Marios Kotoulas
- DIGITAL TRANSFORMATION
- 10 min read
The present digital landscape driven by the Internet of Things (IoT), is characterized by vast volume of data generated by million devices around the world. Environmental data is a prime example of this reality. The need to continuously gather and analyze the everchanging environmental data from various sensors around the globe, introduces new challenges to the Information Technology industry. Draxis, as a cutting-edge environmental IT company could not be excluded from these challenges. Join us as we explore the specific problem and the solution proposed and implemented by our experts.
Identifying the Challenge
As we mentioned, the overall goal is to create the infrastructure to gather and analyze large volumes of real-time data gathered from IoT devices.
The specific needs that will need to be addressed are:
- Support for efficient and complex queries for analytics and insights.
- Support for efficient handling of geospatial data and the ability to perform location-based analysis.
- Ensure scalability, performace and easy maintenance.
Potential Solutions
- Time-Series Databases
Databases specialized in time-series data with support for data retention policies, crucial for IoT data management. However, the main disadvantages of this solution are the limitations in handling complex queries outside of time-series data and limited support for geospatial data management.
- NoSQL Databases
Very flexible databases in terms of schema design, very efficient in horizontal scalability. The main disadvantages are the fact that they may not be as efficient for complex queries compared to relational databases and limited support for geospatial data management.
- Relational Databases
The most well-know and used type of databases, they support complex queries and are well-established technologies. Their main diasadvantages consist of the facts that they may not be very efficient in scalability with large volumes of data and data retention policies may impact performance.
Our selected approach (PostgreSQL + extensions)
As a company we use mainly PostgreSQL for database solution. The fact that, along with PostGIS extension, PostgreSQL is perfect for storing and manage geospatial data makes it our first choice for any project since most of them require some geospatial calculation. With this in mind, we investigated the posibility to use PostgreSQL and find ways to mitigate the disadvantages of the relational databases mentioned above. This was made possible with the method of table partitioning which is supported by PostgreSQL. Let’s try to break down what exactly table partitioning is and its implementation.
Table Partitioning
Database partitiong is a powerful feature to handle large volumes of data within a database, by dividing large tables into smaller, more managable segments.
Table partitions are based on specific criteria which are:
- Range of values, based on specific ranges of values.
- Lists, based on specific values.
- Hashes, when there is not a clear pattern used to ditribute data.
- Combination of the above
Our case
We needed a range type partitioning, since our main goal was to distribute data based on the IoT device measurement datetime. Below is an example of this kind of partitioning implementation using PostgreSQL SQL editor.
-- CREATE A PARTITIONED BY RANGE TABLE
CREATE TABLE MEASUREMENTS_PARTITIONED (
MEASURED_DATE DATE NOT NULL,
VALUE DECIMAL
) PARTITION BY RANGE (MEASURED_DATE);
-- CREATE partitions for each month
CREATE TABLE measurements_january PARTITION OF measurements_partitioned FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE measurements_february PARTITION OF measurements_partitioned FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
Postgresql Partitiong Benefits
With this implementation the measurements are distributed in different partitions according to their measurement date. The system knows that in order to find a measurement in a specific month, only the specific partition should be accessed. Moreover, data retention is simpler. For example, in order to delete or archive old measurements there is no need to access a large table, which will be time-consuming and will impact the performance of the system for the time the procedure is taking place. Since old measurements are in their specific partitions only these will be deleted or archived without any impact on performance.
To sum up the benefits of partitiong are:
- Improved query performance, reduce the amount of dta that need scanning for each query.
- Streamlined Maintenance, archiving and purging become more efficient.
- Scalability, partitioning offers a scalable solution for large datasets.
Is there an easier way?
Partitioning is a great techique with major advantages, but adds maintenance overhead. All partitions should be created in advance (imagine a measurement for March is inserted but the partition for this date range is not yet created.). All retention policies should be scheduled periodically and there must be continuously overlook of the system. It would be great if there was a tool that offers this maintenance procedures automatically after it is properly configured. Well, we are lucky there is such a tool as a PostgreSQL extension, introducing pg_partman.
Installation requirements:
PostgreSQL >= 14
pg_partman has a number of possible settings that handle the maintenance of table partitioning. Below we present an example along with explanation for the most useful settings.
SELECT partman.create_parent(
P_parent_table := 'partman.measurements'
P_control := 'measured_at'
P_interval := '1 day'
P_premake := 4
P_template_table := 'partman.measurements_table_template'
P_retention := '1 month'
P_retention_schema = 'partman_archived'
The above command creates the partitions for table measurements using date range (daily) for the column ‘measured_date’. Let’s explain the settings included in the above command.
- p_control: The column by which the partitoning will be implemented.
- p_interval: The date range of the partitioning.
- p_premake: The number of partitions to stay ahead. For this example, partman will create partitions for 4 days after the last measurement.
- p_retention: The data retention policy. Any measurement older than one month will be moved to the retention schema.
- p_retention_schema: The retention schema name mentioned above.
Conclusion
The solution of table partitioning helped us mitigate all potential problems of relation databases, and more specific PostgreSQL, regarding the handling of large volumes of time-based data including geospatial information, data gathered from various IoT devices gathering environmental insight.
The knowledge obtained will be used in the future to accomodate the various implementations which rely heavily on IoT device data. The various dashboards and graphical representation of this kind of data depends on the ability to process and perform complex queries on IoT data. With the presented solution we can give valuable insight in our users and help them in decision making, by organizing and presenting large amounts of data with a way that is human readable.
We will continue to search for ways to further optimize our solution, for any enquiry, suggestion, question and discussion regarding this topic in general, don’t hesitate to get in touch with us. We love talking technology!
