Why pgvector for timeseries data works
A timeseries is, at heart, an ordered list of numbers. If you slice a metric into equal-length segments — say, the last twenty samples of a host's CPU utilization — each segment becomes a point in a twenty-dimensional space. Two segments that look alike sit close together in that space; a segment that behaves strangely sits far away from everything else. This geometric view is exactly what pgvector was built to exploit: it stores those segments as a nativevector type and compares them with distance operators that can be backed by an approximate-nearest-neighbor index.
The practical payoff of using pgvector for timeseries data is that similarity search, clustering, and outlier detection all become ordinary SQL. You keep your metrics, your application data, and your analysis in one PostgreSQL instance, and you avoid the operational weight of a separate model-serving pipeline.
MinervaDB takeaway: Treat a fixed-length slice of any metric as a vector, and pgvector turns anomaly detection into a distance query — no model training, no extra infrastructure.
A sample telemetry schema
Throughout this guide we use a small, generic table of per-minute host metrics. Nothing here is domain-specific; the same approach works for latency, queue depth, temperature, request rates, or any other regularly sampled signal.CREATE TABLE host_metrics (
host_id text NOT NULL,
sampled_at timestamptz NOT NULL,
cpu_pct real NOT NULL, -- 0..100
PRIMARY KEY (host_id, sampled_at)
);
Shaping each metric into a fixed-length window
The first step in applying pgvector for timeseries data is to collect a rolling window of recent samples for each host. We use a window frame that gathers the current sample and the preceding nineteen, giving twenty values per window. Ordering inside the frame matters, so we sort by time.SELECT host_id, sampled_at,
array_agg(cpu_pct) OVER w AS window_vals
FROM host_metrics
WINDOW w AS (
PARTITION BY host_id
ORDER BY sampled_at
ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
);
Normalizing and storing the window as a vector
Raw percentages work, but MinervaDB recommends normalizing each window so that the shape of the signal drives similarity rather than its absolute level. A simple, robust choice is to subtract the window mean and divide by its standard deviation (a z-score per window). We then cast the twenty-element array to avector(20). Guarding against short windows keeps the cast valid.
CREATE MATERIALIZED VIEW host_windows AS
WITH framed AS (
SELECT host_id, sampled_at,
array_agg(cpu_pct) OVER w AS vals,
count(*) OVER w AS n,
avg(cpu_pct) OVER w AS mu,
stddev_pop(cpu_pct) OVER w AS sd
FROM host_metrics
WINDOW w AS (PARTITION BY host_id ORDER BY sampled_at
ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
)
SELECT host_id, sampled_at,
(SELECT array_agg((v - mu) / NULLIF(sd, 0))
FROM unnest(vals) AS v)::vector(20) AS shape
FROM framed
WHERE n = 20 AND sd > 0;
Watch out: a vector cannot contain NULL or non-finite values. Dividing by a zero standard deviation (a perfectly flat window) yields NULLs, so we exclude flat windows with
sd > 0. Handle those separately if a flat line is itself meaningful for you.Building a healthy behavior profile
To decide what is abnormal, we first describe what is normal. One lightweight approach is to compute a centroid — the average of every window vector — which represents the typical shape of the metric across the fleet.SELECT avg(shape) AS centroid FROM host_windows;
Scoring anomalies with distance operators
With a profile in hand, anomaly detection using pgvector for timeseries data is a single ranking query. We measure how far each window sits from the centroid using cosine distance (<=>) and surface the windows with the largest distance.
WITH profile AS (
SELECT avg(shape) AS centroid FROM host_windows
)
SELECT w.host_id, w.sampled_at,
w.shape <=> p.centroid AS anomaly_score
FROM host_windows w, profile p
ORDER BY anomaly_score DESC
LIMIT 25;
anomaly_score are the windows whose shape least resembles normal behavior — sudden spikes, stalls, oscillations, or step changes. Because the score is just a number, you can threshold it, alert on it, or join it back to your incident data for triage.
| Distance operator | Best when you care about |
|---|---|
Cosine <=> |
The shape/pattern of the window, ignoring scale |
Euclidean <-> |
Absolute differences between samples |
Inner product <#> |
Directional alignment on normalized vectors |
Indexing and scaling in production
On large fleets the window table grows quickly, so an approximate-nearest-neighbor index keeps distance queries fast. MinervaDB generally starts with an HNSW index matched to the distance operator you query with, then benchmarks recall and latency against real traffic.CREATE INDEX host_windows_hnsw ON host_windows USING hnsw (shape vector_cosine_ops);
Pro tip from MinervaDB: store the anomaly score alongside each window and index it. You then get both fast similarity search and cheap threshold-based alerting from the same table.
