Prometheus & Grafana Integration

Getting TimescaleDB automation metrics out of the database and onto a scrape target is the difference between knowing a compression job failed and discovering it a week later when disk fills. This guide wires the internal timescaledb_information views — job health, refresh lag, chunk counts, compression ratio — through postgres_exporter into Prometheus, renders them on Grafana, and fires Alertmanager rules that page only on problems that actually warrant a human. It is part of the broader work on monitoring, observability and alerting for TimescaleDB automation and assumes a production hypertable already running refresh, compression, and retention policies.

The metrics that matter here are not the generic PostgreSQL counters every deployment already scrapes — connection counts, cache hit ratio, replication lag. They are the automation signals unique to TimescaleDB: whether the background scheduler ran the last refresh, how far a continuous aggregate has fallen behind real time, how many chunks are compressed versus raw, and what compression ratio each hypertable is actually achieving. None of those are exposed as native Prometheus metrics. They live in catalog views, and the only supported way to lift them out is a custom-query exporter selecting from those views and shaping each row into a labelled time series.

# Prerequisites

Before the first scrape returns a TimescaleDB series, three moving parts must be in place: an exporter that can run custom SQL, a reachable Prometheus and Grafana, and a database role scoped tightly enough that a compromised exporter cannot read or mutate telemetry rows. Confirm each item below.

• postgres_exporter (or an equivalent custom-query exporter) is deployed and can reach the TimescaleDB instance on the Postgres port; it must support a queries.yaml-style custom-metric file.
• Prometheus is running and its config file is reloadable (SIGHUP or the --web.enable-lifecycle reload endpoint) so a new scrape job takes effect without a restart.
• Grafana is reachable and has the Prometheus instance registered as a data source.
• Alertmanager is wired to Prometheus (alerting.alertmanagers in prometheus.yml) with at least one receiver so a firing rule reaches a human.
• A least-privilege database role exists for the exporter — CONNECT on the database, USAGE on timescaledb_information, and SELECT only on the views (or a purpose-built monitoring view) it reads. It must not own hypertables or hold write rights.
• TimescaleDB 2.10+ on PostgreSQL 14+, so timescaledb_information.jobs, job_stats, and chunks expose the columns the queries below reference.

Create the monitoring role and, optionally, a narrow view that hides everything the exporter does not need:

-- Least-privilege exporter role.
CREATE ROLE ts_exporter LOGIN PASSWORD 'REDACTED';
GRANT CONNECT ON DATABASE metrics TO ts_exporter;
GRANT USAGE ON SCHEMA timescaledb_information TO ts_exporter;
GRANT SELECT ON ALL TABLES IN SCHEMA timescaledb_information TO ts_exporter;

-- Optional: a single flattened view keeps the exporter query readable and the
-- grant surface minimal. The exporter then needs SELECT on just this view.
CREATE SCHEMA IF NOT EXISTS monitoring;
CREATE OR REPLACE VIEW monitoring.job_health AS
SELECT
    j.job_id,
    j.proc_name,
    coalesce(j.hypertable_name, 'n/a') AS hypertable_name,
    js.total_runs,
    js.total_failures,
    js.total_successes,
    js.last_run_status,
    js.last_run_duration,
    js.last_successful_finish
FROM timescaledb_information.jobs j
LEFT JOIN timescaledb_information.job_stats js USING (job_id);
GRANT SELECT ON monitoring.job_health TO ts_exporter;

# Step-by-Step Implementation

The four steps below map onto the diagram nodes: you author the exporter queries (step 1), point Prometheus at the /metrics endpoint (step 2), draw the Grafana panels (step 3), and add the alert rules Prometheus evaluates on Alertmanager’s behalf (step 4).

# 1. Write custom exporter queries (queries.yaml)

postgres_exporter turns each row of a custom query into one or more series. Every non-LABEL column becomes a metric named <mapkey>_<column>; every LABEL column becomes a Prometheus label on those series. The art is choosing labels that are bounded — a fixed set of jobs, a modest set of hypertables — and never per-chunk identifiers, which explode cardinality (see the reference table below).

