Home / Build it / Semantic Search With Postgres and pgvector

Semantic Search With Postgres and pgvector

Semantic search with Postgres and pgvector stores embedding vectors in a vector column next to your relational data and ranks rows by vector distance in plain SQL. pgvector is an open-source Postgres extension that adds vector types, distance operators and two approximate indexes, HNSW and IVFFlat. Semantic search becomes an ORDER BY on distance with a LIMIT, filters are ordinary WHERE clauses, and hybrid search joins the vector results with Postgres full-text search. The appeal is one database, one backup and one permission model. The catch is filtered search on an approximate index, which applies the filter after the scan and can return fewer rows than asked for. pgvector 0.8.0 added iterative index scans to fix that. As of October 2026 the current release is 0.8.7.

What is semantic search with Postgres and pgvector?

Semantic search with Postgres and pgvector is meaning-based retrieval run inside an existing Postgres database, using the pgvector extension to store embeddings and order rows by their distance from a query embedding. The embeddings come from any model; Postgres stores and searches them.

pgvector adds a vector data type and distance operators to Postgres. An application embeds each document with a model, writes the vector into a column, and at query time embeds the question and asks Postgres for the nearest rows. Because the vectors live in an ordinary table, every Postgres feature still applies: joins, transactions, row-level security, replication and point-in-time recovery. The general build steps are in how to build semantic search.

That answers a question many teams ask: can a relational database do vector search well? For many workloads it can. Postgres with pgvector is a relational database with vector search and, through its full-text features, hybrid search, all open source. Where it stops being the best tool is covered in pgvector vs a dedicated vector database. The first step is installing the extension.

How do you install pgvector and create a vector column?

You install pgvector by adding the extension package to your Postgres server, running CREATE EXTENSION vector in the database, and declaring a column of type vector with the embedding model’s dimension count. Managed Postgres services usually ship pgvector already.

The pgvector README lists installation from source, Docker images, Homebrew, PGXN, APT and Yum. On a managed service, only the CREATE EXTENSION step is needed. The table in the example holds a tenant column, a category, the text body, a 384-dimension embedding and a generated full-text column for hybrid search.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    tenant_id int NOT NULL,
    category text,
    body text NOT NULL,
    embedding vector(384),
    textsearch tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);

The number in vector(384) must match the model’s output. A model that returns 1,536 numbers needs vector(1536). The README gives the indexable limits: up to 2,000 dimensions for vector, 4,000 for halfvec, which stores half-precision floats, and 64,000 for bit. Larger embeddings can still be stored, but an HNSW or IVFFlat index will refuse them, so halfvec or a model with fewer dimensions is the usual answer.

Every SQL statement shown was run on PostgreSQL 16 with pgvector 0.8.7. With the table in place, the next step is filling it.

How do you store embeddings in Postgres from Python?

You store embeddings in Postgres from Python by encoding each text with an embedding model, registering pgvector’s type adapter on the connection, and inserting the arrays like any other column. The pgvector-python package converts NumPy arrays to the vector type.

import psycopg2
from pgvector.psycopg2 import register_vector
from psycopg2.extras import execute_values
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("sentence-transformers/all-MiniLM-L6-v2")   # 384 dimensions
rows = [(1, "billing", "Refunds go to the original payment method."),
        (1, "account", "Reset your password from account settings.")]
vectors = model.encode([r[2] for r in rows], normalize_embeddings=True)

conn = psycopg2.connect("postgresql://localhost/app")
register_vector(conn)
with conn, conn.cursor() as cur:
    execute_values(cur,
        "INSERT INTO documents (tenant_id, category, body, embedding) VALUES %s",
        [(t, c, b, v) for (t, c, b), v in zip(rows, vectors)])

Batch inserts matter for large loads. The README recommends COPY for bulk loading and suggests adding indexes after the initial load, since building an index once is faster than updating it row by row. For a first million rows, load the data, then create the HNSW index.

