Production RAG with PostgreSQL and pgvector: HNSW, Filters, and Tenant Search

Many teams already operate PostgreSQL before they build retrieval-augmented generation (RAG). Adding embeddings to the existing data platform can reduce synchronization work and keep document metadata, access rules, and application records close together. With pgvector, PostgreSQL can also perform vector similarity search.

That does not make every PostgreSQL instance a drop-in replacement for a dedicated vector database. Production retrieval depends on workload shape: corpus size, update rate, filter selectivity, latency targets, recall requirements, and the operational limits of the database. The important question is not “Can Postgres store vectors?” It is “Can this query return the right authorized evidence, at an acceptable latency, under the real tenant and freshness constraints?”

When PostgreSQL is a good fit

PostgreSQL with pgvector is worth evaluating when:

• your application already depends on PostgreSQL and the expected retrieval load fits its capacity;
• vectors must be joined to frequently changing relational metadata;
• tenant, document, and permission filters are part of nearly every search;
• the team wants fewer operational systems and a familiar backup and access-control model;
• you need a practical first production version before introducing a separate retrieval platform.

It may not be the best fit when vector search is a dominant, very high-throughput workload, indexes consume too much of the database's memory or I/O budget, or the product requires specialized sparse, multimodal, or distributed search capabilities. AWS's RAG architecture guidance compares managed and custom approaches; its recommendation depends on existing systems and requirements, not on one universal database choice.

Keep source records, chunks, and embeddings connected

The searchable row should preserve provenance and authorization context. A minimal schema might include:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document_chunks (
  id              bigserial PRIMARY KEY,
  tenant_id       uuid NOT NULL,
  document_id     uuid NOT NULL,
  document_version integer NOT NULL,
  chunk_number    integer NOT NULL,
  embedding_model text NOT NULL,
  embedding       vector(1536) NOT NULL,
  content         text NOT NULL,
  search_text     tsvector GENERATED ALWAYS AS
                    (to_tsvector('english', content)) STORED,
  updated_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, document_id, document_version, chunk_number, embedding_model)
);

CREATE INDEX document_chunks_tenant_doc
  ON document_chunks (tenant_id, document_id);

CREATE INDEX document_chunks_search_text
  ON document_chunks USING gin (search_text);

CREATE INDEX document_chunks_embedding_hnsw
  ON document_chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Choose the vector dimension and operator class to match the embedding model and distance metric. Store model and document versions so a model upgrade or re-index can be rolled out deliberately. Never overwrite the source document with a generated chunk: retain identifiers that let the application resolve a result back to the current source and authorization policy.

The values above are an example, not universal tuning recommendations. Index size, write rate, and query latency depend on vector dimensions, corpus size, and database resources. Benchmark with representative data before selecting index parameters.

HNSW is a latency-recall trade-off, not a correctness switch

An exact vector scan can be a useful baseline for a small corpus or selective filter. It evaluates more candidates and can provide exact nearest neighbors, at a compute cost that grows with the amount of data. Approximate indexes such as HNSW trade some recall for faster candidate retrieval. The pgvector index documentation describes this trade-off and the controls for index construction and search.

Do not tune only for query time. Measure retrieval quality against a labeled or human-reviewed query set, and record:

• recall at the candidate depth used by the application;
• p50 and p95 latency under concurrent load;
• rows returned after tenant and permission filters;
• index size, build time, insert throughput, and database CPU and memory;
• quality changes after document updates or embedding-model changes.

If the index returns a fast but incomplete candidate set, the language model cannot recover evidence it never received. Evaluate both the search stage and the final answer with citations.

Filtering and multi-tenant access need special care

RAG queries usually filter by tenant, access group, document status, product, or region. Approximate index search and relational filtering interact: if the nearest candidates are mostly excluded by a selective filter, the final result set may contain fewer authorized rows than requested.

pgvector 0.8.0 added iterative index scans, which can continue searching until enough filtered results are found or a scan limit is reached. This can improve filtered recall, but the extra scan work must be measured. The pgvector README's filtering section documents iterative scans and their limits.

For supported versions, a transaction-scoped query may enable an iterative scan before retrieval:

BEGIN;
SET LOCAL hnsw.iterative_scan = strict_order;

SELECT id, document_id, content,
       embedding <=> $1 AS distance
FROM document_chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 40;

COMMIT;

Use SET LOCAL so the setting does not leak through a pooled database connection. The query's tenant predicate is necessary, but it is not a complete authorization design. Derive tenant identity from authenticated server-side context, enforce document-level access rules before returning context, and consider PostgreSQL row-level security as defense in depth. Never accept a tenant ID supplied only by the prompt or client as proof of access.

If many queries use highly selective filters, compare iterative scans with exact search on the filtered subset, partitioning, or a dedicated vector-search service. A single global HNSW index is not automatically the fastest or most complete plan for every tenant shape.

Combine semantic and lexical retrieval where it helps

Vector similarity is useful for paraphrases and conceptual matches. It can be weak for exact identifiers, product codes, names, dates, and error messages. PostgreSQL full-text search can complement vector retrieval for those terms.

A common pattern is to fetch a candidate list from each retriever, normalize or fuse the ranks (for example with reciprocal rank fusion), then rerank the smaller combined set. Avoid adding a complex pipeline by default: start with representative queries and determine whether lexical retrieval improves answer evidence enough to justify the additional query and tuning work.

Include filter semantics in both paths. A lexical query that checks tenant access while the vector query does not is a data-exposure bug, not a relevance issue. The final context builder should validate authorization again against current source records when permission changes can race with indexing.

Keep ingestion and retrieval consistent

Document ingestion is a separate reliability path from query serving. A production pipeline needs stable document versions, idempotent chunk writes, retry handling, and a way to identify incomplete embeddings. A useful sequence is:

1. Record a new source version and its access metadata.
2. Split and embed the content asynchronously with a bounded retry policy.
3. Write all chunks under that version and verify expected counts.
4. Atomically mark the version searchable or switch the active-version pointer.
5. Retire old chunks only after the new version is queryable and retention rules allow deletion.

This avoids exposing a half-indexed document or removing the previous valid version before its replacement is ready. For sensitive data, propagate deletion and access revocation to chunks, embeddings, caches, and derived indexes. The data sovereignty guide covers the broader lifecycle and regional controls for derived AI data.

Large backfills can compete with live retrieval and overload the primary database. Use bounded batches, measure write amplification and replica lag, and schedule index creation or rebuilds with an operational plan. If retrieval traffic and ingestion start affecting transactional workloads, isolate the workload using replicas or a separate search system rather than allowing customer-facing transactions to degrade silently.

A practical production evaluation

Before committing to pgvector, build a benchmark using real query shapes and permission filters. Compare exact search with the selected approximate index; test common and worst-case tenants; include concurrent writes; measure recall, p95 latency, and resource consumption; and test document updates and permission revocation. Repeat after changes to the embedding model, chunking, filters, index settings, or PostgreSQL version.

For a moderate RAG workload with relational metadata and strong tenant constraints, pgvector can be a straightforward, capable starting point. For a search-heavy workload, it may still be the right choice, but only after filtered recall and database headroom are demonstrated. BoundLayer builds RAG and data platforms with secure retrieval, tenant-aware authorization, measurable evaluation, and production operations. We can assess an existing PostgreSQL design or benchmark whether it should remain the retrieval store as usage grows.

Further reading

• pgvector documentation: HNSW, filtering, and iterative scans
• AWS: Running pgvector in production on Amazon Aurora PostgreSQL
• AWS Prescriptive Guidance: Choosing a RAG option
• Context engineering for production AI agents