tutorial

Monitoring TimescaleDB: Chunk Compression, Continuous Aggregates, and Vigilmon Time-Series SLAs

A practical guide to monitoring TimescaleDB — chunk compression status, continuous aggregate refresh lag, retention policy execution, hypertable query performance, and Vigilmon for time-series database SLAs.

TimescaleDB's value proposition depends on features that run silently in the background: chunk compression reduces storage costs, continuous aggregates keep dashboards fast, and retention policies prevent unbounded growth. When any of these background processes stalls — a compression job that errors out, a continuous aggregate that stops refreshing, a retention policy that hasn't run in a week — the symptoms appear gradually: storage fills up, dashboards slow down, queries time out under load. By the time you notice, you're already in trouble.

Vigilmon provides external HTTP monitoring for TimescaleDB health endpoints and heartbeat monitors for scheduled jobs. This tutorial covers the full operational stack for time-series production deployments.

TimescaleDB Background Jobs

TimescaleDB's automation center is the _timescaledb_config.bgw_job table and its scheduling framework. Background jobs include:

  • Compression policy — compresses old chunks according to per-hypertable policies
  • Continuous aggregate refresh — refreshes materialized aggregate views
  • Retention policy — drops chunks older than the configured interval
  • Reorder policy — reorders chunks by a specified index for query performance

Monitor these from PostgreSQL:

-- All background jobs and their last run status
SELECT
    j.id,
    j.application_name,
    j.scheduled,
    j.schedule_interval,
    js.last_run_started_at,
    js.last_successful_finish,
    js.last_run_status,
    js.next_scheduled_run,
    js.total_runs,
    js.total_failures
FROM timescaledb_information.jobs j
LEFT JOIN timescaledb_information.job_stats js ON j.id = js.job_id
ORDER BY j.id;

A last_run_status of 'Failed' or a next_scheduled_run that is hours overdue are actionable signals.

Step 1: Build a TimescaleDB Health Endpoint

Expose TimescaleDB health via a FastAPI endpoint your application or a monitoring sidecar provides:

import asyncpg
import os
from datetime import datetime, timezone
from fastapi import FastAPI
from fastapi.responses import JSONResponse

app = FastAPI()

async def get_db_conn():
    return await asyncpg.connect(os.environ["DATABASE_URL"])

@app.get("/health/timescaledb")
async def timescaledb_health():
    try:
        conn = await get_db_conn()

        # Check TimescaleDB extension is installed and version
        version = await conn.fetchval(
            "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb'"
        )
        if not version:
            await conn.close()
            return JSONResponse(status_code=503, content={
                "status": "down",
                "error": "TimescaleDB extension not installed",
            })

        # Check for failed background jobs
        failed_jobs = await conn.fetch("""
            SELECT
                j.application_name,
                js.last_run_started_at,
                js.last_run_status,
                js.total_failures
            FROM timescaledb_information.jobs j
            JOIN timescaledb_information.job_stats js ON j.id = js.job_id
            WHERE js.last_run_status = 'Failed'
            AND js.last_run_started_at > NOW() - INTERVAL '24 hours'
        """)

        await conn.close()

        failed = [dict(r) for r in failed_jobs]
        status = "ok" if not failed else "degraded"
        code = 200 if not failed else 503

        return JSONResponse(status_code=code, content={
            "status": status,
            "timescaledb_version": version,
            "failed_jobs_last_24h": failed,
        })
    except Exception as e:
        return JSONResponse(status_code=503, content={"status": "down", "error": str(e)})

Step 2: Chunk Compression Status

Chunk compression is one of TimescaleDB's most impactful features — it can reduce storage by 90%+. When compression stops working, storage costs grow unchecked. Monitor it explicitly:

@app.get("/health/timescaledb/compression")
async def compression_health():
    try:
        conn = await get_db_conn()

        # Check uncompressed chunks that should be compressed by now
        uncompressed = await conn.fetch("""
            SELECT
                hypertable_name,
                chunk_name,
                range_start,
                range_end,
                is_compressed
            FROM timescaledb_information.chunks
            WHERE is_compressed = false
            AND range_end < NOW() - INTERVAL '7 days'
            ORDER BY range_end DESC
            LIMIT 20
        """)

        # Check compression job failures
        compression_jobs = await conn.fetch("""
            SELECT
                j.application_name,
                js.last_run_status,
                js.last_successful_finish,
                js.total_failures,
                EXTRACT(EPOCH FROM (NOW() - js.last_successful_finish)) AS seconds_since_success
            FROM timescaledb_information.jobs j
            JOIN timescaledb_information.job_stats js ON j.id = js.job_id
            WHERE j.application_name ILIKE '%compress%'
        """)

        await conn.close()

        stale = [dict(r) for r in uncompressed]
        jobs = [dict(r) for r in compression_jobs]

        # Alert if chunks older than 7 days are uncompressed (compression policy should have caught them)
        degraded = len(stale) > 0 or any(j.get("last_run_status") == "Failed" for j in jobs)
        status = "degraded" if degraded else "ok"
        code = 503 if degraded else 200

        return JSONResponse(status_code=code, content={
            "status": status,
            "stale_uncompressed_chunks": stale,
            "compression_jobs": jobs,
        })
    except Exception as e:
        return JSONResponse(status_code=503, content={"status": "down", "error": str(e)})

