Making pgvector RAG Survive Production: Chunks, Metadata Filters, and Citations

Retrieving document chunks and embeddings in PostgreSQL

In this article, we will create a basic search structure for a document-type RAG service using PostgreSQL and pgvector.

Although it is possible to adopt a dedicated vector DB right away, for training and initial service, the choice to start vector search within PostgreSQL is quite practical. This is because if users, documents, question history, and index status are already stored in PostgreSQL, embeddings can also be managed in the same DB.

Analysis base date: 2026-05-12 Practice environment: PostgreSQL 16+, pgvector, Python, FastAPI Main reference materials: pgvector GitHub, Supabase pgvector Docs, RAG Paper

Key takeaways

• pgvector is an extension that enables vector similarity search in PostgreSQL.
• In document type RAG, the tablesdocuments,document_chunks,embeddings, andindex_jobsare basic.
• Search queries must apply not only vector similarity but also metadata filters and permission conditions.
• chunk_id is a key identifier linked to citation and eval.
• Initially, we start with an exact search, and as the data grows, we review the index strategy.

1. Why pgvector

The advantage of pgvector is its operational simplicity.

For initial learning projects, pgvector is good. This is because you can understand the data model and search together with SQL.

2. RAG data model

The basic table is as follows:

Copy1# This is an example.2documents3- id4- title5- source_type6- source_url7- version8- created_at9- updated_at10 11document_chunks12- id13- document_id14- chunk_index15- content16- token_count17- metadata18- embedding19- created_at20 21index_jobs22- id23- document_id24- idempotency_key25- status26- error_code27- created_at28- updated_at

3. Install pgvector and activate extension

Copy1/* This is example SQL.*/2CREATE EXTENSION IF NOT EXISTS vector;

Assuming the embedding dimension is 1536, you can create a column like this:

Copy1/* This is example SQL.*/2CREATE TABLE document_chunks (3    id UUID PRIMARY KEY,4    document_id UUID NOT NULL,5    chunk_index INT NOT NULL,6    content TEXT NOT NULL,7    token_count INT NOT NULL,8    metadata JSONB NOT NULL DEFAULT '{}',9    embedding vector(1536),10    created_at TIMESTAMPTZ NOT NULL DEFAULT now()11);

The embedding dimension should match the embedding model you use.

4. Document and chunk table design

The original document and chunk must be separated.

The chunk must containdocument_id,chunk_index,content,metadata, andembedding.

5. Save Embedding

Embedding creation flow:

Copy1# This is an example.2Upload document3to text extraction4→ chunking5to create embedding6Save to document_chunks

Python example:

Copy1# This is example code.2async def index_chunks(document_id: str, chunks: list[str]):3    for idx, content in enumerate(chunks):4        embedding = await create_embedding(content)5        await repo.insert_chunk(6            document_id=document_id,7            chunk_index=idx,8            content=content,9            embedding=embedding,10        )

6. Similarity Search query

In pgvector, you can find similar vectors using the distance operator.

Copy1/* This is example SQL.*/2SELECT3    id,4    document_id,5    content,6    metadata,7    embedding <-> :query_embedding AS distance8FROM document_chunks9ORDER BY embedding <-> :query_embedding10LIMIT 5;

A lower distance value means closer. The distance method used should be selected according to the embedding model and search strategy.

7. metadata filter and Permissions

In actual services, you should not search for vector similarity alone. You need to filter document permissions and scope.

Copy1/* This is example SQL.*/2SELECT3    c.id,4    c.document_id,5    c.content,6    c.embedding <-> :query_embedding AS distance7FROM document_chunks c8JOIN documents d ON d.id = c.document_id9WHERE d.source_type = :source_type10  AND d.visibility = 'public'11ORDER BY c.embedding <-> :query_embedding12LIMIT :limit;

If there are user-specific permissions, they must be joined with the ACL table.

8. Citation connection

To provide a citation in a RAG answer, a chunk_id must be associated with the answer.

Copy1// This is an example JSON structure.2{3  "answer": "Cache Aside is a pattern that searches the cache first and then searches the DB when there is a miss.",4  "citations": [5    {6      "document_id": "doc_123",7      "chunk_id": "chunk_456",8      "quote": "check Redis first, return cached data on a hit..."9    }10  ]11}

Citations should not be randomly generated by the model. You should only select from the list of chunks provided as search results.

