Vector search and AI with pgvector

Store embeddings in your PostgreSQL database and search them by meaning - switching pgvector on, a working example, indexes, and what to check first.

An AI feature - semantic search, "find similar", retrieval for a chatbot, a recommendation - needs somewhere to keep embeddings and a way to find the nearest ones. On ISOGrid that is your ordinary PostgreSQL database with the pgvector extension: the vectors sit beside the rows they describe, in the same transactions, the same backups and the same access rules. There is no second database to run.

Switching it on

Open the database, go to its Extensions view, and switch on pgvector. The platform creates the extension for you, because creating it takes a superuser and your own role is not one.

Two things to check if pgvector is listed as not on the server:

A database server of your own created before pgvector arrived runs an older image. Redeploy the instance; it moves onto the current image and the extension becomes available. Your data is untouched.
A database on a shared server gets pgvector when the region's shared server carries it. If yours does not yet, order a database server of your own, which always does.

A working example

-- One row per document, with its embedding. 1536 is the size of the vectors
-- your embedding model returns; use the size of yours.
CREATE TABLE documents (
    id        bigserial PRIMARY KEY,
    title     text NOT NULL,
    body      text NOT NULL,
    embedding vector(1536)
);

-- Store a document with the vector your embedding model returned for it.
INSERT INTO documents (title, body, embedding)
VALUES ('Refund policy', 'Refunds are made within 14 days...', '[0.012, -0.034, ...]');

-- The five documents closest in meaning to a question: embed the question with
-- the same model, then order by distance. <=> is cosine distance.
SELECT id, title
FROM documents
ORDER BY embedding <=> '[0.008, -0.021, ...]'
LIMIT 5;

The embeddings themselves come from the model you choose and call from your application; the database stores and searches them. Use one model for a column: vectors from different models cannot be compared.

Making it fast

Without an index every search reads the whole table, which is fine up to some tens of thousands of rows. Past that, add an approximate index:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Match the operator class to the operator you search with: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. An index built for one is not used by another. Building it takes memory and time in proportion to the table; do it on a database sized for it, not on the smallest one.

Filtering and combining

Because it is PostgreSQL, a vector search is one clause of an ordinary query. Restrict by tenant, date or status in the same statement:

SELECT id, title
FROM documents
WHERE customer_id = 42 AND published
ORDER BY embedding <=> '[...]'
LIMIT 5;

That is the main reason to keep vectors in your database rather than in a separate vector store: the filter and the search cannot disagree about which rows exist.

What to know before you rely on it

Backups include the vectors, like everything else in the database.
Switching pgvector off fails while a column or an index still uses it, with PostgreSQL's own explanation.
A highly available database replicates vectors with the rest; read-heavy search can be sent to the copies.