State tracking overview

Track state transitions and system liveness over time. These functions help you analyze systems that switch between discrete states, monitor heartbeat signals for liveness detection, and calculate durations spent in different states.

TimescaleDB Toolkit provides three approaches to state tracking:

• compact_state_agg: Track time spent in each state with minimal memory usage
• state_agg: Track state transitions with full timestamp information
• heartbeat_agg: Monitor system liveness based on heartbeat signals

Two-step aggregation

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions.

The two-step aggregation pattern has several advantages:

1. More efficient because multiple accessors can reuse the same aggregate
2. Easier to reason about performance, because aggregation is separate from final computation
3. Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
4. Perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

Samples

Track time in different states

Use compact_state_agg to efficiently track how much time a system spends in each state:

SELECT
    device_id,
    toolkit_experimental.compact_state_agg(time, status) AS state_data
FROM devices
GROUP BY device_id;

Query the duration spent in each state:

WITH state_data AS (
    SELECT
        device_id,
        toolkit_experimental.compact_state_agg(time, status) AS agg
    FROM devices
    GROUP BY device_id
)
SELECT
    device_id,
    toolkit_experimental.duration_in('running', agg) AS running_time,
    toolkit_experimental.duration_in('error', agg) AS error_time
FROM state_data;

Analyze state transitions

Use state_agg to track when state transitions occur:

WITH state_data AS (
    SELECT state_agg(time, status) AS agg
    FROM devices
    WHERE device_id = 'device_1'
)
SELECT *
FROM unnest((SELECT state_timeline(agg) FROM state_data));

Monitor system liveness

Use heartbeat_agg to track uptime and downtime:

WITH heartbeats AS (
    SELECT heartbeat_agg(
        ping_time,
        '2024-01-01'::timestamptz,
        '7 days'::interval,
        '5 minutes'::interval
    ) AS agg
    FROM system_health
)
SELECT
    uptime(agg) AS total_uptime,
    downtime(agg) AS total_downtime
FROM heartbeats;

Available functions

Compact state aggregation

• compact_state_agg(): track time spent in states with minimal memory usage

State aggregation with transitions

• state_agg(): track state transitions with full timestamp information

Heartbeat monitoring

• heartbeat_agg(): monitor system liveness based on heartbeat signals