Vector database showroom. Part 1 pgvector — The Station Wagon Already in Your Garage.

Welcome to the Vector Database Showroom — a guided tour through today's most popular vector databases, where we look past the marketing brochures and judge each one by its real cost of ownership. Up first: pgvector, the quiet contender that's probably already sitting in your garage.

🚙 pgvector — The Station Wagon Already in Your Garage

pgvector isn't a separate database. It's a Postgres extension: one CREATE EXTENSION later, your vectors live next to your regular data.

Under the hood: the vector type, two index types (HNSW, IVFFlat), three core distance metrics (L2, cosine, inner product). Since v0.6: halfvec (half the memory) and sparse vectors. Since v0.7: iterative index scans for filtered search.

Where it shines:

• ACID transactions: the embedding and its metadata commit atomically. No dual-write sync, no "eventually consistent" surprises
• Joins: "find similar documents AND fetch related rows" without an application-level roundtrip
• Backups, monitoring, access control, read replicas — everything you've already set up for Postgres covers your vectors for free
• Row Level Security for multi-tenant setups

Where it stalls:

• Vertical ceiling. Comfortable up to ~1M vectors; workable to ~10M with tuning (halfvec, partitioning, Timescale's pgvectorscale). Beyond that — different league
• RAM appetite: 1M × 1536-dim vectors ≈ 6 GB of raw data — plus roughly the same again for the HNSW index itself, which stores a copy of every vector inside the graph. Budget ~12–16 GB of RAM/cache, not 6
• Rebuilding the index at scale takes hours, not minutes

What breaks if you skip the manual:

• Filters + ANN — you have three tools, pick per scenario. A bare WHERE is applied after the HNSW scan returns its top-k, so you can get fewer rows than LIMIT asked for:
  • Pre-filter via b-tree — the classic RAG pattern. Index the filter column (CREATE INDEX ON documents ((meta->>'year'))); on a selective filter, Postgres fetches matching rows first and computes exact distances on just those. 100% recall — and the planner usually picks this route on its own
  • Iterative scans (v0.7+) — SET hnsw.iterative_scan = strict_order keeps walking the HNSW graph until the filter is satisfied. The choice when the filter matches many rows and b-tree pre-filtering would scan half the table
  • Overfetch — grab LIMIT × 5–10 and filter in the app. Quick, dirty, fine for prototypes
• The dimension limits. A vector column stores up to 16,000 dims, but indexes top out at 2,000 (vector) and 4,000 (halfvec). A 3072-dim model like OpenAI's text-embedding-3-large? Won't index as vector — but fits as halfvec (fp16, minimal quality loss). Beyond 4,000 dims: truncate with a Matryoshka-trained model (near-lossless) or move to a dedicated vector DB — Qdrant and Milvus index far higher
• Recall disappointing? Raise hnsw.ef_search — the default 40 is conservative
• Frequent vector UPDATEs → bloat: every edit rewrites ~6 KB per row. A bulk re-embedding is an operation — plan a VACUUM afterwards

Cost of ownership: free under the PostgreSQL license. Zero extra spend, provided your existing instance has the CPU/RAM headroom. Available on RDS, Aurora, Cloud SQL, AlloyDB, Azure, Supabase, Neon — basically everywhere.

Mechanics & parts: any backend developer can drive this car, and every ORM ships an integration (Django, Rails, SQLAlchemy, Prisma). One honest caveat: the project has historically been driven by one very productive person (Andrew Kane), with a growing contributor base — plus an ecosystem of companies (Supabase, Neon, Timescale) building businesses on top of it. The counterweight: it's a Postgres extension, not a black box — worst case, you're holding a documented SQL type and well-known algorithms.

✅ Take it if: you already run Postgres, you're under ~1M vectors, and data consistency matters (RAG over your own DB)

❌ Pass if: you're heading for billions of vectors or distributed scale — the station wagon won't become a freight train

Test drive:

CREATE EXTENSION vector;

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text,
  meta jsonb,
  embedding vector(1536)
);

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

-- b-tree on the filter column → exact pre-filtered search
CREATE INDEX ON documents ((meta->>'year'));

-- operators: <=> cosine | <-> L2 | <#> negative inner product
-- ORDER BY sorts ascending: smallest value = closest neighbor
SELECT id, content FROM documents
WHERE meta->>'year' = '2024'
ORDER BY embedding <=> $1
LIMIT 10;

Gotcha for NumPy/FAISS folks: <#> returns the negative inner product — pgvector makes every operator sort smallest-first.

If you know SQL, you already know pgvector. That's the whole car.