Vector search on pgvector: where RAG breaks
On a demo stand, RAG over an internal knowledge base responds in fractions of a second: while the corpus is several thousand chunks, pgvector compares the query vector with each one and stays within an acceptable response time. The limit appears imperceptibly.
Exact vector search is linear in corpus size, and at hundreds of thousands of chunks search begins to compete for CPU with other database operations — first the latency tail grows, then the overall background.
The second symptom is memory consumption for intermediate computations. To return the top matches, the database keeps all distances in the sort, not just the needed top; under concurrent queries this evicts the page cache, and response time stops being predictable.
Add an attribute filter — access rights, document date, source — and the plan may bypass the index, even if it is built.
Search degradation as the corpus grows rarely manifests as an explicit error. More often it is a quiet divergence: today some chunks got into the model context, tomorrow others, and answers fluctuate from query to query.
Vector indexing removes the computational ceiling, but brings its own settings — how many links to keep in the graph, how many candidates to examine per query — and with wrong values, recall is quietly cut on rare but important phrasings.
A separate trap is mixing two modes in one index: part of the corpus updates more often than the graph is rebuilt, and fresh documents fall out of search even though they are in the table.
That is why quality here is measured not on one beautiful question, but on a set with known correct answers — before and after every parameter change. How this is closed in practice and in what order, we discuss further.
What RAG search delivers: timelines, accuracy, failures
We record four effects at acceptance, and each is checked on your corpus: search response time, share of relevant results, eliminated failure class, and volume of manual review of results. Below is how this is achieved and what exactly is measured rather than declared.
Response speed as the corpus grows
Without a vector index, response time grows with the corpus: every query is compared with all chunks, so with a tenfold increase in documents, search slows down by the same factor.
The HNSW index in pgvector builds a graph of links between vectors, and traversal follows it rather than the entire table — response time remains almost constant while the corpus size grows severalfold.
Accuracy is controlled by the traversal parameter: the wider it is, the closer the results are to exhaustive search, and this is a conscious choice, not hidden degradation.
Share of relevant results and manual review
The second measurement is the share of queries where the needed chunk appears in the top lines of results.
It depends on how documents are chunked: large chunks blur the meaning, small ones lose context, so we select size and overlap based on your corpus and add metadata filters — date, document type, department.
The failure class "wrong thing found" goes away predictably: alongside vector search we place full-text search and reranking so that keyword and semantic matches do not compete.
Along with this, the volume of manual review drops: the analyst stops checking every result and looks only at those where the low-confidence threshold triggered.
Comparison of approaches to vector search on pgvector
The search mechanism determines how fast the knowledge base grows and when a migration to another stack will be needed. Below are four schemes among which people actually choose when developing search on pgvector inside PostgreSQL.
| Option | When it fits | Limitations |
|---|---|---|
| Exact search | Up to hundreds of thousands of vectors, reference recall required | Response time grows linearly: the entire table is read |
| Approximate index in the database | Millions of vectors, loss of a fraction of a percent in recall is acceptable | Requires parameter tuning and periodic index rebuild |
| External vector store | Tens of millions of vectors, separate load circuit | Second data source, desynchronization, its own fault-tolerance circuit |
| Hybrid scheme with text search | Queries with words, SKUs, names | More complex ranking: two result sets are merged into one list |
Approximate index vs exact search
Exact search guarantees the correct top of results, but on large volumes it reads the entire table, and response time grows linearly. Approximate index search responds many times faster, but may miss some close vectors.
Recall is controlled by the search parameter: it is raised to the level at which the results match the reference on a test set of questions.
When an external vector store is excessive
A separate store is justified when volume and load do not fit into one database circuit. If documents and vectors lie next to each other and are updated together, moving them out adds network exchange, a second access scheme, and desynchronization during reindexing.
As long as we are talking about millions of rows, an index inside the database is enough, and hybrid search is assembled with an ordinary full-text query alongside the vector one.
Stages of implementing RAG search in a product
Implementing document search rarely runs into the choice of database — it runs into data quality and predictability of updates. The route goes from analyzing the corpus and queries to designing storage, and only then to tuning results.
- Corpus and query analysis. Formats, volume, duplicates, frequency of changes, and how employees actually phrase questions.
- Vector storage schema. In pgvector we design chunk tables and metadata for filtering by access rights and document versions.
- Search and chunking strategy. Chunk size and overlap, weighting, hybrid mode with full-text search.
- Index updates. Reindexing when documents change, deleting outdated versions, controlling storage growth.
- Quality checks. Running a set of control queries and comparing them with reference chunks.
- Handover to operations. Regulations, monitoring, instructions for the support team.
Document chunking and vector preparation
We select chunk size according to the text structure: regulations and tables require one approach, continuous text another. Overlap preserves meaning at boundaries, and metadata — document, section, version, rights — cuts off the unnecessary before the search itself.
Verification on a set of control queries
We collect real questions with reference chunks and see whether the needed source appears in the top lines of results. We analyze discrepancies and fix them with chunking, weights, or search strategy.
What we hand over: configuration and regulations
The handover package is not a folder with code, but a set by which the maintenance team reproduces search without us. Below is what exactly we record in documentation and configuration.
Storage schema and index parameters
- Table schema: chunking, field types, links to the source, deletion policy when a document is revoked.
- Vector index configuration in pgvector: type, vector dimensionality, build and search parameters, recorded in the migration file.
- Description of text chunking: chunk size, overlap, rules for handling tables, headings, and code.
- Set of reference queries with expected results — they are used to check that search quality has not dropped after index rebuild.
Update and access regulations
- Corpus update regulations: frequency and triggers for reindexing, procedure for loading new documents, handling of modified and deleted ones.
- Access policies for documents and chunks: row-level filters and binding rights to sections — search does not return what an employee is not supposed to see.
- Index rebuild procedure: run on a copy, comparison with reference queries, rollback point.
- Instructions for the on-call shift: typical failures, how to distinguish a loading problem from search degradation, whom to escalate to.
Separately, there is search documentation: how to phrase queries, how to read relevance metrics, and which corpus edits require approval from the data owner.
Case study: knowledge base search on pgvector
The task came from the support team: a knowledge base of several thousand documents — regulations, instructions, incident reviews — was growing faster than it could be described, and keyword search found a page only with an exact match of the wording.
How it was before implementation
Specialists looked for the answer visually through the table of contents: open a section, skim related documents, go back.
On real tickets, one question took from two to three minutes, and the needed section was found in about four out of ten cases — the rest was resolved by asking a colleague in chat.
Manual search was the main way of working, and new employees reached the standard speed only after several weeks.
What changed after index tuning
Search was moved to PostgreSQL: pgvector stores vector representations alongside document text, so filters by department, type, and date are applied in the same query as the similarity comparison.
The index was tuned in two passes — first we loaded the corpus and measured baseline recall, then selected list parameters and the number of segments for the database size so that accuracy would not drop as it grows.
An answer to a question now arrives in 2-4 seconds instead of minutes, hit rate for the needed section grew from 40 to 85% on the same set of queries, and requests for manual search decreased fourfold.
The corpus is updated by nightly reindexing: a new regulation appears in results the next day without manual edits.
How pgvector differs from a separate vector database
pgvector is a PostgreSQL extension in which vectors live in the same table as ordinary data.
Nearest-neighbor search runs in one query together with metadata filtering — tenant, access rights, document freshness; the vector and the primary document record land in one transaction, and there is still only one backup.
A separate vector store is used for a different task: when the search index does not fit in memory together with working data and search over a distributed index is needed.
It provides predictable latency on very large collections, but moves complexity into integration — a second source of truth appears.
The main cost is synchronization of data and vectors. A row and its vector are linked by a background task or change stream, and between the write and indexing there is a window when the document is already updated but search returns the old chunk.
Deletion is solved separately — otherwise orphaned chunks accumulate — as is full reindexing when the embedding model changes.
Next come failure points: a service is added to the database, along with its accesses, its backup, and its schema. A database failure would already have stopped the application, but now two independent circuits can break.
That is why the choice is made by volume threshold: while the corpus and index fit in the node's memory and search does not compete with working queries, one database is simpler — one backup, one transaction, no synchronization.
Separation is worth it where the index stops fitting or search starts interfering with the main work with data.
Technical doubts: limitations and degradation
While the corpus is tens of thousands of chunks, search in pgvector responds quickly and predictably.
At hundreds of thousands, the same queries take noticeably longer, and results for paraphrased questions diverge: the same meaning, expressed in different words, leads to different documents.
This is a consequence of the index having been built on a different volume and data structure than what is in the table now — and the question of running on top of an already deployed system is separate.
How to understand that the index needs rebuilding?
Signs: response time grows with an unchanged number of rows, the share of relevant answers in the top of results falls, and after mass loads and deletions a garbage periphery appears.
An index built on an incomplete corpus loses accuracy as more data is loaded, so we rebuild it when the bulk of documents is in place — in a separate branch, with checks on control questions and subsequent switching.
How to isolate data of different customers in one database?
We separate at the schema and row level: a separate partition per customer plus a mandatory filter by its identifier, applied before the ranking stage. We add row-level access policies in PostgreSQL and control on the side of the service that builds the query.
Verification is by a negative test: a query on behalf of one customer finds no chunks belonging to another.
Why are results unstable for similar phrasings?
Vector search poorly distinguishes queries close in meaning when the corpus has many similar documents. Combining with full-text search, a refinement ranking stage, a similarity cutoff threshold, and query normalization help. Cached results for frequent phrasings remove part of the load from the database.
Will the database degrade when running on top of an existing system?
Search reads data separately from the main load: queries go to a replica, heavy rebuilding goes into a maintenance window. We add the vector schema alongside, population runs in the background, and search is enabled gradually. Rollback is disabling the search layer: the rest of the system continues to work as before.
Let's discuss the task: what to prepare for the conversation
To make the estimate substantive rather than "for vector search in general," a few inputs are enough for the conversation. We will clarify some in correspondence and cover the rest at the meeting.
- Search task statement: what decisions a person makes from the results — finds a specific clause of a regulation, compares conditions, or asks for synthesis across several documents at once. This determines whether only pgvector search is needed or a generation layer on top of it.
- Examples of user queries — 10–20 real phrasings, including short ones, with typos, and in two languages. We use them to tune chunking and understand where keyword search is additionally needed.
- Volume and types of documents: how many files, which formats, how fast the corpus grows. This determines the choice of index in pgvector and the order of reindexing without stopping the service.
- Data freshness requirements: acceptable lag between publication of a document and its appearance in results. If we are counting in minutes, we include streaming loading and a vector update queue.
- Information access scheme: who sees which documents. We delimit rights with row-level filters in PostgreSQL so that they are applied before selecting nearest vectors, not after.
- Current load and failure behavior: how many requests per second at peak, what response time is acceptable, and what the service does when the database is unavailable — returns cache or honestly reports an error.
Describe the task in free form — we will return with an estimate and a staged work plan.
