Monitoring, Observability & Alerting for TimescaleDB Automation

Every continuous aggregate refresh, every compression run, and every retention drop in a TimescaleDB deployment executes as an invisible background job — and when one of those jobs quietly stops, nothing throws an error to the application, the dashboards keep serving the last-materialized numbers, and the divergence between what your queries return and what actually happened widens by the hour. Observability closes that gap: TimescaleDB exposes the full state of its automation through a set of timescaledb_information system views, and the discipline covered here is turning that introspection surface into metrics, dashboards, and alerts that catch a stalled scheduler, a starved worker pool, or a collapsing compression ratio before a downstream consumer ever notices. This guide is written for the time-series data engineers, IoT platform developers, DevOps operators, and Python automation builders who run TimescaleDB at scale, and it maps the whole observability layer — from the raw catalog views through a collector and metrics store to Grafana panels and Alertmanager rules — while linking out to the focused guides that go deeper on each stage.

# Architecture Baseline

Observability for TimescaleDB automation is not an add-on agent that watches the process from outside — it is a query workload that reads the same catalog the scheduler writes to. That makes it cheap and authoritative, but it also means the monitoring role, the extension version, and the metrics store all have to be provisioned deliberately before the first scrape. Confirm each of the following:

• TimescaleDB 2.10 or later, with CREATE EXTENSION timescaledb run in every database you intend to observe — the timescaledb_information.job_stats, job_errors, and per-job views stabilised across the 2.8–2.10 line, and job_errors in particular is only present on recent versions.
• PostgreSQL 14 or later, so the statistics views the collector joins against (pg_stat_activity, pg_locks) expose the wait-event and lock columns the diagnostics queries rely on.
• You know which introspection surface is actually available in your build: the timescaledb_information views are always present, but hypertable_compression_stats() and chunk_compression_stats() require that compression has been enabled on at least one hypertable, and the Community edition exposes a slightly different job set than a managed service.
• A metrics store is chosen and reachable — Prometheus with a remote-write backend, or any store the collector can push to. Per-chunk metrics are high-cardinality, so decide up front whether the store aggregates or retains them raw.
• A dedicated, least-privilege monitoring role exists: CREATE ROLE ts_monitor LOGIN, granted pg_monitor (for pg_stat_activity and pg_locks visibility) and explicit SELECT on the timescaledb_information views, and nothing more — never point a collector at a superuser or the table owner.
• timescaledb.telemetry_level and your scrape interval are set together: a 15-second scrape of the full job and chunk surface across thousands of chunks is a real query load, so the interval is a capacity decision, not a default.

The monitoring role is the piece most deployments get wrong. TimescaleDB’s information views are security-barrier views that already filter rows to what the calling role may see, so a low-privilege collector will silently observe fewer jobs than a superuser would. Grant pg_monitor and view-level SELECT, verify the collector sees every job you expect, and treat any discrepancy as a privilege gap rather than a missing job — a subtlety examined in depth in background worker and lock monitoring.

# The TimescaleDB Introspection Surface

TimescaleDB runs its automation — continuous aggregate refreshes, compression, retention, reorder, and user-defined actions — as jobs dispatched onto a shared pool of background workers by an internal scheduler. Everything you need to observe that machinery is exposed through a stable set of views under the timescaledb_information schema, and understanding what each one holds is the foundation for every dashboard and alert downstream.

• timescaledb_information.jobs — the definition of every scheduled job: job_id, application_name, schedule_interval, config (the JSONB arguments, including start_offset/end_offset for a refresh policy), the owner role, and the proc_name that runs. This is the static picture — what is supposed to run and how often.
• timescaledb_information.job_stats — the runtime picture, one row per job: last_run_status (Success or Failed), last_successful_finish, last_start, next_start, total_runs, total_successes, total_failures, and total_duration. This single view answers “is automation healthy right now?” for the whole instance.
• timescaledb_information.job_errors — an append-only history of individual failures, with job_id, proc_name, start_time, and an error_data JSONB payload carrying the SQLSTATE and message. Because a policy can fail transiently and then succeed on retry, job_stats alone hides intermittent problems that this view preserves.
• timescaledb_information.chunks — one row per chunk across every hypertable: hypertable_name, chunk_name, range_start/range_end, is_compressed, and whether the chunk is a dropped tombstone. Counting and bucketing these rows is how you measure fragmentation and retention reclamation.
• timescaledb_information.continuous_aggregates — the mapping from a view_name to its internal materialization_hypertable_name, plus materialized_only, finalized, and the compression/refresh flags. A refresh job attaches to the materialization hypertable, not the view, so this view is the join key that connects a cagg’s name to its job_stats row.
• hypertable_compression_stats() and chunk_compression_stats() — table functions returning before_compression_total_bytes, after_compression_total_bytes, and per-chunk equivalents. Their ratio is the compression health signal, and timescaledb_information.compression_settings records the segmentby/orderby columns that determine whether that ratio is any good.

