--- title: "Choosing a Vector Database: pgvector vs Dedicated Stores" author: "Anshu Pathak" published: "2026-10-29T03:29:33.066Z" updated: "2026-10-29T03:29:33.066Z" url: "https://zyvop.com/choosing-a-vector-database-pgvector-vs-dedicated-stores-gr2u4" canonical: "https://zyvop.com/choosing-a-vector-database-pgvector-vs-dedicated-stores-gr2u4" tags: ["RAG","postgresql","pgvector","Embeddings","vector database"] --- # Choosing a Vector Database: pgvector vs Dedicated Stores When Postgres with pgvector is enough, when a dedicated vector database earns its keep, and how to decide using scale, filtering, and operations. ![Cover image]() 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: ```mermaid flowchart LR subgraph SA["Option A: pgvector"] App1["Application"] --> PG1[("Postgres
rows + embeddings
+ HNSW index")] end subgraph SB["Option B: dedicated store"] App2["Application"] --> PG2[("Postgres
source of truth")] App2 --> VS["Vector store
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: ```sql 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: ```sql 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. ```mermaid 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 ```mermaid 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: ```sql 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.