pgvector Semantic Search in PostgreSQL: A Python Checklist

Originally published on kuryzhev.cloud

A product team has 40,000 support articles in PostgreSQL and a search box that only matches exact keywords. Someone suggests bolting on a separate vector database, but the articles, permissions, and metadata already live in Postgres. For many workloads, pgvector semantic search inside the existing database is the smaller and easier-to-operate choice, provided the setup is done deliberately.

This article is a checklist, not a tutorial. Work through it before you ship, and again when recall or latency looks wrong.

Semantic search failures rarely raise errors. A query returns ten rows and they look plausible. Nobody notices that the index is ignored, the embedding model changed, or a filter silently removed the best matches. The system "works" while quietly returning worse results.

The typical failure modes fall into three groups:

A checklist catches these because each one is a yes/no question you can verify.

Details here follow the documented behavior of recent pgvector releases. Some features depend on the extension version. For example, halfvec requires 0.7.0+ and iterative index scans require 0.8.0+. Verify with the pgvector README for the version your server runs. Managed services often lag behind the latest release.

SELECT extversion FROM pg_extension WHERE extname = 'vector'; and compare it to the features you plan to use. Managed PostgreSQL offerings document which versions they ship.vector(N) where N matches the model output. A mismatch fails on insert, which is the good outcome.<=>, L2 is <->, and negative inner product is <#>. The index operator class must match the operator you query with.hnsw.ef_search; for IVFFlat, ivfflat.probes. Start with the schema and index. This example assumes a 1536-dimension embedding model; change the number to match yours.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
    id          bigserial PRIMARY KEY,
    tenant_id   integer NOT NULL,
    content     text NOT NULL,
    embed_model text NOT NULL,          -- record which model produced the vector
    embedding   vector(1536) NOT NULL   -- must equal the model's output dimension
);

-- HNSW with cosine ops; must match the <=> operator used in queries
CREATE INDEX docs_embedding_hnsw
    ON docs USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Higher ef_search improves recall at the cost of latency (per session)
SET hnsw.ef_search = 100;

Next, the Python side with psycopg 3 and the pgvector package. The embed() function is a placeholder for your embedding provider, such as the OpenAI API, Amazon Bedrock, or a local open-weight model.

import numpy as np
import psycopg
from pgvector.psycopg import register_vector

DSN = "postgresql://app:secret@localhost:5432/appdb"
MODEL = "your-embedding-model"  # keep identical for ingest and query

def embed(texts: list[str]) -> list[np.ndarray]:
    """Call your embedding provider here; return one vector per text."""
    raise NotImplementedError

def search(query: str, tenant_id: int, k: int = 5):
    q = np.array(embed([query])[0], dtype=np.float32)
    with psycopg.connect(DSN) as conn:
        register_vector(conn)  # extension must already exist in this database
        rows = conn.execute(
            """
            SELECT id, content, embedding <=> %s AS distance
            FROM docs
            WHERE tenant_id = %s AND embed_model = %s
            ORDER BY embedding <=> %s   -- ascending distance expression + LIMIT enables the index
            LIMIT %s
            """,
            (q, tenant_id, MODEL, q, k),
        ).fetchall()
    return rows  # cosine similarity is 1 - distance

Watch out for filters that defeat the index. With an approximate index, the database retrieves candidate neighbors first and then applies WHERE. A selective filter, such as one small tenant, can leave you with fewer than LIMIT rows, or none. pgvector 0.8.0 and later offer iterative index scans to keep scanning until enough rows match. For HNSW, use SET hnsw.iterative_scan = relaxed_order; or strict_order. IVFFlat has ivfflat.iterative_scan. Alternatives include partial indexes per large tenant or partitioning. Check which options your version supports.

Watch out for mixing models. If you re-embed with a newer model but leave old vectors in place, searches will still return results, just meaningless ones. Even models with the same dimension produce incompatible spaces. The embed_model column above exists so you can filter and migrate in batches.

Other items that tend to slip through:

vector indexes cap at 2,000 dimensions. Larger embeddings have three options: halfvec (pgvector 0.7.0+, indexable up to 4,000 dimensions).maintenance_work_mem. When it no longer fits, pgvector emits a NOTICE to the client session. Watch for it on large builds.vector_l2_ops is not used for a For general PostgreSQL behavior around extensions and index maintenance, the official CREATE EXTENSION documentation covers the installation side that managed providers wrap.

Checklists decay unless something enforces them. A few checks are cheap to put into CI or a scheduled job.

Plan assertion. Run EXPLAIN on a representative query against a staging database with realistic row counts. Fail the pipeline if the plan contains a sequential scan on the docs table. Tiny test tables will usually choose a sequential scan. Either seed enough data for the planner to behave like production, or verify with SET enable_seqscan = off in that test only.

Recall regression test. Keep a small set of labeled queries with expected documents. Compute exact nearest neighbors by running the same query without the index, for example in a rolled-back transaction with index scans disabled. Then compare the exact results to the approximate ones. Track the overlap over time. Rerun after any change to m, ef_search, the model, or the extension version. The following sketch shows the idea:

def recall_at_k(conn, q, k=10):
    approx = {r[0] for r in conn.execute(
        "SELECT id FROM docs ORDER BY embedding <=> %s LIMIT %s", (q, k))}

    with conn.transaction(force_rollback=True):
        conn.execute("SET LOCAL enable_indexscan = off")
        conn.execute("SET LOCAL enable_bitmapscan = off")
        exact = {r[0] for r in conn.execute(
            "SELECT id FROM docs ORDER BY embedding <=> %s LIMIT %s", (q, k))}

    return len(approx & exact) / k  # alert if this drops below your threshold

Model-drift guard. Add a scheduled query that counts distinct embed_model values per table. Alert when more than one is present outside a planned migration. A one-line check like this helps catch the silent quality loss described above.

Migration scripts. Keep extension creation, table DDL, and index parameters in versioned migrations, not in ad hoc notebooks. That way ef_construction and the opclass are reviewed like any other schema change.

More infrastructure-focused checklists for running data services live on kuryzhev.cloud. For search, the pattern holds: pick the model, match the operator and index, set recall explicitly, and measure it on a schedule.