Skip to content
Documentation Prelude Collector

TimescaleDB output

Write collected telemetry to a TimescaleDB / PostgreSQL hypertable, one row per numeric metric. Self-provisioning, compression and retention policies, an hourly rollup, and example configuration.

The TimescaleDB output writes collected telemetry to a TimescaleDB / PostgreSQL hypertable, one row per numeric metric. Choose it when you want collected data in SQL — for ad-hoc queries, joins against your own inventory tables, or Grafana dashboards driven by the PostgreSQL datasource. After the first mention this page refers to Prelude Collector as "the collector".

When to use

Pick TimescaleDB when you want full SQL over collected telemetry with storage you control: ad-hoc joins, window functions, compression and retention policies, and an hourly rollup for cheap long-range queries. It overlaps with InfluxDB — choose TimescaleDB if your team already runs PostgreSQL or prefers SQL over Flux. See Output selection for how it compares to the other backends.

How it works

Each record is flattened into one row per numeric field. The destination is a fixed, narrow schema shared by every model:

Column Type Source
time timestamptz the record's timestamp, or the current time if none is set
device text device hostname
device_id bigint device ID — joins cleanly against an inventory table
model text data-model name
key text per-record key (e.g. an interface name)
metric text the flattened field name
value double precision the numeric value

Non-numeric values (strings such as "up", booleans, nested objects) are skipped — TimescaleDB stores metrics, not labels. Numeric strings such as "1e-12" are kept. Rows are buffered and written in bulk with COPY, flushed when the buffer reaches the batch size or the flush interval expires, whichever comes first.

Self-provisioning

On connect the backend creates everything it needs, idempotently — re-enabling an already-provisioned output is a no-op:

  • The destination table (default collector_metrics) and an analytics index on (device_id, metric, time DESC).
  • Promotion to a hypertable on time. If the timescaledb extension is not installed this step is skipped with a warning and the backend runs against a plain PostgreSQL table instead — telemetry still flows, only the policies below are skipped.
  • Analytics policies: chunk sizing, columnar compression, raw retention, and an hourly continuous aggregate. Each is best-effort and independent.

The hourly rollup

With the continuous aggregate enabled (the default), a materialized view <table>_hourly rolls the raw data into hourly buckets with avg, max, min, and a sample count per series, kept current by a refresh policy. Point long-range dashboards at the rollup instead of scanning raw rows.

Connection settings

Configure this backend through PUT /api/v1/outputs/timescaledb.

Field JSON key Type Required Default Description
Host host string Yes — TimescaleDB / PostgreSQL host
Port port int No 5432 Server port
Database database string Yes — Target database
Username username string Yes — Connection user
Password password string Yes — Connection password. Sensitive — masked as "********" in API responses.
SSL mode ssl-mode string No require disable, allow, prefer, require, verify-ca, verify-full
Hypertable table string No collector_metrics Destination table name (auto-created)
Batch size batch-size int No 1000 Rows buffered before a COPY flush
Flush interval flush-interval int No 5000 Maximum time between flushes (ms)

password is stored encrypted. When updating the configuration, sending "" or "********" preserves the existing value — only re-supply the password when it has actually changed.

Analytics policies

These provision TimescaleDB-native policies and need the timescaledb extension. Each interval takes a <N> <unit> value such as 7 days or 2 years. Leave a field blank to apply the recommended default; set it to off to disable that policy.

Field JSON key Default Description
Chunk interval chunk-interval 1 day Hypertable chunk size
Compress after compress-after 7 days Compress chunks older than this
Raw retention retention-period 90 days Drop raw chunks older than this; off keeps raw forever
Continuous aggregate continuous-aggregate on Create the <table>_hourly hourly rollup
Rollup retention aggregate-retention 2 years Drop rollup buckets older than this

Configuration example

{
  "enabled": true,
  "config": {
    "host": "timescaledb.example.com",
    "port": 5432,
    "database": "prelude_metrics",
    "username": "collector",
    "password": "<your-db-password>",
    "ssl-mode": "require",
    "table": "collector_metrics",
    "batch-size": 1000,
    "flush-interval": 5000,
    "compress-after": "7 days",
    "retention-period": "90 days",
    "aggregate-retention": "2 years"
  }
}

Apply it with:

export BASE="https://collector.example.com"
export TOKEN="<your-api-token>"

curl -s -X PUT "$BASE/api/v1/outputs/timescaledb" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d @timescaledb-output.json

Bruno: 08 Outputs / Update output config

Validate connectivity

POST /api/v1/outputs/timescaledb/detect runs three checks and stops at the first failure:

Check Meaning
reachable The host accepted a connection
authenticated The credentials were accepted on the target database
timescaledb-extension The timescaledb extension is installed — a warning, not a failure; the backend falls back to a plain table without it

Send the candidate configuration in the request body, or omit the body to probe the saved configuration.

Behavior on failure

Writes are asynchronous and batched: the per-batch write call returns success unconditionally, so — as with InfluxDB — the TimescaleDB backend's failures counter on /api/v1/outputs/metrics is always zero. A failed batch is dropped (rather than retried, which would stall the pipeline) and logged. Watch for TimescaleDB warnings in the collector log channel if you need a programmatic signal.

The in-memory buffer is bounded — if the database is unreachable long enough, the oldest rows are dropped in favor of fresh telemetry. There is no on-disk buffer, so rows held in memory at a hard crash are lost. Durable, replayable delivery is NATS's or Kafka's job, not this analytics sink's.

Limitations

  • Only numeric fields are stored; string and boolean values are dropped. Use NATS, Kafka, or webhook for non-numeric payloads.
  • The schema and column set are fixed (time, device, device_id, model, key, metric, value); custom columns are not configurable.
  • There is no on-disk write-ahead buffer — in-flight rows are lost on a hard crash before flush, and the buffer drops its oldest rows once a wedged database exceeds the bound.
  • The analytics policies require the timescaledb extension; on plain PostgreSQL the table works but stays unmanaged (no compression, retention, or rollup).

See also

Filtering by: