Vector Database Architecture: What It Is, What It Isn't, and When SQL Wins

Vector databases became a critical piece of enterprise AI infrastructure when retrieval-augmented generation moved from research to production. The rapid adoption brought with it a common architectural error: teams treated vector databases as a universal data store, applying them to problems that relational databases solve better, faster, and at lower cost. A vector database is a specialized index for one specific operation. Understanding what that operation is — and what it is not — determines whether you have selected the right tool for your problem.

The marketing narrative around vector databases contributed to the confusion. Phrases like "AI-native database" and "the database of the future" implied that traditional relational databases were obsolete, when the accurate framing is much narrower: vector databases add a capability that relational databases traditionally lacked, and that capability is specifically approximate nearest neighbor search over high-dimensional embedding vectors. For the problems that require that capability, vector databases are essential. For the vast majority of enterprise data problems, relational databases remain the appropriate tool.

This post explains what a vector database actually is at the mechanical level, describes the HNSW indexing algorithm that powers most production vector search systems, provides a clear framework for when vector search is the right choice versus when SQL wins, and evaluates the five most common vector database options against their architectural tradeoffs. It concludes with the hybrid architecture that most mature production RAG systems end up using.

What a Vector Database Actually Is

A vector database stores high-dimensional numerical vectors — the outputs of embedding models — and enables a specific type of search: given a query vector, find the k vectors in the database that are most similar to it by some distance metric, typically cosine similarity or Euclidean distance. The database does not store the original text, images, or other unstructured data directly — it stores the numerical representations of that data produced by embedding models, along with metadata and an identifier that links back to the original content.

The technical problem that vector databases solve is approximate nearest neighbor (ANN) search in high-dimensional spaces. "Nearest neighbor" means finding the vectors closest to a query vector by distance. "Approximate" means the search returns high-probability nearest neighbors rather than guaranteed exact nearest neighbors, because exact nearest neighbor search in high-dimensional spaces is computationally intractable at scale. A 1536-dimensional embedding space (typical for a text embedding model) makes exact search prohibitively slow for large corpora.

The key word is "approximate." This is a fundamental characteristic of vector search, not a limitation to be engineered away. When a user queries "what is the company's parental leave policy," the query is converted to a vector and the vector store finds the k document chunks whose vectors are most similar. "Most similar" is determined by mathematical proximity in a high-dimensional space. Mathematical proximity is a proxy for semantic relevance, not a guarantee of it. In most cases the proxy is good enough. In some cases it is not, which is why retrieval evaluation is a necessary part of any production RAG system.

How HNSW Indexing Works

Hierarchical Navigable Small World (HNSW) is the dominant indexing algorithm in production vector databases. Understanding how it works explains both why vector search is fast and why it is approximate.

HNSW builds a multi-layer graph of vectors. At the top layer, there are very few nodes — a sparse sampling of the full vector set. Each layer below adds more nodes, with the bottom layer containing all vectors. Each node at a given layer is connected to its nearest neighbors at that layer via directed edges. The graph is built during index construction by inserting vectors one at a time and greedily connecting each new vector to its neighbors.

Search proceeds top-down. The algorithm starts at a fixed entry point in the top layer, navigates greedily toward the query vector by following edges to neighboring nodes that reduce the distance to the query, then drops to the next layer and repeats. At the bottom layer, the algorithm performs a local beam search to find the nearest neighbors within a bounded exploration. The result is a set of approximate nearest neighbors found through hierarchical navigation rather than exhaustive comparison.

This hierarchical approach achieves O(log n) search time — logarithmic in the number of indexed vectors — compared to O(n) for a brute-force exact search. At one million vectors, this means a 60,000x speed improvement over exact search, at the cost of returning approximate rather than guaranteed exact results. The approximation quality is controlled by the ef parameter (exploration factor during search): higher ef examines more candidates, improving recall at the cost of speed. The right ef setting is a tradeoff calibrated to the application's requirements.

Index construction is expensive. Building an HNSW index over millions of vectors takes significant time and memory — often hours for large corpora. The entire index is typically held in RAM for fast access, which means memory requirements scale with the number of indexed vectors and their dimensionality. A corpus of one million 1536-dimensional float32 vectors requires approximately 6GB of RAM just for the raw vectors, plus additional overhead for the HNSW graph structure. Memory planning is a non-trivial operational concern for large-scale vector database deployments.

When Vector Search Is the Right Tool

Vector search is the appropriate choice for a narrow but important category of data problems: those that require finding items that are semantically similar to a query, where "semantically similar" means similar in meaning or concept rather than identical in structure or value.

Semantic search over unstructured documents is the archetypal use case and the one that drove vector database adoption in enterprise AI. A user asks a question in natural language; the system retrieves the document chunks most semantically relevant to that question. The query and the documents do not share exact keywords — the relevance is conceptual. This is precisely the capability that embedding-based vector search provides and that keyword search or SQL cannot replicate.