Keep the model name with the data. Every query must be embedded by the same model, and switching models means re-embedding every row; migrating to a new vector database covers that work. The Python semantic search tutorial explains the embedding step itself. Once rows are in, a query is one SQL statement.

How do you run a semantic search query with pgvector?

You run a semantic search query with pgvector by ordering rows by the distance between their embedding and the query embedding, using a distance operator, and limiting the result. The ORDER BY with LIMIT form is what lets Postgres use a vector index.

SELECT id, body, embedding <=> %(query_vector)s AS distance
FROM documents
ORDER BY embedding <=> %(query_vector)s
LIMIT 5;

pgvector’s operators define the distance. The README lists <-> for L2 distance, <#> for negative inner product, <=> for cosine distance and <+> for L1 distance, plus Hamming and Jaccard distance for binary vectors. Cosine distance is 1 minus cosine similarity, so smaller means closer. For normalized embeddings, all three main operators give the same order; inner product is the cheapest to compute.

Without an index, this query is exact: Postgres compares the query with every row and returns the true nearest neighbours. The README notes that exact search gives perfect recall. It is often fast enough for tens of thousands of rows. Beyond that, an approximate index trades a little recall for much faster queries, and adding one changes results slightly, which the README also warns about. Choosing that index is the next decision.

Which pgvector index should you use, HNSW or IVFFlat?

You should use HNSW for most pgvector semantic search, because the README states it has a better speed-recall trade-off than IVFFlat, and choose IVFFlat only when build time or memory is the tighter constraint. Both are approximate and both need one index per distance operator.

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

SET hnsw.ef_search = 100;   -- per session; SET LOCAL inside a transaction for one query

HNSW builds a layered graph. Its defaults in pgvector are m of 16 connections per layer and ef_construction of 64, and queries use hnsw.ef_search, which defaults to 40. Raising ef_search improves recall at the cost of speed. HNSW builds slower and uses more memory than IVFFlat, and the README advises raising maintenance_work_mem so the graph fits in memory while it builds. An HNSW index can also be created on an empty table.

IVFFlat splits vectors into lists and searches the nearest few. It needs data before it is built, and the README’s starting points are lists of rows divided by 1,000 up to a million rows, and the square root of rows above that. At query time, ivfflat.probes defaults to 1 and a good start is the square root of lists. Search index schema design covers index choices across systems. Filters are where pgvector needs the most care.

How does pgvector metadata filtering work?

pgvector metadata filtering works by adding a WHERE clause to the nearest neighbour query. With an approximate index, the filter runs after the index scan, so restrictive filters can return fewer rows than the LIMIT unless iterative scans are on. Exact search with a B-tree index on the filter column is the other option.

The README gives the arithmetic: if a condition matches 10 percent of rows, HNSW with the default ef_search of 40 returns only about 4 matching rows on average. Since version 0.8.0, setting hnsw.iterative_scan to strict_order or relaxed_order makes the index keep scanning until enough rows pass the filter. Relaxed order gives better recall but can return results slightly out of distance order. The scan stops at hnsw.max_scan_tuples, 20,000 by default.

CREATE INDEX ON documents (tenant_id);
SET hnsw.iterative_scan = relaxed_order;

SELECT id, body FROM documents
WHERE tenant_id = %(tenant)s AND category = 'billing'
ORDER BY embedding <=> %(query_vector)s
LIMIT 5;

The README offers other tools. A B-tree index on the filter column gives fast exact search when the condition matches a small share of rows. A partial HNSW index works when there are only a few filter values. Partitioning suits many values. Weaviate takes a different route: its documentation describes pre-filtering, where an inverted index builds an allow-list before the HNSW search, with the ACORN strategy as the default since version 1.34. Pre-filtering vs post-filtering compares the approaches. Filters handle structure; keywords need full-text search.

How do you build hybrid search in Postgres with pgvector?

You build hybrid search in Postgres by running a pgvector nearest neighbour query and a full-text query in the same SQL statement, then fusing the two ranked lists with reciprocal rank fusion. The README points to exactly this pattern, using Postgres full-text search for the keyword side.

