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.

What a vector database actually does

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.

Do you need a dedicated vector database, or is Postgres enough?

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.

SignalStay on Postgres with pgvectorStart evaluating a dedicated store
Vector countThousands to low millions of rows in one tableTens of millions and growing, or you are sharding the vector table for reasons unrelated to your relational data
Query concurrencyRetrieval traffic is bursty and shares headroom with your OLTP workloadSustained retrieval QPS competes with transactional queries for the same buffer cache and connection pool
Metadata filteringFilters are selective and expressible as ordinary WHERE clauses on indexed columnsFilters are low-selectivity across a very large corpus, so the planner keeps choosing between a bad scan and a bad index path
Multi-tenancyA tenant_id column plus row-level security or a partitioned table is sufficientYou need per-tenant index isolation, per-tenant resource limits, or tenants numerous enough that one shared graph is the wrong shape
Hybrid searchPostgres full-text search fused with vector distance in one query covers your relevance needsYou need first-class sparse and dense fusion, learned sparse vectors, or built-in reranking as a product feature
Update patternIncremental inserts and updates, index maintained in placeDaily bulk re-ingestion where index build time is the bottleneck in your pipeline
Decision signals. Two or more right-hand matches is the threshold worth acting on, not one.

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.

Vector database options compared

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.

SystemDeploymentPricing modelFilteringHybrid searchOps burdenBest fit
pgvector on PostgresExtension on any Postgres you already run, self-hosted or managedFree extension. You pay only for the Postgres instanceFull SQL WHERE, joins, row-level security, partial indexesBuild it yourself by fusing tsvector ranking with vector distanceLowest, if you already run Postgres. Zero new systemsAlmost every B2B retrieval workload under tens of millions of vectors
QdrantOpen source self-hosted, managed cloud, hybrid and private cloudFree forever tier at 0.5 vCPU, 1 GB RAM, 4 GB disk. Paid tiers billed hourly on vCPU, memory and storageRich payload filtering integrated with the searchSparse plus dense fusion supportedModerate self-hosted, low on managedTeams that want a dedicated store without a proprietary lock-in
WeaviateOpen source self-hosted or Weaviate CloudFree 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 monthFiltered vector search with a typed schemaNative hybrid search on all tiersModerate. Schema and module configuration are real workTeams that want schema-driven search plus built-in vectorizer modules
PineconeManaged only. No self-hosted optionStarter 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 monthMetadata filtering with namespaces for isolationDense, sparse and full-text indexes across plansLowest of the dedicated stores. Nothing to operateTeams with no platform engineering capacity who accept a managed-only dependency
Milvus / Zilliz CloudOpen source Milvus self-hosted, or managed Zilliz Cloud including BYOCMilvus is free and open source. Zilliz free tier at 5 GB storage and 5 collections. Dedicated clusters from 126 USD per GB per monthScalar filtering with partition keysSparse and dense retrieval supportedHighest self-hosted. Milvus is a distributed system with several componentsGenuine billion-scale corpora with a platform team behind them
ElasticsearchSelf-hosted or Elastic CloudCluster-based, priced on the nodes you runFilters applied during approximate kNN, not afterNative. Combine kNN with BM25 query clauses and score fusionModerate to high, but zero incremental if you already run it for logsTeams already operating Elastic who need lexical and vector relevance together
MongoDB Atlas Vector SearchManaged Atlas onlyIncluded in the Atlas cluster costPre-filter applied before the HNSW traversalCombine with Atlas Search for lexical scoringZero incremental if your documents already live in AtlasTeams whose source of truth is already MongoDB
Figures verified against vendor pricing pages in August 2026: Pinecone, Weaviate, Qdrant. Usage-based rates change; confirm before budgeting.

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 versus IVFFlat, and why rebuild cost matters

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.

