tutorial

How to Monitor DuckDB with Vigilmon

DuckDB is the go-to embedded OLAP engine for analytics pipelines — but silent query hangs, memory exhaustion, and extension load failures can crash your data processing jobs invisibly. Learn how to monitor query execution health, memory utilization, extension status, and pipeline liveness with Vigilmon.

DuckDB is an in-process analytical database designed for fast OLAP queries directly inside Python, R, Node.js, and Go applications. It reads Parquet, CSV, JSON, and Arrow data at native speed without a server daemon, making it the engine of choice for analytics pipelines, data science workflows, embedded BI, and ETL jobs that run in serverless or container environments.

But DuckDB failures are deceptively quiet. A query that consumed all available memory and was killed mid-execution, an extension that failed to load and silently fell back to a slower path, or a long-running pipeline that stopped making progress — none of these show up in a process monitor because the DuckDB process stays alive even when queries are failing. Vigilmon adds external visibility through custom health endpoints wrapped around your DuckDB application code, plus heartbeat monitors for pipeline liveness.

Why DuckDB Needs External Monitoring

DuckDB has no server process or admin console to probe — monitoring must be embedded in or alongside your application. External monitoring with Vigilmon provides:

  • Query execution health checks that run a lightweight test query and verify the engine is responsive
  • Memory utilization alerts before DuckDB exhausts its configured memory limit and begins spilling to disk or failing queries
  • Extension load status checks to verify spatial, httpfs, parquet, and other extensions loaded correctly at startup
  • Pipeline liveness heartbeats for ETL jobs that must prove they are making forward progress
  • Embedded OLAP metrics surfaced as a health endpoint so Vigilmon can probe them from outside your application

Step 1: Create a DuckDB Health Check Module

DuckDB has no standalone /health endpoint — you embed one in your application. The simplest approach is a lightweight Flask or FastAPI sidecar that wraps DuckDB queries:

# duckdb_health.py
import duckdb, os, time, threading
from flask import Flask, jsonify

app = Flask(__name__)

DB_PATH = os.environ.get('DUCKDB_PATH', ':memory:')
MEMORY_LIMIT_MB = int(os.environ.get('DUCKDB_MEMORY_LIMIT_MB', '4096'))
MEMORY_WARN_PCT = float(os.environ.get('DUCKDB_MEMORY_WARN_PCT', '0.80'))

# Connection pool: reuse one connection for health checks to avoid overhead
_conn = None
_conn_lock = threading.Lock()

def get_conn():
    global _conn
    with _conn_lock:
        if _conn is None:
            _conn = duckdb.connect(DB_PATH)
            _conn.execute(f"SET memory_limit='{MEMORY_LIMIT_MB}MB'")
        return _conn

@app.route('/health/duckdb')
def health():
    issues = []
    conn = get_conn()

    # Basic query execution health
    try:
        t0 = time.time()
        result = conn.execute("SELECT 42 AS ping").fetchone()
        latency_ms = int((time.time() - t0) * 1000)
        if result[0] != 42:
            return jsonify(status='down', reason='query_returned_wrong_result'), 503
    except Exception as e:
        return jsonify(status='down', reason=f'query_failed: {e}'), 503

    # Memory usage check
    try:
        mem_row = conn.execute(
            "SELECT current_setting('memory_limit') AS limit"
        ).fetchone()
        # DuckDB memory usage via pragma
        mem_usage = conn.execute(
            "SELECT * FROM pragma_database_size()"
        ).fetchone()
    except Exception:
        mem_usage = None

    # Extension status
    try:
        extensions = conn.execute(
            "SELECT extension_name, loaded, installed FROM duckdb_extensions()"
        ).fetchall()
        ext_status = {row[0]: {'loaded': row[1], 'installed': row[2]}
                      for row in extensions}
        critical_exts = os.environ.get('DUCKDB_REQUIRED_EXTENSIONS', '').split(',')
        for ext in critical_exts:
            ext = ext.strip()
            if ext and ext_status.get(ext, {}).get('loaded') is False:
                issues.append(f'extension_{ext}_not_loaded')
    except Exception as e:
        issues.append(f'extension_check_failed: {e}')
        ext_status = {}

    if issues:
        return jsonify(
            status='degraded',
            issues=issues,
            latency_ms=latency_ms,
        ), 503

    return jsonify(
        status='ok',
        latency_ms=latency_ms,
        db_path=DB_PATH,
        extensions_loaded=sum(1 for v in ext_status.values() if v.get('loaded')),
        extensions_total=len(ext_status),
    ), 200

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=3020)