Step 3: Continuous Aggregate Refresh Lag

Continuous aggregates pre-materialize expensive time-series aggregations. When they stop refreshing, dashboards show stale data and users query raw hypertables instead — causing performance regressions:

from datetime import timedelta

CAGG_LAG_THRESHOLD_MINUTES = 30  # Alert if aggregate is more than 30 minutes stale

@app.get("/health/timescaledb/aggregates")
async def continuous_aggregate_health():
    try:
        conn = await get_db_conn()

        # Get continuous aggregate refresh job stats
        cagg_jobs = await conn.fetch("""
            SELECT
                j.application_name,
                j.hypertable_name,
                js.last_successful_finish,
                js.last_run_status,
                js.next_scheduled_run,
                EXTRACT(EPOCH FROM (NOW() - js.last_successful_finish)) / 60.0 AS lag_minutes
            FROM timescaledb_information.jobs j
            JOIN timescaledb_information.job_stats js ON j.id = js.job_id
            WHERE j.application_name ILIKE '%refresh%continuous%'
               OR j.application_name ILIKE '%continuous_agg%'
            ORDER BY lag_minutes DESC NULLS LAST
        """)

        await conn.close()

        jobs = [dict(r) for r in cagg_jobs]
        lagging = [
            j for j in jobs
            if (j.get("lag_minutes") or 0) > CAGG_LAG_THRESHOLD_MINUTES
            or j.get("last_run_status") == "Failed"
        ]

        status = "ok" if not lagging else "degraded"
        code = 200 if not lagging else 503

        return JSONResponse(status_code=code, content={
            "status": status,
            "threshold_minutes": CAGG_LAG_THRESHOLD_MINUTES,
            "lagging_aggregates": lagging,
            "all_jobs": jobs,
        })
    except Exception as e:
        return JSONResponse(status_code=503, content={"status": "down", "error": str(e)})

Step 4: Retention Policy Execution Check

Retention policies prevent hypertables from growing indefinitely. A policy that stops executing causes storage exhaustion:

@app.get("/health/timescaledb/retention")
async def retention_health():
    try:
        conn = await get_db_conn()

        # Check retention job health and when it last ran
        retention_jobs = await conn.fetch("""
            SELECT
                j.application_name,
                j.hypertable_name,
                js.last_run_status,
                js.last_successful_finish,
                js.next_scheduled_run,
                js.total_failures,
                EXTRACT(EPOCH FROM (NOW() - js.last_successful_finish)) / 3600.0 AS hours_since_success
            FROM timescaledb_information.jobs j
            JOIN timescaledb_information.job_stats js ON j.id = js.job_id
            WHERE j.application_name ILIKE '%retention%'
        """)

        # Also check total hypertable size to catch runaway growth
        hypertable_sizes = await conn.fetch("""
            SELECT
                hypertable_name,
                hypertable_schema,
                pg_size_pretty(total_bytes) AS total_size,
                total_bytes,
                num_chunks
            FROM timescaledb_information.hypertables h
            JOIN (
                SELECT hypertable_name, hypertable_schema,
                       hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass) AS total_bytes,
                       num_chunks
                FROM timescaledb_information.hypertables
            ) s USING (hypertable_name, hypertable_schema)
            ORDER BY total_bytes DESC
            LIMIT 10
        """)

        await conn.close()

        jobs = [dict(r) for r in retention_jobs]
        sizes = [dict(r) for r in hypertable_sizes]

        # Alert if retention job hasn't run successfully in over 25 hours (schedule is usually daily)
        overdue = [j for j in jobs if (j.get("hours_since_success") or 0) > 25]
        failed = [j for j in jobs if j.get("last_run_status") == "Failed"]

        status = "ok" if not (overdue or failed) else "degraded"
        code = 200 if not (overdue or failed) else 503

        return JSONResponse(status_code=code, content={
            "status": status,
            "overdue_jobs": overdue,
            "failed_jobs": failed,
            "top_hypertables_by_size": sizes,
        })
    except Exception as e:
        return JSONResponse(status_code=503, content={"status": "down", "error": str(e)})

Step 5: Hypertable Query Performance Probe

TimescaleDB's chunk exclusion and index optimization only work if the planner is using them correctly. A query latency probe confirms end-to-end hypertable performance:

import time