PropertyHNSWIVFFlat
StructureMulti-layer proximity graphInverted lists over k-means centroids
Build timeSlow. Grows with m and ef_constructionFast
Memory footprintHigher. Graph edges live alongside the vectorsLower
Recall at equal latencyBetterWorse
Update behaviourInserts are absorbed into the graph, no rebuild neededCentroids drift as data changes. Requires rebuild to stay accurate
Cold startCan be created on an empty tableNeeds data loaded first, or the centroids are meaningless
Query knobhnsw.ef_search, default 40ivfflat.probes, default 1
Availabilitypgvector, Qdrant, Weaviate, Milvus, Elasticsearch, MongoDB Atlaspgvector, Milvus and other engines with IVF families
Index characteristics per the pgvector documentation (version 0.8.5) and vendor docs for the other engines.

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.

Retrieval quality is not a database problem

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.

Chunking

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.

Metadata attached at ingestion

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.

Permission filtering: why post-filtering is a correctness bug

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.

Implementing vector search in Postgres with pgvector

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.

Reindexing and embedding-model migration

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:

  1. Add the new model as a second set of rows, or a second table if the dimension differs, keyed by the same source and chunk index. The unique constraint above already allows this.
  2. Turn on dual writes in the ingestion pipeline so every new or updated chunk is embedded with both models. This stops the backfill chasing a moving target.
  3. Backfill the historical corpus into the new model in batches, with a resumable cursor and a rate limit that respects the embedding provider’s quota.
  4. Shadow-read: run production queries against both indexes and compare the top-k overlap and your evaluation set scores. Do not cut over on vibes.
  5. Flip the model constant in the query, watch for a week, then drop the old rows and rebuild the index to reclaim the space.

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.

Failure modes in production retrieval

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 modeWhat it looks likeFix
Unfiltered top-k across tenantsAn agent cites a plausible, well-written passage that belongs to a different customerFilter 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 ingestionLatency is fine, recall quietly collapses. Common with IVFFlat after the distribution shiftsRebuild IVFFlat on a schedule tied to ingestion volume, or move to HNSW and stop thinking about it
Re-embedding cost surpriseA model upgrade turns into an unbudgeted invoice and a rate-limit incident mid-backfillCount tokens before you start, batch with a resumable cursor, and schedule the backfill against your quota
No evaluation setRetrieval degrades over months and the first report comes from a customer, not a dashboardKeep 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 recordA reindex loses documents because the only copy of the chunk text was in the vector storeKeep source documents in durable storage. The vector index is derived data and must be rebuildable from scratch
Mixed embedding generationsResults are erratic for a subset of documents after a partial migrationRecord model and dimension per row, filter on the model in every query, never let a query span two generations
Failure modes ordered by how often they reach production before anyone notices.

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.

The verdict by workload profile

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.

Frequently asked questions

Is pgvector fast enough for production retrieval?

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.

How many vectors can Postgres handle before I need something else?

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.

Should I use HNSW or IVFFlat in pgvector?

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.

Does changing embedding model mean re-embedding everything?

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.

What chunk size should I use for document retrieval?

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.

Why is post-filtering search results a problem?

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.

Can I do hybrid keyword and vector search without a dedicated store?

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.

Can a vector database be my system of record?

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.

Elizabeth Sramek

Elizabeth Sramek is an independent advisor on search visibility and demand architecture for B2B companies operating in high-competition markets. Based in Prague and working globally, she specializes in designing search presence for AI-mediated discovery and building category visibility that survives algorithmic shifts.

Recent Posts

Sanitizing PII Payloads Natively Before Routing to Public LLM Endpoints

Regex, masking loops, and data scrubbers implemented at the preprocessing edge.

8 hours ago

Make Webhooks vs WordPress REST API: My Draft Creation Workflow

A practical Triumphoid guide to make webhooks vs wordpress rest api: my draft creation workflow,…

1 day ago

Prompt Caching Architectures to Slash LLM API Costs by 80%

Database-level orchestration steps to cache systemic instructions and context arrays natively in your pipelines.

2 days ago

Free Rank Tracking Tools: Search Console Beats All of Them

Google Search Console is the best free rank tracker and most lists omit it. What…

3 days ago

WordPress REST API vs WP Admin: Where Automation Saves Me Time

A practical Triumphoid guide to wordpress rest api vs wp admin: where automation saves me…

4 days ago

Elementor vs WPBakery in 2026: Which Builder Should You Use?

A practical comparison of Elementor and WPBakery for editing experience, templates, performance, theme compatibility, and…

6 days ago