Skip to content

SYSTEM Cited by 7 sources

pgvector

pgvector is the open-source PostgreSQL extension that adds a vector column type plus approximate-nearest-neighbour (ANN) indexing and distance operators, letting a relational database store and query embeddings alongside ordinary rows and transactions. It is the de-facto default for teams that want vector search without standing up a separate vector database — the embeddings live in the same Postgres instance as the business data, inside the same transaction and backup domain.

Project: github.com/pgvector/pgvector.

What it provides

  • vector column type — fixed-dimension float arrays stored in a Postgres column (e.g. a 1024-dim or 1536-dim embedding).
  • Distance operators — most notably <=> (cosine distance), plus <-> (L2) and <#> (negative inner product). Cosine similarity is computed as 1 - (a <=> b).
  • ANN indexes — HNSW (graph ANN, the common choice) and IVFFlat (inverted-file clustering). Without an index, queries fall back to exact brute-force scan.
  • SQL-native retrieval — top-k similarity queries are ordinary SELECT ... ORDER BY embedding <=> $1 LIMIT k, so vector search composes with WHERE filters, JOINs, GROUP BY, and ORDER BY over relational columns.

Why teams reach for it

The appeal is colocation: the embedding sits next to the row it describes, so there is no second datastore to keep consistent, no cross-store transaction problem, and retrieval can be expressed as plain SQL that mixes semantic distance with relational predicates (filter by tenant, rank by a business counter, etc.). For RAG systems whose corpus is already in Postgres, pgvector removes an entire moving part.

Zalando RAG-flywheel usage (SQL-native consensus ranking)

Zalando's schema-mapping RAG Flywheel (sources/2026-10-11-zalando-automating-data-ingestion-with-a-rag-flywheel) is a compact, representative pgvector production shape. Human-approved (source_column → target_field) mappings are embedded (1024-dim, Titan Text Embeddings V2) and stored in a column_mappings table with an HNSW index. The warm-start retrieval query shows pgvector's defining strength — semantic distance and relational aggregation in one SQL statement:

SELECT
    cm.source_column,
    cm.target_field AS target_column,
    COUNT(DISTINCT cm.mapping_config_id) AS approval_count,
    MAX(1 - (cm.embedding <=> $1::vector)) AS similarity
FROM column_mappings cm
WHERE cm.embedding IS NOT NULL
  AND (1 - (cm.embedding <=> $1::vector)) >= 0.8   -- cosine similarity ≥ 0.8
GROUP BY cm.source_column, cm.target_field
ORDER BY approval_count DESC, similarity DESC
LIMIT 3;

Notable: the <=> cosine-distance operator supplies the semantic ranking, while GROUP BY + COUNT(DISTINCT ...) + ORDER BY approval_count DESC layer a consensus signal on top — "a mapping confirmed multiple times across different uploads naturally outweighs a single anomaly." Expressing both in one query is exactly the composition a standalone vector DB makes awkward. The same history also powers the system's graceful degradation: when the LLM is unavailable, a pgvector lookup alone can return a previously-approved mapping, and the embeddings are deliberately rebuildable (deleting old rows removes them from future retrieval).

The scaling ceiling (HNSW RAM bound)

pgvector's common failure mode at scale is inherited from its index. Because HNSW search is random-access graph traversal, queries are fast only while the whole index is RAM-resident; spill to disk causes a large slowdown. Databricks' Lakebase team measured the cliff (sources/2026-09-28-databricks-lakebase-search): a 768-dim float32 vector is ~3.3 KB, so 100M rows ≈ 330 GB RAM, with 10×–50× slowdown on disk spill, disk-spilling index builds taking ~50 hours, REINDEX locking the table, and no intra-query parallelism for the HNSW scan — the same RAM-bound critique PlanetScale raised about HNSW generally. Databricks' and PlanetScale's answers (an IVF + binary-quantized index over object storage; SPANN/SPFresh) are explicitly alternatives to the pgvector/HNSW default for very large corpora. For small-to-mid corpora that fit in RAM — like Zalando's historical-mapping index — pgvector remains an excellent fit.

Seen in

Last updated · 854 distilled / 2,448 read