Similarity search — "find products similar to this product," "find customers similar to this customer profile" — is a common recommendation system and analytics use case. The items being compared are represented as embedding vectors, and the vector store finds items whose embeddings are close to the query item's embedding in the learned feature space. This captures learned similarity relationships that are not expressible as SQL predicates.

Anomaly detection in high-dimensional feature spaces uses vector search to identify items that are far from all their neighbors — outliers in the embedding space. Transaction fraud detection, network intrusion detection, and manufacturing defect detection are domains where this approach is valuable.

Recommendation systems use vector representations of user behavior, item attributes, or both, and retrieve items whose vectors are closest to the user's current interest vector. The efficiency of ANN search makes this tractable at the real-time latency requirements of recommendation systems.

When SQL Wins

The majority of enterprise data problems do not require semantic similarity search. For those problems, relational databases are faster, cheaper, more reliable, and better supported than vector databases. The discipline required here is resisting the temptation to reach for the newest tool when the established one is better suited.

Exact lookups are the simplest case. Querying a user record by ID, retrieving an order by order number, finding all transactions for a specific account — these queries require exact value matching, not similarity search. A B-tree index in PostgreSQL performs this operation in O(log n) time with deterministic, exact results. A vector similarity search would be nonsensical for these use cases — there is no embedding space in which "user ID = 12345" is a similarity problem.

Transactional workloads require ACID guarantees that most vector databases do not provide. If two operations must succeed or fail together — debiting one account and crediting another, creating a record and its associated audit log entry — you need a database that supports transactions with rollback semantics. Most vector databases offer eventual consistency rather than strong consistency, which makes them unsuitable for transactional workflows.

Structured queries with multiple exact filters are a SQL strength. "Find all active customers in Germany with accounts opened before 2024 who have made a purchase in the last 30 days" is a multi-predicate filter query that SQL handles with high efficiency through index combinations. A vector database would need to retrieve results by similarity and then apply post-hoc filtering, which is slower and less reliable than SQL's query optimizer.

Compliance and audit requirements frequently mandate exact, reproducible query results with a full audit trail. A query to a vector database may return different results on different runs if the approximate search produces different results due to index structure changes or parameter variations. Regulatory contexts that require exact reproducibility of data access need SQL's deterministic behavior.

Cost is a practical consideration that often determines the right choice in enterprise settings. SQL databases are well-understood, widely operated, and have decades of operational tooling. Vector databases are newer, require more specialized operational expertise, and their managed service pricing can be substantially higher per query than equivalent SQL operations. For high-volume, low-complexity queries, the cost difference is significant.

"A vector database is a specific tool for a specific problem: finding semantically similar items quickly in high-dimensional space. Using it where SQL fits better is like using a specialized index on a full-text search problem — you pay the complexity cost without capturing the benefit."

The Hybrid Architecture

The architecture that most mature production RAG systems converge on is not a pure vector database — it is a hybrid that uses vector search for semantic retrieval and SQL for filtering, metadata management, and transactional operations. This hybrid approach is more complex to design than either approach alone, but it captures the strengths of both without the weaknesses of either applied inappropriately.

The pattern works as follows. The vector store performs the initial retrieval step: given a query embedding, retrieve the top-k candidate document chunks by semantic similarity. The returned candidates include metadata fields: document source, date, department, access permission level, version number. A SQL query then filters the candidate set by metadata predicates: retain only documents from departments the requesting user has access to, published after a specific date, marked as the current version. The filtered candidate set is assembled into the context for the LLM.

This hybrid pattern solves one of the persistent practical problems in enterprise RAG: access control. A pure vector search returns the semantically most similar documents regardless of who is asking. Enterprise document corpora typically have per-document or per-department access controls. The SQL filtering layer applies those access controls as a post-retrieval filter, ensuring that the LLM only receives context that the requesting user is authorized to see. Attempting to encode access control into the vector space directly — by generating different embeddings for different permission contexts — is technically intractable and architecturally fragile.

pgvector, the PostgreSQL extension for vector similarity search, enables the hybrid architecture within a single database system. By adding a vector column to a PostgreSQL table, teams can perform combined queries that execute vector similarity search and SQL filtering in a single operation, benefiting from the query planner's ability to optimize the combination. pgvector's performance scales to tens of millions of vectors in production deployments, which covers the majority of enterprise RAG corpus sizes. At extreme scale — hundreds of millions to billions of vectors — a dedicated vector database may be necessary, but reaching that scale typically requires substantial enterprise growth before it becomes a binding constraint.

The Five Vector Database Options: Architectural Tradeoffs

