How to Set Up pgvector for Semantic Search in Postgres

Most engineering teams already run Postgres in production. pgvector lets you keep embeddings in the same database as the rows they describe, so a similarity search is one SQL query instead of a second datastore to sync, secure, and pay for. The short version: install the extension with CREATE EXTENSION vector, store each embedding in a vector column, build an HNSW index, then query with the cosine operator <=> ordered ascending with a LIMIT. This guide walks the whole path, including the index choice that decides whether a query returns in single-digit milliseconds or crawls.

What You Need Before You Start

• PostgreSQL 13 or newer. Postgres 14+ is a safer floor for parallel index builds.
• pgvector 0.8.0 or later, so you get halfvec, iterative scans, and the improved query planner.
• An embedding model and a way to call it. A hosted API like OpenAI, or a local model served through Ollama, both work.
• psql or any Postgres client, plus a driver for your language if you script the inserts.

How to Add Semantic Search to a Postgres Table

These six steps take you from a plain database to working nearest-neighbor search. Finish them in order and you have a query you can ship.

1. Install pgvector and enable it in your database. On a self-managed host, build from source, then enable the extension once per database. Managed providers (RDS, Aurora, Supabase, Neon) ship it already, so you skip straight to the CREATE EXTENSION line.

   cd /tmp
   git clone --branch v0.8.5 https://github.com/pgvector/pgvector.git
   cd pgvector
   make
   sudo make install

   -- inside psql, run once per database
   CREATE EXTENSION vector;

2. Create a table with a vector column sized to your model. The number in vector(N) is the embedding dimension. Get it wrong and every insert fails, so read it off your model's docs first.

   CREATE TABLE documents (
       id         bigserial PRIMARY KEY,
       content    text NOT NULL,
       source     text,
       embedding  vector(1536)   -- 1536 = OpenAI text-embedding-3-small
   );

3. Generate embeddings and insert them. Embed the text with your model, then store the raw float array pgvector accepts as a bracketed string. This Python example uses the OpenAI client, but any model that returns a float list works the same way.

   import psycopg
   from openai import OpenAI
   
   client = OpenAI()
   conn = psycopg.connect("dbname=mydb")
   
   def embed(text):
       r = client.embeddings.create(model="text-embedding-3-small", input=text)
       return r.data[0].embedding   # a list of 1536 floats
   
   rows = [("Postgres stores vectors natively with pgvector.", "docs"),
           ("HNSW indexes trade memory for fast recall.", "docs")]
   
   with conn.cursor() as cur:
       for text, src in rows:
           cur.execute(
               "INSERT INTO documents (content, source, embedding) VALUES (%s, %s, %s)",
               (text, src, str(embed(text))),   # str([...]) yields '[0.1, 0.2, ...]'
           )
       conn.commit()

4. Build an HNSW index with the operator class for your metric. Cosine similarity is the default choice for text embeddings, so use vector_cosine_ops. Build the index after some data lands, and raise maintenance_work_mem first if the table is large.

   SET maintenance_work_mem = '2GB';
   CREATE INDEX ON documents
       USING hnsw (embedding vector_cosine_ops)
       WITH (m = 16, ef_construction = 64);

5. Query for the nearest neighbors. Embed the query text with the same model, then order by distance ascending and cap the result set. The <=> operator returns cosine distance, so smaller is closer.

   -- $1 is the query embedding, passed as a parameter
   SELECT id, content, source,
          embedding <=> $1 AS distance
   FROM documents
   ORDER BY embedding <=> $1
   LIMIT 5;

6. Tune recall at query time if the top hits look thin. hnsw.ef_search controls how hard the index looks. The default of 40 is fast; raise it for better recall at some latency cost. Set it per session or per transaction.

   SET hnsw.ef_search = 100;   -- default is 40

That is the entire task. Everything below explains the choices those steps assumed, so your search holds up as the table grows.

Which Index Should You Use?

pgvector offers exact search with no index, plus two approximate index types. Exact search is correct but scans every row, so it only stays fast on small tables. The two index types differ in how they group vectors and what they cost.

Default to HNSW unless memory is scarce. Its recall degrades gracefully and it does not need training data up front. IVFFlat clusters vectors into lists at build time, so it must see representative data first, and you widen the search with ivfflat.probes.

-- IVFFlat alternative: build after loading data
CREATE INDEX ON documents
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

SET ivfflat.probes = 10;   -- default is 1

How Do You Pick the Distance Metric?

Use the metric your embedding model was trained for, then pair it with the matching operator and operator class. Most modern text models return normalized vectors, so cosine and inner product rank results identically, and cosine is the safe default.

How Do Filtered Queries Work Without Dropping Results?

Combining a similarity search with a WHERE clause used to be a gamble. The index returned its best matches first, the filter threw most of them away, and you were left with fewer rows than your LIMIT. pgvector 0.8.0 solved this with iterative index scans: when the first pass comes up short, the index keeps scanning until it satisfies the query or hits a cap.

-- keeps exact ordering, scans further when filtered
SET hnsw.iterative_scan = strict_order;

-- prioritizes speed, returns rows slightly out of order
SET hnsw.iterative_scan = relaxed_order;

Relaxed order typically holds 95%-99% of the quality of strict order while cutting latency on heavily filtered queries. Reach for it when a status or tenant filter narrows the candidate set hard.

How to Troubleshoot the Usual Failures

• Query ignores the index. The planner only uses a vector index for ORDER BY [operator] LIMIT k in ascending order. Run EXPLAIN ANALYZE and confirm an index scan appears. A WHERE on distance instead of an ORDER BY will not trigger it.
• Fewer rows than the LIMIT after adding HNSW. Results are capped by hnsw.ef_search (default 40). Raise it, or enable iterative scans for filtered queries.
• "expected N dimensions, not M" on insert. The column size does not match your model output. Recreate the column with the correct vector(N), or switch models deliberately.
• Index build is painfully slow. Raise maintenance_work_mem and max_parallel_maintenance_workers before CREATE INDEX. HNSW builds far faster when the graph fits in memory.
• Some rows never match. NULL vectors are not indexed, and zero vectors are undefined for cosine distance. Filter or backfill them.

What to Do Next

A working nearest-neighbor query is the retrieval half of a RAG system. From here:

• Wire retrieval into a generation step and orchestrate the pipeline. My guide on using LangChain for advanced workflow automation covers the plumbing around retrieve-then-generate.
• Cut your embedding bill by generating vectors locally. See running large language models locally with Ollama and serve nomic-embed-text from your own hardware.
• Expose the search as a tool an agent can call by building an MCP server in Python with FastMCP, so retrieval becomes a callable capability rather than glue code.

Add a B-tree index on the columns you filter by, keep an ANALYZE in your load job so the planner has fresh statistics, and benchmark recall against exact search on a sample before you trust the numbers.

Sources

• pgvector README and reference
• pgvector 0.8.0 release notes
• pgvector-python client library
• OpenAI embeddings guide
• Ollama embedding models
• PostgreSQL CREATE INDEX documentation