pgvector (Postgres Native Vector)

When to use

Already use Postgres — add vector search to the same database. Single source of truth for relational metadata and embeddings.

Analogy

Adding a VECTOR to your existing filing cabinet. The cabinet knows everything else about the document (author, date, tags); now it also knows how similar it is to other documents.

Data-flow diagram

  CREATE EXTENSION vector;
  CREATE TABLE chunks (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536),
    metadata jsonb,
  );
  CREATE INDEX idx_chunks_vec ON chunks
    USING hnsw (embedding vector_cosine_ops);

Deep explanation

pgvector is the Postgres extension that adds a vector(N) type, distance operators (<-> L2, <=> cosine, <#> inner product), and ANN indexes (HNSW and IVFFlat). It is the right pick when you already run Postgres and want to keep embeddings and metadata in one place. Combine with jsonb for chunk metadata — a single SELECT can join semantic similarity with structured filters.

Examples

Example 1

-- create extension and column
CREATE EXTENSION IF NOT EXISTS vector;
ALTER TABLE chunks ADD COLUMN embedding vector(1536);

The <=> operator is cosine distance; pairs with vector_cosine_ops index.

Example 2

-- nearest-neighbour retrieval
SELECT id, content
  FROM chunks
 ORDER BY embedding <=> :query_vec
 LIMIT 5;

Filtered search pushes metadata into the same plan — no separate filtering service.

Example 3

-- filtered ANN search
SELECT id, content
  FROM chunks
 WHERE tenant_id = :tenant
   AND metadata @> '{"source_kind":"pdf"}'::jsonb
 ORDER BY embedding <=> :query_vec
 LIMIT 10;

jsonb metadata alongside vectors enables compound search: semantic + structured filters in one query.

Common mistake

Filtering AFTER ANN. Letting the index return 100 candidates and then filtering them down to 10 means most candidates are filtered out — low effective recall.

Key takeaway

pgvector is the easy path when you already run Postgres. Use HNSW with vector_cosine_ops, pre-filter on metadata, store hot metadata as plain columns.

Production Failure Playbook

Failure scenario 1: post-filter-collapse

• Symptom: Filtered vector query returned 3 results instead of 10; recall dropped.
• Detection: EXPLAIN showed index scan returned 1000 rows; only 3 matched filter.
• Root cause: Filter was applied AFTER the ANN scan returned candidates.
• Mitigation: Switch to pre-filter: include tenant_id and metadata in the indexed column or use FILTER with HNSW.
• Prevention: Every filtered vector query is reviewed with EXPLAIN; pre-filter is the default pattern.

Failure scenario 2: wrong-index-op-class

• Symptom: Search latency was 2 seconds; HNSW index was in the plan but not used.
• Detection: EXPLAIN ANALYZE showed Seq Scan despite index existence.
• Root cause: Index op-class was vector_l2_ops but operator used in query was <=> (cosine).
• Mitigation: DROP INDEX; recreate WITH vector_cosine_ops.
• Prevention: Schema review: index op-class matches query operator; CI asserts with EXPLAIN test.