PostgreSQL Data Warehouse Implementation and Performance Optimization For Energy Companies

With smart grids replacing traditional energy grids in recent years, energy companies are facing a new challenge to efficiently manage and analyze the collected data. The use of sensors in smart grids has led to a surge in the data being collected. Due to its design, the classic relational Online Transaction Processing (OLTP) relational database management system (RDBMS) starts to become inefficient for queries against the RDBMS beyond a terabyte size. For data extraction efficiency on large volumes of data, OLAP along with data warehousing (DW) has become a popular solution. A Business Intelligence (BI) tools are often used on the top of DWs [1]. The adoption of big- data-driven technologies is still lagging among energy companies.In this paper, based on our previous research projects and research results [2]–[5], we show that, through an optimization process, a gain in performance that is up to 2800 times faster compared with that of the original OLTP DBMS using the same hardware and the same operating system is possible.The applied research project was funded by an NSERC grant and was conducted in 2020-2023 at Okanagan and Langara Colleges.