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.
Free MinervaDB whitepaper (PDF)
We packaged this method into a free, 8-page MinervaDB engineering whitepaper, Using pgvector for Timeseries Data: A MinervaDB Field Guide to Vector-Based Anomaly Detection in PostgreSQL. It expands on schema design, window normalization, per-cluster profiles, index tuning, and an end-to-end worked example. Enter your details below for instant access.Frequently asked questions
Is pgvector only for AI use cases?
No. pgvector stores and compares fixed-length numeric arrays. Embeddings are one source of those arrays, but a normalized window of any metric is equally valid, which is why pgvector for timeseries data works so well.How long should each window be?
Long enough to capture the pattern you care about and short enough to stay responsive. MinervaDB commonly starts near 20 to 60 samples per window and tunes from there against real data.Why normalize each window?
Normalization (for example a per-window z-score) makes detection focus on the shape of the signal rather than its baseline level, so a busy host and an idle host are judged by behavior, not raw magnitude.Does this replace a full ML anomaly system?
Not always, but it covers a large share of practical cases with far less operational cost, and it is an excellent first line of defense that lives entirely inside PostgreSQL.Conclusion
Vectors are not just for AI. As this guide to pgvector for timeseries data has shown, by slicing a signal into fixed-length windows, normalizing them, and comparing them with distance operators, pgvector for timeseries data gives you a clean, model-free way to detect abnormal behavior directly in PostgreSQL. It is simple to reason about, cheap to operate, and it scales with a single index. MinervaDB uses variations of this pattern to help teams get real value from data they already store.Related MinervaDB PostgreSQL resources
Learn more about our PostgreSQL consulting and performance work, our PostgreSQL DBA services, and further engineering deep-dives on the MinervaDB blog. For the extension itself, see the pgvector project on GitHub and the PostgreSQL window functions documentation.Running this in production?
MinervaDB provides PostgreSQL Consulting, PostgreSQL Support and PostgreSQL Remote DBA with 24x7 coverage and a 15-minute S1 response. Talk to an engineer.
