heartbeat_agg() functions

heartbeat_agg(
    heartbeat TIMESTAMPTZ,
    agg_start TIMESTAMPTZ,
    agg_duration INTERVAL,
    heartbeat_liveness INTERVAL
) RETURNS HeartbeatAgg

This takes a set of heartbeat timestamps and aggregates the liveness state of the underlying system for the specified time range.

Examples

Given a table called system_health with a ping_time column, construct an aggregate of system liveness for 10 days starting from Jan 1, 2022. This assumes a system is unhealthy if it hasn't been heard from in a 5 minute window:

SELECT heartbeat_agg(
  ping_time,
  '01-01-2022 UTC',
  '10 days',
  '5 min')
FROM system_health;

Copy

dead_ranges(
    agg HEARTBEATAGG
) RETURNS TABLE (
    start TIMESTAMPTZ,
    end TIMESTAMPTZ
)

Given a heartbeat aggregate, this will return a set of (starttime, endtime) pairs representing when the underlying system did not have a valid heartbeat during the interval of the aggregate.

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, we can use the following to get the intervals where the system was down during the week of Jan 9, 2022:

SELECT dead_ranges(health)
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

dead_ranges                     
-----------------------------------------------------
("2022-01-09 00:00:00+00","2022-01-09 00:00:30+00")
("2022-01-12 15:27:22+00","2022-01-12 15:31:17+00")

downtime(
    agg HEARTBEATAGG
) RETURNS INTERVAL

Given a heartbeat aggregate, this will sum all the ranges where the system did not have a recent enough heartbeat.

There may appear to be some downtime between the start of the aggregate and the first heartbeat. If there is a heartbeat aggregage covering the previous period, you can use its last heartbeat to correct for this using interpolated_downtime().

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, we can use the following to get the total downtime of the system during the week of Jan 9, 2022:

SELECT downtime(health)
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

downtime     
--------
00:04:25

interpolate(
    agg HEARTBEATAGG,
    pred HEARTBEATAGG
) RETURNS HEARTBEATAGG

This takes a heartbeat aggregate and the aggregate immediately preceding it. It updates the aggregate to include any live ranges that should have been carried over from the last heartbeat in the predecessor, even if there aren't heartbeats for that range in the interval covered by this aggregate. It returns the updated aggregate, which can then be used with any of the heartbeat aggregate accessors.

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, we can use the following to get the intervals where the system was unhealthy during the week of Jan 9, 2022. This correctly excludes any ranges covered by a heartbeat at the end of the Jan 2 week:

SELECT dead_ranges(
  interpolate(
    health,
    LAG(health) OVER (ORDER BY date)
  )
)
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

dead_ranges                     
-----------------------------------------------------
("2022-01-12 15:27:22+00","2022-01-12 15:31:17+00")

interpolated_downtime(
    agg HEARTBEATAGG,
    pred HEARTBEATAGG
) RETURNS INTERVAL

This behaves very similarly to downtime(), but it also takes the heartbeat aggregate from the preceding interval. It checks when the last heartbeat in the predecessor was received and makes sure not to consider the heartbeat interval after that time as unhealthy, even if it extends into the current aggregate prior to the first heartbeat.

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, you can use this command to get the total interpolated downtime of the system during the week of Jan 9, 2022:

SELECT interpolated_downtime(
  health,
  LAG(health) OVER (ORDER BY date)
)
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

interpolated_downtime     
---------------------
       00:03:55

interpolated_uptime(
    agg HEARTBEATAGG,
    pred HEARTBEATAGG
) RETURNS INTERVAL

This behaves very similarly to uptime(), but it also takes the heartbeat aggregate from the preceding interval. It checks when the last heartbeat in the predecessor was received and makes sure that the entire heartbeat interval after that is considered live. This addresses the issue where uptime would consider the interval between the start of the interval and the first heartbeat as dead.

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, you can use this command to get the total interpolated uptime of the system during the week of Jan 9, 2022:

SELECT interpolated_uptime(
  health,
  LAG(health) OVER (ORDER BY date)
)
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

interpolated_uptime     
-------------------
  6 days 23:56:05

live_at(
    agg HEARTBEATAGG,
    test TIMESTAMPTZ
) RETURNS BOOL

Given a heartbeat aggregate and a timestamp, this returns whether the aggregate has a heartbeat indicating the system was live at the given time.

Note that this returns false for any time not covered by the aggregate.

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, we can use the following to see if the sytem was live at a particular time:

SELECT live_at(health, '2022-01-12 15:30:00+00')
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

live_at     
---------
 f

live_ranges(
    agg HEARTBEATAGG
) RETURNS TABLE (
    start TIMESTAMPTZ,
    end TIMESTAMPTZ
)

Given a heartbeat aggregate, this returns a set of (starttime, endtime) pairs representing when the underlying system was live during the interval of the aggregate.

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, we can use the following to get the intervals where the system was live during the week of Jan 9, 2022:

SELECT live_ranges(health)
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

live_ranges                     
-----------------------------------------------------
("2022-01-09 00:00:30+00","2022-01-12 15:27:22+00")
("2022-01-12 15:31:17+00","2022-01-16 00:00:00+00")

num_gaps(
    agg HEARTBEATAGG
) RETURNS BIGINT

Given a heartbeat aggregate, this returns the number of gaps between the periods of liveness. Additionally, if the aggregate is not live at the start or end of its covered interval, these are also considered gaps.

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, use this query to see how many times the system was down in a particular week::

SELECT num_gaps(health)
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

num_gaps
---------
4

num_live_ranges(
    agg HEARTBEATAGG
) RETURNS BIGINT

Given a heartbeat aggregate, this returns the number of live periods.

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, use this query to see how many intervals the system was up in a given week::

SELECT num_live_ranges(health)
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

num_live_ranges
---------
5

trim_to(
    agg HEARTBEATAGG,
    start TIMESTAMPTZ,
    duration INTERVAL
) RETURNS HEARTBEATAGG

Given a heartbeat aggregate, this reduces the time range covered by that aggregate. This can only be used to narrow the covered interval, passing arguments that would extend beyond the range covered by the initial aggregate gives an error.

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, use this query to roll up several weeks and trim the result to an exact month::

SELECT trim_to(rollup(health), '03-1-2022 UTC', '1 month')
FROM liveness
WHERE date > '02-21-2022 UTC' AND date < '3-7-2022 UTC'

Copy

uptime(
    agg HEARTBEATAGG
) RETURNS INTERVAL

Given a heartbeat aggregate, this sums all the ranges where the system was live and returns the total.

There may appear to be some downtime between the start of the aggregate and the first heartbeat. If there is a heartbeat aggregage covering the previous period, you can use its last heartbeat to correct for this using interpolated_uptime().

Examples

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, you can use this command to get the total uptime of the system during the week of Jan 9, 2022:

SELECT uptime(health)
FROM liveness
WHERE date = '01-9-2022 UTC'

Copy

uptime     
-----------------
6 days 23:55:35

rollup(
  heartbeatagg HEARTBEATAGG
) RETURNS HEARTBEATAGG

This combines multiple heartbeat aggregates into one. This can be used to combine aggregates into adjacent intervals into one larger interval, such as rolling daily aggregates into a weekly or monthly aggregate.

Another use for this is to combine heartbeat aggregates for redundant systems to determine if there were any overlapping failures. For instance, a master and standby system can have their heartbeats combined to see if there were any intervals where both systems were down at the same time. The result of rolling overlapping heartbeats together like this is a heartbeat aggregate which considers a time live if any of its component aggregates were live.