pgvector in Practice: What to Know When Running Vector Search on PostgreSQL

The first choice for embedding search is not a dedicated vector database — it’s pgvector. If your service already runs PostgreSQL, one extension adds vector search, while transactions, joins, and backups remain alongside your existing data. What embeddings are and what kind of search they enable was covered in embeddings and vector search; this article covers what comes next — the operational practice. The baseline is the pgvector 0.8 line (with PostgreSQL 18 support).

Fundamentals: type, operators, index #

CREATE EXTENSION vector;

CREATE TABLE documents (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content   text NOT NULL,
    category  text NOT NULL,
    embedding vector(1536)          -- must match your embedding model's dimension count
);

-- 10 nearest by cosine distance
SELECT id, content
FROM documents
ORDER BY embedding <=> '[0.01, -0.02, ...]'::vector
LIMIT 10;

Three distance operators do most of the work: <=> (cosine distance), <-> (Euclidean), <#> (negative inner product). Text embeddings mostly default to cosine distance (<=>) — follow whatever distance your model’s documentation specifies. It works without an index, but that’s a full scan; past tens of thousands of rows an index is mandatory.

Indexing: HNSW is the default #

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

The working default is HNSW. IVFFlat builds cheaply, but its structure fixes cluster centroids from the data at index-creation time, so quality erodes on services whose data keeps growing. HNSW builds heavier (mitigated by parallel builds) but handles inserts well and keeps recall stable. One caution: these are approximate (ANN) indexes. They return “neighbors that are almost always right” quickly, not a 100%-exact nearest set, and you trade recall against speed with parameters like hnsw.ef_search. Whether the index is actually used is checked, as always, with EXPLAIN.

The practical trap: combining with filters #

pgvector’s real value is the combination with SQL — “the 10 nearest among rows where category is ‘docs’” is one query of vector search plus WHERE. But here lives the signature trap. The index fetches “N nearest candidates” first and the filter applies afterward, so a tight filter can eliminate the candidates and leave you short of 10 results — underfetching. From pgvector 0.8.0, iterative index scans absorb this: when results run short, the scan digs further into the index for more candidates.

SET hnsw.iterative_scan = strict_order;  -- the safety latch for filtered search

If the filter is extremely selective (0.1% of the table), a partial index covering just that subset — or a separate table — may be the better design in the first place.

At scale: halfvec and dimension savings #

A 1536-dimension float vector is about 6KB per row. At millions of rows the vectors alone reach tens of GB, and the moment the index no longer fits in memory, search slows sharply. The response sequence: first, halfvec (half precision) halves storage (halfvec(1536); recall loss is mostly negligible, and the HNSW dimension limit rises to 4,000). Second, reduce dimensions on the embedding-model side (major models support reduced-dimension output — see choosing an embedding model). Third, if the index still exceeds memory, that’s the fork in the road below.

When a dedicated vector database becomes necessary #

The criterion is simple: when vector search becomes your service’s primary workload, at a scale one PostgreSQL server’s memory and CPU can’t carry. When conditions like tens to hundreds of millions of vectors, thousands of queries per second, and millisecond latency requirements stack up, dedicated engines (distributed sharding, GPU acceleration) come into their own. Conversely, below a few million vectors — with requirements like “join with existing data and wrap in transactions” — the cost of operating one more system outweighs a dedicated database’s gains and then some. The “cost of adding a specialized store” logic from DynamoDB vs RDS applies here unchanged.

Summary #

• If you already run PostgreSQL, pgvector is the first choice for vector search. Transactions, joins, and backups remain alongside your existing data.
• The index default is HNSW; IVFFlat degrades on growing data. Remember ANN is approximate, and ef_search trades recall for speed.
• The filtered-search underfetching trap is handled by 0.8.0’s iterative index scans.
• The scale sequence is halfvec → dimension reduction → only then consider a dedicated database.
• A dedicated vector database’s seat is “vectors as the primary workload, past a single server’s limits.” Until then, not adding a system is the win.