For file-backed databases, this same pattern works — set DUCKDB_PATH=/data/analytics.duckdb and the health check opens the same database file your application uses.

Step 2: Monitor Memory Utilization and Spill-to-Disk

DuckDB enforces a configurable memory limit and spills to disk when the limit is approached. Disk spill dramatically slows query execution. Add a dedicated memory health check:

@app.route('/health/duckdb/memory')
def memory():
    conn = get_conn()
    issues = []

    try:
        # Query memory allocation state
        # DuckDB exposes resource usage through internal views
        rows = conn.execute("""
            SELECT
                database_size,
                block_size,
                free_blocks,
                total_blocks
            FROM pragma_database_size()
        """).fetchone()

        if rows:
            total_blocks = rows[3] or 0
            free_blocks  = rows[2] or 0
            used_blocks  = total_blocks - free_blocks
            fill = used_blocks / total_blocks if total_blocks > 0 else 0

            if fill >= MEMORY_WARN_PCT:
                issues.append(f'storage_fill_{int(fill*100)}pct')

            if issues:
                return jsonify(
                    status='degraded',
                    issues=issues,
                    used_blocks=used_blocks,
                    total_blocks=total_blocks,
                    fill_pct=round(fill * 100, 1),
                ), 503

            return jsonify(
                status='ok',
                used_blocks=used_blocks,
                total_blocks=total_blocks,
                fill_pct=round(fill * 100, 1),
                database_size_bytes=rows[0],
            ), 200

        return jsonify(status='ok', note='no_database_size_data'), 200

    except Exception as e:
        return jsonify(status='error', reason=str(e)), 503

For applications where you set an explicit memory_limit, add a test query that intentionally uses a known amount of memory and verify it completes within your time threshold — this catches spill-to-disk conditions before they affect production queries.

Step 3: Extension Load Status Checks

DuckDB extensions — httpfs for S3/HTTP access, spatial for geospatial queries, parquet for Parquet reading, json for JSON parsing — must be explicitly installed and loaded. An extension that failed to load causes silent query failures or falls back to slower code paths.

@app.route('/health/duckdb/extensions')
def extensions():
    conn = get_conn()

    try:
        rows = conn.execute("""
            SELECT
                extension_name,
                loaded,
                installed,
                description
            FROM duckdb_extensions()
            ORDER BY extension_name
        """).fetchall()

        result = []
        issues = []
        required = set(filter(None, os.environ.get(
            'DUCKDB_REQUIRED_EXTENSIONS', 'parquet,json').split(',')))

        for name, loaded, installed, desc in rows:
            status = 'ok' if loaded else ('installed_not_loaded' if installed else 'not_installed')
            result.append({'name': name, 'status': status})
            if name in required and not loaded:
                issues.append(f'required_extension_{name}_{status}')

        if issues:
            return jsonify(status='degraded', issues=issues, extensions=result), 503

        return jsonify(status='ok', extensions=result), 200

    except Exception as e:
        return jsonify(status='error', reason=str(e)), 503

Set DUCKDB_REQUIRED_EXTENSIONS=parquet,httpfs,spatial to enumerate which extensions must be loaded for your workload. The health check returns 503 if any required extension is absent.

Step 4: Configure Vigilmon HTTP Monitors

  1. Log in to vigilmon.online and go to Monitors → New Monitor
  2. Choose HTTP / HTTPS
  3. Set the URL to your DuckDB health endpoint: https://your-app.example.com/health/duckdb
  4. Set the check interval to 1 minute
  5. Under Expected response, configure:
    • Status code: 200
    • Response body contains: "status":"ok"
    • Response time threshold: 5000ms
  6. Under Alert channels, assign your Slack or PagerDuty integration
  7. Save the monitor

Add monitors for each health dimension:

| Monitor URL | Purpose | Interval | |---|---|---| | /health/duckdb | Query execution health + extension status | 1 min | | /health/duckdb/memory | Storage fill and block utilization | 2 min | | /health/duckdb/extensions | Extension load status per extension | 5 min |

For long-running analytics servers, set the response time threshold higher on the main health monitor (up to 10000ms) to account for warm-up queries on cold databases.

