pgvector vs Dedicated Vector Database
On this page
Learning Objective
Evaluate the real tradeoffs between adding vector search to an existing operational database and adopting a dedicated vector store, including the transactional and operational advantages of colocation, the scale and feature points where a specialized system genuinely wins, and the cost of the dual-store consistency problem.
Concept Explanation
The honest starting position is that for a large share of applications, adding vector search to the database you already run is the right answer, and the burden of proof sits with the decision to add a second system. pgvector is a PostgreSQL extension providing a vector column type, the standard distance operators, and both IVFFlat and HNSW indexes, and it is available on the major managed Postgres offerings including Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, Azure Database for PostgreSQL, and platforms such as Supabase and Neon. That availability matters as much as the feature set, because it means vector search inherits an operational story - backups, point-in-time recovery, replication, failover, access control, monitoring, and a team that already knows the system - that a new component has to build from scratch.
The strongest technical argument for colocation is transactional consistency. In a single database, inserting a document, its chunks, its metadata, and its embeddings happens in one transaction that either fully commits or fully rolls back. Split those across two systems and you have a distributed write problem with no transaction spanning it: the row is committed but the vector write failed, or the vector is written but the row rolled back, or a delete succeeded in one store and not the other. Every team that runs a dual-store architecture eventually builds the same machinery - an outbox table, a reconciliation job, a repair script - and that machinery is real, ongoing engineering work that a colocated design simply does not need.
Colocation also makes filtering natural rather than architectural. A realistic query is almost never a pure nearest-neighbor search; it is nearest neighbors among documents this tenant owns, not archived, of a permitted document type, updated within some window. In Postgres those predicates are ordinary WHERE clauses over real, typed, indexed columns, evaluated by a planner that has statistics about them, and joins to the rest of your schema are free. In a split architecture, every filterable attribute must be duplicated into the vector store's metadata, kept in sync, and expressed in whatever filter dialect that store supports, and anything requiring a join has to be done in application code.
The case for a dedicated store is real and specific rather than dismissible. At very large scale, tens or hundreds of millions of vectors and up, purpose-built systems have invested heavily in memory efficiency, quantization schemes, distributed sharding of the index itself, and separation of storage from compute, and they will outperform a general-purpose database that was not designed around a memory-resident graph index. They typically ship advanced features earlier: sophisticated filtered search that pushes predicates into graph traversal, multiple named vectors per record, built-in hybrid search with fusion, binary and scalar quantization with automatic rescoring, and serverless consumption models. There is also a legitimate operational argument for isolation: a memory-hungry index rebuild sitting inside your primary transactional database competes for exactly the resources your checkout flow needs.
The practical decision procedure is therefore ordered rather than comparative. Start by computing the index memory footprint from the previous lessons. If it comfortably fits alongside your existing database workload and your corpus is in the low millions of vectors, use the extension and stop, because the consistency and operational savings are substantial and immediate. If the footprint would destabilize your primary database, the first question is whether a dedicated read replica or a separate Postgres instance for vectors solves it, which keeps the same technology and skills. Only when scale, index-level features, or genuinely independent scaling requirements make that inadequate does a dedicated vector database earn its second-system cost, and at that point the migration is mostly a matter of moving an index rather than rewriting an application, provided the retrieval layer was kept behind an interface from the start.
| Dimension | Vectors in Your Existing Database | Dedicated Vector Database |
|---|---|---|
| Write consistency | One transaction covers rows, metadata, and vectors | Dual write with no spanning transaction; needs an outbox and reconciliation |
| Filtering and joins | Ordinary WHERE clauses and joins over typed, indexed columns | Metadata must be duplicated and kept in sync; joins move to application code |
| Operational surface | Inherits existing backup, replication, failover, and access control | A second system to secure, monitor, back up, and upgrade |
| Scale ceiling | Comfortable into the millions; memory competes with OLTP workload | Designed for tens of millions and up with distributed index sharding |
| Index features | HNSW and IVFFlat, improving steadily | Quantization with rescoring, filtered graph traversal, multi-vector records, native hybrid |
| Resource isolation | Index builds compete with your transactional workload | Fully isolated, scales independently of the primary database |
Worked Example: The Dual-Store Consistency Problem, and the Interface That Defers the Choice
The first half shows the machinery a split architecture forces you to build. The second half is the more important lesson: keeping retrieval behind a narrow interface makes this a reversible decision rather than a foundational one.
# THE PROBLEM a split architecture creates and a colocated one does not:
def index_document(doc):
with postgres.transaction():
doc_id = postgres.insert_document(doc)
chunks = postgres.insert_chunks(doc_id, split(doc))
# <-- transaction has COMMITTED. The next call is outside it.
pinecone.upsert([(c.id, c.embedding, c.metadata) for c in chunks])
# If this line fails, times out, or partially succeeds, the two stores
# now disagree and nothing will ever notice on its own.
# THE MACHINERY every dual-store system ends up building:
# 1. outbox table written inside the same transaction as the rows
# 2. a worker draining the outbox into the vector store, with retries
# 3. idempotent upserts keyed by chunk id (the worker WILL replay)
# 4. a nightly reconciliation job comparing counts and checksums per tenant
# 5. a repair path for the drift it finds, because it will find drift
# THE COLOCATED VERSION - the same guarantee, none of the machinery:
# with postgres.transaction():
# doc_id = insert_document(doc)
# insert_chunks_with_embeddings(doc_id, split_and_embed(doc))
# AND THE INTERFACE that keeps this decision reversible either way:
class Retriever(Protocol):
def search(self, query_vec, filters: dict, top_k: int) -> list[Chunk]: ...
def upsert(self, chunks: list[Chunk]) -> None: ...
def delete(self, chunk_ids: list[str]) -> None: ...
# PgVectorRetriever today; QdrantRetriever when the memory math says so.
# Without this, "we outgrew pgvector" is a rewrite instead of a swap.Real-World Examples
- pgvector, an open-source PostgreSQL extension created by Andrew Kane, went from a side project to being offered on Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, Azure Database for PostgreSQL, Supabase, and Neon within roughly two years, and version 0.5.0 added HNSW indexing alongside the original IVFFlat. The speed of that adoption across every major managed Postgres provider is the clearest signal that vector search became a database feature rather than a separate product category.
- Elasticsearch and OpenSearch, both built on Lucene, now serve BM25 lexical scoring and HNSW vector search from the same engine with built-in result fusion, which lets teams that already operate a search cluster add semantic retrieval without introducing a second datastore or a synchronization path. That is the same colocation argument as pgvector, arriving from the search side rather than the relational side.
Review Questions & Answers
Because a document, its chunks, its metadata, and its embeddings are one logical unit, and in a single database they commit or roll back together with no additional code. Splitting them across two systems creates a distributed write with no transaction spanning it, so any failure between the two writes leaves the stores disagreeing, and nothing detects that on its own. Closing the gap requires an outbox table written inside the original transaction, a worker draining it with retries, idempotent upserts because the worker will replay, and a reconciliation job with a repair path. That is a meaningful and permanent amount of engineering that colocation simply removes.
Because the filter predicates must be evaluated where the vectors live, which means every attribute you might filter on has to be duplicated into the vector store as metadata and kept synchronized with the authoritative copy, adding another consistency surface. The filter language of the vector store is typically much less expressive than SQL, so anything involving a join, a subquery, or a computed predicate has to be restructured or moved into application code, which usually means fetching more candidates than needed and filtering them after the fact. In a colocated design those predicates are ordinary indexed columns evaluated by a planner that has real statistics about them.
A resident index footprint large enough that hosting it alongside the transactional workload would destabilize the primary database, and which a separate instance of the same technology does not adequately solve; a corpus in the tens or hundreds of millions of vectors where distributed sharding of the index itself is required; a genuine need for index-level features such as quantization with automatic rescoring, filtered graph traversal that pushes predicates into the search, or multiple named vectors per record; or a requirement to scale search capacity independently of database capacity. Absent at least one of those, the second system adds operational surface and a consistency problem without a corresponding benefit.
Because the information needed to make the decision correctly - actual corpus size, actual query patterns, actual memory pressure, actual filter requirements - does not exist at the time the decision is first made, and it only becomes clear once the system has real data and real traffic. An interface exposing just search, upsert, and delete means the eventual answer is implementing one class rather than touching every call site, and it also lets you run both implementations in parallel against the same eval set to measure whether the migration actually improves anything. Without it, the migration arrives as a large refactor at exactly the moment the current system is under strain.