Build a small vector search with Python and PostgreSQL

A vector search retrieves items whose numeric representations are close under a chosen distance measure. PostgreSQL with pgvector lets you keep those representations beside ordinary document metadata. Before connecting an embedding model or a generator, it helps to inspect a tiny example. This tutorial builds the retrieval layer only. Its three-dimensional vectors are invented for demonstration and are not meaningful language embeddings.

Prepare a disposable environment

Use a local PostgreSQL database with the pgvector extension installed on the server. Follow the pgvector project’s installation instructions for your environment, and make sure the database user can create the extension. Use a disposable database for the example, not an existing production database.

Install Psycopg with pip install "psycopg[binary]" in a virtual environment. Set DATABASE_URL in your shell to your local connection string. Keep credentials out of source control and browser code. The binary package is a convenient way to try the example; choose the appropriate installation method for your production environment.

Reference: pgvector — Official project and query examples ↗ · Psycopg — Installation ↗

Create a tiny collection and query it

The Python 3 example creates a table named demo_chunks if it does not exist, adds two fictional records and retrieves one nearest neighbor using cosine distance. The primary key and conflict handling make repeated runs update the same example records instead of multiplying them.

Save it as search_demo.py and run python search_demo.py. With the configured database and extension available, the top result should be the returns record with distance 0. The query vector exactly matches that record’s invented vector. This proves the plumbing, not the quality of semantic search.

import os
import json
import psycopg

records = [
    ("returns", "Unopened items follow the returns policy.", [1, 0, 0]),
    ("shipping", "Delivery depends on the destination.", [0, 1, 0]),
]

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
    conn.execute("""CREATE TABLE IF NOT EXISTS demo_chunks (
        id text PRIMARY KEY, body text NOT NULL,
        embedding vector(3) NOT NULL
    )""")
    for record_id, body, vector in records:
        conn.execute("""INSERT INTO demo_chunks VALUES (%s, %s, %s::vector)
            ON CONFLICT (id) DO UPDATE SET
            body = EXCLUDED.body, embedding = EXCLUDED.embedding""",
            (record_id, body, json.dumps(vector)))
    rows = conn.execute("""SELECT id, body, embedding <=> %s::vector AS distance
        FROM demo_chunks ORDER BY distance LIMIT 1""",
        (json.dumps([1, 0, 0]),)).fetchall()
    print(rows)

Reference: pgvector — Official project and query examples ↗

Replace the toy vectors deliberately

For semantic retrieval, generate each document embedding with the same model and preprocessing contract used for query embeddings. Match the table’s vector dimension to that model’s output. Changing models or dimensions usually requires a deliberate re-embedding and migration plan.

Store a stable source ID, document version and any access-control metadata beside each chunk. A vector is not enough to explain where a passage came from or whether the current user may read it. Keep the original source available for inspection.

Begin with exact search and a baseline

pgvector performs exact nearest-neighbor search by default and also supports approximate indexes such as HNSW and IVFFlat. An approximate index introduces a speed-versus-recall tradeoff. It is not automatically necessary for a small document collection.

First compare retrieved source IDs against a small question set. If you later add an index, measure latency and recall on the same queries, including access filters. Use parameterized SQL as shown above and let the database enforce the application’s data boundaries.

Reference: pgvector — Official project and query examples ↗

Connect generation only after retrieval is useful

Pass a small set of relevant, permitted passages to your answer-generation step and preserve their source IDs. Define behavior for no useful match rather than always sending the nearest row as if it must be relevant.

Keep retrieval and answer evaluation separate. If the wrong passage appears, improve collection preparation or search. If the right evidence appears but the answer misstates it, work on the generation and validation step. This separation makes debugging much more concrete.