Because these are ordinary views and functions, the collector’s core is a single read-only snapshot query. The annotated statement below pulls a per-job health row and joins continuous aggregates back to their scheduler entries so a stalled refresh is attributable to a named view rather than an opaque hypertable id.

-- Instance-wide automation health snapshot.
-- Joins the scheduler's runtime view to the cagg catalog so every refresh job
-- is labelled with the user-facing view it maintains.
SELECT
    j.job_id,
    j.proc_name,                                  -- policy_refresh_continuous_aggregate, etc.
    j.application_name,
    ca.view_name,                                 -- NULL for non-cagg jobs (retention, compression)
    js.last_run_status,                           -- 'Success' | 'Failed'
    js.last_successful_finish,
    js.next_start,
    now() - js.last_successful_finish AS since_success,
    js.total_failures,
    js.total_runs,
    -- mean duration is total_duration / total_runs; guard against divide-by-zero
    CASE WHEN js.total_runs > 0
         THEN js.total_duration / js.total_runs END AS mean_duration
FROM timescaledb_information.jobs        AS j
JOIN timescaledb_information.job_stats   AS js USING (job_id)
LEFT JOIN timescaledb_information.continuous_aggregates AS ca
       ON ca.materialization_hypertable_name = js.hypertable_name
ORDER BY js.last_run_status DESC, since_success DESC NULLS FIRST;

The LEFT JOIN is deliberate: retention and compression jobs have no view_name, and the snapshot must still surface them. This one query is enough to detect the most dangerous failure — a job whose next_start is in the past while last_successful_finish keeps receding — which is the exact signal explored in job stats and scheduler diagnostics.

# Automation Patterns

A production collector runs the snapshot on a fixed cadence, adds the two signals the catalog does not compute directly — real-time refresh lag (watermark to now) and compression ratio — and emits structured metrics for the exporter to publish. The psycopg v3 routine below samples job health, refresh lag, chunk counts, and compression ratio in a single connection and returns a flat list of metric records that a Prometheus client or remote-write shim can serialise directly.

from __future__ import annotations
import psycopg
from psycopg.rows import dict_row
from dataclasses import dataclass, asdict

DB_CONN_STR = "postgresql://ts_monitor@localhost:5432/telemetry_db"


@dataclass(frozen=True)
class Metric:
    name: str
    value: float
    labels: dict[str, str]


def collect() -> list[Metric]:
    """Sample TimescaleDB automation health into flat metric records."""
    metrics: list[Metric] = []
    # A single connection; each query is a short, index-friendly catalog read.
    with psycopg.connect(DB_CONN_STR, row_factory=dict_row) as conn:
        with conn.cursor() as cur:
            # 1. Per-job status + failure counters.
            cur.execute("""
                SELECT js.job_id, j.proc_name,
                       (js.last_run_status = 'Success')::int AS ok,
                       js.total_failures,
                       EXTRACT(epoch FROM now() - js.last_successful_finish) AS since_success
                FROM timescaledb_information.job_stats js
                JOIN timescaledb_information.jobs j USING (job_id);
            """)
            for r in cur.fetchall():
                lbl = {"job_id": str(r["job_id"]), "proc": r["proc_name"]}
                metrics.append(Metric("tsdb_job_last_success", r["ok"], lbl))
                metrics.append(Metric("tsdb_job_total_failures", r["total_failures"] or 0, lbl))
                if r["since_success"] is not None:
                    metrics.append(Metric("tsdb_job_seconds_since_success",
                                          float(r["since_success"]), lbl))

            # 2. Continuous-aggregate refresh lag: watermark to now, per view.
            cur.execute("""
                SELECT ca.view_name,
                       EXTRACT(epoch FROM now() -
                         _timescaledb_internal.to_timestamp(
                           _timescaledb_internal.cagg_watermark(cat.mat_hypertable_id)
                         )) AS lag_seconds
                FROM timescaledb_information.continuous_aggregates ca
                JOIN _timescaledb_catalog.continuous_agg cat
                  ON cat.user_view_name = ca.view_name;
            """)
            for r in cur.fetchall():
                if r["lag_seconds"] is not None:
                    metrics.append(Metric("tsdb_cagg_refresh_lag_seconds",
                                          float(r["lag_seconds"]),
                                          {"view": r["view_name"]}))

            # 3. Chunk counts (total + compressed) per hypertable.
            cur.execute("""
                SELECT hypertable_name,
                       count(*) AS chunks,
                       count(*) FILTER (WHERE is_compressed) AS compressed
                FROM timescaledb_information.chunks
                GROUP BY hypertable_name;
            """)
            for r in cur.fetchall():
                lbl = {"hypertable": r["hypertable_name"]}
                metrics.append(Metric("tsdb_chunks_total", r["chunks"], lbl))
                metrics.append(Metric("tsdb_chunks_compressed", r["compressed"], lbl))

            # 4. Compression ratio per hypertable (before / after bytes).
            cur.execute("""
                SELECT h.hypertable_name,
                       s.before_compression_total_bytes AS before_b,
                       s.after_compression_total_bytes  AS after_b
                FROM timescaledb_information.hypertables h
                CROSS JOIN LATERAL
                     hypertable_compression_stats(
                       format('%I.%I', h.hypertable_schema, h.hypertable_name)::regclass
                     ) AS s
                WHERE s.after_compression_total_bytes > 0;
            """)
            for r in cur.fetchall():
                ratio = r["before_b"] / r["after_b"]
                metrics.append(Metric("tsdb_compression_ratio", ratio,
                                      {"hypertable": r["hypertable_name"]}))
    return metrics


