Vector Databases
Intermediate 13 min read Vector Databases

pgvector in Postgres

Vectors where your rows already live

pgvector adds vector columns and ANN indexes inside Postgres — one database for metadata, transactional data and embeddings. It's the pragmatic default when you already live in Postgres.

pgvectorPostgresSQLvector columnHNSWone database
Prerequisites: Vector Indexing
Before we start

What you will learn

  • Create a pgvector table + index
  • Run k-NN and hybrid queries in SQL
  • Know when Postgres is not the right embedding store
Introduction

What is this?

pgvector is a Postgres extension adding a vector type, similarity operators, and exact/ANN (ivfflat, HNSW) indexes. It lets you store embeddings beside their metadata and query with plain SQL, joining vector search with relational filters — the single-database simplicity that makes it a top default for RAG under 10M-ish vectors.

Motivation

Why do you need it?

  • No second system to sync: rows, metadata, vectors and access control in one engine.
  • SQL gives hybrid search (vector AND metadata, JOIN with transactional tables) for free.
  • Operational posture: backups, replication and tooling you already have.
  • For small-to-mid scale it is honestly simpler than a dedicated vector DB.
Think about it

Mental model

Imagine

A card file kept inside the main ledger

Think of

pgvector

One book holds every record plus the index cards interleaved — look up by number (exact) or by topic (vector) without consulting a second library. Fine until the file outweighs the ledger and you need specialized shelving.

Mechanics

How it works

  • CREATE EXTENSION vector; add a column of type vector(dim).
  • Index with ivfflat (build: lists) or HNSW (build: m/ef_construction).
  • Query using <=> (cosine), <-> (L2), <#> (inner product) operators.
  • Predicate filters (metadata) run in the same query as the ANN walk.
  • Vectors participate in normal Postgres DDL, backups and migrations.

Vector search folded into Postgres.

Table
Embeddings
Index
Query
Results
Hands-on

Examples

pgvector in SQL

pgvector in SQL
-- session: CREATE EXTENSION vector;
CREATE TABLE docs (
  id bigserial PRIMARY KEY,
  title text,
  embedding vector(1024)
);
 
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
 
SELECT id, title, 1 - (embedding <=> $1) AS similarity
FROM docs
WHERE public = true            -- metadata filter with vector search
ORDER BY embedding <=> $1
LIMIT 5;

One query: vector similarity + a relational filter + ordering, all in Postgres.

Real world

In production

  • Team knowledge bases: doc rows + embeddings + permissions in one PG database.
  • Support copilots join ticket metadata with embedding tables in SQL.
  • Mid-scale RAG with existing postgres infra avoids a second operational surface.
Guidelines

Best practices

  • Always index (HNSW preferred) once you exceed a few thousand vectors.
  • Keep vectors in a dedicated table — pgvector's optimizer behavior improves with isolation.
  • Set indexing dims exactly equal to your embedding model's output.
  • Plan for size: pgvector memory grows with index; consider dedicated plane when it hurts.
Watch out

Common mistakes

  • Running queries without any vector index (full table scan at scale).
  • Mismatched vector dimensions at insert time.
  • Assuming it's free forever — postgres backup/replica cost grows with index size.
Root cause analysis

Common issues

Error
pgvector queries do full scans
Root cause

No ANN index or planner indifference

Fix

Add an HNSW index and ANALYZE; steer with 'SET hnsw.' params if needed.

Error
Index build hangs on huge table
Root cause

LIST under memory

Fix

Build in maintenance window with tuned work_mem, or batch-load in chunks.

When things break

Debugging tips

  • Slow vector queries → check executor plans; ensure fallback scans share the right knobs.
  • Index not built → verify column type + extent version compatible.
  • Unusable index on metadata-heavy queries → index ASSUMES filters; check planner hybrid behavior.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

pgvector lets you store vectors…

2

For huge corpora, pgvector always matches dedicated vector DBs.

Go deeper

Official documentation

pgvector repo
docs
Supabase pgvector guides
docs