Job Stats & Scheduler Diagnostics

Every refresh, compression, and retention policy you register in TimescaleDB becomes a row in a background-job scheduler that keeps its own bookkeeping — and that bookkeeping is the single source of truth for whether your automation is actually running. This guide shows how to read timescaledb_information.jobs, job_stats, and job_errors deterministically so you can tell, for every job, whether it is healthy, failing, stalled, starved for a worker, or simply not scheduled — three failure modes that look identical from the outside but demand completely different fixes. It is part of the broader coverage of monitoring, observability, and alerting for TimescaleDB automation and assumes you already run policies in production.

The reason this matters is that TimescaleDB automation fails silently by design. A refresh policy that stops running does not raise an error to your application — it simply stops advancing, and your dashboards keep serving increasingly stale rollups until someone notices. The scheduler records exactly what happened in three catalog views, and a handful of deterministic queries turn that record into an unambiguous verdict per job.

# Prerequisites

Reading the scheduler’s bookkeeping requires nothing more than SELECT access to the information schema, but the columns referenced below assume a recent extension and a role that can see other owners’ jobs.

• TimescaleDB 2.10 or newer on PostgreSQL 14+ (the job_errors view, last_run_status, and last_run_duration columns used here assume this baseline; older releases expose a narrower job_stats).
• SELECT on timescaledb_information.jobs, timescaledb_information.job_stats, and timescaledb_information.job_errors. These are views over the internal catalog and are readable by any role, but each row is visible only for jobs the role owns unless you connect as a superuser or a member of the owning role.
• SELECT on timescaledb_information.continuous_aggregates so you can map a materialization job back to the user-facing aggregate it maintains.
• The ability to read pg_stat_activity in full — a non-superuser sees only its own backends’ query text, so worker-liveness checks need pg_monitor membership or superuser.
• A clock you trust: every classification below compares next_start and last_successful_finish against now(), so a drifting server clock produces false verdicts.

Confirm the version and that the views are reachable in one pass:

-- Confirm the extension version and that the diagnostic views return rows.
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';
SELECT count(*) AS visible_jobs FROM timescaledb_information.jobs;
SELECT has_table_privilege('timescaledb_information.job_errors', 'SELECT') AS can_read_errors;

# Step-by-Step Implementation

The steps below follow the state diagram left to right: you first enumerate every job (separating scheduled from not scheduled), attach the run history (which resolves success from failed), give each job a human-readable identity, then collapse the whole picture into one classification query and put it on a schedule.

# 1. Enumerate every job from the jobs catalog

timescaledb_information.jobs is the registry of automation. It holds one row per policy — user-defined jobs plus every policy_refresh_continuous_aggregate, policy_compression, and policy_retention the extension created on your behalf. The critical column here is scheduled: a job with scheduled = false has been paused (by alter_job(..., scheduled => false) or by the scheduler after repeated failures) and will never run until re-enabled. That is the diagram’s Not scheduled state, and no amount of worker headroom will revive it.

-- The complete registry of automation, newest first. `scheduled = false`
-- is the "not scheduled / paused" state and can never run until re-enabled.
SELECT
    job_id,
    application_name,
    proc_name,          -- policy_refresh_continuous_aggregate, policy_compression, ...
    hypertable_name,    -- for refresh jobs this is the *materialization* hypertable
    schedule_interval,
    scheduled,          -- false => paused
    config
FROM timescaledb_information.jobs
ORDER BY job_id;

# 2. Join job_stats for the run history

timescaledb_information.job_stats carries the runtime facts the catalog does not: how the last attempt ended and when the next one is due. Join it to jobs on job_id. The columns that decide health are last_run_status (Success or Failed), last_successful_finish (how stale a healthy job’s output is), next_start (a value in the past on a job that is not currently running is the tell-tale of a stalled or starved job), and the running totals total_runs, total_successes, and total_failures.

