How to Implement Vector Search in Postgres

pgvector adds a vector column type to Postgres, and a similarity query is an ORDER BY on a distance operator followed by a LIMIT.

This usually comes up after a help-centre search returns nothing for “cancel my subscription” because the article is titled “Ending your plan”, and someone proposes standing up a managed vector database next to the Postgres you already run. That second service is rarely necessary. The rest of this article is the whole path in SQL: enabling the extension, sizing the column to your embedding model, storing vectors, querying by cosine distance, indexing with HNSW or IVFFlat, joining vector results against ordinary tables, and the queries where vector search is the wrong tool and Postgres full-text search should carry them instead.

What Is Vector Search Used For?

Vector search matches on meaning, so a query for “cancel my subscription” can return a document titled “Ending your plan” even though the two share no words. Each piece of text is converted by an embedding model into a fixed-length list of numbers, and texts with similar meaning land close together in that space. Searching means finding the stored vectors nearest to the query’s vector. For background on how embeddings work, see Vector Databases Explained.

It pays off in three places: site search that tolerates paraphrase, support ticket matching (find previous tickets like this one), and retrieval for RAG, where the nearest documents are fed to a language model as context, as covered in the introduction to RAG for web apps.

Enable pgvector and Add a Vector Column

pgvector is enabled once per database with CREATE EXTENSION vector, and the column type is vector(n), where n is the number of dimensions. Version 0.8.6 supports Postgres 13 and later, though Postgres 13 is past community support, so 14 or newer is the practical floor.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id        bigserial PRIMARY KEY,
  title     text NOT NULL,
  body      text NOT NULL,
  embedding vector(1536)   -- replace 1536 with your embedding model's output size
);

-- or, on a table you already have:
ALTER TABLE documents ADD COLUMN embedding vector(1536);

The number inside vector(n) must equal the length of the vectors your embedding model produces. OpenAI’s text-embedding-3-small returns 1536-dimensional vectors by default, for example. Vectors from different models cannot be meaningfully compared, so switching models means re-embedding the entire column.

Store Embeddings From Any Model

Embeddings are written like any other column value: generate the vector in application code, then pass it as a bind parameter cast to vector. pgvector does not call a model for you, and any language with a Postgres driver works.

INSERT INTO documents (title, body, embedding)
VALUES ($1, $2, $3::vector);

-- re-embed an existing row without creating a duplicate
INSERT INTO documents (id, title, body, embedding)
VALUES ($1, $2, $3, $4::vector)
ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding;

For an initial backfill, pgvector recommends loading in bulk with COPY ... FROM STDIN WITH (FORMAT BINARY), and building indexes once the data is in rather than before.

Which pgvector Distance Operator Should You Use?

Use <=> for text embeddings unless your model’s documentation says otherwise. It returns cosine distance, so sorting ascending puts the closest match first; subtract the result from 1 when you need cosine similarity for display.

-- five closest documents; $1 is the query embedding
SELECT id, title, 1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 5;

pgvector supports six distance operators in total, and each needs a matching index operator class:

<#> hands back the inner product with its sign flipped. Postgres scans indexes in ascending order only, so the negative form keeps the smallest number as the closest match; multiply by -1 to recover the plain inner product. If your vectors are already normalised to length 1, inner product is the quickest option for exact search. <~> (Hamming) and <%> (Jaccard) exist for binary vectors and are out of scope here.

Should You Index With HNSW or IVFFlat?

With no index, pgvector compares the query vector against every row and returns the exact nearest neighbours. Adding an HNSW or IVFFlat index switches that to approximate search, which is much faster, finds most of the true neighbours, and can hand back different rows than the exact query did. Both index types are approximate. Exact search is simply what happens when the column carries no vector index at all.

Default to HNSW unless build time or memory forces IVFFlat. The opclass must match the operator you query with.

-- production: avoid blocking writes
CREATE INDEX CONCURRENTLY documents_embedding_hnsw
  ON documents USING hnsw (embedding vector_cosine_ops);

-- IVFFlat alternative; create only after the table has data
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

HNSW has no training step, so the index can be built before a single row lands in the table. IVFFlat does have one: its lists come from whatever data is present when the index is built, so give it a representative set of rows first. Raising ef_search or probes with SET LOCAL inside a transaction improves recall for one query at the cost of speed.

The planner will only consider a vector index when the query has an ORDER BY directly on a distance operator, sorted ascending, together with a LIMIT; ORDER BY 1 - (embedding <=> $1) DESC will not use it. On a small table the planner may still prefer a sequential scan, so check with EXPLAIN. To measure what the index cost you in recall, force an exact search and compare the two result sets:

BEGIN;
SET LOCAL enable_indexscan = off;  -- exact search for comparison
SELECT id FROM documents ORDER BY embedding <=> $1::vector LIMIT 5;
COMMIT;

Do You Need a Separate Vector Database?