Step 5: Heartbeat Monitoring for DuckDB ETL Pipelines

DuckDB is frequently used in batch ETL pipelines — scheduled jobs that ingest data, run transformations, and write outputs. A pipeline that hanged on a complex aggregation, ran out of disk for spill files, or lost S3 access mid-run may have the DuckDB process alive but making no progress.

Vigilmon heartbeat monitors detect pipeline stalls: your ETL job pings Vigilmon after each successful step. Silence triggers an alert.

Set Up the Heartbeat Monitor

  1. In Vigilmon, go to Monitors → New Monitor → Heartbeat
  2. Set the name: duckdb-etl-pipeline
  3. Set the expected interval: 30 minutes (match your pipeline step cadence)
  4. Set the grace period: 45 minutes
  5. Save — copy the heartbeat URL: https://vigilmon.online/heartbeat/abc123xyz

Wire Into Your ETL Pipeline

# etl_pipeline.py
import duckdb, requests, os
from pathlib import Path

VIGILMON_URL = os.environ['VIGILMON_HEARTBEAT_URL']
S3_BUCKET    = os.environ['S3_BUCKET']

def ping_vigilmon():
    try:
        requests.get(VIGILMON_URL, timeout=5)
    except Exception:
        pass

def run_pipeline():
    conn = duckdb.connect()
    conn.execute("INSTALL httpfs; LOAD httpfs;")
    conn.execute(f"""
        SET s3_region='{os.environ["AWS_REGION"]}';
        SET s3_access_key_id='{os.environ["AWS_ACCESS_KEY_ID"]}';
        SET s3_secret_access_key='{os.environ["AWS_SECRET_ACCESS_KEY"]}';
    """)

    # Step 1: Ingest raw data from S3
    conn.execute(f"""
        CREATE TABLE raw AS
        SELECT * FROM read_parquet('s3://{S3_BUCKET}/raw/*.parquet')
    """)
    ping_vigilmon()
    print("Step 1: ingested raw data")

    # Step 2: Run transformations
    conn.execute("""
        CREATE TABLE transformed AS
        SELECT
            date_trunc('day', event_time) AS day,
            user_id,
            COUNT(*) AS events,
            SUM(value) AS total_value
        FROM raw
        GROUP BY 1, 2
    """)
    ping_vigilmon()
    print("Step 2: transformations complete")

    # Step 3: Write outputs
    conn.execute(f"""
        COPY transformed TO 's3://{S3_BUCKET}/processed/output.parquet'
        (FORMAT parquet, OVERWRITE_OR_IGNORE true)
    """)
    ping_vigilmon()
    print("Step 3: output written")

    conn.close()

if __name__ == '__main__':
    run_pipeline()

Send a heartbeat at each major pipeline stage. If a stage hangs, the heartbeat for subsequent stages will never arrive and Vigilmon will alert.

Step 6: Alert Routing

| Monitor | Alert Channel | Priority | |---|---|---| | Query execution /health/duckdb | Slack + PagerDuty | P1 | | Memory utilization /health/duckdb/memory | Slack | P2 | | Extension status /health/duckdb/extensions | Slack | P2 | | Heartbeat: ETL pipeline | Slack + email | P2 |

Latency threshold guidelines:

  • 1000ms for the basic ping query (SELECT 42) — anything slower indicates I/O contention or a cold database file
  • 5000ms for the main health check — includes extension enumeration overhead
  • If the pipeline heartbeat is 30 minutes, alert after 45 minutes of silence (1.5× the expected interval)

For databases backed by network storage (NFS, S3-mounted filesystems), add a response time alert at 3000ms on the health endpoint — slow I/O shows up as query latency well before it causes outright failures.

Summary

DuckDB failures are invisible at the process level — the engine stays alive even when queries are failing or pipelines are stalled. External monitoring with Vigilmon surfaces these conditions before they affect your analytics or downstream consumers:

| Monitor Type | What It Covers | |---|---| | HTTP monitor on /health/duckdb | Query execution health and latency | | HTTP monitor on /health/duckdb/memory | Storage fill and block utilization | | HTTP monitor on /health/duckdb/extensions | Extension load and installation status | | Heartbeat monitor | ETL pipeline step liveness |

Get started free at vigilmon.online — your first DuckDB monitor is running in under two minutes.

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