Practical: Vector Search in PostgreSQL

Why pgvector

pgvector adds a vector column type and ANN indexes to your existing PostgreSQL database. You keep vectors next to the rows they describe, so filters and joins work as usual.

Enable and Create a Table

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
  id      bigserial PRIMARY KEY,
  title   text,
  body    text,
  embedding vector(384)
);

Insert Vectors

INSERT INTO docs (title, body, embedding)
VALUES ('Spring Boot', 'A Java toolkit for fast web apps',
        '[0.01, 0.22, -0.15, ...]');

In real code, an embedding model produces the number list.

Cosine Similarity Search

SELECT title
FROM docs
ORDER BY embedding <=> '[0.01, 0.22, -0.15, ...]'
LIMIT 5;

The <=> operator computes cosine distance: smaller is more similar.

Add an HNSW Index

CREATE INDEX ON docs
USING hnsw (embedding vector_cosine_ops);

Now similarity searches skip the full scan. Also available: ivfflat with ivfflat_cosine_ops for lower-memory setups.

Key Points

• pgvector stores vectors as a vector(n) column.
• <=> means cosine distance.
• HNSW indexes make searches fast.
• Metadata filters combine with vector search naturally.