How do you enable pgvector and align the column type with the embedding model?

Category: Database & RAG

Answer

pgvector adds a vector(N) column type plus three distance operators (<-> L2, <=> cosine, <#> inner product) and ANN indexes (HNSW and IVFFlat). Choose the column dim to match your embedding model (1536 for text-embedding-3-small, 3072 for -3-large); changing dim requires a re-embed and a table recreate.

Concrete examples from the fca project context

Example 1

CREATE EXTENSION vector; ALTER TABLE chunks ADD COLUMN embedding vector(1536).

Example 2

Index pairs with the operator: vector_cosine_ops with <=>, vector_l2_ops with <->.

Example 3

Bulk insert from a re-embed script: bind numbers per row, never chunk in app code.

Data flow / flow chart

model card -> dimension N
  CREATE TABLE chunks (embedding vector(N))
  CREATE INDEX ... USING hnsw (embedding vector_cosine_ops)

Takeaway

Lock the column dimension to your embedding model; switching requires a re-embed + table recreate, plan the migration.