How to add pgvector to a Postgres database

pgvector adds a vector column type to Postgres plus the operators and index types to search it. For most applications it removes the need for a separate vector database entirely — your embeddings live next to the rows they describe, and you can filter on both in one query, in one transaction, with one backup.

The setup is genuinely short. The parts worth reading carefully are the index choice and the operator matching, because the failure mode there isn't an error — it's a query that quietly ignores your index and does a sequential scan over every row.

Enable the extension

CREATE EXTENSION IF NOT EXISTS vector;

-- Confirm, and note the version — feature availability depends on it
SELECT extversion FROM pg_extension WHERE extname = 'vector';

If that fails with "could not open extension control file", the extension isn't installed on the server and you can't fix it from SQL. On self-hosted Postgres that's an apt or yum package (`postgresql-16-pgvector` on Debian and Ubuntu). On a managed provider it's whatever their allowlist says — most support it now, and on PandaStack the `vector` extension is already created when the database is provisioned, so the statement above is a no-op you can run anyway.

Add the column

The dimension count is fixed per column and must match your embedding model exactly. Get it from the model's documentation, not from memory — 1536 for OpenAI's text-embedding-3-small, 3072 for text-embedding-3-large, 768 for many open models.

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  tenant_id   bigint NOT NULL,
  title       text NOT NULL,
  body        text NOT NULL,
  embedding   vector(1536),
  created_at  timestamptz NOT NULL DEFAULT now()
);

Insert vectors

The wire format is a string that looks like a JSON array. Every client library handles this — here it is with plain psycopg so the shape is visible:

import psycopg
from openai import OpenAI

oai = OpenAI()
conn = psycopg.connect(DATABASE_URL)

def embed(text: str) -> list[float]:
    r = oai.embeddings.create(model="text-embedding-3-small", input=text)
    return r.data[0].embedding

with conn.cursor() as cur:
    vec = embed("Firecracker boots a microVM in about 125 milliseconds.")
    cur.execute(
        "INSERT INTO documents (tenant_id, title, body, embedding) "
        "VALUES (%s, %s, %s, %s)",
        (1, "Boot times", "Firecracker boots...", str(vec)),
    )
conn.commit()

For bulk loading, use COPY rather than a loop of INSERTs, and build the index after the data is in. Building an index on an empty table and then inserting a million rows is slower than the reverse, and for IVFFlat it's actively wrong — see below.

Pick a distance operator, and remember which one

pgvector gives you several distance operators, and the important thing is that an index is built for one of them. Query with a different operator and the planner won't use the index — no error, just a sequential scan.

• <=> — cosine distance. The default choice for text embeddings from OpenAI, Cohere and most open models.
• <-> — L2 (Euclidean) distance. Correct when your vectors aren't normalised and magnitude carries meaning.
• <#> — negative inner product. Fastest when vectors are already normalised; note the sign.

Pick one, write it down, and use the matching operator class when you create the index.

HNSW or IVFFlat

Both are approximate — they trade a little recall for a lot of speed. The choice is straightforward in practice.

HNSW is the default recommendation. It builds a graph, gives better recall-versus-speed than IVFFlat, and — crucially — works on an empty table, so rows inserted later are indexed as they arrive. It costs more memory and takes longer to build.

-- Cosine distance, matching the <=> operator
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Query-time recall knob: higher = better recall, slower
SET hnsw.ef_search = 100;

IVFFlat partitions vectors into lists and searches the nearest few. It builds faster and uses less memory, but it has a sharp edge: the partitions are computed from the data present at build time, so building it on an empty or unrepresentative table gives poor recall forever. Load your data first.

-- Rule of thumb: lists = rows / 1000, up to ~1M rows
CREATE INDEX ON documents
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 500);

SET ivfflat.probes = 20;   -- higher = better recall, slower

Query it — and verify the index is used

SELECT id, title, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

The ORDER BY is what makes the index usable. A query that computes distance in the SELECT list but orders by something else, or that wraps the expression in a function, falls back to a scan. Confirm with EXPLAIN before you trust it:

EXPLAIN ANALYZE
SELECT id FROM documents ORDER BY embedding <=> '[0.1, ...]' LIMIT 10;

-- Want: "Index Scan using documents_embedding_idx"
-- Not:  "Seq Scan on documents"

Filtering, which is where it gets interesting

The reason to keep vectors in Postgres rather than a dedicated vector store is that you can filter on ordinary columns in the same query. But a WHERE clause and an approximate index interact awkwardly: the index returns its nearest candidates, then the filter removes some, and you can end up with fewer than LIMIT rows even though matching rows exist.

-- Tenant-scoped search. Raise ef_search so enough candidates survive the filter.
SET hnsw.ef_search = 200;

SELECT id, title
FROM documents
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;

For a highly selective filter — one tenant out of thousands — a partial index per common filter value, or a plain B-tree on the filter column combined with a smaller candidate set, will beat turning ef_search up. Measure both on your data; the answer depends on how selective the filter is.

Sizing, briefly

A 1536-dimension vector at 4 bytes per float is about 6KB per row before the index. A million rows is roughly 6GB of vectors plus the HNSW graph on top, and HNSW wants to live in memory to be fast. That's the real capacity question — not disk, but whether the index fits in RAM.

If it doesn't, the options in order of effort are: reduce dimensions (many models support shortened embeddings), use `halfvec` for half-precision storage at a small recall cost, or move to a database with more memory. On PandaStack that last one is a clone into a larger RAM tier, which leaves the source database untouched while you test the new one.

The checklist

1. CREATE EXTENSION vector, and check the version.
2. Match the column dimension to your embedding model exactly, and record which model produced the vectors.
3. Choose one distance operator and use it consistently.
4. Load data first if using IVFFlat; HNSW is fine on an empty table and is the better default.
5. Build the index with the operator class matching your query operator.
6. EXPLAIN ANALYZE a real query and confirm you see an index scan.
7. Raise ef_search or probes when you add a WHERE clause, and re-measure recall.