Start pgvector with exact search and a tiny table

For a developer trying pgvector, start with exact search on a tiny table whose distances you can calculate. That gives you a reference for operator meaning, ordering, and filtering before an approximate index adds another source of behavior. A query that returns plausible text is not enough to establish that it is using the intended metric.

The pgvector README, retrieved on September 20, 2026, documents exact and approximate vector search. Its installation example identifies version 0.8.6; the example here was checked against that version’s README as well. The SQL below was executed in an isolated PostgreSQL 16.10 database with pgvector 0.8.0 on September 21, 2026, in Asia/Ho_Chi_Minh. The 0.8.6 README supplies the cited operator reference; it is not the version used for that local execution. No approximate index or production workload was benchmarked.

Create vectors with obvious directions

Use three-dimensional vectors so the schema resembles an embedding column while the arithmetic stays simple. The coordinates are synthetic and do not represent a real text-embedding model.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE demo_passages (
  id bigint PRIMARY KEY,
  label text NOT NULL,
  embedding vector(3) NOT NULL
);

INSERT INTO demo_passages (id, label, embedding) VALUES
  (1, 'same direction', '[2,0,0]'),
  (2, 'diagonal', '[1,1,0]'),
  (3, 'opposite direction', '[-1,0,0]');

Search with query vector [1,0,0]. The <=> operator is cosine distance in pgvector. Cosine distance is one minus cosine similarity, so smaller values are nearer under this metric.

SELECT id, label,
       embedding <=> '[1,0,0]'::vector AS cosine_distance
FROM demo_passages
ORDER BY embedding <=> '[1,0,0]'::vector, id
LIMIT 3;

The expected distances are 0 for the same-direction vector, approximately 0.2929 for the diagonal, and 2 for the opposite-direction vector. The secondary id ordering makes ties deterministic in this small exact-search example. These values follow from the chosen coordinates, not a performance measurement.

View full-size image ↗

The figure emphasizes ascending distance. Sorting descending would return the farthest vector first while still producing a valid SQL result. This is an easy integration mistake to catch with synthetic vectors before loading a large corpus.

Verify the operator before the index

Different distance operators represent different objectives. L2 distance, inner product, and cosine distance are not interchangeable for arbitrary vectors. The model’s embedding documentation should guide the metric and normalization assumptions.

The OpenAI embeddings guide describes embedding generation and similarity use. Other models may require distinct query and document processing or have different conventions. Store the model identifier and preprocessing version with the index rather than inferring compatibility from vector dimensions alone.

A dimension mismatch should fail clearly. A same-length vector from an incompatible model may pass the type check and still produce meaningless neighbors. Keep separate vector spaces during migration, and compare their retrieval quality using labeled queries before moving production traffic.

Add filters as part of correctness

A real passage table may contain tenant, document revision, language, and visibility fields. Apply required authorization filters before returning candidates to the application. A vector score should never determine whether a user is allowed to read a document.

For example, a tenant-scoped query needs a tenant predicate derived from the authenticated request. Do not trust a tenant identifier generated by the language model. If the same retrieval result is cached, the cache key must preserve that scope and any other condition that affects which documents are eligible.

Filters also matter when you later add approximate indexes. Depending on the index and query plan, filtering can reduce the number of qualifying results after candidates are explored. The pgvector documentation discusses filtered approximate searches and tuning options. Test those paths against the exact reference rather than assuming a LIMIT 10 query will always produce ten authorized results when enough exist.

Let an unauthorized nearest neighbor challenge the query

Extend the toy table with two scope fields. The default values assign the three existing rows to the demo tenant and make them active. This is fixture setup, not a migration policy for existing production data. Two new rows point in the query’s direction: one belongs to another tenant, while the other is an inactive demo passage.

ALTER TABLE demo_passages
  ADD COLUMN tenant_id text NOT NULL DEFAULT 'demo',
  ADD COLUMN active boolean NOT NULL DEFAULT true;

