Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?


Querying the Metrics Database


The metrics database uses the TimescaleDB extension to efficiently manage time series, providing additional functions for data aggregation and rollup.

In this chapter we will introduce some of the TimescaleDB functions commonly used to retrieve time series data, as well as the database objects and fields of the metrics database and their usage within an SQL query.

Basic TimescaleDB Functions

The time_bucket function in TimescaleDB allows you to customize the granularity of an underlying dataset. This function accepts arbitrary time intervals as well as optional offsets and returns the bucket start time.

As an example, suppose we have a table temperature_measurement:

Now if measurements are taken every 10 seconds, using time_bucket we can run aggregation functions on a coarser granularity. The query below returns the median value (50th percentile) of the temperature within 30-second buckets:


We recommend that you provide an alias for the field generated by time_bucket, different from the time column. In the above example, the alias 30s_bucket is used.

The same alias must also be used in the GROUP BY and ORDER BY clauses.

Further functions that can be used for advanced analytics queries are found under the heading Advanced analytic-queries in the TimescaleDB documentation.

Database Objects to Query

Each plugin in Paragon Active Assurance has:

  • one view for tests
  • five views for monitors with metrics aggregated at different granularities: 10 seconds (raw), 1 minute, 5 minutes, 30 minutes, and 60 minutes.

The naming convention for the view objects is the following:

  • vw_(monitor|test)_metrics_<plugin_name> for raw data
  • vw_monitor_metrics_minute<minutes>_<plugin_name> for predefined time buckets on raw data for monitoring.

As an example, the HTTP plugin has the following database objects that can be queried:

Each view contains two categories of information:

  • measurement
  • metric

The measurement category is needed to retrieve the information related to a task in a test or monitor, and it must be included in the WHERE clause of an SQL statement. Examples of fields in this category are:

The metric category consists of values of metrics retrieved at a given point in time.

The time column is common to all views and must be included in each query.

The columns holding specific measurements differ from one task to another, and they are usually part of the metrics table in the view definition.

For example, the vw_monitor_metrics_http view contains:

Example: Here is how to write a query for the median of the response time average for an HTTP task associated with "Measurement 1" (test or monitor name) using a 30-second time bucket between 2021-06-21 09:55:30+00 and 2021-06-21 09:57:00+00.