In these industries, once the data has been captured it is often used for a variety of business purposes including;<\/p>\n
- \n
- Revenue Collection or Billing<\/li>\n
- Analysis e.g. Seasonal Trends, Demand Forecasting, Peak periods etc.<\/li>\n
- Reporting e.g. Ad-hoc Customer Enquiries, Supporting dispute resolution etc.<\/li>\n
- Auditing and compliance<\/li>\n<\/ol>\n
What is often downstream of this data is a billing system of some description that is used to transform this data into customer invoices or statements. It is quite often the case that in addition to the billing system there is also an analytics system.<\/p>\n
While the subject of this blog is specific to the Energy\/Electricity industry, the technology we will showcase is equally capable of handling workloads across many industries, including Healthcare, Manufacturing, Monitoring, FSI and others.<\/p>\n
If you want to see how we helped a number of Energy industry companies modernize their data platforms to provide performance, scalability and simplified data management for these ever-growing datasets then please read on.<\/p>\n
What is Azure Cosmos DB for PostgreSQL :<\/h3>\n
Even today we often hear \u201crelational databases can\u2019t scale\u201d and \u201cyou can\u2019t run OLTP and Analytics on the same database\u201d \u2026. whilst those statements may have come from an era where CPUs were few and storage was painfully slow, today with Azure Cosmos DB for PostgreSQL service those statements no longer hold true.<\/p>\n
Azure Cosmos DB for PostgreSQL is a fully managed Azure Database as a Service (DBaaS) where you leave the heavy lifting to us and focus on Applications and Data. More details are in the following link Introduction\/Overview – Azure Cosmos DB for PostgreSQL<\/a> but here is a summary;<\/p>\n
- \n
- Is built with Open Source PostgreSQL and is powered by the Open Source Citus extension<\/li>\n
- Scales to 1000s of vCores and Petabytes of data for a single PostgreSQL \u2018cluster\u2019 that can handle the most of demanding of workloads<\/li>\n
- Is ideal for a number of uses cases such as high throughput transactional, real-time analytics, multi-tenant SaaS and others<\/li>\n<\/ul>\n
We have seen some great articles showcasing the power of this offering when it comes to large scale data requirements such as Petabyte scale analytics and IoT. In particular, these two blogs explain how we Architected petabyte-scale analytics<\/a> internally at Microsoft;<\/p>\n
![\"Figure](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/thumbnail-image-3-of-blog-post-titled.png\")
<\/p>\nand how building IoT applications with Azure Cosmos DB for PostgreSQL, formerly Hyperscale(Citus) Building IoT apps using Postgres, Citus, and Azure<\/a> is a popular choice given the data involved and the typical volumes;<\/p>\n
![\"Figure](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/thumbnail-image-2-captioned-figure-1-a-reference.png\")
<\/p>\nIn terms of benchmarks, Azure Cosmos DB for PostgreSQL is capable of some very impressive performance. We have seen that it also costs significantly less than other distributed PostgreSQL offerings. The full details are here Distributed PostgreSQL benchmarks using HammerDB, by GigaOM – Azure Cosmos DB Blog (microsoft.com)<\/a>, but to summarize, it was able to perform many times better than the competition at a fraction of the cost;<\/p>\n
![\"Figure](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/a-screenshot-of-a-graph-description-automatically.png\")
<\/p>\n![\"Figure](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/a-graph-of-a-graph-description-automatically-gene.png\")
<\/p>\nHopefully you are hanging in there, and you can see that we have a service that is capable of amazing performance, scale and cost effectiveness.<\/p>\n
Background:<\/h3>\n
Billing data is considered business critical by most organisations, as it is the final step in revenue realization. It makes sense that the data that was used to generate the billing information should also be categorized as such. This is where the concept of \u2018Single Source of Truth\u2019 is often heard and this data is typically stored in a transactional database given its criticality.<\/p>\n
In the Australian Electricity Market, metering data is mainly distributed as CSV files and the format of those files is specified by the Australian Energy Market Operator (AEMO). The format specification is called \u2018NEM12\u2019 and is in a structured format. Here is a snippet of such a file. Notice that \u2018300\u2019 Lines \u2018Interval Data Record\u2019 are readings for the given day;<\/p>\n
![\"Figure](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/a-screenshot-of-a-computer-description-automatica.png\")
<\/p>\nThe above screenshot was taken from this document NEM12 Fact Sheet (yurika.com.au)<\/a>. For a more detailed understanding of the file format, you can checkout this document Meter data file format specification nem12 & nem13 (aemo.com.au)<\/a>.<\/p>\n
What typically happens is that this data is read from these CSV files, often by some sort of scripting language such as python, and uploaded to a database for storage and further processing. Many organisations rely on this metering data daily. For example, retailers, distributors and generators often store this data in a relational database, often MS SQL or Oracle. This \u2018metering\u2018 database is typically seen as the \u2018Single Source of Truth\u2019 for this data and as such it often underpins most billing systems and downstream revenue processes.<\/p>\n
This type of metering data is not strictly IoT or Time Series, but almost a hybrid of the two. As we will see throughout this article, Azure Cosmos DB for PostgreSQL has some pretty compelling features that make operations and management of this data very straightforward.<\/p>\n
One of the key requirements of metering data in this context is that often there can be data that is \u2018restated\u2019, which basically means, that it can be updated over time. For example, it is possible that an attempt to read a meter fails for any one of a variety of reasons, such as a communication error. In this situation it is common that \u2018Substitute\u2019 data is provided instead. After some time, the meter will eventually be read and the \u2018Actual\u2019 data will be supplied. There are 2 likely options to handle any data updates;<\/p>\n
- \n
- \n
- \n
- Modify the records in the database for the updated data<\/li>\n
- Insert the updated records and handle the \u201cduplicates\u201d in the application or through database management tasks such as stored procedures<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
Typical requirements for such metering systems are;<\/p>\n
- \n
- \n
- \n
- Scalable ingestion<\/li>\n
- Low latency ad-hoc queries<\/li>\n
- Analytics such as aggregations<\/li>\n
- Individual meter reporting<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
Azure Cosmos DB for PostgreSQL to the Rescue:<\/p>\n
Back to our \u2018NEM12\u2019 data. I have created the following standard PostgreSQL table as an example of one way to store it;<\/p>\n
create table meter_data (<\/span><\/p>\n
nmi varchar(10) not null,<\/span><\/p>\n
uom varchar(5) not null,<\/span><\/p>\n
interval_date date not null,<\/span><\/p>\n
interval_val numeric[],<\/span><\/p>\n
quality varchar(3) not null,<\/span><\/p>\n
nmi_num bigint not null,<\/span><\/p>\n
update_datetime timestamp not null,<\/span><\/p>\n
primary key (nmi, interval_date, quality, update_datetime)<\/span><\/p>\n
) partition by range (interval_date);<\/span><\/p>\n
Table details;<\/p>\n
- \n
- \n
- \n
- \u2018nmi\u2019 is the meter device ID.<\/li>\n
- \u2018interval_val\u2019 is a native PostgreSQL numeric array type, which caters for any number of values such as 30 minute intervals (48 values) or 5 minutes intervals (288 values) without the need to modify the table structure to cater for a changing number of intervals.<\/li>\n
- The primary key includes the \u2018update_datetime\u2019 column to support \u2018restated\u2019 data for an already existing \u2018nmi\u2019 and \u2018interval_date\u2019.<\/li>\n
- PostgreSQL native partitioning has been used to partition the underlying table by \u2018interval_date\u2019 on a monthly basis<\/li>\n
- It is also an option to store the interval values as individual rows but that would lead to significantly more rows in your database for no real gain and perhaps an increased management overhead<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
With this standard PostgreSQL table, we can also make use of the Azure Cosmos DB for PostgreSQL User Defined Function (UDF) to simplify partition creation. Here is an example of a call to the UDF \u2018create_time_partitions\u2019 https:\/\/learn.microsoft.com\/azure\/cosmos-db\/postgresql\/reference-functions#create_time_partitions<\/a> that creates monthly partitions on the meter_data table from 2010 to the end of 2023;<\/p>\n
SELECT create_time_partitions(table_name:= ‘meter_data’,<\/span><\/p>\n
partition_interval:= ‘1 month’,<\/span><\/p>\n
end_at:= ‘2023-12-31’,<\/span><\/p>\n
start_from:= ‘2010-01-01’);<\/span><\/p>\n
In addition, PostgreSQL with the open source \u2018Citus\u2019 extension provides PostgreSQL with the super-power of distributed tables. It can be used to further split, or \u2018shard\u2019 your data across multiple compute nodes. For example, with a 4 node cluster, if you run this command to \u2018shard\u2019 your meter_data table by the \u2018nmi\u2019 column;<\/p>\n
select create_distributed_table(‘meter_data’, ‘nmi’);<\/span><\/p>\n
You would end up with something like this;<\/p>\n
![\"Figure](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/shardpart.png\")
<\/a><\/p>\nNot only have we partitioned by \u2018interval_date\u2019 we have also \u2018sharded\u2019 by \u2018nmi\u2019. And our meter_data table is now spread across 4 nodes. Each of these nodes has their own compute and storage and can operate independently of the other nodes in the cluster. Here is a link to a great article detailing the similarities and differences between partitioning and sharding in PostgreSQL Understanding partitioning and sharding in Postgres and Citus – Microsoft Community Hub<\/a><\/p>\n
It is possible to have 100s of nodes in a cluster allowing for truly massive scale to handle the largest of relational datasets and workloads. Amazingly, your application does not even have to know that your table has been sharded across multiple nodes. You still use standard PostgreSQL SQL commands and if they filter on the shard column, \u2018nmi\u2019 in this example, then they will be directed to the nodes that hold the required data. Once the node receives the query, it will have all the power of partitioning. All you have to do is run the \u2018create_distributed_table\u2019 UDF and the sharding is applied automatically as show in this illustration;<\/p>\n
![\"Figure](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/word-image-7320-7.png\")
<\/p>\nThe co-ordinator node of the cluster stores metadata that identifies which distribution column ranges are stored on which underlying worker node. As you can see, the transparent sharding and partitioning of tables across multiple cluster nodes sets your database up to take advantage of parallelism.<\/p>\n
Ingestion Scalability:<\/h3>\n
To demonstrate the scalability of the service, we created and ingested a small dataset into the meter_data table. We have 47GB of \u2018NEM12\u2019 like CSV files that consists of 10000 unique NMI with 10 years of daily data (5 minute intervals with 288 interval values per day). The resultant PostgreSQL table size is 46GB and contains 36.52 million rows (10,000 * 3652).<\/p>\n
What we are about to illustrate here is an example of one approach where files are ingested into the database in a batch fashion, however, there are many alternate approaches that could be considered such as Real-time data ingestion with Azure Stream Analytics – Azure Cosmos DB for PostgreSQL | Microsoft Learn<\/a>.<\/p>\n
For the tests below, the psql command was run from an Azure VM in the same Australia East region as the database server itself. Specs for the VM were as follows;<\/p>\n
- \n
- \n
- \n
- Standard D16as v5<\/li>\n
- 16 vcpus<\/li>\n
- 64GiB RAM<\/li>\n
- 1TB SSD with max 5000 IOPS<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
Data was loaded in a multi-threaded fashion using the Linux command line and the PostgreSQL \\COPY instruction (PostgreSQL: Documentation: 16: COPY<\/a>) via the standard PostgreSQL \u2018psql\u2019 utility;<\/p>\n
find . -type f | time xargs -n1 -P32 sh -c “psql -U citus -c \\”\\\\copy demo.meter_data from ‘\\$0’ with csv\\””;<\/span><\/p>\n
The following table shows 4 tests, each with a different cluster configuration, and the time taken to load the dataset;<\/p>\n
- \n
- \n
- \n
- \n
![\"Figure](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/a-diagram-of-a-workflow-description-automatically.png\")
<\/p>\n![\"Figure](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/word-image-7320-9.png\")
<\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/12\/query-perf-columnar.png\")
<\/a><\/p>\n