INSERT INTO demo_passages (id, label, embedding, tenant_id, active) VALUES
  (4, 'other tenant', '[1,0,0]', 'other', true),
  (5, 'inactive passage', '[1,0,0]', 'demo', false);

SELECT id, label, embedding <=> '[1,0,0]'::vector AS cosine_distance
FROM demo_passages
WHERE tenant_id = 'demo' AND active
ORDER BY embedding <=> '[1,0,0]'::vector, id
LIMIT 3;

The filtered result remains 1, 2 and 3. Removing both eligibility predicates makes the first three results 1, 4 and 5: the unauthorized and inactive rows have distance zero. Returning those rows to the generator and asking it to ignore them would already have crossed the retrieval boundary. The fixture forces this issue even though all labels and coordinates are synthetic.

View full-size image ↗

In the application, bind the authenticated tenant as a query parameter. Also apply the same policy to any parent-document expansion after retrieval; filtering a chunk does not authorize reading arbitrary neighboring documents. A database row-level security policy can add enforcement when designed for the application’s roles, but this SQL example demonstrates an explicit predicate only. It does not claim that another query under the same database role would be prevented from omitting that predicate.

The returned order is exact for the rows satisfying the query and the current database snapshot. That is a ranking guarantee, not a claim that every returned passage answers the question. Here even the opposite-direction vector appears because three eligible rows exist and the query requests all three. If the product needs to withhold weak candidates, add and evaluate a separate relevance decision; the requested count alone cannot express it.

Check a metric change with a transaction

A same-direction vector can be very long. Temporarily change row 1 from [2,0,0] to [20,0,0], inspect both cosine and Euclidean distances, then roll back. This uses the original three rows to make the comparison easy to read.

BEGIN;
UPDATE demo_passages SET embedding = '[20,0,0]' WHERE id = 1;
SELECT id,
       embedding <=> '[1,0,0]'::vector AS cosine_distance,
       embedding <-> '[1,0,0]'::vector AS l2_distance
FROM demo_passages
WHERE id IN (1, 2, 3)
ORDER BY id;
ROLLBACK;

Cosine distances remain zero, approximately 0.2929 and two. Euclidean distances are now nineteen, one and two. Under Euclidean distance the diagonal row is nearest, whereas under cosine distance the long same-direction row is nearest. Neither ordering is a SQL error; each answers a different geometric question. The rollback restores the original fixture so later checks do not accidentally inherit a changed vector.

Christopher Winslett’s HNSW article for Crunchy Data, published September 1, 2023, explains approximate graph indexing and recommends inspecting query plans. That is a useful next experiment after these exact checks. Its historical tuning discussion is not a promise about a current version, your corpus or query latency. Keep the exact metric-and-scope fixture as a reference when introducing that additional approximation.

Keep an exact reference set

Before creating HNSW or another approximate index, save a fixed corpus snapshot and a representative set of query vectors. Compute the exact top-k identifiers for that snapshot. Then compare the approximate result sets with those references under the same metric and filters.

Report recall at k and latency separately. An index can return results quickly while missing relevant neighbors, and a perfect match on three toy vectors says nothing about a large production corpus. The tiny table verifies semantics; the representative workload evaluates the index tradeoff.

Inspect the actual query plan when evaluating performance. A secondary sort, a distance expression wrapped in another function, or a different operator can affect whether the intended index is used. Do not infer index usage from the mere existence of an index definition.

Turn the toy into a regression fixture

Keep the three-vector case as a small test of distance ordering and tie handling. Add a row outside the caller’s tenant in the application-level version and verify that it never reaches the result, even if it has distance zero. Add a deleted or superseded passage if the product excludes those records.

The useful first milestone is a query whose result you can predict without trusting the embedding model. Once operator semantics, dimensions, and filtering are correct, load real embeddings and evaluate whether the returned passages answer actual questions. Those are separate checks, and the exact-search reference makes both easier to interpret.