@app.get("/health/timescaledb/query-latency")
async def query_latency():
    try:
        conn = await get_db_conn()

        # Run a time-bounded aggregation query typical of time-series workloads
        # Replace 'your_metrics_table' and 'ts' with your hypertable's name and time column
        start = time.monotonic()
        await conn.fetchval("""
            SELECT COUNT(*)
            FROM your_metrics_table
            WHERE ts > NOW() - INTERVAL '1 hour'
        """)
        latency_ms = (time.monotonic() - start) * 1000

        await conn.close()

        status = "ok" if latency_ms < 500 else ("slow" if latency_ms < 2000 else "degraded")
        code = 200 if latency_ms < 2000 else 503

        return JSONResponse(status_code=code, content={
            "status": status,
            "query_latency_ms": round(latency_ms, 2),
        })
    except Exception as e:
        return JSONResponse(status_code=503, content={"status": "down", "error": str(e)})

Step 6: Configure Vigilmon Monitors

Core TimescaleDB Monitor

  1. Log in to vigilmon.online → Monitors → New Monitor → HTTP / HTTPS
  2. URL: https://your-api.example.com/health/timescaledb
  3. Check interval: 1 minute
  4. Expected status: 200; body contains: "status":"ok"
  5. Alert channel: Slack + PagerDuty

Compression Monitor

  1. Monitors → New Monitor → HTTP / HTTPS
  2. URL: https://your-api.example.com/health/timescaledb/compression
  3. Check interval: 15 minutes (compression is less time-sensitive than availability)
  4. Expected status: 200
  5. Alert channel: Slack (storage issue, not usually P1)

Continuous Aggregate Monitor

  1. Monitors → New Monitor → HTTP / HTTPS
  2. URL: https://your-api.example.com/health/timescaledb/aggregates
  3. Check interval: 5 minutes
  4. Expected status: 200; body contains: "status":"ok"
  5. Alert channel: Slack (stale aggregates degrade dashboard performance)

Retention Policy Monitor

  1. Monitors → New Monitor → HTTP / HTTPS
  2. URL: https://your-api.example.com/health/timescaledb/retention
  3. Check interval: 1 hour
  4. Expected status: 200
  5. Alert channel: Slack (daily check frequency; storage risk if missed)

Query Latency Monitor

  1. Monitors → New Monitor → HTTP / HTTPS
  2. URL: https://your-api.example.com/health/timescaledb/query-latency
  3. Check interval: 2 minutes
  4. Response time threshold: 5000ms
  5. Alert channel: Slack (slow queries indicate chunk exclusion failure or bloated hypertable)

Step 7: Heartbeat for Custom TimescaleDB Jobs

If you run custom TimescaleDB maintenance — manual chunk compression, periodic aggregate backfills, data imports — monitor them with Vigilmon heartbeats:

#!/bin/bash
# timescale-maintenance.sh
set -euo pipefail

VIGILMON_HEARTBEAT="${VIGILMON_HEARTBEAT_URL:?}"

# Force-compress any uncompressed chunks older than 7 days
psql "$DATABASE_URL" -c "
  SELECT compress_chunk(c.chunk_schema || '.' || c.chunk_name)
  FROM timescaledb_information.chunks c
  WHERE c.is_compressed = false
  AND c.range_end < NOW() - INTERVAL '7 days';
"

# Refresh continuous aggregates manually if needed
psql "$DATABASE_URL" -c "
  CALL refresh_continuous_aggregate('your_hourly_metrics', NULL, NOW() - INTERVAL '1 hour');
"

# Ping heartbeat only on success
curl -fsS "$VIGILMON_HEARTBEAT" > /dev/null
echo "Heartbeat sent"

Set the Vigilmon heartbeat interval to 1 day with a 2-hour grace period.

Alert Routing Summary

| Monitor | Severity | Channel | |---|---|---| | TimescaleDB availability | P1 | Slack + PagerDuty | | Chunk compression status | P2 | Slack | | Continuous aggregate lag | P2 | Slack | | Retention policy execution | P2 | Slack | | Query latency probe | P2 | Slack | | Maintenance job heartbeat | P2 | Email + Slack |

Summary

TimescaleDB monitoring covers more ground than a simple database health check — background jobs, compression policies, continuous aggregates, and retention policies all require independent monitoring surfaces. Vigilmon wraps all of them in external HTTP checks:

| Layer | Vigilmon Monitor | |---|---| | Core database availability | HTTP monitor on /health/timescaledb | | Chunk compression policy | HTTP monitor on /health/timescaledb/compression | | Continuous aggregate lag | HTTP monitor on /health/timescaledb/aggregates | | Retention policy execution | HTTP monitor on /health/timescaledb/retention | | Hypertable query performance | HTTP monitor on /health/timescaledb/query-latency | | Scheduled maintenance | Heartbeat monitor |

Start monitoring your TimescaleDB for free at vigilmon.online.

Monitor your app with Vigilmon

Free plan — 5 monitors, no credit card required. Up and running in 60 seconds.

Start free →
← Back to all articles