Helm7

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;
The whole idea in one table and one query. With no index, Postgres compares the query against every row, which is an exact scan.

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;
halfvec with HNSW. The README's halfvec example uses L2, so this one does too. Use the operator class that matches the distance your embedding model recommends.

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.

Index choices, from the pgvector README
OptionHow it worksTrade-offSettings that matter
Exact scanNo index. Every row is compared.Perfect recall. Slows as the table grows.None
HNSWA 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)
IVFFlatSplits 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;
An HNSW index for the articles table, at the README defaults for m and ef_construction. Raise ef_search when recall matters more than speed.

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;
With iterative scans on, the index keeps looking until ten rows for tenant 7 have been found, up to the scan cap.

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;
Hybrid search in one statement, with k = 60. :q and :query_embedding are parameters your app fills in. For speed, add a GIN index on the same to_tsvector expression.

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 }
Two Helm7 tool calls. Run the dry run first; it states what the backfill will cost before anything is embedded.

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.

Run your entire app stack, end to end, from $12 a month.

App, Postgres, auth, storage, jobs and domains on one platform, planned and priced before anything changes.

More guides