Analyze energy consumption

Get insights from energy consumption data with Grafana and Tiger Cloud

Energy providers understand that customers tend to lose patience when there is not enough power for them to complete day-to-day activities. Task one is keeping the lights on. If you are transitioning to renewable energy, it helps to know when you need to produce energy so you can choose a suitable energy source.

Real-time analytics refers to the process of collecting, analyzing, and interpreting data instantly as it is generated. This approach enables you to track and monitor activity, make the decisions based on real-time insights on data stored in a and keep those lights on.

Grafana is a popular data visualization tool that enables you to create customizable dashboards and effectively monitor your systems and applications.

This page shows you how to integrate Grafana with a and make insights based on visualization of data optimized for size and speed in the .

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

• Install and run self-managed Grafana, or sign up for Grafana Cloud.

Optimize time-series data in hypertables

s are tables in that automatically partition your time-series data by time. Time-series data represents the way a system, process, or behavior changes over time. s enable to work efficiently with time-series data. Each is made up of child tables called chunks. Each chunk is assigned a range of time, and only contains data from that range. When you run a query, identifies the correct chunk and runs the query on it, instead of going through the entire table.

is the hybrid row-columnar storage engine in used by s. Traditional databases force a trade-off between fast inserts (row-based storage) and efficient analytics (columnar storage). eliminates this trade-off, allowing real-time analytics without sacrificing transactional capabilities.

dynamically stores data in the most efficient format for its lifecycle:

• Row-based storage for recent data: the most recent chunk (and possibly more) is always stored in the , ensuring fast inserts, updates, and low-latency single record queries. Additionally, row-based storage is used as a writethrough for inserts and updates to columnar storage.
• Columnar storage for analytical performance: chunks are automatically compressed into the , optimizing storage efficiency and accelerating analytical queries.

Unlike traditional columnar databases, allows data to be inserted or modified at any stage, making it a flexible solution for both high-ingest transactional workloads and real-time analytics—within a single database.

Because is 100% , you can use all the standard tables, indexes, stored procedures, and other objects alongside your s. This makes creating and working with s similar to standard .

1. Import time-series data into a

   1. Unzip metrics.csv.gz to a <local folder>.

      This test dataset contains energy consumption data.

      To import up to 100GB of data directly from your current based database, migrate with downtime using native tooling. To seamlessly import 100GB-10TB+ of data, use the live migration tooling supplied by . To add data from non- data sources, see Import and ingest data.

   2. In Terminal, navigate to <local folder> and update the following string with your connection details to connect to your .

      psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>?sslmode=require"
      

   3. Create an optimized for your time-series data:

      1. Create a with enabled by default for your time-series data using CREATE TABLE. For efficient queries on data in the , remember to segmentby the column you will use most often to filter your data.

         In your sql client, run the following command:

         CREATE TABLE "metrics"(
           created timestamp with time zone default now() not null,
           type_id integer                                not null,
           value   double precision                       not null
         ) WITH (
           tsdb.hypertable,
           tsdb.segmentby = 'type_id',
           tsdb.orderby = 'created DESC'
         );
         

         When you create a using CREATE TABLE … WITH …, the default partitioning column is automatically the first column with a timestamp data type. Also, creates a columnstore policy that automatically converts your data to the , after an interval equal to the value of the chunk_interval, defined through compress_after in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the conversion, s are compressed by up to 98%, and organized for efficient, large-scale queries.

         You can customize this policy later using alter_job. However, to change after or created_before, the compression settings, or the the policy is acting on, you must remove the columnstore policy and add a new one.

         You can also manually convert s in a to the .

   4. Upload the dataset to your

      \COPY metrics FROM metrics.csv CSV;
      

2. Have a quick look at your data

   You query s in exactly the same way as you would a relational table. Use one of the following SQL editors to run a query and see the data you uploaded:

   • : write queries, visualize data, and share your results in for all your s.
   • SQL editor: write, fix, and organize SQL faster and more accurately in for a .
   • psql: easily run queries on your s or deployment from Terminal.
   SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time",
   round((last(value, created) - first(value, created)) * 100.) / 100. AS value
   FROM metrics
   WHERE type_id = 5
   GROUP BY 1;
   

   On this amount of data, this query on data in the takes about 3.6 seconds. You see something like:

   Time	value
   2023-05-29 22:00:00+00	23.1
   2023-05-28 22:00:00+00	19.5
   2023-05-30 22:00:00+00	25
   2023-05-31 22:00:00+00	8.1

Write fast analytical queries

Aggregation is a way of combining data to get insights from it. Average, sum, and count are all examples of simple aggregates. However, with large amounts of data aggregation slows things down, quickly. s are a kind of that is refreshed automatically in the background as new data is added, or old data is modified. Changes to your dataset are tracked, and the behind the is automatically updated in the background.

