RAG Storage Schema

Q: Version embeddings?

New column embedding_v2 during migration; dual-write then cutover.

A RAG store in Postgres splits documents into chunks, stores embeddings per chunk, and keeps metadata for filtering and citations. Design for tenant isolation, idempotent re-ingestion, and hybrid FTS from day one.

Recipe

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions;
 
CREATE TABLE rag_documents (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid NOT NULL,
  source_uri    text NOT NULL,
  title         text,
  content_hash  text NOT NULL,  -- sha256 of normalized source
  created_at    timestamptz DEFAULT now(),
  UNIQUE (tenant_id, source_uri)
);
 
CREATE TABLE rag_chunks (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid NOT NULL,
  document_id   uuid NOT NULL REFERENCES rag_documents(id) ON DELETE CASCADE,
  chunk_index   int NOT NULL,
  content       text NOT NULL,
  token_count   int,
  metadata      jsonb NOT NULL DEFAULT '{}',
  embedding     extensions.vector(1536),
  content_tsv   tsvector GENERATED ALWAYS AS (
    to_tsvector('english', content)
  ) STORED,
  UNIQUE (document_id, chunk_index)
);

When to reach for this: Building retrieval for LLM apps where source citations, per-tenant corpora, and SQL joins to user permissions matter.

Working Example

-- Tenant-scoped indexes
CREATE INDEX rag_chunks_tenant_hnsw ON rag_chunks
  USING hnsw (embedding extensions.vector_cosine_ops)
  WHERE embedding IS NOT NULL;
 
CREATE INDEX rag_chunks_tenant_fts ON rag_chunks USING gin (content_tsv);
CREATE INDEX rag_chunks_metadata ON rag_chunks USING gin (metadata jsonb_path_ops);
CREATE INDEX rag_chunks_tenant ON rag_chunks (tenant_id);
 
-- Idempotent ingest: skip if source unchanged
INSERT INTO rag_documents (tenant_id, source_uri, title, content_hash)
VALUES ($1, $2, $3, $4)
ON CONFLICT (tenant_id, source_uri) DO UPDATE
  SET content_hash = EXCLUDED.content_hash,
      title = EXCLUDED.title
  WHERE rag_documents.content_hash IS DISTINCT FROM EXCLUDED.content_hash
RETURNING id, (xmax = 0) AS inserted;
 
-- Retrieval with tenant filter + hybrid-ready columns
SELECT c.id,
       c.content,
       d.title AS source_title,
       c.metadata->>'page' AS page,
       c.embedding <=> $2 AS distance
FROM rag_chunks c
JOIN rag_documents d ON d.id = c.document_id
WHERE c.tenant_id = $1
  AND c.embedding IS NOT NULL
ORDER BY c.embedding <=> $2
LIMIT 8;

What this demonstrates:

Document vs chunk separation enables re-chunk without losing source identity
content_hash drives idempotent pipeline skips
Partial HNSW index excludes not-yet-embedded rows
metadata JSONB holds page numbers, section, ACL hints
Generated tsvector supports hybrid search without duplicate parsing logic

Deep Dive

Chunking Metadata

{
  "page": 12,
  "section": "Replication",
  "heading": "Streaming standby",
  "embed_model": "text-embedding-3-small",
  "embed_version": "2026-03-01"
}

Store model name and version in metadata or dedicated columns. Re-embed campaigns filter WHERE metadata->>'embed_model' <> 'new-model'.

Tenant Isolation

Approach	Mechanism	Notes
Column + RLS	`tenant_id` + policy	Default for SaaS
Schema per tenant	`tenant_abc.rag_chunks`	Heavy ops at scale
Database per tenant	Separate DB	Enterprise tier

ALTER TABLE rag_chunks ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON rag_chunks
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Set app.tenant_id per connection from pooler session variable.

Citation Payload

Return to LLM:

chunk.content (trimmed)
document.source_uri
metadata->>'page'
chunk_index for ordering within document

Avoid storing full PDF blobs in chunk table; keep object storage URI in rag_documents.

Ingestion States

ALTER TABLE rag_chunks
  ADD COLUMN embed_status text NOT NULL DEFAULT 'pending'
    CHECK (embed_status IN ('pending', 'ready', 'failed'));

Workers claim pending rows with FOR UPDATE SKIP LOCKED.