# queries.yaml — mounted into postgres_exporter and referenced via
# --extend.query-path=/etc/postgres_exporter/queries.yaml
timescaledb_jobs:
  query: |
    SELECT
      job_id::text                                            AS job_id,
      proc_name,
      hypertable_name,
      total_runs,
      total_failures,
      total_successes,
      CASE WHEN last_run_status = 'Success' THEN 1 ELSE 0 END AS last_run_success,
      EXTRACT(EPOCH FROM last_run_duration)                   AS last_run_duration_seconds,
      EXTRACT(EPOCH FROM (now() - last_successful_finish))    AS seconds_since_success
    FROM monitoring.job_health
  metrics:
    - job_id:          { usage: "LABEL",   description: "Scheduler job id" }
    - proc_name:       { usage: "LABEL",   description: "Policy proc, e.g. policy_compression" }
    - hypertable_name: { usage: "LABEL",   description: "Target hypertable" }
    - total_failures:  { usage: "COUNTER", description: "Cumulative failed runs" }
    - total_successes: { usage: "COUNTER", description: "Cumulative successful runs" }
    - last_run_success:      { usage: "GAUGE", description: "1 if last run succeeded else 0" }
    - last_run_duration_seconds: { usage: "GAUGE", description: "Duration of last run" }
    - seconds_since_success: { usage: "GAUGE", description: "Refresh/job lag: age of last success" }

timescaledb_chunks:
  query: |
    SELECT
      hypertable_name,
      count(*)                              AS chunk_count,
      count(*) FILTER (WHERE is_compressed) AS compressed_chunk_count
    FROM timescaledb_information.chunks
    GROUP BY hypertable_name
  metrics:
    - hypertable_name:        { usage: "LABEL", description: "Target hypertable" }
    - chunk_count:            { usage: "GAUGE", description: "Total chunks" }
    - compressed_chunk_count: { usage: "GAUGE", description: "Chunks in columnar store" }

Compression ratio needs before/after byte totals. Those come from hypertable_compression_stats, a set-returning function that takes one hypertable — aggregate the fleet in a UNION ALL (or a view) so the exporter query stays flat:

timescaledb_compression:
  query: |
    SELECT 'sensor_readings' AS hypertable_name,
           coalesce(before_compression_total_bytes, 0) AS before_bytes,
           coalesce(after_compression_total_bytes, 0)  AS after_bytes
    FROM hypertable_compression_stats('sensor_readings')
    UNION ALL
    SELECT 'device_events',
           coalesce(before_compression_total_bytes, 0),
           coalesce(after_compression_total_bytes, 0)
    FROM hypertable_compression_stats('device_events')
  metrics:
    - hypertable_name: { usage: "LABEL", description: "Target hypertable" }
    - before_bytes:    { usage: "GAUGE", description: "Uncompressed total bytes" }
    - after_bytes:     { usage: "GAUGE", description: "Compressed total bytes" }

Keep the ratio arithmetic in PromQL rather than SQL, so the raw byte counts stay available for capacity dashboards. The compression fraction saved is then a recording rule (step 4) over timescaledb_compression_before_bytes and ..._after_bytes.

# 2. Configure the scrape

Add a scrape job pointing at the exporter’s HTTP port. The job and any static labels you attach become dimensions on every series, which is how you distinguish the same metric across environments or clusters.

# prometheus.yml
scrape_configs:
  - job_name: timescaledb
    scrape_interval: 30s          # match or exceed the exporter query cost
    scrape_timeout: 15s           # custom queries can be slow; leave headroom
    static_configs:
      - targets: ['ts-exporter:9187']
        labels:
          cluster: prod-iot

