ephizen / wiki

pgvector

A PostgreSQL extension that adds vector types, distance operators, and ANN indexes. Turns your existing database into a vector store.

Category
Vector Databases
Difficulty
Beginner
When to use
You already run Postgres and your vector workload is modest to medium — tens of millions of vectors, single-digit ms queries.
When not to use
You need billions of vectors at scale, or you're not already using Postgres.
Alternatives
Pinecone Qdrant Weaviate Milvus
https://github.com/pgvector/pgvector →

At a glance

FieldValue
CategoryVector search extension for Postgres
DifficultyBeginner
When to useYou already run Postgres; small to medium scale
When not to useBillion-vector scale, no existing Postgres
AlternativesPinecone, Qdrant, Weaviate, Milvus

What it is

pgvector adds a vector column type and operators for L2 distance (<->), inner product (<#>), and cosine distance (<=>) to Postgres. It supports two ANN index types — IVFFlat and HNSW — for fast approximate nearest-neighbor search, and you can combine a vector query with normal SQL WHERE clauses in one statement.

When we reach for it at Ephizen

  • Any RAG system where the source documents already live in Postgres alongside users and permissions.
  • Small-team production where running another database is not worth it.
  • Workloads where you need to filter by structured fields (tenant_id, date, tags) before ANN search — one SQL query does both.

Getting started

CREATE EXTENSION vector;
CREATE TABLE docs (
  id bigserial PRIMARY KEY,
  content text,
  embedding vector(1536)
);
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

SELECT id, content
FROM docs
ORDER BY embedding <=> '[0.1,0.2,...]'::vector
LIMIT 5;

Gotchas

  • HNSW builds are memory-hungry. Size maintenance_work_mem before creating the index on a large table.
  • IVFFlat needs training data before you query it — build the index after you’ve inserted rows.
  • Postgres still owns your page cache and WAL. A huge embedding column can push hot data out of cache and hurt other workloads. Consider a dedicated Postgres instance for vector-heavy tables.

Related tools