if __name__ == "__main__":
    for m in collect():
        print(asdict(m))

The routine holds one connection, runs four bounded catalog reads, and never touches the data hypertables themselves, so its cost is independent of ingestion volume. Two design choices keep it robust in production. First, it isolates lag computation (query 2) from job status (query 1): a job that reports Success and an aggregate that is actually fresh are separate assertions, and collapsing them hides the most insidious class of drift. Second, it emits counters (total_failures) alongside gauges (lag, ratio), because a counter that only ever rises lets the metrics store compute a failure rate with rate() even when individual failures were transient and long since retried away. Exporting these records over an HTTP endpoint Prometheus can scrape — including the # TYPE and # HELP framing and label hygiene — is the subject of Prometheus and Grafana integration, and pushing them straight into the store without an intermediate exporter is walked through in exporting TimescaleDB job metrics to Prometheus.

# Performance & Scale

The collector’s overhead is dominated not by the job views — there are only ever a few dozen jobs — but by the chunk-level scans. timescaledb_information.chunks has one row per chunk per hypertable, and a fleet with hundreds of hypertables retaining months of narrow chunks can hold hundreds of thousands of rows. Aggregating with count(*) FILTER (...) in the database, as the collector does, keeps the wire payload tiny; the temptation to export a metric per chunk is where observability itself becomes a scale problem.

Per-chunk cardinality compounds fast. If the store ingests one time series per chunk per metric, the active series count grows as the product of chunks, metrics, and scrape targets — a mid-size deployment can push a Prometheus instance past a million active series purely from monitoring data, which costs more memory than the database it watches. Keep per-chunk detail as an on-demand diagnostic query against the catalog, and export only aggregated gauges (total chunks, compressed chunks, oldest chunk age) on the steady-state scrape path.

The scrape interval is the other lever. Each scrape re-runs the collector’s catalog reads, and the chunk aggregation scales with total chunk count, so a 5-second interval against a heavily fragmented instance turns monitoring into a measurable share of the query load the database is trying to serve. A 30- to 60-second interval is almost always sufficient — automation events are minutes-to-hours phenomena, not sub-second ones, and a lag alarm evaluated once a minute loses nothing meaningful. Where you genuinely need faster resolution on one signal, scrape that signal on its own target with its own interval rather than accelerating the whole snapshot; splitting the collector into a cheap fast path and an expensive slow path is a common outcome of this trade-off.

Alert thresholds should be derived from the policy configuration, not guessed. For a continuous aggregate refreshed by a policy, the expected steady-state refresh lag is bounded by how far behind the watermark the policy deliberately holds, plus how long a cycle takes to come around and complete. A defensible alert threshold on tsdb_cagg_refresh_lag_seconds is:

$$T_{\text{alert}} = e + S + \bar{r} + k\,\sigma_r$$

where $e$ is the policy’s end_offset (the watermark’s built-in distance from now), $S$ is the schedule_interval, $\bar{r}$ is the mean refresh runtime, $\sigma_r$ its standard deviation, and $k$ a margin multiplier (2–3 is typical). A 1-hour aggregate with end_offset => 1 hour, schedule_interval => 30 min, a mean runtime of 40 s, and $\sigma_r$ of 20 s yields $T_{\text{alert}} \approx 3600 + 1800 + 40 + 3(20) = 5500$ s. Anything below that is normal cadence; sustained lag above it means a run is being missed, and that is worth a page. Deriving the same threshold from bucket width and settling time is worked through for the alerting case in alerting on continuous aggregate refresh lag.

# Failure Modes

