Set up the database

A service in Timescale is a cloud instance which contains your database. Each service contains a single database, named tsdb. You can connect to a service from your local system using the psql command-line utility. If you've used PostgreSQL before, you might already have psql installed. If not, check out the installing psql section.

This tutorial uses the energy consumption data for over a year in a typical household. You can use this data to analyze the energy consumption pattern.

Hypertables are the core of Timescale. Hypertables enable Timescale to work efficiently with time-series data. Because Timescale is PostgreSQL, all the standard PostgreSQL tables, indexes, stored procedures and other objects can be created alongside your Timescale hypertables. This makes creating and working with Timescale tables similar to standard PostgreSQL.

When you have your database set up, you can load the energy consumption data into the metrics hypertable.

Loading energy consumption data

Important

This is a large dataset, so it might take a long time, depending on your network connection.

1. Download the dataset:

   metrics.csv.gz

2. Use your file manager to decompress the downloaded dataset, and take a note of the path to the metrics.csv file.

3. At the psql prompt, copy the data from the metrics.csv file into your hypertable. Make sure you point to the correct path, if it is not in your current working directory:

   \COPY metrics FROM metrics.csv CSV;

   Copy

4. You can check that the data has been copied successfully with this command:

   SELECT * FROM metrics LIMIT 5;

   Copy

   You should get five records that look like this:

   created            | type_id | value 
   -------------------------------+---------+-------
    2023-05-31 23:59:59.043264+00 |      13 |  1.78
    2023-05-31 23:59:59.042673+00 |       2 |   126
    2023-05-31 23:59:59.042667+00 |      11 |  1.79
    2023-05-31 23:59:59.042623+00 |      23 | 0.408
    2023-05-31 23:59:59.042603+00 |      12 |  0.96

   Copy

Time-series data usually grows very quickly. And that means that aggregating the data into useful summaries can become very slow. Continuous aggregates makes aggregating data lightning fast.

If you are collecting data very frequently, you might want to aggregate your data into minutes or hours instead. For example, if you have a table of temperature readings taken every second, you can find the average temperature for each hour. Every time you run this query, the database needs to scan the entire table and recalculate the average every time.

Continuous aggregates are a kind of hypertable 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 hypertable behind the continuous aggregate is automatically updated in the background.

You don't need to manually refresh your continuous aggregates, they are continuously and incrementally updated in the background. Continuous aggregates also have a much lower maintenance burden than regular PostgreSQL materialized views, because the whole view is not created from scratch on each refresh. This means that you can get on with working your data instead of maintaining your database.

Because continuous aggregates are based on hypertables, you can query them in exactly the same way as your other tables, and enable compression or tiered storage on your continuous aggregates. You can even create continuous aggregates on top of your continuous aggregates.

By default, querying continuous aggregates 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.

The queries in this tutorial are suitable for visualizing in Grafana. If you want to visualize the results of your queries, connect your Grafana account to the energy consumption dataset.

You can use Grafana to visualize queries directly from your Timescale database.

Before you begin, make sure you have:

• Created a Timescale Cloud service.
• Installed a self-managed Grafana account, or signed up for Grafana Cloud.
• Found the connection details for the database you want to use as a data source. The details are contained in the cheatsheet you downloaded when you created the service.