-- Attach run history to every registered job. A next_start in the past on a
-- job that is not currently running is the signature of a stall or starvation.
SELECT
    j.job_id,
    j.application_name,
    j.proc_name,
    j.scheduled,
    js.last_run_status,          -- 'Success' | 'Failed' | NULL (never run)
    js.last_successful_finish,   -- freshness of the last good output
    js.last_run_started_at,
    js.last_run_duration,
    js.next_start,               -- when the scheduler intends to run it next
    js.total_runs,
    js.total_successes,
    js.total_failures
FROM timescaledb_information.jobs j
LEFT JOIN timescaledb_information.job_stats js USING (job_id)
ORDER BY js.total_failures DESC NULLS LAST, j.job_id;

A LEFT JOIN matters: a freshly created job has no job_stats row until its first run, so last_run_status IS NULL legitimately means “registered but never executed,” distinct from “failed.”

# 3. Resolve which continuous aggregate a job belongs to

For a refresh job, jobs.hypertable_name is the materialization hypertable (an internal name like _materialized_hypertable_7), not the aggregate view your team recognizes. Join timescaledb_information.continuous_aggregates on materialization_hypertable_name to recover the human-facing view_name, so an alert reads “the daily_device_rollup refresh is failing” instead of naming an internal table.

-- Give each refresh job the name of the continuous aggregate it maintains.
SELECT
    j.job_id,
    ca.view_schema,
    ca.view_name AS continuous_aggregate,
    js.last_run_status,
    js.last_successful_finish,
    js.next_start
FROM timescaledb_information.jobs j
JOIN timescaledb_information.continuous_aggregates ca
    ON ca.materialization_hypertable_name = j.hypertable_name
LEFT JOIN timescaledb_information.job_stats js USING (job_id)
WHERE j.proc_name = 'policy_refresh_continuous_aggregate'
ORDER BY js.last_successful_finish NULLS FIRST;

# 4. Build the single classification query

This is the heart of the diagnostic and the diagram’s step 4: one query that labels every job with exactly one state. The CASE ladder is ordered so the most specific, most actionable condition wins. Paused jobs are caught first because nothing else applies to them; a Failed status is next; then an overdue next_start (past due by more than one schedule_interval) marks a job the scheduler wanted to run but did not, which is either stalled or starved — step 6 uses pg_stat_activity to tell those two apart. Anything with a Success and a future next_start is healthy.

-- Deterministic per-job verdict. Exactly one label per job.
SELECT
    j.job_id,
    COALESCE(ca.view_name, j.hypertable_name, j.application_name) AS target,
    j.proc_name,
    js.last_run_status,
    js.last_successful_finish,
    js.next_start,
    CASE
        WHEN NOT j.scheduled                       THEN 'not scheduled'
        WHEN js.last_run_status = 'Failed'         THEN 'failing'
        WHEN js.job_id IS NULL
          OR js.last_run_status IS NULL            THEN 'never run'
        WHEN js.next_start < now() - j.schedule_interval
                                                   THEN 'stalled/starved'
        WHEN js.last_run_status = 'Success'
         AND js.next_start >= now()                THEN 'healthy'
        ELSE 'unknown'
    END AS job_state
FROM timescaledb_information.jobs j
LEFT JOIN timescaledb_information.job_stats js USING (job_id)
LEFT JOIN timescaledb_information.continuous_aggregates ca
    ON ca.materialization_hypertable_name = j.hypertable_name
ORDER BY
    CASE
        WHEN NOT j.scheduled THEN 0
        WHEN js.last_run_status = 'Failed' THEN 1
        WHEN js.next_start < now() - j.schedule_interval THEN 2
        ELSE 3
    END,
    j.job_id;

The ordering pushes every unhealthy job to the top of the result, so a single glance — or a single WHERE job_state <> 'healthy' wrapper — surfaces everything that needs attention.

# 5. Read the failure detail from job_errors

When the classification labels a job failing, timescaledb_information.job_errors holds the actual PostgreSQL error for each failed run — the message you need to fix the cause. This is where a lock timeout, a permission error, or a bad config reveals itself.