1. Silent job death. A refresh or retention job stops firing — a worker slot never frees, a config points at a dropped object, or the job was disabled — and because no client called it, no error propagates. The job_stats row simply stops advancing: next_start slips into the past while last_successful_finish stays frozen. Mitigation: alert on now() - last_successful_finish > k × schedule_interval for every job, not just the ones you remember to watch.
2. Worker-pool starvation. Every refresh, compression, and retention job draws from the single timescaledb.max_background_workers pool. When more jobs come due than there are workers, the surplus queues; individual jobs look healthy but next_start drifts fleet-wide. The symptom is correlated lag across unrelated aggregates. Mitigation: monitor concurrent running jobs against the pool size and stagger schedule_interval values — sizing the pool is covered in sizing max_background_workers for many aggregates.
3. Alert fatigue from noisy thresholds. A refresh-lag alarm set just above the mean fires on every slightly-slow cycle; operators learn to ignore it, and the one real outage scrolls past unread. Mitigation: derive thresholds from the formula above with a real margin, and require a sustained for: duration in the alert rule so a single slow run never pages.
4. Watermark-lag blind spots. Job-level monitoring shows a refresh policy running and succeeding, yet the aggregate is stale because late-arriving data lands behind a start_offset the policy never revisits. job_stats is green; the data is wrong. Mitigation: monitor the watermark-to-now lag (metric 2 above) independently of job status — a succeeding job and a fresh aggregate are two different assertions.
5. Monitoring role privilege gaps. A collector running as an under-privileged role sees a filtered subset of jobs and chunks through the security-barrier views, so a job it cannot see can fail forever without ever entering a dashboard. Mitigation: reconcile the collector’s job count against a known-good superuser count at deploy time, grant pg_monitor plus explicit view SELECT, and re-check after every extension upgrade.

# Monitoring Checklist

Track these five signals continuously; each maps to a query against the system views, and the first two are the ones to page on.

• Refresh lag — the gap between an aggregate’s watermark and now, independent of job status.
• Job failures — non-Success status and rising total_failures.
• Chunk fragmentation — total chunk count and oldest-chunk age per hypertable.
• Compression ratio — before/after bytes, trended so a regression is visible.
• Retention reclamation — that old chunks are actually being dropped, not merely marked.

-- 1. Refresh lag per continuous aggregate (watermark to now).
SELECT ca.view_name,
       now() - _timescaledb_internal.to_timestamp(
                 _timescaledb_internal.cagg_watermark(cat.mat_hypertable_id)
               ) AS refresh_lag
FROM timescaledb_information.continuous_aggregates ca
JOIN _timescaledb_catalog.continuous_agg cat
  ON cat.user_view_name = ca.view_name
ORDER BY refresh_lag DESC;

-- 2. Failing or stalled jobs across the whole instance.
SELECT job_id, last_run_status, total_failures,
       now() - last_successful_finish AS since_success, next_start
FROM timescaledb_information.job_stats
WHERE last_run_status <> 'Success'
   OR next_start < now() - INTERVAL '1 hour'
ORDER BY total_failures DESC;

-- 3. Fragmentation: chunk count and oldest range per hypertable.
SELECT hypertable_name,
       count(*) AS chunks,
       count(*) FILTER (WHERE is_compressed) AS compressed,
       min(range_start) AS oldest
FROM timescaledb_information.chunks
GROUP BY hypertable_name
ORDER BY chunks DESC;

-- 4. Compression ratio per hypertable.
SELECT hypertable_name,
       round(before_compression_total_bytes::numeric
             / nullif(after_compression_total_bytes, 0), 1) AS ratio
FROM hypertable_compression_stats('sensor_metrics_1h');

-- 5. Recent job errors with SQLSTATE for triage.
SELECT job_id, proc_name, start_time,
       error_data ->> 'sqlerrcode' AS sqlstate,
       error_data ->> 'message'    AS message
FROM timescaledb_information.job_errors
ORDER BY start_time DESC
LIMIT 20;

Wire query 1 into the lag threshold from the performance section and query 2 into a status alarm — together they distinguish “the job stopped” from “the job runs but the data is stale,” which demand different responses. Query 3 feeds a fragmentation trend, query 4 a compression regression alarm, and query 5 is the triage view an operator opens first. Building these into panels with sensible units and colour thresholds is the work of building a Grafana dashboard for chunk health, and interpreting the failure counters — why total_failures can climb while last_run_status stays Success — is covered in interpreting total_failures and last_run_status. Trending the compression ratio over weeks, so a drifting segmentby choice is caught before it doubles your storage bill, is the focus of compression and retention observability.

# Related

• Job Stats & Scheduler Diagnostics — reading job_stats, job_errors, and the scheduler to find stalled and failing automation.
• Prometheus & Grafana Integration — exporting catalog metrics and building dashboards on top of them.
• Compression & Retention Observability — trending compression ratios and confirming retention actually reclaims storage.
• Background Worker & Lock Monitoring — watching the worker pool and lock contention that gate every job.

Cross-topic: Refresh Policy Design & Scheduling · Chunk Compression Scheduling & Automation · Asynchronous Execution & Queue Management

← Back to all TimescaleDB topics