92x faster queries: How Open Electricity uses ClickHouse to track Australia’s energy transition in real time

Open Electricity, an open platform providing live Australian energy data, has significantly improved its data processing capabilities by migrating from Postgres and TimescaleDB to ClickHouse, achieving a 92x increase in query speed. This transition allows Open Electricity to efficiently manage around 1 billion records, generated every five minutes from Australia's National Electricity Market and the Wholesale Electricity Market. The migration not only reduced hardware requirements but also facilitated a more seamless handling of complex queries, particularly those involving market and generation data. ClickHouse's column-store architecture and specialized engine types, like ReplacingMergeTree, have been instrumental in optimizing data aggregation and query performance. This new setup enables real-time data accessibility through a public API, supporting around 300 companies and institutions. The migration process involved running the old and new systems in parallel, allowing for careful traffic management and ensuring consistent API outputs. Challenges such as deduplication, timezones, and memory management were navigated, highlighting the intricacies of handling large-scale energy data. The combination of Postgres for relational data and ClickHouse for analytical workloads has proven to be highly effective, empowering Open Electricity to deliver detailed insights into Australia's dynamic energy grid.