Speed up Grafana by auto-switching between different aggregations, using PostgreSQL
Learn how (and why) to speed up your Grafana drill downs, using PostgreSQL to allow "auto-switching" between aggregations, depending on the time interval you select.
The problem: Grafana is slow to load visualizations, especially for non-aggregated, fine-grained data
The Grafana UI is great for drilling down into your data. However, for large amounts of data with second, millisecond, or even nanosecond time granularity, it can be frustratingly slow and result in higher resource usage.
For example, take this graph of all New York City taxi rides during the month of January 2016:
One common workaround: instead of querying raw data and aggregating on the fly, you query and visualize data from aggregates of your raw data (e.g., one minute, one hour, or one day rollups).
For PostgreSQL data sources, we do this by aggregating data into views and querying those instead, and for TimescaleDB, we use continuous aggregates – think “automatically refreshing Postgres views” (for more see the continuous aggregates docs).
However, this often leads to several Grafana panels, each querying the same data aggregated at different granularities. For example, you might capture the same metric over time, but set up aggregates at various intervals, such as in minutely, hourly, and daily intervals.
This then requires 3 separate panels, one for each aggregated interval.
...but, what if we could use one universal panel that could “automatically” switch between minutely, hourly, daily, or any other arbitrary aggregations of our data, depending on the time period we’d like to query and analyze? This would speed up queries and use resources like CPU more efficiently.
Enter the PostgreSQL
UNION ALL function…
The solution: Use Postgres
When we use PostgreSQL as our Grafana data source, we can write a single query that allows us to automatically switch between different aggregated views of our data (e.g daily, hourly, weekly views, etc.) in the same Grafana visualization (!).
🔑 The key: we (1) use the
UNION ALL function to write separate queries to pull data with different aggregations, and (2) then use the
WHERE clause to switch the table (or continuous aggregate view) being queried, depending on the length of the time-interval selected (from either the timepicker, or by highlighting the time period in a graph).
This not only allows us to drill arbitrarily deep into our data, but also makes loading the data as efficient and fast as possible, saving time and CPU resources. (In Grafana, drilling into data is typically done by zooming in and out, highlighting the time period of interest in the graph as shown in the image below),
Try It Yourself: Implementation in Grafana & sample queries
To help you get up and running with
UNION ALL, I’ve put together a short step-by-step guide and a few sample queries (which you can modify to suit your project, app, and the metrics you care about).
We’ll use the use case of monitoring IoT devices, specifically taxis equipped with sensors. For reference, we’ll use a dataset that contains all New York City taxi ride activity for the month of January 2016, from the New York Taxi and Limousine Commission (NYC TLC).
- TimescaleDB instance (Timescale Cloud or self-hosted) running PostgreSQL 11+
- Grafana instance (cloud or self-hosted)
- TimescaleDB instance connected to Grafana (see this tutorial for more)
- To load the sample dataset into TimescaleDB, complete Mission 1 in this tutorial, which takes you through downloading the .CSV file and inserting the data into the database.
- Use the queries below to create 2 continuous aggregates. These will be the aggregate views we switch between in our Grafana visualization:
To create daily aggregates:
This computes a roll up of the total number of rides taken during each day during the time-period of our data (January 2016).
To create hourly aggregates:
This computes a roll up of the total number of rides taken during each hour during the time-period of our data.
For more on how continuous aggregates work, see these docs.
Example 1: Auto-switch between daily aggregate, hourly aggregate, and raw data
In the example below, we have a query using
UNION ALL, where we only select a specific table or view, depending on the length of time selected interval in the Grafana UI (controlled by the
$__timeTo macros in Grafana).
As the comments in the code below show, we use daily aggregates for intervals greater than 14 days, hourly aggregates for intervals between 3 and 14 days, and per minute aggregates calculated on the fly from raw data for intervals less than 3 days:
Switching between daily aggregation, hourly aggregation and minutely aggregations on raw data
This produces the following behavior in our Grafana panels:
Querying daily aggregates for intervals greater than 14 days:
Querying hourly aggregates for intervals between 3-14 days:
Querying raw data for intervals less than 3 days:
This allows you to automatically switch between different aggregations of data, depending on the length of the time interval selected. Notice how the granularity of the data gets richer as we drill down from looking at data over the month of January to looking at data in a single day:
Example 2: Auto-switch between daily, hourly, and 10 minute aggregates
Querying only from continuous aggregates allows us to speed up our dashboards even further. You might not want to directly query the hypertable that houses your raw data, as the queries may be slower, due to things like new data being inserted into the hypertable.
The following example shows a query for switching between aggregations of different granularity without using the raw data hypertable at all (unlike Example 1, which does on-the-fly rollups of raw data).
First, let’s create 10 minute rollups of the raw data:
Switching between daily aggregation, hourly aggregation, and minute aggregations (no raw data involved)
In this post, we saw how to use
UNION ALL to automatically switch which aggregate view we’re querying on based on the time interval selected, so that we can do more efficient drill downs and make Grafana faster
You can find more information about the
UNION ALL function and how it works in this PostgreSQL tutorial - from the aptly named PostgreSQLtutorial.com - and “official” PostgreSQL documentation.
That’s it! You can modify this code to change the aggregates you query and time intervals, as well as the metrics you want to visualize, to suit your needs and projects.
In this tutorial, we learned how to use PostgreSQL
UNION ALL to solve a common Grafana issue: slow loading dashboards when we want to query fine-grained raw data (like millisecond performance metrics).
The result: you create graphs that enable you to automatically switch between different aggregations of your data. This allows you to drill down into your metrics as quickly as possible, saving time and CPU resources!
Want more Grafana tips? Explore our Grafana tutorials (I recommend this one on variables and this one on visualizing missing data).
Need a database to power your dashboarding and data analysis? Get started with Timescale Cloud (it’s our fast, easy-to-use, and reliable cloud-native data platform for time-series built on PostgreSQL,). When you sign up, you’ll get 30 days of completely free use to get you and up and running.