pgvector: Postgres for AI

This cover signals a shift from concept to keyboard. The previous posts explained what pgvector is, why it earns its place, and how its indexes work; this one assembles all of it into a single runnable flow you can lift into your own project.

The stack is deliberately mainstream: Postgres with the vector extension, an external embedding model, the psycopg driver with pgvector's adapter, and an HNSW index. Nothing here is exotic, which is the point — this is the template most production systems actually start from.

These five steps are the skeleton of every pgvector application. First, enable the extension and define a table with a vector column. Second, turn text into an embedding with a model. Third, insert the text and its vector together in one row. Fourth, build an appropriate index once you have representative data. Fifth, run nearest-neighbor queries with a real metadata filter applied first.

The ordering matters, especially that the index comes after data exists. Following these steps in sequence avoids the most common setup mistakes, which the final slides will revisit.

Step one establishes the storage. `CREATE EXTENSION IF NOT EXISTS vector;` makes the type available, and the `docs` table pairs each embedding with the data it describes — a tenant id for multi-tenant filtering and the original body text for display.

Note the `tenant_id` column: including relational metadata directly on the row is exactly what makes pgvector valuable, because it lets later queries filter by tenant in the same statement that ranks by similarity. The `vector(1536)` dimension is chosen to match the embedding model used in the next step.

Step two is the only place intelligence enters the system. The `embed` function calls an external model — here OpenAI's text-embedding-3-small — and returns a plain Python list of 1536 floats. pgvector never sees the text; it only ever receives this array.

Isolating embedding behind a single function is a deliberate design choice. It gives you one place to swap models, add caching, batch requests, or retry on failure, and it keeps the rest of your data-access code unaware of which provider you use. That seam pays off the day you change models.

Step three writes the row. The key line is `register_vector(conn)`, which installs pgvector's psycopg adapter so a Python list is sent to Postgres as a real `vector` literal and parsed back into a list on read. Without it you would have to format vectors as strings by hand and risk subtle bugs.

The insert binds the tenant, the body text, and the embedding as parameters — never string-concatenated — which keeps the statement safe from injection and lets the driver handle type conversion. The explicit `commit()` makes the write durable; wrapping multiple inserts in one transaction is how you load batches atomically.

Step four builds the index, and the comment carries the most important rule: do it after a representative amount of data is loaded. For HNSW this lets the planner and statistics reflect reality; for IVFFlat it is mandatory because the clusters are derived from the data itself.

The `vector_cosine_ops` operator class must match the `<=>` operator used in the queries that follow — a mismatch here is the classic cause of an ignored index. The trailing `ANALYZE docs;` refreshes planner statistics so Postgres makes good decisions about when to use the new index.

Step five is the query that ties everything together, and its structure encodes a best practice: filter first, then rank. The `WHERE tenant_id = %s` restricts the search to one tenant's data, and only the surviving rows are ordered by cosine distance to the query embedding.

The query vector is passed twice — once in the SELECT to surface the distance as a column, and once in the ORDER BY to rank — using the same value so the displayed score matches the ranking. Selecting `embedding <=> %s AS distance` lets the application read how close each result is, not just the order.

This pipeline diagram traces a single document through the entire system: raw text, the `embed()` model call that turns it into a vector, the INSERT that stores the row and its vector together, the HNSW index build that makes search fast, and finally the `<=>` query that retrieves the top-k neighbors.

Seeing the five stages in a row reinforces which parts pgvector owns (store and search) and which parts are yours (chunking and embedding). It is the same shape as the RAG pipeline from post one, now grounded in concrete code.

Interpreting the result requires understanding cosine distance. With `<=>`, a distance of 0 means the two vectors point in exactly the same direction (maximally similar) and 2 means they point in opposite directions. Smaller is always more similar, which is why `ORDER BY distance` puts the best matches first without any extra work.

If you want to show users a friendly 0-to-1 similarity score instead of a raw distance, compute `1 - distance` for cosine. This is purely a presentation transform; the ranking is identical either way. Being explicit about the metric prevents the common confusion of treating a small distance as a low score.

These bullets are the touches that separate a demo from production. Calling `register_vector` ensures vectors round-trip cleanly between Python and Postgres. Batching embedding calls cuts both latency and API cost dramatically when ingesting many documents. Filtering with WHERE before the ORDER BY keeps queries both correct and fast.

Tuning `ef_search` lets you dial recall against latency without rebuilding the index, and storing the embedding model's name per row means you can detect mismatches and re-embed selectively when you upgrade models. Each is small, but together they prevent the most common production headaches.

The mistake here is timing the index wrong. Building it on an empty or tiny table produces meaningless IVFFlat clusters and denies HNSW the statistics it benefits from. The discipline is: load a representative batch first, then build the index, then ANALYZE so the planner knows the real row counts.

The related trap is forgetting that an index is tied to a specific embedding space. If you ever switch embedding models, the stored vectors are no longer comparable to new ones, so you must re-embed the data and rebuild the index. Planning for that from the start — by storing the model name — turns a painful migration into a routine job.

That closes the hands-on post. You now have a complete, adaptable template: schema, embedding, insertion, indexing, and a filtered similarity query, plus the production touches that keep it healthy.

The final post in this set turns to failure modes — the quiet ways pgvector goes wrong in production, from silent full scans to mixed embedding models, and exactly how to catch each one before it reaches users.