Interpreting total_failures and last_run_status

A nonzero total_failures in timescaledb_information.job_stats almost never means a job is broken, and a job that stopped being scheduled almost never announces itself — so reading those columns correctly means combining last_run_status, the failure-versus-success counts, next_start, and last_successful_finish into a single verdict rather than reacting to any one field. This page turns the raw scheduler columns into a deterministic health classification: five verdicts (healthy, transient-recovering, hard-failing, not-scheduled, never-run), a decision tree that produces them, a SQL query that implements the tree, and worked rows that show why the obvious reading is often wrong. It builds on the scheduler introspection surface described in the parent guide on job stats and scheduler diagnostics.

# Input Profiling: Which Columns to Read

timescaledb_information.job_stats exposes one row per registered background job — every continuous aggregate refresh policy, compression policy, retention policy, and user-defined action. Six columns carry the entire health signal, and their exact meaning is what most misreadings get wrong:

• last_run_status — the outcome of the most recent execution only, either Success or Failed. It is the single most important field because it describes the current state, not the history. Before the first execution it is NULL.
• total_failures — a cumulative lifetime counter of every failed run since the job was created. It never resets. A three-month-old refresh policy that hit two lock conflicts in March still shows total_failures = 2 today even though it has run flawlessly since. This is the field people misread as an alarm.
• total_successes and total_runs — companion lifetime counters. For a job at rest, total_runs = total_successes + total_failures; a healthy long-lived job has total_successes overwhelmingly dominating.
• last_successful_finish — the timestamp of the last run that completed successfully. For a job that has never once succeeded, this is -infinity. Combined with last_run_status, it distinguishes “failed once but recovered” from “failing and never recovering”.
• next_start — when the scheduler will next launch the job. If this is in the future the scheduler still owns the job; when a job is paused it holds -infinity.
• job_status — Scheduled, Running, or Paused. Paused is the only value that means the scheduler will not run the job again until you intervene.

The governing insight: total_failures is a historical odometer, while last_run_status and last_successful_finish describe the present. A correct verdict weighs the present-tense fields first and treats the cumulative counters as context, never as a trigger.

# Comparison: Mapping Column Combinations to a Verdict

The decision matrix below is the tree above in tabular form. Read it top to bottom and stop at the first row that matches — the ordering is what makes the classification unambiguous.

The third row is the one that trips up dashboards: a job can carry a large total_failures and still be perfectly healthy, because the last run succeeded. The fourth and fifth rows both show last_run_status = Failed; what separates a recovering job from a broken one is whether a success happened recently and whether the scheduler still intends to retry.

# A single classification query

This query joins job_stats to jobs for each job’s schedule_interval, then encodes the matrix as an ordered CASE. The recency window is three schedule intervals — wide enough to tolerate one or two missed cycles, tight enough to catch a job that has genuinely stopped recovering.

WITH cfg AS (
  SELECT job_id, schedule_interval, application_name, proc_name
  FROM timescaledb_information.jobs
)
SELECT
  s.job_id,
  cfg.application_name,
  s.last_run_status,
  s.total_successes,
  s.total_failures,
  s.last_successful_finish,
  s.next_start,
  CASE
    WHEN s.total_runs = 0                          THEN 'never-run'
    WHEN s.job_status = 'Paused'                   THEN 'not-scheduled'
    WHEN s.last_run_status = 'Success'             THEN 'healthy'
    WHEN s.last_run_status = 'Failed'
         AND s.last_successful_finish
             > now() - (3 * cfg.schedule_interval)
         AND s.next_start > now()                  THEN 'transient-recovering'
    ELSE 'hard-failing'
  END AS verdict
FROM timescaledb_information.job_stats s
JOIN cfg USING (job_id)
ORDER BY s.job_id;

The CASE arms are evaluated in order, so never-run and not-scheduled are caught before any status comparison, and healthy short-circuits before the counters are ever consulted. Because last_successful_finish is -infinity for a job that has never succeeded, that value can never satisfy the transient-recovering window and correctly falls through to hard-failing.

# Optional psycopg v3 wrapper

For automation that needs the verdict as structured data — feeding an alert router, a status endpoint, or a CI gate — wrap the same query in psycopg v3 and return dictionaries:

import psycopg
from psycopg.rows import dict_row

CLASSIFY_SQL = """
WITH cfg AS (
  SELECT job_id, schedule_interval, application_name FROM timescaledb_information.jobs
)
SELECT s.job_id, cfg.application_name, s.total_failures, s.next_start,
  CASE
    WHEN s.total_runs = 0                THEN 'never-run'
    WHEN s.job_status = 'Paused'         THEN 'not-scheduled'
    WHEN s.last_run_status = 'Success'   THEN 'healthy'
    WHEN s.last_run_status = 'Failed'
         AND s.last_successful_finish > now() - (3 * cfg.schedule_interval)
         AND s.next_start > now()        THEN 'transient-recovering'
    ELSE 'hard-failing'
  END AS verdict
FROM timescaledb_information.job_stats s
JOIN cfg USING (job_id)
ORDER BY s.job_id;
"""

ACTIONABLE = {"hard-failing", "not-scheduled", "never-run"}


