Similarity search and a time range, in one Postgres hypertable

Operator notes are time-series data. Each one has a timestamp, a site, a temperature, and a sentence about what happened. Searching them by meaning usually means a second database for the embeddings, plus a job to keep the two stores in sync.

I kept the embedding on the same TimescaleDB hypertable as the note. A question, a site, and a time window are one SQL statement.

The code is at github.com/tharun634/ghw-tiger-vector-search.

The row

ghw_vectors.operator_notes is a hypertable partitioned on observed_at. Eighteen notes span 22 September through 13 October 2026. Each row holds the note text, the site, the line, a severity, the temperature, the humidity, and a vector(384) from sentence-transformers/all-MiniLM-L6-v2, embedded locally with fastembed.

The primary key is (site, observed_at, note_id). On a hypertable every unique constraint has to include the partition column, so a lone id primary key is not valid.

Columnstore is off. A vector index cannot sit on a compressed chunk. The daily temperature rollup is a normal view over time_bucket, so it stays correct without a separate refresh step.

The index

pgvector 0.8.7 supplies the vector type and the cosine operator <=>. pgvectorscale 0.9.0 supplies StreamingDiskANN. On this Tiger Cloud service, DiskANN has a cosine operator class for vector and none for halfvec, so the column is vector(384). Halfvec plus HNSW is the usual default when you only have pgvector. DiskANN is the index Tiger documents for this kind of vector search. At 384 dimensions the memory saving from halfvec is small anyway.

The index is built after the load:

CREATE INDEX operator_notes_embedding_diskann
    ON ghw_vectors.operator_notes
    USING diskann (embedding vector_cosine_ops);

One query

The question was “the air conditioning broke and the room overheated”, limited to the warehouse from 1 October through 15 October:

SELECT observed_at, site, temperature_c,
       embedding <=> $1::vector(384) AS cosine_distance,
       body
FROM ghw_vectors.operator_notes
WHERE site = 'warehouse'
  AND observed_at >= '2026-10-01'::timestamptz
  AND observed_at <  '2026-10-15'::timestamptz
ORDER BY embedding <=> $1::vector(384)
LIMIT 5;

$1 is the same MiniLM model that embedded the rows.

The closest note is a packing-hall chiller trip at 31.4°C (cosine distance 0.5113). Next is an air handler that stopped moving air at 30.2°C, then a cooling-unit fault at 29.8°C. None of those sentences contain “air conditioning” or “overheated”.

The same question with no site filter also ranks a greenhouse heater failure at 12.4°C. The site predicate removes it. The daily view on the same table shows the warehouse peak of 31.4°C on 4 October, the same moment as the closest note.

At 18 rows the planner does not use the DiskANN index. It drops chunks outside the time range, filters site with the btree, and exact-sorts what remains. That is the right plan at this size. The index is valid for when an approximate scan is cheaper than reading the rows.

Why the embedding sits on the note

A separate vector table means a join on every filtered search, and a sync path when a note arrives. Google’s write-up of inline versus separate vector tables measures that join. Putting the vector on the hypertable lets the time partition throw away chunks before any distance math.

DevOps Daily measured full-text, pgvector, and hybrid search on one corpus. pgvector and BM25 tied overall because they win opposite questions: vectors did better on natural language, keywords did better on jargon. Most of the vector latency was the call that turns the question into a vector, not the index. Embedding locally, with the same model for writes and reads, keeps that cost on the machine running the query.

jamilxt’s note on dedicated vector databases makes the other half of the argument: a second store adds a sync job and a second failure mode. For this set of notes, Postgres was the whole retrieval path.

Reproduce

py -3.14 -m venv .venv
.\.venv\Scripts\python.exe -m pip install -r requirements.txt
.\.venv\Scripts\python.exe scripts\build_seed.py
.\.venv\Scripts\python.exe scripts\write_search_sql.py

Apply sql/001_schema.sql, sql/002_seed.sql, sql/003_index.sql, and sql/004_search.sql. The seed and search files already contain the vectors used for the loaded data.