By default, querying s provides you with real-time data. Pre-aggregated data from the materialized view is combined with recent data that hasn’t been aggregated yet. This gives you up-to-date results on every query.

You create s on uncompressed data in high-performance storage. They continue to work on data in the and rarely accessed data in tiered storage. You can even create s on top of your s.

1. Monitor energy consumption on a day-to-day basis

   1. Create a kwh_day_by_day for energy consumption:

      CREATE MATERIALIZED VIEW kwh_day_by_day(time, value)
         with (timescaledb.continuous) as
      SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time",
             round((last(value, created) - first(value, created)) * 100.) / 100. AS value
      FROM metrics
      WHERE type_id = 5
      GROUP BY 1;
      

   2. Add a refresh policy to keep kwh_day_by_day up-to-date:

      SELECT add_continuous_aggregate_policy('kwh_day_by_day',
         start_offset => NULL,
         end_offset => INTERVAL '1 hour',
         schedule_interval => INTERVAL '1 hour');
      

2. Monitor energy consumption on an hourly basis

   1. Create a kwh_hour_by_hour for energy consumption:

      CREATE MATERIALIZED VIEW kwh_hour_by_hour(time, value)
        with (timescaledb.continuous) as
      SELECT time_bucket('01:00:00', metrics.created, 'Europe/Berlin') AS "time",
             round((last(value, created) - first(value, created)) * 100.) / 100. AS value
      FROM metrics
      WHERE type_id = 5
      GROUP BY 1;
      

   2. Add a refresh policy to keep the up-to-date:

      SELECT add_continuous_aggregate_policy('kwh_hour_by_hour',
       start_offset => NULL,
          end_offset => INTERVAL '1 hour',
          schedule_interval => INTERVAL '1 hour');
      

3. Analyze your data

   Now you have made s, it could be a good idea to use them to perform analytics on your data. For example, to see how average energy consumption changes during weekdays over the last year, run the following query:

     WITH per_day AS (
      SELECT
        time,
        value
      FROM kwh_day_by_day
      WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year'
      ORDER BY 1
     ), daily AS (
         SELECT
            to_char(time, 'Dy') as day,
            value
         FROM per_day
     ), percentile AS (
         SELECT
             day,
             approx_percentile(0.50, percentile_agg(value)) as value
         FROM daily
         GROUP BY 1
         ORDER BY 1
     )
     SELECT
         d.day,
         d.ordinal,
         pd.value
     FROM unnest(array['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']) WITH ORDINALITY AS d(day, ordinal)
     LEFT JOIN percentile pd ON lower(pd.day) = lower(d.day);
   

   You see something like:

   day	ordinal	value
   Mon	2	23.08078714975423
   Sun	1	19.511430831944395
   Tue	3	25.003118897837307
   Wed	4	8.09300571759772

Connect Grafana to Tiger Cloud

To visualize the results of your queries, enable Grafana to read the data in your :

1. Log in to Grafana

   In your browser, log in to either:

   • Self-hosted Grafana: at http://localhost:3000/. The default credentials are admin, admin.
   • Grafana Cloud: use the URL and credentials you set when you created your account.

2. Add your as a data source

   1. Open Connections > Data sources, then click Add new data source.

   2. Select PostgreSQL from the list.

   3. Configure the connection:

      • Host URL, Database name, Username, and Password

        Configure using your connection details. Host URL is in the format <host>:<port>.

      • TLS/SSL Mode: select require.

      • PostgreSQL options: enable TimescaleDB.

      • Leave the default setting for all other fields.

   4. Click Save & test.

   Grafana checks that your details are set correctly.

Visualize energy consumption

A Grafana dashboard represents a view into the performance of a system, and each dashboard consists of one or more panels, which represent information about a specific metric related to that system.

To visually monitor the volume of energy consumption over time:

1. Create the dashboard

   1. On the Dashboards page, click New and select New dashboard.

   2. Click Add visualization, then select the data source that connects to your and the Bar chart visualization type in the top right.

      

   3. In the Queries section, select Code on the right, then run the following query based on your :

      WITH per_hour AS (
      SELECT
      time,
      value
      FROM kwh_hour_by_hour
      WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year'
      ORDER BY 1
      ), hourly AS (
       SELECT
            extract(HOUR FROM time) * interval '1 hour' as hour,
            value
       FROM per_hour
      )
      SELECT
          hour,
          approx_percentile(0.50, percentile_agg(value)) as median,
          max(value) as maximum
      FROM hourly
      GROUP BY 1
      ORDER BY 1;
      

      This query averages the results for households in a specific time zone by hour and orders them by time. Because you use a , this data is always correct in real time.

      

      You see that energy consumption is highest in the evening and at breakfast time. You also know that the wind drops off in the evening. This data proves that you need to supply a supplementary power source for peak times, or plan to store energy during the day for peak times.

2. Click Save dashboard

You have integrated Grafana with a and made insights based on visualization of your data.