Vector Search Without a New Database

The standard architecture diagram for retrieval puts a dedicated vector database next to the application, and for a large share of products that box is unnecessary. Postgres has supported vector similarity search for years, and if your data already lives there, keeping the embeddings beside it removes a whole category of problem before it starts.

Why not a new database

The strongest argument is not performance, it is consistency. With vectors in a separate store you own a synchronisation problem forever: a row is updated here and its embedding is updated there, in two systems with no shared transaction. They will drift. You will serve a chunk whose source was deleted, and answer a question with content that no longer exists.

In one database the embedding is a column. It is written in the same transaction as the row, deleted by the same cascade, and covered by the same backup. The failure mode simply does not exist.

The second argument is filtering. Real queries are rarely pure similarity — they are “similar chunks from documents this user may read, in the current version, excluding archived ones.” Those predicates live in your relational data. In one database that is a WHERE clause and a join; across two it is fetching candidates from one system and filtering them in application code, which is both slower and, as below, quietly wrong.

The schema

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id          bigserial PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  tenant_id   uuid   NOT NULL,
  content     text   NOT NULL,
  section     text,                       -- for the context prefix and citations
  embedding   vector(1536) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON chunks (tenant_id, document_id);   -- ordinary filtering

Note the cascade. Deleting a document removes its chunks and their vectors, atomically, with no cleanup job and no orphaned embeddings — the thing that is genuinely tedious to get right across two systems.

Match the distance operator to how your embeddings were trained. Most modern models produce normalised vectors where cosine distance is correct, and using the wrong operator gives results that are subtly bad rather than obviously broken — which is much harder to notice.

HNSW and IVFFlat

Without an index, a similarity query is a sequential scan computing distance against every row. That is exact and fine for thousands of rows; it is not fine for millions. Both available index types are approximate — they trade a little recall for a lot of speed, which is the trade you want, as long as you know you are making it.

HNSW builds a navigable graph. Better recall at a given speed, no training step, and it handles incremental inserts gracefully. It costs more memory and is slower to build. This is the right default.

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- recall/latency dial at query time
SET hnsw.ef_search = 100;

IVFFlat partitions vectors into lists and searches the nearest few. Faster to build and lighter on memory, but it must be built against representative data — building it on an empty or tiny table produces poor partitions, and it does not reorganise as rows are added. If your corpus grows substantially you have to rebuild it.

The practical advice: start with HNSW, leave the build parameters alone until you have measured something, and treat ef_search as the runtime dial when you need more recall.

The filtering trap

This is the part that produces confusing bugs, and it is a property of approximate indexes generally rather than of Postgres.

SELECT id, content
FROM chunks
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;

That looks obviously correct and can quietly return too few rows, or worse ones than exist. The approximate index walks the graph to collect candidates, and the tenant filter is applied to what it returns. If the nearest neighbours in the whole table mostly belong to other tenants, the candidate set is filtered down to two rows and you asked for ten — the good matches for this tenant were never visited.

The symptom is nasty because it is selective: results look fine for your largest tenant and thin for everyone else, which reads like a data problem rather than an index one.

Three ways out, in increasing order of effort:

Raise ef_search so more candidates are visited before filtering. Cheapest, and often enough when the filter is not very selective.
Partial indexes per high-volume tenant or per document class, so the index itself only contains eligible rows. Very effective when there are a handful of large partitions.
Partition the table by tenant, giving each partition its own index. The most work, and the right answer at genuine multi-tenant scale.

Whichever you use, verify with EXPLAIN ANALYZE that the index is used at all — a query that falls back to a sequential scan is exact and will look like a correctness improvement while it destroys your latency.

Dimensions and memory, which people underestimate

A 1536-dimension float vector is about 6KB per row before indexing. A million chunks is roughly 6GB of raw vectors, and an HNSW index adds a graph on top. If that does not fit in memory, every query goes to disk and the speed advantage evaporates.

Two levers worth knowing before you are in trouble:

Smaller embeddings. Several current models support shortening their output with modest quality loss. Halving dimensions halves storage and roughly halves index memory, which is often a much better trade than it sounds.
Quantisation. Storing vectors at reduced precision cuts memory substantially for a small recall cost, and a two-stage approach — retrieve widely on quantised vectors, rescore the top candidates against full-precision ones — recovers most of the loss.

And keep the embedding column out of any query that does not need it. SELECT * on a chunks table drags six kilobytes per row across the wire for no reason, which is a surprisingly common cause of slow endpoints in RAG applications.

When a dedicated store genuinely earns its place

Not never. The honest cases:

Scale beyond a single node's memory — hundreds of millions of vectors, where sharding the index is the actual problem being solved.
Very high query concurrency where you need to scale search independently of your transactional database, rather than competing for the same resources.
Built-in hybrid search and reranking that you would otherwise assemble yourself.
Your source data is not in Postgres anyway, in which case the consistency argument does not apply and the decision is open.

Below those thresholds — which covers internal tools, documentation search, support assistants and most product features — the extra system buys latency you would not have noticed and costs you a synchronisation problem you will definitely notice.

The short version

Keep vectors in the database that owns the rows: same transaction, same cascade, same backup, and filters that are just WHEREclauses. Default to HNSW and tune ef_search rather than the build parameters. Watch for the filter-after-approximate-search trap, which starves selective queries of candidates and looks like missing data. Budget memory for the index rather than the vectors alone. And move to a specialised store when you can name which of its properties you need.