Exporting TimescaleDB Job Metrics to Prometheus

TimescaleDB records every background job outcome in timescaledb_information.job_stats, but that catalog only lives inside the database — to alert on a failed refresh or a lagging aggregate you first have to lift those rows out as Prometheus time series, and the exporter query decides whether you get a tidy handful of series or a cardinality explosion. This page shows how to shape a postgres_exporter custom query that turns job status, failure counts, and refresh lag into numeric gauges with stable, low-cardinality label sets, and how to predict the exact number of series it will produce before you deploy it.

# Input Profiling: Which Columns Become Metrics vs Labels

Every Prometheus series is a metric name plus a unique combination of label values, and its storage and query cost scale with how many of those combinations exist. So the first decision is which job_stats columns are measurements (the numbers you graph and alert on) and which are identity (the dimensions you slice by). Get this split wrong and you either lose the ability to filter by job, or you mint a new series every scrape.

Profile the source before writing any YAML. The timescaledb_information.job_stats view returns one row per registered background job — refresh policies, compression policies, retention policies, and any custom job you scheduled with add_job. Classify each column you care about:

• Metrics (numeric, low-churn values): last_run_status (mapped to a numeric), total_failures, and a derived refresh lag from last_successful_finish. These change value over time but never create new series.
• Labels (stable identity, bounded set): job_id, the target hypertable (or the continuous aggregate view it materializes), and the job_type (procedure name). Each is functionally determined by the job itself, so they move together.
• Do NOT label: anything unbounded or high-churn — timestamps (last_run_started_at, next_start), durations, per-chunk identifiers, or free-form status strings. A timestamp as a label value guarantees a brand-new series on every run.

The cardinality budget is the governing constraint. A healthy job-metrics export should stay in the low hundreds of series regardless of how much data the hypertables hold, because the series count tracks the number of jobs, not the number of rows, chunks, or scrapes. Anything that couples series count to chunk count or to time is a leak that will eventually overwhelm the Prometheus head block.

# Implementation: A Custom Query in queries.yaml

postgres_exporter reads a queries.yaml file where each top-level key becomes a metric namespace, its query runs on every scrape, and each returned column is tagged as either a LABEL or a GAUGE/COUNTER. The block below joins job_stats to continuous_aggregates so that aggregate refresh jobs report the user-facing view name instead of the internal _materialized_hypertable_N identity, does the status-to-numeric mapping in SQL, and computes lag as whole seconds:

timescaledb_job_stats:
  # cache one scrape so a slow catalog read never stalls the exporter
  cache_seconds: 30
  query: |
    SELECT
      s.job_id::text                                          AS job_id,
      COALESCE(c.view_name, j.hypertable_name, 'none')        AS hypertable,
      j.proc_name                                             AS job_type,
      CASE s.last_run_status WHEN 'Success' THEN 1 ELSE 0 END AS last_run_success,
      s.total_failures                                        AS total_failures,
      COALESCE(
        EXTRACT(EPOCH FROM (now() - s.last_successful_finish)),
        -1
      )::bigint                                               AS refresh_lag_seconds
    FROM timescaledb_information.job_stats s
    JOIN timescaledb_information.jobs j USING (job_id)
    LEFT JOIN timescaledb_information.continuous_aggregates c
           ON c.materialization_hypertable_name = j.hypertable_name
  metrics:
    - job_id:
        usage: "LABEL"
        description: "TimescaleDB background job id"
    - hypertable:
        usage: "LABEL"
        description: "Target hypertable, or continuous aggregate view for refresh jobs"
    - job_type:
        usage: "LABEL"
        description: "Registered procedure: policy_refresh_continuous_aggregate, policy_compression, policy_retention, or a custom proc"
    - last_run_success:
        usage: "GAUGE"
        description: "1 when the last run finished Success, 0 otherwise"
    - total_failures:
        usage: "GAUGE"
        description: "Cumulative failed runs since the job was created"
    - refresh_lag_seconds:
        usage: "GAUGE"
        description: "Seconds since last_successful_finish; -1 when the job has never finished"

postgres_exporter prefixes each metric with the namespace key, so this single block emits exactly three series names:

• timescaledb_job_stats_last_run_success
• timescaledb_job_stats_total_failures
• timescaledb_job_stats_refresh_lag_seconds

Each one carries the same three labels (job_id, hypertable, job_type). Because those labels are all derived from the job row itself, they never combine independently — one job produces one value of each label, not a cross-product.

That last point is what keeps cardinality predictable. In the general case, a metric’s series count is the product of its label cardinalities:

$$S = M \times \prod_{i=1}^{L} C_i$$

where $M$ is the number of distinct metric names and $C_i$ is the number of distinct values label $i$ takes. Here the labels are functionally dependent on job_id, so the product collapses to the number of jobs $J$:

$$S = M \times J \qquad (M = 3)$$

The moment a label varies independently of the job — a chunk name, a timestamp, a run counter — that product stops collapsing and the series count multiplies out instead of adding up.

# Worked Example: Series for N Aggregates