If you already run Postgres, you probably do not need a separate vector database: pgvector gives you vector search with the same backups, the same transactions, and the ability to join vector results against normal tables in one query. A document and its embedding are written in one INSERT, so there is no sync pipeline between two stores and no orphaned-vector failure mode. Vectors travel through the write-ahead log like every other column, so replicas and point-in-time restores pick them up with no extra work.

The join is where this becomes concrete. Finding the nearest support tickets, restricted to enterprise customers, is a single statement:

SELECT t.id, t.subject, c.plan, t.embedding <=> $1::vector AS distance
FROM tickets t
JOIN customers c ON c.id = t.customer_id
WHERE c.plan = 'enterprise'
ORDER BY t.embedding <=> $1::vector
LIMIT 10;

Approximate indexes come with one catch: the index is scanned first and the WHERE clause is then applied to whatever it returned, so a selective filter can leave you with fewer rows than LIMIT asked for. A B-tree on the filter column often gives fast exact results; iterative scans and partial indexes handle the rest.

Dedicated vector databases still earn their place at billions of vectors or extreme write rates. Below that, the extra service is operational cost without a user-visible benefit.

Where Vector Search Falls Short: Hybrid Search With Full-Text

Vector search finds rows that mean the same thing; full-text search finds rows that contain the same words, and production search usually runs both and merges the two ranked lists. Exact identifiers, product codes, error strings and people’s names have no useful “meaning” to an embedding model, and a query for SKU-4471 should return the row containing that token, not rows about similar products. Those queries want Postgres full-text search, which pgvector’s docs pair with vector search for hybrid queries.

Start with a stored generated tsvector column and a GIN index over it:

ALTER TABLE documents
  ADD COLUMN body_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX ON documents USING gin (body_tsv);

Then combine the two rankings with Reciprocal Rank Fusion, following the shape of pgvector’s own RRF example:

-- $1 = query embedding, $2 = raw query text
WITH semantic AS (
  SELECT id, RANK() OVER (ORDER BY embedding <=> $1::vector) AS rnk
  FROM documents
  ORDER BY embedding <=> $1::vector
  LIMIT 20
),
lexical AS (
  SELECT id, RANK() OVER (ORDER BY ts_rank_cd(body_tsv, q) DESC) AS rnk
  FROM documents, plainto_tsquery('english', $2) AS q
  WHERE body_tsv @@ q
  ORDER BY ts_rank_cd(body_tsv, q) DESC
  LIMIT 20
)
SELECT COALESCE(s.id, l.id) AS id,
       COALESCE(1.0 / (60 + s.rnk), 0.0) + COALESCE(1.0 / (60 + l.rnk), 0.0) AS score
FROM semantic s
FULL OUTER JOIN lexical l ON l.id = s.id
ORDER BY score DESC
LIMIT 10;

Each list contributes 1 / (k + rank), so a document near the top of either list scores well and one present in both scores best. The constant k = 60 comes from Cormack, Clarke and Büttcher, who fixed it in a pilot study and reported that the exact value is not critical. Treat the query as illustrative and confirm with EXPLAIN (ANALYZE, BUFFERS) that both the HNSW and GIN indexes are used on your data.

Where to Start

The whole implementation is a column, an operator and an index, all inside the database you already back up and replicate. Add the vector(n) column sized to your model, write the ORDER BY ... <=> ... LIMIT query without an index first, then add HNSW and check the recall difference with enable_indexscan = off. Once semantic results look right, wire in the full-text side, because the first user to paste an order number will find the gap otherwise.

FAQs

Can pgvector index embeddings with more than 2,000 dimensions?

Not with the standard vector type alone. HNSW and IVFFlat index vector columns up to 2,000 dimensions, although the column itself stores up to 16,000. For larger embeddings, cast to halfvec in an expression index (indexable to 4,000 dimensions), use binary quantization with re-ranking (up to 64,000), index a subvector, or request fewer output dimensions from the model, which OpenAI's text-embedding-3 models support via a dimensions parameter.

Do I need to rebuild a pgvector index after inserting new rows?

Not for HNSW: new rows are added to the graph as they are inserted, which is why the index can be built on an empty table. IVFFlat behaves differently. Its list centroids are computed by k-means once at build time and never move, so recall can degrade as the table grows or its distribution shifts. Rebuild IVFFlat indexes after large loads or distribution changes with REINDEX INDEX CONCURRENTLY.

Why does a query return fewer rows than LIMIT after adding an HNSW index?

Because an HNSW scan returns at most hnsw.ef_search candidates, 40 by default, and any WHERE filter is applied to those candidates afterwards. A LIMIT above 40, a selective filter, or dead tuples can all leave the result short. Raise hnsw.ef_search with SET LOCAL, or in pgvector 0.8.0 and later enable iterative index scans by setting hnsw.iterative_scan to strict_order so the scan continues until enough rows match.

Can I store embeddings from two different models in the same table?

Yes, but not in one shared indexed column. Use a separate vector(n) column per model, each sized to that model's output and each with its own index. pgvector also permits an untyped vector column holding mixed dimensions, but an index can only cover rows of a single dimension, built as an expression index with a cast plus a partial WHERE on the model identifier. Distances across models are meaningless.