Semantic search on Postgres with pgvector
pgvector is a Postgres extension that stores embeddings in a column and finds the nearest ones by distance. To build semantic search, you embed your text, store the vectors, and order by distance to the embedding of the query. Small tables need no index at all, bigger ones use HNSW, and good search blends vector results with full-text results.
Checked against current documentation on 16 November 2026.
What pgvector gives you
An embedding is a list of numbers that stands for the meaning of a piece of text. Texts with similar meaning get lists that sit close together. Semantic search embeds the question, then looks for the stored lists nearest to it. That is how a search for "refund policy" can find a page titled "Getting your money back".
pgvector adds the types and operators to do that inside Postgres. You switch it on with CREATE EXTENSION vector. The pgvector README lists the distance operators: <-> for L2 distance, <#> for negative inner product, <=> for cosine distance and <+> for L1 distance.
CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE articles ( id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, tenant_id bigint NOT NULL, title text NOT NULL, body text NOT NULL, embedding vector(512) ); -- The five nearest articles to a query embedding, by cosine distance. -- :query_embedding is a parameter your app fills in, such as '[0.01, -0.02, ...]'. SELECT id, title FROM articles ORDER BY embedding <=> :query_embedding LIMIT 5;
Keeping vectors next to your data has a real benefit. The same row holds the text, the owner, the permissions and the vector. You can join, filter and apply row level security in one statement. A separate vector database makes you copy all of that across and keep it in step.
vector or halfvec, and how many dimensions
The README says the vector type stores up to 16,000 dimensions, but an index on it supports only 2,000. The halfvec type holds half-precision numbers and can be indexed up to 4,000 dimensions. The README also shows halfvec as a way to get smaller indexes.
The model you choose sets the width. OpenAI documents text-embedding-3-small as producing 1,536 numbers by default, with a maximum input of 8,192 tokens. Its embeddings guide says the suggested way to get a shorter vector is to pass the dimensions parameter when you create the embedding. The guide also says that if you cut an embedding down yourself, you must normalise it afterwards. See OpenAI's embeddings guide.
Shorter vectors are cheaper to store and faster to compare. They can lose some accuracy, so test with your own queries before you commit. A width of 512 works with text-embedding-3-small, and Helm7 uses it.
-- Half-precision storage, 512 dimensions, indexed with HNSW using L2 distance. CREATE TABLE notes ( id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, body text NOT NULL, embedding halfvec(512) ); CREATE INDEX notes_embedding_idx ON notes USING hnsw (embedding halfvec_l2_ops); SELECT id FROM notes ORDER BY embedding <-> :query_embedding LIMIT 5;
Exact scan, HNSW or IVFFlat
There are three ways to run the nearest-neighbour query. An exact scan checks every row and always returns the true nearest ones. HNSW and IVFFlat are approximate indexes. They look at only part of the data, so they are faster, and they can miss a true neighbour. That miss rate is called recall.
| Option | How it works | Trade-off | Settings that matter |
|---|---|---|---|
| Exact scan | No index. Every row is compared. | Perfect recall. Slows as the table grows. | None |
| HNSW | A multilayer graph of vectors. | Better speed and recall than IVFFlat. Slower to build, more memory. | m (default 16), ef_construction (64), hnsw.ef_search (40) |
| IVFFlat | Splits vectors into lists and searches the nearest few. | Faster to build, less memory. Lower query performance. | lists, ivfflat.probes (default 1) |
HNSW has no training step, so you can create it on an empty table. IVFFlat needs data first, because it learns its lists from the rows you have. The README suggests rows divided by 1,000 for the list count up to a million rows, and the square root of the row count above that.
For many apps the right answer is no index yet. A table of a few thousand to a few tens of thousands of rows scans quickly, and exact results mean there is nothing to tune. Add HNSW when query time stops being acceptable. Helm7 follows that rule: it keeps an exact scan below 50,000 rows and builds HNSW above that.
CREATE INDEX articles_embedding_hnsw ON articles USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); -- Look at more candidates per query: better recall, slower answer. SET hnsw.ef_search = 100;
Filters, and why iterative scans matter
Real queries have a WHERE clause: this customer's notes, this status, this date range. An approximate index returns its nearest candidates first, and Postgres filters afterwards. If the filter removes most of them, you get fewer rows than you asked for, sometimes none.
pgvector 0.8.0 added iterative index scans for this. The index keeps scanning until enough rows pass the filter. HNSW accepts strict_order or relaxed_order. IVFFlat accepts relaxed_order. For HNSW, hnsw.max_scan_tuples caps the work and defaults to 20,000.
SET hnsw.iterative_scan = relaxed_order; SELECT id, title FROM articles WHERE tenant_id = 7 ORDER BY embedding <=> :query_embedding LIMIT 10;
The README gives other advice for filters. Index the filter column if the condition matches a small share of rows. Use a partial index if you filter on only a few distinct values. Use partitioning if you filter on many.
Hybrid search with full-text and reciprocal rank fusion
Vectors are good at meaning and weak at exact terms. Search for an error code or a part number and a keyword match wins. Postgres has full-text search built in, with a GIN index as the preferred index type. The README suggests combining it with vector results, either with reciprocal rank fusion or a cross-encoder.
Reciprocal rank fusion, or RRF, merges two ranked lists without comparing their scores. Each result gets 1 divided by (rank plus k) from each list it appears in, and the scores are added. Microsoft's Azure AI Search documentation notes that experiments show it works well with k set to a small value such as 60. Results near the top of both lists win. See the Azure AI Search RRF page.
WITH text_hits AS (
SELECT id, row_number() OVER (
ORDER BY ts_rank_cd(to_tsvector('english', title || ' ' || body),
websearch_to_tsquery('english', :q)) DESC) AS rnk
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
@@ websearch_to_tsquery('english', :q)
ORDER BY rnk
LIMIT 200
), vector_hits AS (
SELECT id, row_number() OVER (ORDER BY embedding <=> :query_embedding) AS rnk
FROM articles
ORDER BY embedding <=> :query_embedding
LIMIT 200
)
SELECT a.id, a.title,
coalesce(1.0 / (60 + t.rnk), 0) + coalesce(1.0 / (60 + v.rnk), 0) AS rrf
FROM articles a
LEFT JOIN text_hits t ON t.id = a.id
LEFT JOIN vector_hits v ON v.id = a.id
WHERE t.id IS NOT NULL OR v.id IS NOT NULL
ORDER BY rrf DESC
LIMIT 10;Fuse after permissions, not before
Here is a mistake that is easy to make. Suppose each list is built over the whole table, then fused, then filtered for what the user may see. RRF scores depend on rank. A document the user cannot see still takes a slot in the lists. It pushes visible documents down, so their scores change. The user never sees the hidden document, but the ranking still reflects it.
The safer order is to apply permissions first. Join the candidates back to the table under the caller's own role, with row level security on, and only then rank and fuse. A hidden row then has no effect on any score.
This is easy to do in a single statement. The two CTEs above read from the articles table. If you run the statement as a role that has row level security on that table, both lists contain only rows that role can see, and the fusion happens on those. If instead you query through a role that bypasses row security and filter in your application, the problem comes back.
How Helm7 does it
Helm7 keeps semantic search off until you turn it on for a table. It then adds a helm7 schema inside your own Postgres, and nothing is copied out. For each searchable table there is a side table with the primary key, a hash, a text search vector and a halfvec(512) embedding, about 1 KB a row. It embeds with OpenAI text-embedding-3-small at 512 dimensions, through llm11, and bills embedding tokens at cost: $0.02 per million tokens, the OpenAI list price with no margin. Only the columns you pick are sent.
Triggers on your table queue changed rows. A worker runs every minute, skips text whose hash has not changed, and writes new vectors. Rows over about 2,000 characters are split into chunks. Below 50,000 rows it uses an exact scan; above that it builds an HNSW index with m = 16, concurrently, one build at a time. A GIN index on the text vector is always there.
The search call, database_search, does the hybrid pattern above in one statement. An exact match on the primary key or a unique column is pinned first. Full-text search and vector search each take their top 200. Reciprocal rank fusion with k = 60 runs after the candidates are joined back to your table under the app's own role, so row level security applies before ranking. With explain set to true you get the SQL, the count and time of each stage, and the reason each row came back.
// Preview the cost: row estimate, backfill tokens and their price, index plan.
database_semantic_enable
{ "productId": "prd_...", "databaseId": "DATABASE_ID",
"table": "articles", "columns": ["title", "body"],
"language": "english", "dry_run": true }
// Search, and ask why each row matched.
database_search
{ "productId": "prd_...", "databaseId": "DATABASE_ID",
"table": "articles", "query": "getting my money back",
"k": 10, "explain": true }If embedding cannot run, results come back labelled as keyword-only. They never quietly pass as semantic. Helm7 is a convenient way to get this set up and kept in sync. If you want to tune index settings by hand, or you already run pgvector yourself, the SQL in this guide is all you need. For a related pattern, see agent memory on Postgres, and the databases page for the other switches.
Common questions
Should I use vector or halfvec?
Use halfvec when you want smaller storage and smaller indexes, and when your width is between 2,001 and 4,000, because halfvec can be indexed up to 4,000 dimensions and vector only to 2,000. Test recall on your own queries first.
How many dimensions should I use with text-embedding-3-small?
The default is 1,536. OpenAI says you can pass the dimensions parameter to get a shorter vector. Helm7 uses 512. Shorter vectors cost less to store and compare, but test them against your own queries.
HNSW or IVFFlat?
HNSW gives a better speed and recall trade-off and needs no training data, but it builds more slowly and uses more memory. IVFFlat builds faster and uses less memory, but queries are slower. For small tables, skip both and use an exact scan.
What are iterative index scans?
A pgvector 0.8.0 feature. When a filter removes many of the candidates an index returns, the scan continues until enough rows pass. Turn it on with hnsw.iterative_scan or ivfflat.iterative_scan.
Why fuse results after permission filtering?
Reciprocal rank fusion scores depend on rank. If hidden rows are in the lists, they change the rank of visible rows. Filtering first means an unseen row can never move the ranking a user sees.