pgvector Semantic Search in PostgreSQL: A Python Checklist

A developer published a checklist for running pgvector semantic search inside PostgreSQL, aimed at teams whose documents, permissions and metadata already live in the database. The writeup covers version verification, dimension and operator-class matching, HNSW and IVFFlat index tuning, and a psycopg 3 query pattern, warning that filters can silently defeat the index and that failures usually surface as plausible but worse results rather than errors.

Originally published on kuryzhev.cloud https://kuryzhev.cloud/2026/10/03/pgvector-semantic-search-in-postgresql-a-python-checklist 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 https://github.com/pgvector/pgvector 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. python 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 https://www.postgresql.org/docs/current/sql-createextension.html 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: python def recall at k conn, q, k=10 : Approximate result: whatever the planner picks with the index enabled approx = {r 0 for r in conn.execute "SELECT id FROM docs ORDER BY embedding <= %s LIMIT %s", q, k } Exact result: disable index scans, then force a rollback so the SET LOCAL settings never leak into the rest of the session 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 https://kuryzhev.cloud/ . For search, the pattern holds: pick the model, match the operator and index, set recall explicitly, and measure it on a schedule.