pgvector

pgvector: The Monolith Strikes Back

In 2023, the tech world convinced itself it needed specialized infrastructure for AI. "Vector Databases" became the hottest category. pgvector offers a boring, pragmatic counter-argument: you don't need a new database; you just need a better plugin for the one you already have.

Under the Hood: HNSW and C-Level Speed

pgvector is not a Python wrapper. It is a native PostgreSQL extension written in C. This allows it to hook directly into the database's core query planner and storage engine.

Initially, it relied on IVFFlat (Inverted File Flat) indexing—fast to build but accuracy-dependent on list size. Today, it supports HNSW (Hierarchical Navigable Small Worlds). This is the industry standard for graph-based indexing. It allows for ultra-fast approximate nearest neighbor searches with high recall. It supports L2 distance, inner product, and cosine distance natively, handling embeddings from OpenAI, Hugging Face, or local models with mathematical precision.

The Killer Feature: Vectors + SQL

The true power of pgvector isn't raw speed; it's context.

In a specialized vector DB, you often face the "metadata filtering" problem. You find similar vectors, but then you need to filter them by user ID, date, or category. This often involves complex synchronization or inefficient post-filtering.

With pgvector, this is just a WHERE clause. You can perform a vector similarity search and a relational join in a single, ACID-compliant SQL query.

SQL

___________________________

SELECT * FROM items

WHERE category_id = 123

ORDER BY embedding <-> '[...]'

LIMIT 5;

___________________________

Because it is PostgreSQL, you get Write-Ahead Logging (WAL), point-in-time recovery, and established backup tools essentially for free.

Where Postgres Stumbles (Trade-offs)

PostgreSQL is a monolith. It scales vertically.

• Scale Limits: If you are storing 100 million vectors, pgvector is fantastic. If you are storing 10 billion vectors, the vertical scaling limits of a single Postgres instance will hurt you. Specialized databases are often easier to shard horizontally.

• Resource Contention: Vector index creation (especially HNSW) is CPU and RAM hungry. If you run heavy vector workloads on the same instance serving your critical transactional app, you risk degrading overall performance.

Verdict for Architects

"Boring" technology is usually the right choice. For 95% of use cases—RAG apps, recommendation features, semantic search—pgvector is the correct starting point. It simplifies your stack, reducing maintenance overhead. Only look at specialized vector databases if you hit massive scale or require throughput that a single SQL instance cannot handle.