Reload Prometheus (curl -X POST http://prometheus:9090/-/reload with the lifecycle API enabled) and confirm the target is UP on the Status → Targets page before moving on. A 30-second interval is a sensible floor: automation metrics change on the order of minutes, and a tight interval only multiplies the cost of the catalog queries against your database.

# 3. Build Grafana panels

With series arriving, build panels against the Prometheus data source. Four panels cover the automation story — job health, refresh lag, chunk composition, compression ratio. Each is a PromQL expression:

# Panel: refresh lag per continuous-aggregate job (seconds behind last success)
timescaledb_jobs_seconds_since_success{proc_name="policy_refresh_continuous_aggregate"}

# Panel: compression fraction saved per hypertable (0..1)
1 - (
  timescaledb_compression_after_bytes
  / clamp_min(timescaledb_compression_before_bytes, 1)
)

Use a stat panel with thresholds for lag (green under an interval, red past it), a time-series panel for the compression fraction trend, and a bar gauge for compressed-versus-total chunks via timescaledb_chunks_compressed_chunk_count / timescaledb_chunks_chunk_count. Template the dashboard with a hypertable_name variable — label_values(timescaledb_chunks_chunk_count, hypertable_name) — so one dashboard serves every table. The deeper panel-by-panel build, including the chunk-composition heatmap, is covered in building a Grafana dashboard for chunk health.

# 4. Define alert rules

Alerts belong in Prometheus rule files; Alertmanager only routes what Prometheus fires. Two rules earn their keep immediately: refresh lag past a threshold, and any job failure. Add a recording rule for the compression fraction so both dashboards and alerts read the same pre-computed series.

# timescaledb.rules.yml — referenced from prometheus.yml rule_files
groups:
  - name: timescaledb-automation
    rules:
      - record: timescaledb:compression_fraction_saved
        expr: |
          1 - (timescaledb_compression_after_bytes
               / clamp_min(timescaledb_compression_before_bytes, 1))

      - alert: ContinuousAggregateRefreshLag
        expr: |
          timescaledb_jobs_seconds_since_success{
            proc_name="policy_refresh_continuous_aggregate"} > 3600
        for: 10m
        labels: { severity: warning }
        annotations:
          summary: "Refresh lag on {{ $labels.hypertable_name }}"
          description: "Job {{ $labels.job_id }} last succeeded {{ $value | humanizeDuration }} ago."

      - alert: TimescaleJobFailing
        expr: increase(timescaledb_jobs_total_failures[15m]) > 0
        for: 0m
        labels: { severity: critical }
        annotations:
          summary: "Scheduler job {{ $labels.job_id }} ({{ $labels.proc_name }}) failed"

The for: 10m on the lag rule suppresses transient spikes — a single slow refresh should not page. increase(...[15m]) > 0 on the failure counter fires the instant a run fails and resolves once no new failures land in the window, which is exactly the paging behaviour you want. The mechanics of choosing lag thresholds are developed further in alerting on continuous aggregate refresh lag.

# Configuration Parameters Reference

Each custom metric is defined by five fields in queries.yaml. Getting usage right is what makes a series behave as a counter (safe to rate()/increase()) versus a gauge (safe to read directly), and getting labels right is what keeps your series count sane.

Cardinality is the failure mode that quietly kills this integration. Total series produced is, per query, the number of numeric (value) columns times the number of distinct label combinations returned:

$$N_{\text{series}} = \sum_{q} V_q \cdot L_q$$

where $V_q$ is the count of value columns and $L_q$ the count of distinct label rows for query $q$. With a handful of jobs and a dozen hypertables the numbers stay tiny — the timescaledb_jobs query above at, say, 20 jobs and 5 value columns is 100 series. Replace the hypertable_name label with a per-chunk label and $L_q$ jumps from a dozen to thousands: a fleet with 8,000 chunks would emit 40,000 series from one query, and Prometheus memory scales with active series. The rule is absolute — aggregate per-chunk facts to the hypertable in SQL (as timescaledb_chunks does with count(*) FILTER (...)) and never emit a chunk_name label from a scrape target. If you genuinely need per-chunk visibility, drive it from an ad-hoc query at investigation time, not a continuous scrape.

# Integration with Adjacent Features

This scrape pipeline is the transport layer beneath every other observability concern, so it links tightly to its neighbours.

• Scheduler diagnostics. The timescaledb_jobs metrics are a thin projection of the same job_stats columns analysed in job stats and scheduler diagnostics. Prometheus tells you that total_failures climbed; interpreting why — reading last_run_status alongside the error — is covered in interpreting total_failures and last_run_status.
• Compression and retention observability. The before_bytes/after_bytes series feed the ratio trends analysed in compression and retention observability; the underlying policy those metrics measure is defined in chunk compression scheduling and automation.
• Worker and lock monitoring. A refresh-lag alert often traces back to a starved worker pool or lock contention — extend the exporter with pg_stat_activity and pg_locks queries as described in background worker and lock monitoring.
• The refresh policy itself. Lag alerts are only meaningful once you understand what a healthy refresh cadence looks like; that baseline comes from continuous aggregate creation and refresh management and its refresh policy design and scheduling.

# Performance Validation

Confirm the pipeline end to end before trusting an alert to stay silent. First, that the exporter query itself is fast and the series exist:

-- Time the exporter's heaviest query as the ts_exporter role would see it.
EXPLAIN (ANALYZE, TIMING ON)
SELECT job_id, proc_name, hypertable_name,
       total_failures, EXTRACT(EPOCH FROM (now() - last_successful_finish))
FROM monitoring.job_health;

If that runs in single-digit milliseconds you have ample headroom under a 30-second scrape. Then confirm Prometheus is actually storing the series — query the exporter endpoint and the Prometheus API:

# Raw exporter output should contain the custom metric families.
curl -s http://ts-exporter:9187/metrics | grep '^timescaledb_jobs_'

# Prometheus should report matching series with the expected labels.
curl -s 'http://prometheus:9090/api/v1/series?match[]=timescaledb_jobs_total_failures'

Finally, validate the alert logic without waiting for a real incident: in the Prometheus expression browser, evaluate the alert expression directly (timescaledb_jobs_seconds_since_success{proc_name="policy_refresh_continuous_aggregate"}) and confirm the returned value tracks the seconds_since_success you can compute by hand from job_stats.last_successful_finish. A quick cross-check against the source view keeps the exporter honest:

SELECT job_id, proc_name,
       EXTRACT(EPOCH FROM (now() - last_successful_finish))::int AS seconds_since_success
FROM timescaledb_information.job_stats
JOIN timescaledb_information.jobs USING (job_id)
WHERE proc_name = 'policy_refresh_continuous_aggregate';

# Troubleshooting

Series count balloons and Prometheus memory climbs after a config change. You added a high-cardinality label — almost always chunk_name or a raw timestamp. Check sum(scrape_samples_scraped) by job and count distinct labels with count by (__name__)({job="timescaledb"}). Remove the offending LABEL mapping and aggregate to the hypertable in SQL. Cardinality is set by the query, not by Prometheus, so the fix is always in queries.yaml.

Metrics go stale — the graph flatlines at the last value. Either the exporter scrape is failing or the custom query is erroring. Check Prometheus Status → Targets for a non-UP state and a scrape error; then read the exporter’s own logs. A query that raises inside Postgres (a renamed view, a dropped hypertable in the UNION ALL) makes postgres_exporter drop that whole metric family, so the series simply stops updating rather than erroring loudly.

pq: permission denied for view job_stats in the exporter log. The ts_exporter role lacks SELECT on a view it queries. Re-run the grants from the prerequisites, and remember that job_stats exposes rows filtered by ownership — the exporter role sees stats only for jobs it is entitled to, so a role that owns nothing may return an empty set rather than an error. Prefer the monitoring.job_health view with an explicit grant.

scrape_timeout exceeded intermittently. The compression stats function is expensive on hypertables with many chunks, and it runs every scrape. Raise scrape_timeout, lengthen scrape_interval for this job, or move hypertable_compression_stats into a materialized snapshot refreshed on its own schedule that the exporter reads cheaply.

Alert never fires even though a job failed. total_failures is a counter, so a bare > 0 comparison fires forever once any failure has ever happened. Use increase(timescaledb_jobs_total_failures[15m]) > 0 to catch only new failures in a window, and confirm the rule file is listed under rule_files and shows on Status → Rules.

Frequently Asked Questions

# Related & Navigation

← Back to Monitoring, Observability & Alerting for TimescaleDB Automation

• Exporting TimescaleDB Job Metrics to Prometheus — the exporter query patterns for job health in depth.
• Building a Grafana Dashboard for Chunk Health — panel-by-panel dashboard construction from these series.
• Job Stats & Scheduler Diagnostics — the source views behind the job metrics.
• Compression & Retention Observability — turning the byte-total series into ratio trends.
• Background Worker & Lock Monitoring — extending the exporter to worker and lock signals.