def classify_jobs(conn_str: str) -> list[dict]:
    """Return one verdict per registered background job."""
    with psycopg.connect(conn_str, row_factory=dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute(CLASSIFY_SQL)
            return cur.fetchall()


def jobs_needing_attention(conn_str: str) -> list[dict]:
    """Filter to verdicts a human should act on; transient/healthy are ignored."""
    return [row for row in classify_jobs(conn_str) if row["verdict"] in ACTIONABLE]

Keeping transient-recovering out of ACTIONABLE is deliberate: it is the verdict that most often generates false pages. A job that fails and then clears on its own scheduled retry is behaving exactly as the retry machinery intends, and alerting on it trains operators to ignore the alert channel.

# Worked Example

Consider three real rows from a production job_stats, each of which looks alarming under a naive reading of total_failures alone.

Row A — refresh policy, job_id 1001. last_run_status = Success, total_runs = 5000, total_successes = 4998, total_failures = 2, last_successful_finish two minutes ago, next_start eight minutes out. A dashboard that alerts on total_failures > 0 would page here. Walking the tree: total_runs is nonzero, not paused, and last_run_status = Success — so the verdict is healthy and we never even look at the two historical failures. They are the odometer reading from two lock conflicts long since resolved.

Row B — compression policy, job_id 1002. last_run_status = Failed, total_runs = 1500, total_successes = 1463, total_failures = 37, last_successful_finish eight minutes ago, next_start two minutes out, schedule_interval of one hour. The last run failed, but a success landed eight minutes ago — well inside the three-hour window — and the scheduler will retry in two minutes. Verdict: transient-recovering. This is the classic “fails under contention, succeeds on retry” pattern, and the growing total_failures is a symptom of how it recovers, not evidence that it is broken. If the pattern is chronic, address the contention through error handling and retry mechanisms rather than by silencing the job.

Row C — retention policy, job_id 1003. last_run_status = Failed, total_runs = 40, total_successes = 0, total_failures = 40, last_successful_finish = -infinity, next_start in the future, schedule_interval of one day. The last run failed, next_start is set, but there has never been a successful finish, so the recency test fails and the row falls to hard-failing. This is the true emergency of the three, yet its total_failures (40) is barely larger than Row B’s (37) — proof that the failure count on its own cannot separate a broken job from a healthy one. Only last_successful_finish reveals that this retention policy has never once dropped a chunk.

# Edge Cases & When to Deviate

• Always-fails-then-succeeds jobs. When a policy reliably fails its first attempt and succeeds on retry within the same cycle, total_failures climbs by one every period forever while the job stays healthy or transient-recovering. Do not alert on the counter’s slope. If the retries are wasteful, fix the root cause (tune max_runtime, stagger overlapping policies) instead of treating the count as the defect.
• Paused jobs. A job with job_status = 'Paused' (equivalently scheduled = false in timescaledb_information.jobs) will never run again on its own — next_start sits at -infinity. This is invisible to any check that only inspects last_run_status, because the last run may well have been a success. The not-scheduled verdict exists precisely to surface a job that silently left the schedule, whether from a manual alter_job(..., scheduled => false) or an operational freeze.
• Brand-new jobs. Immediately after add_*_policy, total_runs = 0, last_run_status is NULL, and last_successful_finish is -infinity. The never-run verdict prevents a fresh job from being misclassified as hard-failing before it has had a chance to run. If next_start is also NULL or far in the future, the registration itself may be wrong.
• Wide schedule intervals. The three-interval recency window scales with schedule_interval, so a daily job tolerates a three-day gap while a five-minute continuous aggregate refresh tolerates only fifteen minutes. Widen the multiplier for jobs whose failures are expected to span several cycles; narrow it where staleness is costly.
• Running jobs. A row caught mid-execution shows job_status = 'Running', and its last_run_status still reflects the previous run. The classifier treats it by that previous outcome, which is correct: a currently-running job’s health is defined by its last completed result until the in-flight run finishes.

# Verification

Run the classifier across the whole deployment and roll it up so a single glance tells you whether anything needs attention. This query wraps the classification in a summary and surfaces staleness in human units:

WITH classified AS (
  SELECT
    s.job_id,
    s.last_run_status,
    s.total_failures,
    round(extract(epoch FROM now() - s.last_successful_finish) / 60.0)
      AS minutes_since_success,
    CASE
      WHEN s.total_runs = 0              THEN 'never-run'
      WHEN s.job_status = 'Paused'       THEN 'not-scheduled'
      WHEN s.last_run_status = 'Success' THEN 'healthy'
      WHEN s.last_run_status = 'Failed'
           AND s.last_successful_finish > now() - (3 * j.schedule_interval)
           AND s.next_start > now()      THEN 'transient-recovering'
      ELSE 'hard-failing'
    END AS verdict
  FROM timescaledb_information.job_stats s
  JOIN timescaledb_information.jobs j USING (job_id)
)
SELECT verdict, count(*) AS jobs, sum(total_failures) AS lifetime_failures
FROM classified
GROUP BY verdict
ORDER BY array_position(
  ARRAY['hard-failing','not-scheduled','never-run',
        'transient-recovering','healthy'], verdict);

A deployment is in good shape when every row falls under healthy or transient-recovering. Any count in hard-failing, not-scheduled, or never-run is the actionable signal — and note that lifetime_failures can be large in the healthy bucket without meaning anything, which is the entire point of reading these columns together rather than trusting total_failures alone. To drill into an individual job’s underlying error text once the verdict flags it, pair this rollup with the per-run diagnostics in the job stats and scheduler diagnostics guide.

# Related

• Job Stats & Scheduler Diagnostics — the parent guide on reading the full scheduler introspection surface
• Alerting on Continuous Aggregate Refresh Lag — turning these verdicts into time-based lag alerts
• Error Handling & Retry Mechanisms for Continuous Aggregates — fixing the root causes behind a transient-recovering or hard-failing verdict
• Monitoring, Observability & Alerting for TimescaleDB Automation — where scheduler verdicts fit in the wider observability stack

← Back to Job Stats & Scheduler Diagnostics