Take a platform with 40 continuous aggregates, each driven by one refresh policy job. The raw hypertables underneath also carry lifecycle automation: 12 compression policies and 12 retention policies across the base tables. That is a total registered-job count of:

$$J = 40_{\text{refresh}} + 12_{\text{compression}} + 12_{\text{retention}} = 64 \text{ jobs}$$

With three metric names, the export produces:

$$S = M \times J = 3 \times 64 = 192 \text{ series}$$

192 series is trivial for Prometheus — it fits comfortably in a single scrape and adds negligible head-block memory. Critically, that number is invariant to data volume: whether each aggregate covers a million rows or a billion, whether the hypertables hold 200 chunks or 200,000, the export stays at 192 series. Add ten more aggregates and you add exactly 30 series ($3 \times 10$). This linear, job-proportional growth is the whole reason to resist chunk-level labels, and it lets you capacity-plan the export the same way you would size the background workers that run those jobs.

# Edge Cases & When to Deviate

The query above is deliberately conservative. The failure modes it sidesteps are the ones that quietly break job-metrics exports in production:

• Per-chunk metrics explode cardinality. It is tempting to join timescaledb_information.chunks and label each series by chunk_name to see which chunk a compression job touched. Don’t. Chunk count grows without bound as data accumulates, so a chunk_name label turns $S = M \times J$ into $S = M \times \text{chunks}$ — potentially hundreds of thousands of series from a single hypertable. Keep chunk-level detail in ad-hoc SQL, and reserve the exporter for job-level and hypertable-level aggregates only.
• NULL last_successful_finish. A job that has been created but has never completed a successful run — freshly added, or failing since inception — returns NULL for last_successful_finish, and now() - NULL is NULL. A NULL metric value is silently dropped by the exporter, so the series vanishes exactly when you most want an alert. The COALESCE(..., -1) sentinel keeps the series present with an unmistakable value; alert on refresh_lag_seconds == -1 as a distinct “never succeeded” condition separate from ordinary lag.
• String status must map to a numeric. Prometheus only stores floats, so last_run_status cannot be exported as text. Mapping 'Success' → 1 and everything else → 0 collapses Failed and any future status value into the not-success bucket, which is the right default for alerting. If you need to distinguish a paused or running job, read job_status from the catalog rather than overloading this gauge — the semantics of those raw values are covered in the guide on interpreting total_failures and last_run_status.
• Duplicate hypertable labels. If a base table has both a compression and a retention job, two rows share the same hypertable value but differ by job_id and job_type — that is correct and stays unique because job_id is in the label set. Never drop job_id to “clean up” the labels; it is the only guaranteed-unique dimension.

# Verification

After mounting queries.yaml and restarting the exporter, confirm the series exist and carry the expected labels before wiring any alert rules. Scrape the exporter’s metrics endpoint and filter for the namespace — the raw exposition text shows the metric name, every label, and the current value on one line:

# Inspect the exporter output directly; grep the namespace prefix.
curl -s http://exporter-host:9187/metrics | grep '^timescaledb_job_stats_'

You should see three lines per job, for example:

timescaledb_job_stats_last_run_success{job_id="1000",hypertable="temperature_hourly",job_type="policy_refresh_continuous_aggregate"} 1
timescaledb_job_stats_total_failures{job_id="1000",hypertable="temperature_hourly",job_type="policy_refresh_continuous_aggregate"} 0
timescaledb_job_stats_refresh_lag_seconds{job_id="1000",hypertable="temperature_hourly",job_type="policy_refresh_continuous_aggregate"} 42

Then validate the series in Prometheus itself. Confirm the total count matches your $M \times J$ prediction, surface any failing jobs, and find the worst refresh lag per hypertable:

# Total series for this namespace — should equal 3 x job_count.
count({__name__=~"timescaledb_job_stats_.+"})

# Any job whose last run did not succeed.
timescaledb_job_stats_last_run_success == 0

# Worst refresh lag per hypertable, ignoring the never-run sentinel.
max by (hypertable) (timescaledb_job_stats_refresh_lag_seconds > 0)

Cross-check the exported values against the catalog by running the same SELECT in psql; the last_run_success and refresh_lag_seconds numbers should match row-for-row. If a job you expect is missing, verify the exporter’s connection role can read timescaledb_information views, and confirm the job actually appears in job_stats — a job that has never been scheduled to run yet will not. With the series confirmed, they become the data source for the panels in building a Grafana dashboard for chunk health and for lag-based alerting on the refresh scheduler.

# Related

• Prometheus & Grafana Integration — the parent guide on wiring TimescaleDB into a Prometheus and Grafana stack
• Building a Grafana Dashboard for Chunk Health — turning these series into panels
• Interpreting total_failures and last_run_status — what the raw catalog columns mean before you map them
• Alerting on Continuous Aggregate Refresh Lag — building alert rules on the refresh_lag_seconds gauge
• Sizing max_background_workers for Many Aggregates — planning worker capacity for the jobs you are now measuring

← Back to Prometheus & Grafana Integration

Up: Monitoring, Observability & Alerting for TimescaleDB Automation