Gotchas

Chunks without document parent - Orphan cleanup fails. Fix: FK with ON DELETE CASCADE from chunks to documents.
Embedding before content final - Race on streaming ingest. Fix: Status column; index WHERE embed_status = 'ready'.
Oversized chunks - Entire manual in one row blows context and embedding quality. Fix: Target 400-800 tokens with overlap (store overlap in metadata).
JSONB bloat on hot path - Huge metadata per chunk. Fix: Promote filter keys to real columns (language, product_id).
Missing tenant in index predicate - HNSW scans all tenants. Fix: Partial index per large tenant or composite partition key.
Unique on content only - Duplicate chunks across docs. Fix: UNIQUE (document_id, chunk_index) plus content hash at document level.

Alternatives

Alternative	Use When	Don't Use When
Single table (no document)	Tiny static FAQ	Multi-source citations required
pgvector + object storage	Large PDF originals	Need transactional chunk + blob together
External vector DB	Dedicated search team and scale	Permission joins live in Postgres
Materialized view of chunks	Read-heavy analytics on corpus	Write-heavy ingest pipeline

FAQs

Chunk size?

Model-dependent; 512 tokens common for text-embedding models. Measure retrieval MRR.

Overlap?

50-100 tokens overlap reduces boundary misses; store start_offset in metadata.

Delete stale docs?

CASCADE delete chunks when document removed or hash unchanged skip re-embed.

Version embeddings?

New column `embedding_v2` during migration; dual-write then cutover.

ACL in metadata vs RLS?

RLS on tenant_id mandatory; object ACL in metadata for fine-grained optional filter.

Full-text on chunks?

Yes; hybrid search article merges with vector score.

Batch embed?

COPY chunks without embedding; worker UPDATE sets vector and status ready.

Checksum algorithm?

SHA-256 of normalized UTF-8 text; document when normalization changes.

UUID vs bigint ids?

UUID fine for distributed ingest; bigint smaller indexes if single-writer.

Reference layout?

See case study reference pgvector RAG store for tuned HNSW example.

pgvector Basics - vector type
IVFFlat vs HNSW Indexes - index choice
Hybrid Search - FTS + vector
Row-Level Security - tenant policies
Reference: pgvector RAG Store - worked example

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.

Recipe

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions;
 
CREATE TABLE rag_documents (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid NOT NULL,
  source_uri    text NOT NULL,
  title         text,
  content_hash  text NOT NULL,  -- sha256 of normalized source
  created_at    timestamptz DEFAULT now(),
  UNIQUE (tenant_id, source_uri)
);
 
CREATE TABLE rag_chunks (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid NOT NULL,
  document_id   uuid NOT NULL REFERENCES rag_documents(id) ON DELETE CASCADE,
  chunk_index   int NOT NULL,
  content       text NOT NULL,
  token_count   int,
  metadata      jsonb NOT NULL DEFAULT '{}',
  embedding     extensions.vector(1536),
  content_tsv   tsvector GENERATED ALWAYS AS (
    to_tsvector('english', content)
  ) STORED,
  UNIQUE (document_id, chunk_index)
);

When to reach for this: Building retrieval for LLM apps where source citations, per-tenant corpora, and SQL joins to user permissions matter.

Working Example

-- Tenant-scoped indexes
CREATE INDEX rag_chunks_tenant_hnsw ON rag_chunks
  USING hnsw (embedding extensions.vector_cosine_ops)
  WHERE embedding IS NOT NULL;
 
CREATE INDEX rag_chunks_tenant_fts ON rag_chunks USING gin (content_tsv);
CREATE INDEX rag_chunks_metadata ON rag_chunks USING gin (metadata jsonb_path_ops);
CREATE INDEX rag_chunks_tenant ON rag_chunks (tenant_id);
 
-- Idempotent ingest: skip if source unchanged
INSERT INTO rag_documents (tenant_id, source_uri, title, content_hash)
VALUES ($1, $2, $3, $4)
ON CONFLICT (tenant_id, source_uri) DO UPDATE
  SET content_hash = EXCLUDED.content_hash,
      title = EXCLUDED.title
  WHERE rag_documents.content_hash IS DISTINCT FROM EXCLUDED.content_hash
RETURNING id, (xmax = 0) AS inserted;
 
