Then came the embedding boom, and a new wave of vendors began suggesting that SQL itself was on its way out. The marketing line went something like this: store your vectors in a purpose-built engine, run approximate nearest neighbor search at scale, and let your relational layer quietly retire. It is a clean story, and like most clean stories in AI infrastructure, it deserves a careful second look.
The framing matters because the choice of database for an AI-backed feature is rarely a binary decision between two camps. It is, more honestly, a question of where to draw the line between transactional consistency, similarity search, and operational complexity. Both architectural families have matured in ways that erode the clean dichotomy the marketing decks imply. Understanding that erosion is what separates a defensible production stack from one that quietly buckles under its first million embeddings.
The Architectural Divide: Structured Data vs. High-Dimensional Embeddings
A relational database treats data as a set of rows inside rigid, pre-declared tables. Each column has a known type, each row conforms to a schema, and queries operate over exact matches — equality on an ID, a range on a date, a join between two foreign keys. The whole engine is optimized for transactional ACID compliance: writes either fully land or fully roll back, and concurrent readers see a consistent snapshot. That contract is what made these systems the default for invoicing, inventory, user accounts, and every other load-bearing piece of business logic built since the 1990s.
A vector database operates in a fundamentally different geometry. Instead of rows and columns, it stores dense numerical arrays — embeddings — that represent text, images, or audio as points in a high-dimensional space. A query does not ask "does this string match?"; it asks "which previously-stored points lie nearest to this new point?" The retrieval is approximate, probabilistic, and built around similarity rather than equality. This is the entire reason vector stores exist: exact nearest-neighbor search over millions of high-dimensional points is computationally intractable, so the field standardized on Approximate Nearest Neighbor (ANN) techniques.
The dominant indexing structures in that world are HNSW (Hierarchical Navigable Small World) and IVFFlat (Inverted File Flat). HNSW builds a layered proximity graph that makes neighborhood queries extremely fast, often single-digit-millisecond at moderate scale. IVFFlat partitions the space into Voronoi cells and limits the search to a small subset of those cells. Both techniques trade a small, tunable amount of recall for an order of magnitude or more in throughput. That trade is invisible in benchmarks but very visible the first time a junior engineer queries the wrong index and ships a RAG system that confidently hallucinates. Those edge cases are where rigor tends to come from, not from the happy-path benchmark.
The vector sizes themselves are non-trivial. OpenAI's widely used text-embedding-3-small produces vectors of 1,536 dimensions, and the larger text-embedding-3-large goes up to 3,072. At three thousand floats per record, you start to feel the storage and bandwidth footprint of even modest corpora. That single number — the dimensionality of your embeddings — quietly drives most of the architectural choices that follow.
Extending PostgreSQL: The Role of pgvector in Modern AI Stacks
For teams already running PostgreSQL, the cleanest escape from the binary is pgvector, an extension that adds a vector column type and a small set of distance operators directly into the relational engine. The headline benefit is unglamorous but powerful: embeddings and relational metadata now live in the same table, under the same transactional rules. You can update a document, its embedding, and its audit log in a single SQL transaction, and a hybrid query can join WHERE category = 'legal' with ORDER BY embedding <=> $1 without crossing a network boundary or coordinating a second system.
The available distance operators are worth knowing by name:
| Operator | Meaning | Common use |
|---|---|---|
<=> | Cosine distance | Normalized text embeddings |
<-> | L2 (Euclidean) distance | Image and dense feature vectors |
<#> | Negative inner product | Models trained with dot-product objectives |
For most LLM-driven applications where embeddings are normalized, cosine distance is the default and <=> is the operator you will reach for. The fact that these are ordinary SQL operators — not a foreign SDK call — is exactly what makes the hybrid pattern so attractive for teams who want to ship AI features without standing up a new database cluster.
The extension is also reasonably mature. In practice, pgvector handles millions of embeddings effectively for the workloads that most no-code and custom web apps ever produce. The ceiling is not architectural; it is operational. It is a single-node extension on top of a single-node PostgreSQL, and that constraint is the one that quietly pushes teams off the relational extension and toward dedicated engines.
Performance Trade-offs: ANN Search vs. Transactional ACID Consistency
Specialized vector databases — Pinecone, Milvus, Qdrant, Weaviate, ChromaDB — were designed from the ground up for the ANN problem. Their horizontal scaling story is real: sharding by vector space, replicated indexes, and cluster topologies that grow with dataset size. They also tend to ship with first-class tooling for index tuning, quantization, metadata filtering, and hybrid lexical-plus-semantic retrieval out of the box. If your dataset is in the tens of millions of vectors and your traffic pattern is read-heavy with occasional bulk reindexing, a purpose-built engine will outperform a relational extension on raw similarity-search latency.
But — and this is where the marketing tends to get slippery — those gains come at the cost of the transactional contract your application code probably depends on. A standalone vector engine is, by itself, not a system of record. It cannot guarantee that a write to a documents table, a write to an embeddings table, and a write to an audit_log table either all succeed or all fail. Most teams solve this by standing up a relational database anyway, alongside the vector engine, and accepting that they now operate two databases with their own consistency windows.
The inverse problem exists too. PostgreSQL with the vector extension does not give you cluster-wide horizontal scaling of the index, replication-aware sharding of high-dimensional data, or the kind of background reindexing that the specialized engines treat as a routine operation. For many teams this is a non-issue. For teams ingesting millions of vectors per day from continuous document pipelines, it eventually becomes the binding constraint.
The honest framing, then, is not "vector databases are faster" or "PostgreSQL is more reliable" but "you are trading transactional consistency for horizontal scale of similarity search, and the threshold where that trade makes sense depends on your ingestion rate, your cardinality, and how badly you can tolerate cross-system drift."
Scaling Constraints: When to Move Beyond Relational Vector Extensions
The threshold question is one I have seen bite a lot of small teams. They start with pgvector, ship a RAG-powered customer-support bot, watch adoption grow, and six months later find themselves staring at a table with ten million rows and a p95 query latency that has crept past the point of being defensible in front of a paying customer. The migration path from "PostgreSQL with pgvector" to "managed vector database" is not trivial, and the early signs of needing to make that move are worth naming explicitly.
A short, practical list of the conditions that tend to push teams off the relational extension:
- Embedding cardinality crossing roughly the tens of millions, where single-node IVFFlat/HNSW indexes begin to dominate RAM.
- Write concurrency that exceeds what a single PostgreSQL writer can absorb without write-amplification on the index.
- A need for filtered vector search at high QPS, where the planner overhead of pushing metadata predicates into a vector scan starts to dominate.
- Multi-region or multi-tenant sharding requirements that the extension does not address.
- Inference workloads that depend on millisecond-scale retrieval across very large corpora, where ANN index latency becomes the bottleneck of the whole request.
When those conditions arrive, specialized engines are usually the right next step. What they are not, however, is a replacement for the relational store. They become one more service in a now-heterogeneous system, and the team inherits the operational burden of keeping two databases in step.
The migration threshold is rarely about the database. It is about whether your team can carry a second system of record without losing the consistency contract the application depends on.
Hybrid Search Architectures: Combining SQL Metadata with Vector Similarity
The most interesting pattern I have seen in production AI applications this year is not "vector database" or "relational database" — it is the deliberate combination of both. A typical hybrid stack looks like this: PostgreSQL holds documents, users, permissions, billing, and embeddings as columns on the documents table; a specialized vector engine holds an index of the same embeddings for low-latency recall; and a thin orchestration layer decides, per query, whether to route through the relational store, the vector store, or both fused by reciprocal rank.
The reason this pattern wins in practice is that most real queries are not pure similarity. A support assistant that returns "the most semantically similar documents" without filtering by tenant, region, or publication status is not a support assistant — it is a leak. Filtering happens in SQL. Semantic ranking happens in the vector engine. Fusing the two — for example, by retrieving top-k from the vector index, then re-ranking or filtering with a relational query — is where the actual product quality lives.
| Layer | Typical technology | Responsibility |
|---|---|---|
| System of record | PostgreSQL with pgvector | Documents, metadata, permissions, transactional consistency |
| ANN retrieval | Pinecone / Milvus / Qdrant / Weaviate / ChromaDB | Low-latency similarity search over large embedding corpora |
| Orchestration | Application or workflow layer | Query planning, filtering, fusion, response synthesis |
A second, increasingly common hybrid is the one staying entirely inside the relational engine. SQL Server 2025 introduced a native VECTOR data type supporting up to 1,998 dimensions, with a VECTOR_DISTANCE() function for similarity queries. The dimensionality ceiling matters: at 1,998 dimensions, it comfortably covers most embedding models in production today, including text-embedding-3-small at 1,536 dimensions, but it stops short of larger embeddings like text-embedding-3-large at 3,072 dimensions. For teams already invested in the Microsoft data stack, that single line of compatibility is enough to keep the whole architecture on one engine.
The deeper question, and the one that will define the next couple of years of this stack, is whether relational vendors will continue narrowing the gap until the hybrid architecture collapses back into a single database — or whether specialized engines will keep pulling ahead on the raw similarity problem and force the hybrid to remain the default. The answer depends less on clever engineering and more on how willing the database vendors are to treat embeddings as a first-class data type rather than an extension bolted on after the fact.
Both architectural families are converging on the same workload from opposite directions. The team that wins is the one that picks a line — and knows exactly what they gave up on the other side of it.
The honest answer to "vector database vs traditional database for AI apps" is that neither side has won. Each has spent the last three years borrowing the other's ideas, and the most defensible production stacks today are the ones that use both deliberately. Whether that convergence continues, stabilizes, or reverses under the next generation of embedding models and inference workloads is the question worth keeping open.