You already run Postgres. It holds your users, your orders, your documents. So when someone tells you that shipping "search that understands meaning" requires standing up a separate vector database — a new service to deploy, secure, back up, and pay for — be skeptical. For almost every product at launch, it doesn't. The pgvector extension turns the Postgres you already have into a competent vector store, with real approximate-nearest-neighbor indexing.

This is a start-to-finish tutorial for a technical founder. We'll enable the extension, add a vector column, generate embeddings from an OpenAI-compatible API, backfill your existing rows, build an HNSW index, and write the similarity query. By the end you'll have working semantic search running on one database — no new infrastructure.

Step 1 — Enable the pgvector extension#

pgvector ships as a standard Postgres extension. Most managed providers (Supabase, Neon, RDS, Cloud SQL) already have it available; you just turn it on. Connect with psql and run:

CREATE EXTENSION IF NOT EXISTS vector;

That's it — the vector, halfvec, and bit types and their operators are now available in this database. Note the extension is installed per database, so run it once in each database where you'll store embeddings.

Step 2 — Add a vector column to your schema#

A vector column has a fixed dimensionality that must match your embedding model's output. We'll use OpenAI's text-embedding-3-small, which produces 1536-dimensional vectors, so the column is vector(1536). Add it to an existing table:

ALTER TABLE documents ADD COLUMN embedding vector(1536);

Or create a fresh table, matching the canonical pgvector example:

CREATE TABLE documents (
  id       bigserial PRIMARY KEY,
  content  text,
  embedding vector(1536)
);

Why fixed dimensions? The index and the distance operators need every row to live in the same space. If you later switch models, you add a new column and re-embed — you don't mutate the old one in place.

Step 3 — Generate embeddings from an OpenAI-compatible API#

An embedding is just an array of floats. You get one by sending text to an embeddings endpoint. The OpenAI Python SDK exposes this as client.embeddings.create(...):

from openai import OpenAI

client = OpenAI()  # reads OPENAI_API_KEY

def embed(texts: list[str]) -> list[list[float]]:
    response = client.embeddings.create(
        input=texts,
        model="text-embedding-3-small",
    )
    return [item.embedding for item in response.data]

Because this is an OpenAI-compatible call, the same code works against any provider that speaks the protocol — a self-hosted model, a gateway, or an alternative vendor — by passing base_url=... and your key to OpenAI(...). The response's .data list preserves input order, so you can batch many strings in one request.

Cost is negligible for launch-scale corpora — embeddings are billed per token and are among the cheapest calls you'll make. Check your provider's current pricing page before you backfill millions of rows.

Step 4 — Insert and backfill embeddings#

Use the pgvector Python integration so you can pass native lists instead of hand-formatting strings. Register the type on your connection, wrap vectors in Vector, and insert:

import psycopg
from pgvector.psycopg import register_vector, Vector

conn = psycopg.connect("postgresql://...", autocommit=True)
register_vector(conn)

def backfill(batch_size: int = 100):
    while True:
        rows = conn.execute(
            "SELECT id, content FROM documents "
            "WHERE embedding IS NULL LIMIT %s",
            (batch_size,),
        ).fetchall()
        if not rows:
            break
        vectors = embed([content for _id, content in rows])
        for (row_id, _content), vec in zip(rows, vectors):
            conn.execute(
                "UPDATE documents SET embedding = %s WHERE id = %s",
                (Vector(vec), row_id),
            )

backfill()

New writes follow the same pattern — embed the text, INSERT ... VALUES (%s, %s) with Vector(embedding).

Step 5 — Build an HNSW index#

Without an index, a query scans every row (exact but slow). pgvector's HNSW index builds a multilayer graph for fast approximate nearest-neighbor search. Match the operator class to your distance metric — for embeddings you almost always want cosine, which is vector_cosine_ops:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Build it after your initial backfill — like other index types, creating the index once your data is loaded is faster than maintaining it during a bulk load. You can tune build quality with WITH (m = 16, ef_construction = 64) (those are the defaults). At query time, SET hnsw.ef_search = 100; raises recall at the cost of speed (default is 40).

Step 6 — Run the KNN query with <=>#

The <=> operator computes cosine distance. Order by it ascending and take the top matches. Cosine similarity is just 1 - distance:

SET hnsw.ef_search = 100;

SELECT content,
       1 - (embedding <=> %s) AS similarity
FROM documents
ORDER BY embedding <=> %s
LIMIT 5;

Bind the same query embedding (from embed(["user query"])) to both %s. pgvector also offers <-> (L2), <#> (inner product), and <+> (L1) — but if you indexed with vector_cosine_ops, query with <=> so the planner can actually use the index.

Hybrid search: keyword + vector#

Pure vector search can miss exact terms — product SKUs, names, error codes. The fix is hybrid search, and pgvector explicitly supports using it together with Postgres's built-in full-text search. Keep a tsvector alongside your embedding, run both a keyword match and a vector match, then blend the two ranked lists (reciprocal rank fusion is a common, simple way to merge them). You get the recall of semantics and the precision of keywords — still on one database, no extra service.

When to reach for a real vector DB#

pgvector is enough to launch and comfortably scales into the millions of rows. Consider a dedicated vector store when:

Until you hit one of those, a separate vector DB is infrastructure you're carrying for no benefit.

The 5-minute version#