-- The most recent error text per failing job.
SELECT DISTINCT ON (job_id)
    job_id,
    pid,
    finish_time,
    sqlerrcode,
    err_message
FROM timescaledb_information.job_errors
ORDER BY job_id, finish_time DESC;

# 6. Schedule the diagnostic check itself

Run the classification on a cadence and act on it. The cleanest pattern is a user-defined job that runs the classification and raises a warning (or writes to an audit table your alerting reads). Register it with add_job so the scheduler you are monitoring also drives the monitor — with the deliberate caveat that a fully dead scheduler cannot report its own death, which is why step 6 of the performance section cross-checks with pg_stat_activity from an external connection.

import psycopg
from psycopg.rows import dict_row

def unhealthy_jobs(conn_str: str) -> list[dict]:
    """Return every job whose state is not 'healthy', worst first.
    Intended to run from an external scheduler (cron, Airflow) so a
    stalled in-database scheduler cannot suppress its own alert."""
    query = """
        SELECT job_id, target, proc_name, last_run_status, next_start, job_state
        FROM (
            SELECT
                j.job_id,
                COALESCE(ca.view_name, j.hypertable_name, j.application_name) AS target,
                j.proc_name,
                js.last_run_status,
                js.next_start,
                CASE
                    WHEN NOT j.scheduled                              THEN 'not scheduled'
                    WHEN js.last_run_status = 'Failed'               THEN 'failing'
                    WHEN js.last_run_status IS NULL                  THEN 'never run'
                    WHEN js.next_start < now() - j.schedule_interval THEN 'stalled/starved'
                    WHEN js.last_run_status = 'Success'
                     AND js.next_start >= now()                      THEN 'healthy'
                    ELSE 'unknown'
                END AS job_state
            FROM timescaledb_information.jobs j
            LEFT JOIN timescaledb_information.job_stats js USING (job_id)
            LEFT JOIN timescaledb_information.continuous_aggregates ca
                ON ca.materialization_hypertable_name = j.hypertable_name
        ) t
        WHERE job_state <> 'healthy'
        ORDER BY job_state, job_id;
    """
    with psycopg.connect(conn_str, row_factory=dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute(query)
            return cur.fetchall()

# Configuration Parameters Reference

These are the columns the diagnostic queries lean on, what each means, and the value a healthy job shows.

A cheap health signal is the lifetime success ratio $R$, useful for flagging a job that fails intermittently rather than outright. For a job with $n_s$ successes and $n_f$ failures:

$$R = \frac{n_s}{n_s + n_f}, \qquad \text{alert when } R < R_{\min}\ \text{and}\ (n_s + n_f) > N_{\min}$$

The $N_{\min}$ floor prevents a brand-new job’s single early failure from paging anyone; a value of $R_{\min} = 0.98$ with $N_{\min} = 20$ catches a job failing more than one run in fifty once it has enough history to judge.

# Integration with Adjacent Features

Scheduler diagnostics are the reading layer beneath every other kind of TimescaleDB observability, and each policy type it watches has its own detailed coverage.

• Continuous aggregates. A policy_refresh_continuous_aggregate job is the automation behind every rollup created in continuous aggregate creation and refresh management. When step 3 maps a job to its view_name, a Failed status or an overdue next_start translates directly into refresh lag on that aggregate — the subject of alerting on continuous aggregate refresh lag. How aggressively you set schedule_interval on those jobs is decided in refresh policy design and scheduling.
• Compression and retention. policy_compression and policy_retention jobs surface in the same views. A compression job that fails on lock contention shows up here as a rising total_failures, and the fix lives in chunk compression scheduling and automation and TTL policy mapping and enforcement. The storage-side view of those same jobs is compression and retention observability.
• The worker pool. The starved state is never about the job — it is about the shared pool that all jobs draw from. Whether a job can find a worker at next_start is governed by the asynchronous execution queue and by pool sizing, covered in background worker and lock monitoring and, specifically, sizing max_background_workers for many aggregates.
• Exported metrics. Once you can classify jobs deterministically, the natural next step is to emit those states as time series through Prometheus and Grafana integration so trends and pages are automatic.

# Performance Validation

The catalog views tell you what the scheduler recorded; pg_stat_activity tells you what the workers are doing right now, and the two together disambiguate the states the catalog alone cannot. First, confirm the scheduler itself is alive — if this returns zero rows, no job in the database can run and every “overdue” verdict is really a dead scheduler:

-- Is the background-worker scheduler alive? Zero rows here means no job runs.
SELECT pid, backend_start, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE backend_type = 'TimescaleDB Background Worker Scheduler';

Next, distinguish starved from stalled. A starved job is genuinely waiting for a free worker while others run; a stalled job is overdue while the pool sits idle. Compare the number of active job workers against the pool ceiling:

-- Workers busy right now versus the pool ceiling. busy == ceiling while jobs
-- are overdue => starvation; idle pool with overdue jobs => a stall to investigate.
SELECT
    (SELECT count(*) FROM pg_stat_activity
      WHERE backend_type = 'TimescaleDB Background Worker'
        AND application_name LIKE 'Refresh Continuous Aggregate%'
         OR application_name LIKE 'Compression%'
         OR application_name LIKE 'Retention%')            AS busy_job_workers,
    current_setting('timescaledb.max_background_workers')  AS worker_ceiling;

If busy_job_workers sits at the ceiling every time jobs fall overdue, the pool is the bottleneck — raise timescaledb.max_background_workers (and max_worker_processes above it). If the pool is idle while a job is overdue, the job is stalled on something specific — a long-held lock or a next_start that a manual alter_job pushed into the future — and job_errors or the lock views are the place to look.

# Troubleshooting

last_run_status = 'Failed' and total_failures climbing. The job runs but its body errors every time. Pull the exact message from job_errors (step 5). Common causes: the owning role lost a privilege, a refresh window reaches into a compressed chunk, or a config change made the proc arguments invalid. Fix the underlying cause, then let the retry backoff pick it up or force a run with CALL run_job(<job_id>).

next_start is in the past and there is no last_successful_finish advancing. The job is overdue. Run the two pg_stat_activity checks: if the scheduler row is missing, the scheduler crashed — check the server log for a worker that failed to launch and confirm max_worker_processes leaves room for the scheduler plus the pool. If the scheduler is alive but the pool is saturated, the job is starved, not broken.

A job you expect is missing entirely from jobs. Either it was never created, or it was created by a different role and your current role cannot see it. Reconnect as the owning role or a superuser and re-run the enumeration; if it is truly absent, the add_policy/add_job call never committed. Recreate it with if_not_exists => true so the retry is idempotent.

scheduled = false on a job you did not pause. TimescaleDB automatically pauses a job after repeated consecutive failures to stop it hammering the pool. The job_errors history explains why it kept failing. Fix the cause, then re-enable with SELECT alter_job(<job_id>, next_start => now(), scheduled => true);.

last_run_duration approaching schedule_interval. The job takes almost as long as the gap between runs, so runs risk overlapping and the job monopolizes a worker. Lengthen schedule_interval, narrow the work per run (a smaller refresh window or fewer chunks per compression pass), or give the pool more headroom.

Frequently Asked Questions

# Related & Navigation

← Back to Monitoring, Observability & Alerting for TimescaleDB Automation

• Alerting on Continuous Aggregate Refresh Lag — turn a failing or overdue refresh job into a freshness alert.
• Interpreting total_failures and last_run_status — read the run-history counters in depth.
• Prometheus & Grafana Integration — export the per-job states as time series.
• Background Worker & Lock Monitoring — diagnose the worker pool that starved jobs compete for.
• Compression & Retention Observability — the storage-side view of the compression and retention jobs classified here.