WITH semantic AS (
    SELECT id, RANK() OVER (ORDER BY embedding <=> %(v)s) AS rank
    FROM documents ORDER BY embedding <=> %(v)s LIMIT 40
), keyword AS (
    SELECT id, RANK() OVER (ORDER BY ts_rank_cd(textsearch, q) DESC) AS rank
    FROM documents, websearch_to_tsquery('english', %(q)s) q
    WHERE textsearch @@ q ORDER BY ts_rank_cd(textsearch, q) DESC LIMIT 40
)
SELECT COALESCE(s.id, k.id) AS id,
       COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0) AS score
FROM semantic s FULL OUTER JOIN keyword k ON s.id = k.id
ORDER BY score DESC LIMIT 10;

Each side returns its top 40, and the full outer join keeps documents found by either side. Each document scores 1 divided by 60 plus its rank in each list, summed. The constant 60 comes from the original reciprocal rank fusion paper and is explained in reciprocal rank fusion.

Postgres full-text ranking is not BM25. ts_rank_cd weights term proximity and frequency, and the query parser matters: plainto_tsquery requires every term to match, which empties results for long questions, while websearch_to_tsquery accepts search-engine syntax. Teams that need BM25 add an extension or keep keyword search in a search engine. Hybrid search in SQL databases covers the alternatives. Isolation between customers is the next concern.

How does Postgres handle tenants and data residency for vector search?

Postgres handles tenants for vector search through list partitioning or separate tables, and handles data residency the same way as any Postgres data, by where the server runs. Vectors get no special treatment, which is the advantage.

The pgvector README warns that a shared approximate index lets one tenant’s vectors affect recall and speed for others. Its advice for isolation is list partitioning by tenant or separate tables, so each tenant gets its own index. Row-level security can restrict which rows a database role sees, though it does not change how the shared index is searched.

Residency follows the database. Embeddings are derived from the documents and can reveal their content, so they belong in the same region and under the same controls as the source text. Keeping them in the same Postgres instance means one set of backups, encryption settings and access logs. The weak point is usually the embedding model: a hosted embedding API sends the text to its provider, so residency rules apply to that call too. Self-hosted models avoid it.

Weaviate offers native multi-tenancy with a separate index per tenant, covered in choosing a vector database for semantic search. That comparison leads to the limits of the Postgres approach.

When is Postgres with pgvector not enough for semantic search?

Postgres with pgvector is not enough when vector search dominates the workload: very large collections, high query rates, heavily filtered queries at scale, or a need for built-in vectorization, BM25 hybrid search and reranking. Short of that point, one database is usually simpler.

HNSW indexes perform best when they fit in memory, so very large collections need large instances, and index builds compete with transactional work. Scaling out means read replicas or sharding extensions, which the README discusses. Dedicated engines such as Weaviate, Qdrant and Milvus are built around vector workloads, with features like integrated embedding models, BM25 hybrid fusion and quantization. Search engines such as Elasticsearch and OpenSearch pair vectors with mature text search.

Each has a tutorial: Weaviate, Qdrant, Milvus, Pinecone, Chroma and Redis. Frameworks such as LangChain and LlamaIndex include pgvector stores, and semantic search over documents and PDFs covers splitting files before loading.

A practical rule is to start in Postgres when the data already lives there, measure recall and latency on real queries, and move only the vector workload when those numbers stop meeting the target.

Semantic search with Postgres and pgvector puts embeddings in a vector column and ranks rows by distance in SQL. Create the extension, size the column to the model, load vectors in batches, and add an HNSW index with cosine operators once the data is in. Use WHERE clauses for metadata filters, and turn on iterative scans in pgvector 0.8.0 and later so restrictive filters still fill the limit. For hybrid search, fuse a vector query and a full-text query with reciprocal rank fusion in one statement. Stay in Postgres while one database serves the load, and move the vector workload to a dedicated engine such as Weaviate when scale, filtering or search features demand it.