Pinecone is a fully managed, serverless vector database. Setup takes minutes; no infrastructure to operate. The tradeoff is cost — Pinecone's per-query pricing is higher than self-hosted alternatives at scale — and vendor dependency. There is no self-hosting option. Pinecone suits teams that want to move fast without operational investment and whose query volumes are moderate enough that the managed service premium is acceptable.

Weaviate is open source and supports hybrid search natively, combining vector similarity and BM25 keyword search in a single query. This hybrid search capability makes Weaviate particularly well-suited for enterprise corpora where exact keyword matching matters alongside semantic similarity — technical documentation with specific product identifiers, legal documents with defined terms, financial records with standard codes. The tradeoff is operational complexity: running Weaviate in production requires Kubernetes expertise, monitoring configuration, and ongoing maintenance.

Qdrant is open source, written in Rust for performance, and offers strong support for filtered vector search with complex metadata predicates. Its filtering implementation is among the most efficient of the standalone vector databases, making it well-suited for the hybrid retrieval pattern where SQL-style filters are applied in combination with vector search. The ecosystem is newer and smaller than Weaviate's, which means less community tooling and fewer production case studies to reference.

pgvector is a PostgreSQL extension that adds a vector data type and similarity search operators to an existing PostgreSQL instance. Its primary architectural advantage is eliminating the separate vector database: RAG systems that use pgvector can store document metadata and vector embeddings in the same PostgreSQL database, enabling the hybrid query pattern within a single system with full ACID guarantees. Its limitation is performance at extreme scale: pgvector's HNSW implementation is slower than dedicated vector databases for very large indexes (hundreds of millions of vectors). For most enterprise RAG deployments, pgvector's performance is entirely adequate.

Chroma is lightweight, developer-friendly, and designed for rapid prototyping. It is the easiest vector database to get started with and the most popular choice for development environments. It is not production-hardened for large-scale deployments: it lacks the operational tooling, performance benchmarks at scale, and enterprise support contracts that production systems require. Chroma is an appropriate choice for evaluation and development; it should not be the choice for production systems expecting millions of vectors and high query volumes.

Operational Considerations

Vector databases introduce operational concerns that are distinct from relational databases and that enterprise teams often underestimate during initial deployment planning.

Index rebuild time is a significant operational constraint. When you switch embedding models — to a more capable model, to a domain-adapted model, or because your current model is deprecated — you must re-embed every document in your corpus and rebuild the HNSW index from scratch. For a corpus of 500,000 documents, this operation can take 4–8 hours of embedding generation time (at typical embedding API throughput) plus additional time for index construction. Planning for embedding model lifecycle management means planning for periodic re-indexing windows.

Memory requirements are primarily determined by the number of vectors and their dimensionality. Text embeddings at 1536 dimensions for one million chunks require approximately 6GB for the raw vectors, plus 1.5–3x overhead for the HNSW graph structure, totaling 9–18GB of RAM for the index alone. Production vector database servers need to be sized for the index to fit entirely in memory. Disk-based vector search is significantly slower and not appropriate for interactive use cases with latency requirements below one second.

Consistency guarantees differ from relational databases. Most vector databases offer eventual consistency: when you add a new document, there is a brief window during which the document may not be returned by search. For most RAG applications, this is acceptable. For applications where a document must be immediately searchable after it is added — a regulatory filing system where users need to search newly submitted documents instantly — eventual consistency may not meet requirements, and alternative approaches (direct retrieval of known recent documents, or a relational database as the canonical store with vector search as a secondary index) should be considered.

Four Questions to Ask Your CTO

1. Are we using a vector database for a problem that requires semantic similarity search? If the core query pattern is exact filtering by structured attributes, a relational database is the appropriate tool and a vector database adds unnecessary complexity and cost.

2. What is our embedding model lifecycle plan? When the embedding model needs to change, what is the re-indexing process, how long will it take, and how will search quality be maintained during the transition? This question should have a defined answer before the initial deployment, not after the first model deprecation notice.

3. Have we evaluated pgvector as an alternative to a standalone vector database? For most enterprise RAG deployments, pgvector provides adequate performance with substantially lower operational complexity. If the team is already operating PostgreSQL, pgvector is often the right choice unless scale requirements clearly exceed its capabilities.

4. How are access controls applied to retrieved documents? The answer should describe where in the retrieval pipeline access control is enforced. "The vector search returns all similar documents and we filter after" is one approach — it should be articulated explicitly, not assumed. The filtering mechanism should be verified against the organization's access control requirements, not taken on trust that "the system handles it."

Vector databases are powerful and genuinely important tools for enterprise AI architecture. Their importance is specific: they solve the approximate nearest neighbor search problem efficiently at scale. That specificity is also their limitation. Treating them as general-purpose data infrastructure leads to unnecessary complexity, higher operational cost, and capability gaps in workloads where relational databases excel. The teams that use vector databases most effectively are those that deploy them precisely for the problems they solve well, and continue using relational databases for everything else.