Choosing a Vector Database: pgvector vs Dedicated Stores

Teams already running Postgres with corpora in the millions of vectors should start with the pgvector extension, which keeps embeddings in the same row and transaction as source data and provides HNSW and IVFFlat approximate indexes, according to a comparison of pgvector against dedicated vector stores. The comparison advises migrating to a dedicated store only when a measurable problem appears — the index no longer fits in memory, filtered queries miss acceptable recall, vector load degrades transactional latency, or the corpus approaches hundreds of millions of vectors. It also notes Amazon S3 Vectors reached general availability in December 2025 with support for up to 2 billion vectors per index and roughly 100 ms latency for frequent queries.

The real question isn't which vector database is best. It's where your vectors should live: next to the rest of your data in Postgres, or in a system built only for similarity search. Many teams already run Postgres, so the decision often comes down to this: is pgvector enough, or have you hit a specific wall that a dedicated store would fix? This post compares the two on the things that actually decide it: scale, filtering, hybrid search, operations, and cost. It ends with a decision flow and a short benchmark plan so you can settle the question with your own data instead of a vendor chart. The short version - If you already run Postgres and your corpus is in the millions of vectors, start with pgvector. You get one system, SQL filtering, and transactional consistency with your source data. - Move to a dedicated store when you can measure a problem: the index no longer fits in memory, filtered queries can't reach acceptable recall, vector load hurts your transactional latency, or the corpus is heading into the hundreds of millions. - "Everyone else uses a vector database" is not a reason. Each extra system is another thing to sync, secure, monitor, and pay for. What you're choosing between pgvector is a Postgres extension. It adds the vector , halfvec , and sparsevec column types, distance operators which also work on Postgres's built-in bit type for binary vectors , and two approximate indexes: HNSW and IVFFlat. With no index, it does exact nearest-neighbor search, which gives perfect recall but scans every row. Postgres-side extensions stretch pgvector further. pgvectorscale adds a StreamingDiskANN index aimed at workloads where the HNSW index no longer fits in RAM. VectorChord takes an IVF-based approach with quantization, aimed at very large datasets. Some managed services add their own indexes too, such as AlloyDB's ScaNN-based option. Treat vendor benchmarks for all of these with caution and run your own. Dedicated vector databases are built around search. Pinecone is a managed service. Its bring-your-own-cloud option, in public preview for Enterprise plans at the time of writing, keeps the data plane in your own cloud account, but Pinecone still operates it for you. Qdrant is open source written in Rust and can be self-hosted or rented. Weaviate is open source written in Go with a focus on hybrid search. Milvus is open source and designed for very large scale, with Zilliz Cloud as its managed version. Object-storage vector search is a newer category. Amazon S3 Vectors reached general availability in December 2025, supports up to 2 billion vectors per index, and AWS cites roughly 100 ms for frequent queries and sub-second for infrequent ones. It is built to minimize storage cost rather than deliver the lowest possible latency, and it is AWS-only. The architectural difference is where the vectors live and what keeps them in sync: flowchart LR subgraph SA "Option A: pgvector" App1 "Application" -- PG1 "Postgres<br/ rows + embeddings<br/ + HNSW index" end subgraph SB "Option B: dedicated store" App2 "Application" -- PG2 "Postgres<br/ source of truth" App2 -- VS "Vector store<br/ embeddings + metadata" PG2 -. "sync or dual write" .- VS end Where pgvector wins One system of record. Embeddings sit in the same row, and the same transaction, as the data they describe. Delete a document and its vectors disappear atomically. Backups, permissions, and row-level security all keep working. There is no pipeline that can drift out of sync. SQL for filters and joins. Real retrieval is rarely "nearest neighbors, full stop." It's "nearest neighbors among documents this user can see, published after March, in this tenant." In Postgres that is just a query, with one caveat about approximate indexes covered below: CREATE EXTENSION IF NOT EXISTS vector; -- Assumes a documents table with id and published columns CREATE TABLE chunks id bigserial PRIMARY KEY, tenant id bigint NOT NULL, doc id bigint NOT NULL REFERENCES documents id ON DELETE CASCADE, content text NOT NULL, embedding halfvec 1536 NOT NULL ; CREATE INDEX ON chunks USING hnsw embedding halfvec cosine ops ; SELECT c.id, c.content FROM chunks c JOIN documents d ON d.id = c.doc id WHERE c.tenant id = 42 AND d.published ORDER BY c.embedding <= $1::halfvec 1536 LIMIT 10; Less to operate. If you already run managed Postgres, you probably already have pgvector available. There is no new service to provision, secure, monitor, and put on call. Cheap to start. You can ship a first version in an afternoon and learn what your workload really looks like before committing to anything bigger. Where pgvector strains These are the walls that actually push teams off it. Each is measurable, so check for them before you migrate. Memory HNSW performs best when the index fits in RAM. The pgvector README documents the raw storage per value: 4 × dimensions + 8 bytes for a vector and 2 × dimensions + 8 bytes for a halfvec . For 1,536-dimension embeddings, that works out to: | Vectors | vector float32 | halfvec float16 | |---|---|---| | 1 million | 6.15 GB | 3.08 GB | | 10 million | 61.5 GB | 30.8 GB | | 100 million | 615 GB | 308 GB | These figures are raw vector data only. Row overhead and the HNSW index come on top, so measure your real index with pg relation size . If it won't fit, your options are to store halfvec about half the size, so check recall on your data , use binary quantization with re-ranking, try a disk-friendly index such as pgvectorscale's DiskANN, or move to a store designed for larger-than-memory search. Published guidance on how many vectors a single pgvector instance handles well ranges from roughly one million to tens of millions. The spread is real: dimensions, RAM, filters, recall targets, and concurrent load all move the number. Filtered search This is the most common surprise. An approximate index returns its nearest candidates first, and your WHERE clause is applied afterward . HNSW keeps hnsw.ef search candidates 40 by default . If a filter matches 10% of rows, about 4 of those 40 candidates survive, so you can get fewer rows than you asked for, silently. Raising ef search only helps so far, because it tops out at 1,000. pgvector 0.8.0 added iterative index scans , which keep scanning the index until enough rows pass the filter: SET hnsw.iterative scan = relaxed order; -- or strict order SET hnsw.max scan tuples = 20000; -- the default; where the scan gives up strict order guarantees results come back in exact distance order, while relaxed order can return them slightly out of order in exchange for better recall IVFFlat supports only the relaxed mode . A scan still stops at hnsw.max scan tuples or a memory cap, so very selective filters can still come up short. Other tools help too: a regular index on the filter column with exact search when the filter matches few rows, partial indexes, or one HNSW index per tenant through partitioning. HNSW indexes a single vector column, so you can't fold the filter column into the index. If you've tried all of that and recall under your real filters is still poor, that is a legitimate reason to evaluate a dedicated engine. Many build filtering into the search itself, but check each product's behavior with your own filters. Dimension limits A vector column can hold up to 16,000 dimensions, but HNSW and IVFFlat indexes stop at 2,000 for vector and 4,000 for halfvec . A 3,072-dimension model therefore needs halfvec to be indexed, and a 4,096-dimension model doesn't fit either limit without dimensionality reduction or quantization. Some embedding models can return shorter vectors on request, which is another way under the limit. The error appears at CREATE INDEX , after you've loaded the data, so check this early. Sharing a server with your transactional workload Index builds and vector queries are heavy on CPU and memory, and they compete with your OLTP traffic on the same instance. A read replica dedicated to vector queries often helps: physical streaming replication copies index structures too, so replicas can serve similarity searches. Replicas lag the primary slightly and need enough memory to hold the index, so size them for it. If vector load still hurts transactional latency, the ability to scale the two independently is a real benefit of splitting them. Hybrid search Search quality often improves when you combine vector similarity with keyword relevance. Postgres has full-text search, but its ranking is not BM25, so you typically run both queries and merge them yourself, for example with reciprocal rank fusion, or add an extension that provides BM25. It works, but it is more assembly than a store with hybrid search built in. Where dedicated stores win - Scale. Hundreds of millions to billions of vectors, with sharding and replication designed for it. Milvus and S3 Vectors target this range explicitly. - Isolation. Vector traffic can't slow down your transactional database, and you scale each independently. - Built-in search features. Depending on the product: native hybrid search, multiple quantization options, multi-tenancy, and tuning built around approximate search. - Zero-ops options. A fully managed service like Pinecone or S3 Vectors suits teams without database engineers. What the split costs you Putting vectors in a second system means every write now has two destinations, and the cost shows up in several places: - Sync and consistency. Updates and deletes must propagate, and you need a plan for failures, retries, and drift. Stale or orphaned vectors are a common bug. - A two-step query. The vector store returns IDs, then you fetch the real rows from Postgres. - Permissions. Row-level security in Postgres doesn't reach the vector store. Either replicate access-control metadata into it, or filter afterward and accept short result lists. - Pricing models. Several comparisons report that usage-priced managed services get expensive as the corpus grows. Price your own workload rather than trusting a headline number. sequenceDiagram participant App participant VS as Vector store participant PG as Postgres App- VS: search query embedding, filter, k=20 VS-- App: ids and scores App- PG: fetch rows for those ids, apply permissions PG-- App: rows the user may see Side by side | | pgvector | Dedicated store | |---|---|---| | Consistency with source data | Transactional, same database | Eventual, via sync or dual writes | | Filtering and joins | Full SQL, with the post-filtering caveat above | Product-specific metadata filters, usually no relational joins | | Practical scale | One node plus replicas; millions to tens of millions depending on hardware and tuning | Hundreds of millions to billions | | Hybrid search | Assemble it yourself or add an extension | Often built in | | Resource isolation | Shares the database | Independent scaling | | Operations | One system you likely already run | A second system, or a managed service | | Cost model | Instance size and storage | Usage-based or cluster-based | How to decide php flowchart TD A "Do you already run Postgres for your data?" A -- |No| B "Choose on scale and ops: a managed dedicated store, or managed Postgres with pgvector" A -- |Yes| C "Heading into hundreds of millions of vectors, or need to scale search independently?" C -- |Yes| D "Dedicated store" C -- |No| E "Does the index fit in memory with headroom?" E -- |No| F "Try halfvec or quantization, or pgvectorscale, or a dedicated store" E -- |Yes| G "pgvector with HNSW" G -- H "Do filtered queries hit your recall target after tuning?" H -- |Yes| I "Stay on pgvector" H -- |No| D Measure before you commit Whichever way you lean, test it on your data: 1. Collect a few thousand real queries, with the filters your app really uses. 2. Compute the exact top-k for each as a baseline. 3. Measure recall@k and p95 latency for each candidate at your target query rate. 4. Record index build time and write throughput, and test at two to three times your expected corpus size. 5. Price each option at that size. For the pgvector baseline, disable the index inside a transaction to force an exact scan: BEGIN; SET LOCAL enable indexscan = off; -- forces an exact scan for the baseline SELECT id FROM chunks WHERE tenant id = 42 -- use the same filters as your real queries ORDER BY embedding <= $1::halfvec 1536 LIMIT 10; COMMIT; Then compare those IDs with what the indexed query returns. Keep the migration cheap You may start on pgvector and move later, or the reverse. A few habits make either direction easier: - Keep Postgres as the source of truth. Treat any dedicated store as an index you can rebuild from it. - Store the embedding model name and version next to each vector. Switching models, which forces a re-embed, is often a bigger migration than switching databases. - Hide retrieval behind a thin interface so application code doesn't depend on one engine's query syntax. Wrapping up Start where your data already lives. For many teams that means pgvector: one system, real SQL, and plenty of headroom for a long time. Reach for a dedicated store when a measurement tells you to: memory pressure you can't engineer around, filtered recall you can't tune up, load that hurts your main database, or a corpus that has simply outgrown a single node. Until then, a second database is a cost you don't need to pay.