-- Retrieval with tenant filter + hybrid-ready columns
SELECT c.id,
       c.content,
       d.title AS source_title,
       c.metadata->>'page' AS page,
       c.embedding <=> $2 AS distance
FROM rag_chunks c
JOIN rag_documents d ON d.id = c.document_id
WHERE c.tenant_id = $1
  AND c.embedding IS NOT NULL
ORDER BY c.embedding <=> $2
LIMIT 8;

What this demonstrates:

Document vs chunk separation enables re-chunk without losing source identity
content_hash drives idempotent pipeline skips
Partial HNSW index excludes not-yet-embedded rows
metadata JSONB holds page numbers, section, ACL hints
Generated tsvector supports hybrid search without duplicate parsing logic

Deep Dive

Chunking Metadata

{
  "page": 12,
  "section": "Replication",
  "heading": "Streaming standby",
  "embed_model": "text-embedding-3-small",
  "embed_version": "2026-03-01"
}

Store model name and version in metadata or dedicated columns. Re-embed campaigns filter WHERE metadata->>'embed_model' <> 'new-model'.

Tenant Isolation

Approach	Mechanism	Notes
Column + RLS	`tenant_id` + policy	Default for SaaS
Schema per tenant	`tenant_abc.rag_chunks`	Heavy ops at scale
Database per tenant	Separate DB	Enterprise tier

ALTER TABLE rag_chunks ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON rag_chunks
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Set app.tenant_id per connection from pooler session variable.

Citation Payload

Return to LLM:

chunk.content (trimmed)
document.source_uri
metadata->>'page'
chunk_index for ordering within document

Avoid storing full PDF blobs in chunk table; keep object storage URI in rag_documents.

Ingestion States

ALTER TABLE rag_chunks
  ADD COLUMN embed_status text NOT NULL DEFAULT 'pending'
    CHECK (embed_status IN ('pending', 'ready', 'failed'));

Workers claim pending rows with FOR UPDATE SKIP LOCKED.

Gotchas

Chunks without document parent - Orphan cleanup fails. Fix: FK with ON DELETE CASCADE from chunks to documents.
Embedding before content final - Race on streaming ingest. Fix: Status column; index WHERE embed_status = 'ready'.
Oversized chunks - Entire manual in one row blows context and embedding quality. Fix: Target 400-800 tokens with overlap (store overlap in metadata).
JSONB bloat on hot path - Huge metadata per chunk. Fix: Promote filter keys to real columns (language, product_id).
Missing tenant in index predicate - HNSW scans all tenants. Fix: Partial index per large tenant or composite partition key.
Unique on content only - Duplicate chunks across docs. Fix: UNIQUE (document_id, chunk_index) plus content hash at document level.

Alternatives

Alternative	Use When	Don't Use When
Single table (no document)	Tiny static FAQ	Multi-source citations required
pgvector + object storage	Large PDF originals	Need transactional chunk + blob together
External vector DB	Dedicated search team and scale	Permission joins live in Postgres
Materialized view of chunks	Read-heavy analytics on corpus	Write-heavy ingest pipeline

FAQs

Chunk size?

Model-dependent; 512 tokens common for text-embedding models. Measure retrieval MRR.

Overlap?

50-100 tokens overlap reduces boundary misses; store start_offset in metadata.

Delete stale docs?

CASCADE delete chunks when document removed or hash unchanged skip re-embed.

Version embeddings?

New column `embedding_v2` during migration; dual-write then cutover.

ACL in metadata vs RLS?

RLS on tenant_id mandatory; object ACL in metadata for fine-grained optional filter.

Full-text on chunks?

Yes; hybrid search article merges with vector score.

Batch embed?

COPY chunks without embedding; worker UPDATE sets vector and status ready.

Checksum algorithm?

SHA-256 of normalized UTF-8 text; document when normalization changes.

UUID vs bigint ids?

UUID fine for distributed ingest; bigint smaller indexes if single-writer.

Reference layout?

See case study reference pgvector RAG store for tuned HNSW example.

pgvector Basics - vector type
IVFFlat vs HNSW Indexes - index choice
Hybrid Search - FTS + vector
Row-Level Security - tenant policies
Reference: pgvector RAG Store - worked example

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.

RAG Storage Schema

Search Documentation

RAG Storage Schema