# pgvector Semantic Search in PostgreSQL: A Python Checklist > Source: > Published: 2026-10-03 07:02:22+00:00 *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.