9. Operational indicators and limitations

pgvector is good for initial RAG implementation, but as the data grows and search requirements become more complex, you should consider a dedicated vector DB, hybrid search, or reranker.

10. Practical checklist

Copy1# This is an example.2[ ] Have you separated documents and chunks?3[ ] Do you save the document version?4[ ] Is chunk_id connected to citation?5[ ] Are the embedding model and dimensions recorded?6[ ] Do you apply metadata filter?7[ ] Are user rights enforced during the retrieval step?8[ ] Do you track index job status?9[ ] Are search failure cases saved as eval dataset?

Failure example: RAG that searches but fails to explain the basis for the answer

The first failure you encounter after attaching pgvector is the situation of “Similar documents were found, but the quality of the answer cannot be explained.” All you need is an embedding column and a similarity query to create a demo quickly. When a user sends a question, the nearby chunk is retrieved, and the model answers based on that chunk. However, when operators start asking “Why did you pick this document?”, “Are there any unauthorized documents mixed in?”, or “What exactly is the chunk used in the answer?”, a simple vector search falls short.

The cause of failure is usually the late entry of metadata and citation design. If you only savecontentandembedding, you can search, but you cannot track document version, tenant, permission, source URL, chunk order, and embedding model version. Even if a hallucination report comes in later, it is difficult to find which chunk entered the prompt.

Implementation example: citation-driven table design

The RAG table must consider auditability before answer generation.

Copy1create table document_chunks (2  id uuid primary key,3  document_id uuid not null,4  tenant_id text not null,5  source_url text,6  chunk_index integer not null,7  content text not null,8  content_hash text not null,9  embedding_model text not null,10  embedding vector(1536),11  created_at timestamptz not null default now()12);13 14create index document_chunks_tenant_idx15  on document_chunks (tenant_id, document_id);

Search queries should not only look at similarity, but also consider tenant and document status.

Copy1select id, document_id, source_url, chunk_index, content2from document_chunks3where tenant_id = $14order by embedding <=> $25limit 8;

This example is simple, but adds two values. First, you can leave the chunk id you used in your answer as a citation. Second, put the permission conditions within the same query, not outside the vector search. In an actual service, the document disclosure scope, deletion status, and latest version should also be included in the filter.

Checklist application results

If you pass this table, you go up one level from “pgvector attached” to “can operate document-type RAG service.”

Operational comparison: pgvector alone vs. RAG product

The decision to adopt pgvector does not end with search engine selection. In actual products, it is more important to “find explanatory evidence within a document visible to the user, within a time limit” than to “find a nearby vector.”

For example, in your company policy document RAG, let's say a user asks "vacation carryover criteria." A vector search can find similar paragraphs, but if the document is an obsolete old regulation, you should not use it in your answer. Therefore, conditions such asdocuments.status = 'active',documents.effective_from <= now(), andtenant_id = current_tenantmust be applied together with the similarity query. You should leave the usedchunk_idanddocument.versionin the answer log so that you can find out which answers are outdated when the regulations change in the future.

Conversely, a failure case is when a metadata filter is added later. First, put all your documents in one table and see if they are searchable. After gaining permission, we try to filter the search results at the application layer. This method may result in empty results after filtering, since top-k is already filled with unauthorized documents. Permission conditions must be included in the query itself to select candidates, not after the search. This will reduce situations where “the search is successful, but the user does not receive any response.”

11. Q&A

Q1. Is pgvector alone enough?

This may be sufficient for initial learning and small services. However, when large-scale searches, complex hybrid searches, and high-performance requirements arise, a separate search engine or vector DB must be considered.

Q2. What is the appropriate chunk size?

There is no right answer. It depends on the document type, question pattern, and embedding model. Usually you need to experiment with different sizes and compare them with retrieval eval.

Q3. What if I change the embedding model?

If the dimension or semantic space is different from the existing embedding, re-indexing is required. You must save embedding_model_version.

12. References and uncertainty

References

• pgvector GitHub:https://github.com/pgvector/pgvector
• Supabase pgvector Docs:https://supabase.com/docs/guides/database/extensions/pgvector
• RAG Paper:https://arxiv.org/abs/2005.11401

uncertainty

• The embedding dimension and distance operator depend on the model you use.
• Index strategies require experimentation depending on data size and latency requirements.

---