Quick answer
A vector database stores embeddings and runs approximate nearest-neighbour search over them so an AI agent can retrieve text by meaning rather than keyword. Most B2B teams do not need a dedicated one. Postgres with pgvector handles low millions of vectors comfortably. Move to a dedicated store when filtering complexity, query concurrency or tenant isolation break Postgres, not when the corpus merely grows.
Every article on this topic follows the same arc. Three hundred words explaining what an embedding is, a diagram of points in space, then a recommendation for whichever vector database paid for the placement. None of them tell you the thing that actually costs money, which is that you are about to add a second stateful system to a stack that already has a database, and you will own its backups, its version upgrades, its network policy and its 3am pager.
This post is the decision, not the tutorial. It covers the concrete signals that justify a dedicated store, a comparison of the real options with verified pricing shapes, working pgvector SQL with a tenant filter applied inside the search, and the migration pattern for the day you change embedding model and invalidate every vector you have stored.
The short version: retrieval quality problems are almost never database problems, and teams who swap vendors to fix bad answers usually get the same bad answers on a larger bill.
A vector database indexes high-dimensional float arrays and answers the question “which stored vectors are closest to this query vector” without comparing against every row. That is the whole product. Everything else, the SDKs, the ingestion pipelines, the managed embedding endpoints, is packaging around approximate nearest-neighbour search.
The word doing the work is approximate. Exact nearest-neighbour search over a million 1,536-dimension vectors is a full scan and a million dot products. An ANN index avoids that by building a structure that skips most of the corpus, and it pays for the speed in recall: some genuinely close vectors will not be returned. Every ANN system exposes a knob that moves along this curve, and every knob has a default that is tuned for demos rather than for your workload.
In pgvector that knob is hnsw.ef_search, which defaults to 40, or ivfflat.probes, which defaults to 1. In Elasticsearch and MongoDB Atlas it is num_candidates and numCandidates. MongoDB’s documentation recommends setting numCandidates to at least 20 times the number of documents you want back to reach roughly 90 to 95 percent recall, which is a usefully honest statement of how far the defaults sit from exact results.
Recall is the number nobody measures. If your agent returns a confidently wrong answer, the cause is frequently that the right chunk existed, was indexed, and simply was not in the top ten because the index skipped it.
You need a dedicated vector database when filtering complexity, query concurrency or tenant isolation breaks Postgres, and almost never because the corpus got big. Corpus size is the signal everyone reaches for because it is the easiest to measure, and it is the weakest predictor of whether pgvector will hold.
Work through the signals below honestly. If you are not hitting two or more of the right-hand column, adding a second datastore buys you operational surface area and nothing else.
| Signal | Stay on Postgres with pgvector | Start evaluating a dedicated store |
|---|---|---|
| Vector count | Thousands to low millions of rows in one table | Tens of millions and growing, or you are sharding the vector table for reasons unrelated to your relational data |
| Query concurrency | Retrieval traffic is bursty and shares headroom with your OLTP workload | Sustained retrieval QPS competes with transactional queries for the same buffer cache and connection pool |
| Metadata filtering | Filters are selective and expressible as ordinary WHERE clauses on indexed columns | Filters are low-selectivity across a very large corpus, so the planner keeps choosing between a bad scan and a bad index path |
| Multi-tenancy | A tenant_id column plus row-level security or a partitioned table is sufficient | You need per-tenant index isolation, per-tenant resource limits, or tenants numerous enough that one shared graph is the wrong shape |
| Hybrid search | Postgres full-text search fused with vector distance in one query covers your relevance needs | You need first-class sparse and dense fusion, learned sparse vectors, or built-in reranking as a product feature |
| Update pattern | Incremental inserts and updates, index maintained in place | Daily bulk re-ingestion where index build time is the bottleneck in your pipeline |
Below that threshold, this is solved in Postgres. Your vectors sit in the same transaction as the row they describe, your backups already cover them, your access control already applies, and you have one system to upgrade instead of two. That is not a compromise. For a retrieval layer feeding an internal agent over a few hundred thousand document chunks, it is the correct architecture, and the same build-versus-buy logic that governs choosing custom software over another SaaS subscription applies here with extra force, because the thing you are buying is mostly an index.
The realistic shortlist is pgvector, three dedicated stores, one open-source engine with a managed arm, and vector search inside a database you probably already run. Pricing models differ enough that headline numbers are not comparable, so treat the figures below as shape rather than as a quote.
| System | Deployment | Pricing model | Filtering | Hybrid search | Ops burden | Best fit |
|---|---|---|---|---|---|---|
| pgvector on Postgres | Extension on any Postgres you already run, self-hosted or managed | Free extension. You pay only for the Postgres instance | Full SQL WHERE, joins, row-level security, partial indexes | Build it yourself by fusing tsvector ranking with vector distance | Lowest, if you already run Postgres. Zero new systems | Almost every B2B retrieval workload under tens of millions of vectors |
| Qdrant | Open source self-hosted, managed cloud, hybrid and private cloud | Free forever tier at 0.5 vCPU, 1 GB RAM, 4 GB disk. Paid tiers billed hourly on vCPU, memory and storage | Rich payload filtering integrated with the search | Sparse plus dense fusion supported | Moderate self-hosted, low on managed | Teams that want a dedicated store without a proprietary lock-in |
| Weaviate | Open source self-hosted or Weaviate Cloud | Free sandbox at 100,000 objects, 1 GB memory, 10 GB disk. Flex from 45 USD per month, priced from 0.00465 USD per 1M dimensions and from 0.12 USD per GiB. Premium from 400 USD per month | Filtered vector search with a typed schema | Native hybrid search on all tiers | Moderate. Schema and module configuration are real work | Teams that want schema-driven search plus built-in vectorizer modules |
| Pinecone | Managed only. No self-hosted option | Starter free at 2 GB storage, 2M write units and 1M read units per month. Builder at 20 USD per month, Standard from 50 USD per month, Enterprise from 500 USD per month | Metadata filtering with namespaces for isolation | Dense, sparse and full-text indexes across plans | Lowest of the dedicated stores. Nothing to operate | Teams with no platform engineering capacity who accept a managed-only dependency |
| Milvus / Zilliz Cloud | Open source Milvus self-hosted, or managed Zilliz Cloud including BYOC | Milvus is free and open source. Zilliz free tier at 5 GB storage and 5 collections. Dedicated clusters from 126 USD per GB per month | Scalar filtering with partition keys | Sparse and dense retrieval supported | Highest self-hosted. Milvus is a distributed system with several components | Genuine billion-scale corpora with a platform team behind them |
| Elasticsearch | Self-hosted or Elastic Cloud | Cluster-based, priced on the nodes you run | Filters applied during approximate kNN, not after | Native. Combine kNN with BM25 query clauses and score fusion | Moderate to high, but zero incremental if you already run it for logs | Teams already operating Elastic who need lexical and vector relevance together |
| MongoDB Atlas Vector Search | Managed Atlas only | Included in the Atlas cluster cost | Pre-filter applied before the HNSW traversal | Combine with Atlas Search for lexical scoring | Zero incremental if your documents already live in Atlas | Teams whose source of truth is already MongoDB |
Notice the last two rows. If your operational data already lives in Atlas or you already run Elasticsearch for log aggregation, the marginal cost of vector search is close to zero, and both apply filters inside the search rather than after it, which is the property that matters most in B2B.
HNSW gives better recall at a given latency and handles incremental updates gracefully, while IVFFlat builds far faster and uses less memory but needs representative data present before you build it. pgvector 0.8.5 supports both and nothing else, which is a feature rather than a limitation.
| Property | HNSW | IVFFlat |
|---|---|---|
| Structure | Multi-layer proximity graph | Inverted lists over k-means centroids |
| Build time | Slow. Grows with m and ef_construction | Fast |
| Memory footprint | Higher. Graph edges live alongside the vectors | Lower |
| Recall at equal latency | Better | Worse |
| Update behaviour | Inserts are absorbed into the graph, no rebuild needed | Centroids drift as data changes. Requires rebuild to stay accurate |
| Cold start | Can be created on an empty table | Needs data loaded first, or the centroids are meaningless |
| Query knob | hnsw.ef_search, default 40 | ivfflat.probes, default 1 |
| Availability | pgvector, Qdrant, Weaviate, Milvus, Elasticsearch, MongoDB Atlas | pgvector, Milvus and other engines with IVF families |
The update row is the one that decides it for most teams. If your corpus changes daily, IVFFlat quietly degrades: the centroids were fitted to yesterday’s distribution and today’s inserts land in lists they do not belong in. Recall falls, nobody gets an error, and the only symptom is that answers get slightly worse over weeks. HNSW absorbs inserts without a rebuild, which is why it is the sane default for anything with a live ingestion pipeline.
Use IVFFlat when the corpus is static, when index build time is genuinely blocking a nightly job, or when memory is the binding constraint. pgvector also offers halfvec at two bytes per dimension instead of four, and binary_quantize for a bit-based index, both of which cut the footprint before you consider trading down the index type.
Pro tip
Build the HNSW index after bulk loading, not before. Creating it on an empty table and then inserting a million rows is dramatically slower than loading first and building once. Raise maintenance_work_mem for the build, then put it back.
Switching vector databases almost never fixes bad retrieval, because the thing producing bad retrieval is upstream of the index. The index returns the nearest vectors to your query vector. If the nearest vector is the wrong passage, that is a chunking, metadata or query-construction failure, and it will follow you to the new vendor unchanged.
Chunk on structure before you chunk on length. Split at headings, list boundaries and table boundaries, then only fall back to a token window when a section is too long. A chunk that spans the end of one topic and the start of another produces an embedding that sits between both and is nearest to neither query.
For prose documentation, chunks in the region of 300 to 800 tokens with 10 to 15 percent overlap is a reasonable starting band. Overlap exists to stop a sentence that answers the question from being cut in half, not to pad the corpus, and it costs you storage and embedding tokens linearly. Anything above about 20 percent overlap is usually a sign the split points are wrong.
Attach at ingestion time everything the agent will need to filter on or cite, because retrofitting it means re-ingesting. At minimum: source identifier, canonical URL or document path, heading path within the document, chunk index and total chunks, effective date, audience or permission tags, and the embedding model name and dimension. The heading path matters more than people expect: prepending it to the chunk text before embedding gives short chunks the context they otherwise lack, and gives the agent something specific to cite.
This is an ETL problem wearing an AI hat. The same discipline you would apply to an ETL pipeline that has to be idempotent and re-runnable applies to an ingestion pipeline, including deduplication on a stable source key so a re-crawl does not double your corpus.
If your documents have different audiences, the permission filter has to be applied inside the search at query time, and applying it after the search is both a security bug and a relevance bug. This is the single most common serious defect in B2B retrieval systems.
The security half is obvious once stated. If you retrieve the top 20 chunks and then drop the ones the user is not entitled to see, those chunks were already in your process, already in your logs, and one refactor away from being in the prompt. Any bug in the post-filter is a cross-tenant disclosure, not a display glitch.
The relevance half is subtler and hits you every single query. Your top-k budget is fixed by the context window and the latency you will tolerate. If you ask for 20 and discard 14 as unauthorised, the model gets 6 chunks, not 20, and there is no mechanism that goes back for more. A tenant whose documents are a small fraction of the corpus systematically receives fewer relevant chunks than a large tenant, and the failure is invisible in aggregate metrics.
Every serious engine handles this properly. Elasticsearch applies the filter during approximate kNN so that k matching documents are returned, and falls back to brute force per segment when the filtered set is smaller than num_candidates. MongoDB Atlas pre-filters before the HNSW traversal. pgvector combines a WHERE clause with the ORDER BY distance, and since 0.8 offers iterative index scans so a highly selective filter does not silently return fewer rows than your LIMIT.
Watch out
A filter inside the query is not automatically a pre-filter. Some client libraries accept a filter argument and apply it after retrieval. Read the engine’s documentation for that specific parameter, then verify with a query whose filter matches roughly one percent of the corpus and check you still get a full LIMIT of results back.
A working pgvector setup is one extension, one table with its metadata columns, an HNSW index and a query that filters and ranks in a single statement. Here is the schema, with the permission and model-provenance columns that people usually add too late.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE doc_chunk (
id bigserial PRIMARY KEY,
tenant_id uuid NOT NULL,
source_id text NOT NULL,
source_url text,
heading_path text,
chunk_index int NOT NULL,
content text NOT NULL,
audience text[] NOT NULL DEFAULT '{}',
embedding_model text NOT NULL,
embedding_dim int NOT NULL,
embedding vector(1536),
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (tenant_id, source_id, chunk_index, embedding_model)
);
-- Build the index AFTER the bulk load, not before.
CREATE INDEX doc_chunk_embedding_hnsw
ON doc_chunk USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
CREATE INDEX doc_chunk_tenant_idx ON doc_chunk (tenant_id);
CREATE INDEX doc_chunk_audience_idx ON doc_chunk USING gin (audience); The unique constraint on tenant, source, chunk index and model is what makes re-ingestion idempotent. Re-crawl the same document and you upsert rather than duplicate. Including the model in the key is what lets two embedding generations coexist during a migration.
The query applies the tenant and audience filters and the distance ordering together, so the filter is part of the search rather than a step after it. The <=> operator is cosine distance, so subtracting from one gives cosine similarity for a relevance threshold.
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = relaxed_order;
SELECT
id,
source_url,
heading_path,
content,
1 - (embedding <=> $1::vector) AS cosine_similarity
FROM doc_chunk
WHERE tenant_id = $2::uuid
AND audience && $3::text[]
AND embedding_model = 'text-embedding-3-small'
ORDER BY embedding <=> $1::vector
LIMIT 10;
COMMIT; Three details worth keeping. ef_search at 100 rather than the default 40 buys recall for latency, and is the first thing to tune when answers are subtly wrong. iterative_scan set to relaxed_order tells pgvector to keep scanning the index when the filter removes most candidates, which is what stops a selective tenant filter from returning three rows instead of ten. And SET LOCAL requires a transaction, so if your client runs statements in autocommit, use plain SET on a dedicated connection instead.
For strict isolation, add row-level security keyed on tenant_id so the filter is enforced by the database rather than by whichever code path happens to build the query. If you are already syncing operational records into Postgres from your CRM, the retrieval table joins to those records directly, which is a capability no external vector store gives you.
Changing embedding model invalidates every vector you have stored, with no error and no warning. Vectors from two different models are not comparable, and if you mix them the distances are arithmetic noise: queries still return ten rows, they are just the wrong ten. This is why embedding_model and embedding_dim belong in the table from day one, not added during the incident.
The migration pattern is a shadow index with dual writes:
Budget the re-embedding before you start. Worked example, assumptions stated: 500,000 chunks averaging 500 tokens each is 250 million tokens for a full corpus pass. Multiply that by your provider’s current per-million-token embedding rate to get the one-off cost, then double it, because dual writes mean you pay for the overlap period and almost everyone runs one failed backfill before the one that finishes. The token count is the number to check against your rate limits too: 250 million tokens through a per-minute quota is a scheduling problem, not a script.
Retrieval fails quietly, which is what makes it dangerous: a broken HTTP integration pages you, a broken index just returns worse answers. These are the ones that recur, and they map closely onto the wider catalogue of automation failure modes that only show up under production load.
| Failure mode | What it looks like | Fix |
|---|---|---|
| Unfiltered top-k across tenants | An agent cites a plausible, well-written passage that belongs to a different customer | Filter inside the search on tenant_id, enforce with row-level security, and add a test that asserts zero cross-tenant rows for a known query |
| Index never rebuilt after bulk ingestion | Latency is fine, recall quietly collapses. Common with IVFFlat after the distribution shifts | Rebuild IVFFlat on a schedule tied to ingestion volume, or move to HNSW and stop thinking about it |
| Re-embedding cost surprise | A model upgrade turns into an unbudgeted invoice and a rate-limit incident mid-backfill | Count tokens before you start, batch with a resumable cursor, and schedule the backfill against your quota |
| No evaluation set | Retrieval degrades over months and the first report comes from a customer, not a dashboard | Keep 50 to 100 real questions with known-correct source chunks. Score recall at k on every ingestion change and every model change |
| Vector store treated as system of record | A reindex loses documents because the only copy of the chunk text was in the vector store | Keep source documents in durable storage. The vector index is derived data and must be rebuildable from scratch |
| Mixed embedding generations | Results are erratic for a subset of documents after a partial migration | Record model and dimension per row, filter on the model in every query, never let a query span two generations |
The evaluation set row is the one to act on this week. Without it you have no way to tell whether a change helped, which means every subsequent decision, including the decision to switch databases, is guesswork. Fifty questions in a CSV with the expected source document is enough to start.
If you already run Postgres and your corpus is under a few million chunks, use pgvector and do not add a vector database. That covers internal knowledge agents, support deflection over a documentation set, contract and policy search, and most of what a RAG-backed agent built on n8n and LangChain actually needs. Spend the time you saved on chunking and on an evaluation set.
If your source of truth is already MongoDB Atlas, or you already operate Elasticsearch, use their vector search. Both pre-filter properly, both are already in your backup and access-control story, and the incremental operational cost is close to nothing. Adding a third system to a stack that has two working options is not architecture, it is shopping.
If you have tens of millions of vectors, sustained retrieval concurrency competing with transactional load, or per-tenant isolation requirements that a shared index cannot express, pick a dedicated store. Qdrant if you want an open-source core with a managed option and no lock-in. Weaviate if schema-driven search and native hybrid retrieval are load-bearing. Pinecone if you have no platform capacity and accept a managed-only dependency. Milvus if you are genuinely at billion scale and have the team to run a distributed system.
And if your retrieval is returning wrong answers today, do not shop. Fix the chunk boundaries, attach the metadata you are missing, move the permission filter inside the query, and build the evaluation set. A different vector database will return the same wrong chunks faster.
For most B2B workloads, yes. With an HNSW index and hnsw.ef_search raised above the default of 40, pgvector answers filtered similarity queries on low millions of rows within the latency budget of an LLM call, which is already measured in seconds. The binding constraint is usually sustained query concurrency competing with your transactional workload, not raw search speed.
There is no clean number, which is why corpus size is the wrong signal. Teams run tens of millions of rows on pgvector successfully with enough memory and a well-tuned HNSW index. The practical trigger is a combination of factors: low-selectivity filters over a very large corpus, sustained retrieval concurrency, or per-tenant index isolation that a single shared graph cannot express.
Use HNSW unless you have a specific reason not to. It gives better recall at a given latency and absorbs inserts without a rebuild, which matters when your corpus changes daily. IVFFlat builds much faster and uses less memory, but its centroids drift as data changes, so recall degrades silently until you rebuild the index.
Yes. Vectors produced by two different models are not comparable, and mixing them returns results that look normal but are effectively random. Run a shadow migration: store the new model as a second set of rows, dual-write during the transition, backfill the history in resumable batches, compare top-k overlap against an evaluation set, then cut over and drop the old rows.
Split on document structure first, at headings, list boundaries and table boundaries, and only fall back to a token window when a section is too long. For prose documentation, 300 to 800 tokens with 10 to 15 percent overlap is a reasonable starting band. Prepend the heading path to the chunk text before embedding so short chunks carry their context.
It fails twice. Retrieving unauthorised chunks and discarding them afterwards means that content entered your process and your logs, so any bug in the filter becomes a disclosure. It also wastes your top-k budget: ask for twenty and discard fourteen, and the model receives six chunks with no mechanism to fetch more. Filter inside the search instead.
Yes. Postgres full-text search gives you a lexical score from tsvector and ts_rank, which you can fuse with cosine distance from pgvector in a single query using reciprocal rank fusion or a weighted sum. It takes more SQL than a vendor flag. Elasticsearch and Weaviate offer hybrid retrieval natively if it is central to your product.
No. Treat the vector index as derived data that must be rebuildable from scratch. Keep source documents and chunk text in durable storage such as object storage or your relational database. Teams that store the only copy of chunk text inside the vector store discover the problem during their first reindex, when documents quietly disappear.
Regex, masking loops, and data scrubbers implemented at the preprocessing edge.
A practical Triumphoid guide to make webhooks vs wordpress rest api: my draft creation workflow,…
Database-level orchestration steps to cache systemic instructions and context arrays natively in your pipelines.
Google Search Console is the best free rank tracker and most lists omit it. What…
A practical Triumphoid guide to wordpress rest api vs wp admin: where automation saves me…
A practical comparison of Elementor and WPBakery for editing experience, templates, performance, theme compatibility, and…