pgvector in Machine Learning

The Operational Tax of Separate Vector Databases

Let's start with the pain point. You are building a RAG-powered application. You have PostgreSQL for your users, documents, permissions, and audit logs. Now you need vector search for semantic retrieval. The conventional advice says: spin up Pinecone, or deploy Qdrant, or run Milvus on Kubernetes.

But here is what actually happens. You now have two databases to operate. Two sets of connection strings. Two backup strategies. Two failure domains. Two billing dashboards. And worst of all, you need to keep data synchronized between them -- when a document is deleted in Postgres, its embedding must also be deleted from the vector database. That synchronization layer is where bugs hide and consistency breaks down.

For a well-funded team with dedicated infrastructure engineers, this is manageable. For a 5-person startup in Bengaluru trying to ship their first AI product? That operational tax can be crippling.

PostgreSQL as the Universal Foundation

PostgreSQL has been the world's most advanced open-source relational database for over 35 years. Its extension system is arguably its greatest architectural decision -- it allows third-party code to add new data types, index methods, operators, and query planner hooks without forking the core database.

pgvector exploits this extension architecture to add vector similarity search as a native capability. When you CREATE EXTENSION vector;, PostgreSQL gains a vector data type, distance operators (<-> for L2, <=> for cosine, <#> for inner product), and two ANN index types (IVFFlat and HNSW). Your vectors live in the same tables, participate in the same transactions, and are backed up by the same pg_dump you already run.

The Timeline That Made pgvector Essential

pgvector's growth mirrors the AI application boom:

• April 2021: v0.1.0 released with basic vector type and exact (brute-force) search
• October 2022: v0.3.0 added IVFFlat indexing for approximate nearest neighbor search
• August 2023: v0.5.0 introduced HNSW indexing -- the same algorithm used by Pinecone, Qdrant, and Weaviate internally
• May 2024: v0.7.0 added halfvec (16-bit floats), sparsevec, binary vectors, and quantized expression indexes
• October 2024: v0.8.0 brought iterative index scans -- a game-changer for filtered vector search that solved the long-standing "overfiltering" problem

Each release narrowed the gap between pgvector and dedicated vector databases. Today, for datasets up to tens of millions of vectors, pgvector is a genuinely competitive choice -- not just a convenience hack.

Key Insight: pgvector exists because the operational cost of running a separate vector database often exceeds the performance benefit, especially for teams that already depend on PostgreSQL. It turns "should we add another database?" into "should we add another extension?" -- and that's a much easier question to answer.

Think of It as a New Column Type

Here is the simplest mental model: pgvector adds a new column type to PostgreSQL, just like integer, text, or jsonb. Except this column stores arrays of floating-point numbers (embeddings), and you can create indexes on it that optimize for geometric proximity rather than alphabetical or numerical ordering.

When you write SELECT * FROM documents ORDER BY embedding <=> query_vector LIMIT 10;, PostgreSQL's query planner sees the <=> operator, recognizes that an HNSW or IVFFlat index exists on the embedding column, and uses that index to find the approximate nearest neighbors -- just like it would use a B-tree index for an ORDER BY created_at query.

That's it. No special API. No separate query language. No SDK. Just SQL.

Why This Matters More Than You Think

The power of this approach isn't just simplicity -- it's composability. Because vectors live in regular PostgreSQL tables, you can:

• JOIN vector results with user permissions tables to enforce access control
• WHERE clause filter on metadata columns (category, tenant_id, created_at) in the same query as vector search
• Use transactions to ensure that a document and its embedding are inserted or deleted atomically
• Run standard backups that include both your relational data and your vectors
• Apply row-level security policies to vector data, which is critical for multi-tenant SaaS applications

Dedicated vector databases are catching up on some of these features, but none of them have the 35-year head start that PostgreSQL's SQL engine provides.

The Tradeoff You Are Making

The flip side is real: pgvector shares resources (CPU, memory, I/O) with your application's relational workload. A heavy vector index build or a burst of similarity queries can impact your regular SELECT and INSERT performance. Dedicated vector databases don't have this problem because vector search is all they do.

Think of it like living in a studio apartment versus having a separate office. The studio is simpler and cheaper, but your work and personal life share the same space. When your vector workload is modest relative to your relational workload, the studio is perfect. When it grows large, you might need that separate office.

Rule of Thumb: If your vector workload is less than 30-40% of your total database workload, pgvector is almost certainly the right choice. Beyond that, start benchmarking against dedicated alternatives.

The Vector Type

pgvector introduces a vector(d) column type where $d$ is the dimensionality. Internally, each vector is stored as an array of float4 (32-bit IEEE 754 floating-point) values. The halfvec(d) type uses float2 (16-bit) values, halving storage at the cost of reduced precision.

Supported Distance Functions

pgvector supports three distance operators that map directly to mathematical distance functions:

L2 (Euclidean) distance via the <-> operator: $d_{L2}(q, v) = \sqrt{\sum_{i=1}^{d} (q_i - v_i)^2}$

Cosine distance via the <=> operator: $d_{\cos}(q, v) = 1 - \frac{\sum_{i=1}^{d} q_i \cdot v_i}{\sqrt{\sum_{i=1}^{d} q_i^2} \cdot \sqrt{\sum_{i=1}^{d} v_i^2}}$

Note: this returns distance (0 = identical), not similarity. Cosine similarity = $1 - d_{\cos}$.

Inner product (negative) via the <#> operator: $d_{ip}(q, v) = -\sum_{i=1}^{d} q_i \cdot v_i$

The negative sign converts the maximum inner product problem into a minimum distance problem, allowing PostgreSQL's index scan to work correctly (it always scans for minimum values).

IVFFlat Index

The IVFFlat (Inverted File with Flat quantization) index partitions the vector space into $L$ Voronoi cells using k-means clustering. At query time, it searches the $P$ nearest cells (controlled by the probes parameter) and performs exact distance computation within those cells.

• Build complexity: $O(n \cdot L \cdot I)$ where $n$ is the number of vectors, $L$ is the number of lists, and $I$ is the number of k-means iterations
• Query complexity: $O(P \cdot n/L \cdot d)$ where $P$ is the number of probes and $d$ is the dimensionality
• Space complexity: $O(n \cdot d)$ -- the raw vectors are stored flat within each cell

HNSW Index

The HNSW (Hierarchical Navigable Small World) index builds a multi-layer proximity graph. Each layer contains a subset of vectors connected by edges to their approximate nearest neighbors. Search starts at the top (sparsest) layer and greedily descends to the bottom (densest) layer.

• Build complexity: $O(n \cdot \log(n) \cdot M \cdot \text{ef\_construction})$
• Query complexity: $O(\log(n) \cdot M \cdot \text{ef\_search})$
• Space complexity: $O(n \cdot (d + M))$ where $M$ is the maximum number of connections per node

The key parameters are:

• m (default 16): maximum number of bi-directional links per node per layer. Higher values improve recall but increase memory and build time.
• ef_construction (default 64): size of the dynamic candidate list during index building. Higher values yield better graph quality at the cost of build time.

Critical: The m and ef_construction parameters are set at index creation time and cannot be changed without rebuilding the index. The query-time parameter hnsw.ef_search (default 40) can be tuned per-session via SET hnsw.ef_search = 100;.

Wrapping Up

pgvector is the open-source PostgreSQL extension that brings vector similarity search directly into the world's most popular open-source relational database. By adding vector, halfvec, sparsevec, and bit data types along with HNSW and IVFFlat index access methods, it eliminates the need for a separate vector database for a wide range of ML applications -- from RAG pipelines and semantic search to recommendation engines and duplicate detection.

The core value proposition is operational simplicity through unification: your embeddings live in the same database as your users, documents, and application state. They participate in the same transactions, are protected by the same access control policies, and are backed up by the same procedures. The SQL composability this enables -- combining vector similarity with JOINs, WHERE clauses, full-text search, and even geospatial queries -- is unmatched by any dedicated vector database.

pgvector is not without limitations. It shares resources with your relational workload, scales vertically rather than horizontally, lacks GPU acceleration, and its HNSW index builds are slower than distributed alternatives. For datasets beyond 50-100 million vectors or workloads demanding sub-millisecond latency at thousands of QPS, dedicated vector databases like Qdrant, Milvus, or Pinecone have architectural advantages. But for the vast majority of applications -- including those built by early-stage startups in Bengaluru, growing SaaS companies in Hyderabad, and enterprise teams in Mumbai -- pgvector on a well-tuned managed PostgreSQL instance delivers more than enough performance at a fraction of the cost and complexity. Start here, measure, and scale when the data tells you to.