The first tenant has 20,000 chunks. The largest tenant has 40 million. The query has WHERE tenant_id = $1 ORDER BY embedding <=> $2 LIMIT 10, and the team expects Postgres to make that safe, fast, and fair for everyone. Multi-tenant vector search is not solved by adding tenant_id to the table.

Short Version

For multi-tenant RAG in pgvector, tenant filtering is a correctness requirement and a performance variable. The design has to decide whether tenants share one table and one vector index, use partial indexes for large tenants, use partitions, or move extreme tenants into separate physical boundaries.

Start simple when tenants are small and similar: shared chunk table, mandatory tenant filter, supporting B-tree indexes, and a vector index tested with representative filters. Add partial indexes or partitions when tenant size, query volume, compliance, or operational isolation justifies the extra complexity.

Do not partition because it sounds enterprise-grade. Partition because it improves pruning, restore, maintenance, tenant isolation, or query predictability enough to pay for the operational cost.

Situation

Multi-tenant RAG usually starts as a shared table because that is the fastest way to ship. The tenant ID is present, queries include a filter, and the first few customers look similar enough that the design feels settled.

The Problem

Approximate vector search and tenant filtering interact in ways that surprise relational engineers. A normal B-tree lookup on tenant_id narrows the candidate set exactly. A vector index ranks nearby vectors, then filters and limits interact with that candidate flow. Under selective filters, approximate search can return too few rows or spend work on vectors the tenant is not allowed to see.

The problem gets worse with uneven tenants:

  • One large tenant can dominate index size and cache behavior.
  • Small tenants may under-return if global nearest neighbors mostly belong to others.
  • A noisy tenant can create query load that affects everyone.
  • Tenant deletion or export is harder in a large shared table.
  • Per-tenant restore may be impossible without careful design.

The database architecture needs to encode tenant shape, not only tenant identity.

Core Technical Explanation

The baseline shared-table design looks simple:

SELECT document_id, chunk_id, body
FROM document_chunks
WHERE tenant_id = $1
  AND deleted_at IS NULL
ORDER BY embedding <=> $2
LIMIT 10;

This query has two jobs. It enforces authorization, and it ranks by vector distance. The hard part is making both jobs efficient and reliable.

Common physical designs:

DesignGood fitMain cost
Shared table and shared vector indexMany small similar tenantsFiltered ANN surprises
Shared table with partial indexesFew large or high-value tenantsIndex sprawl and migration work
Hash partition by tenantMany tenants with broad distributionMore partitions to manage
List partition by tenant tier or tenantClear tenant boundaries or large tenantsPartition count and DDL overhead
Separate database or clusterStrict isolation or very large tenantsCost and operational duplication

PostgreSQL partition pruning can reduce the data touched when the partition key is constrained — the planner excludes partitions that cannot match the WHERE clause before scanning anything. Whether a pgvector query benefits depends on whether tenant_id is the (or part of the) partition key, whether each partition carries its own vector index, and whether the query’s tenant filter is a literal or bindable constant the planner can prune on at plan time versus execution time.

pgvector filtered search behavior and iterative scan options are version-specific: hnsw.iterative_scan was added in pgvector 0.8.0 (October 2024) and defaults to off. On partitioned tables, iterative scan applies per-partition — each partition’s index scan expands independently — so a query touching several tenant partitions pays the iterative-scan cost multiple times, not once.

In Practice

A practical operating model starts with tenant tiers.

Tier 1: shared tenants. Most tenants live in a shared partition or table. They use mandatory tenant filters, lifecycle filters, and standard retrieval limits. This keeps onboarding simple.

Tier 2: heavy tenants. Tenants with large corpora or high query volume get partial indexes, dedicated partitions, or stronger routing rules. Their query plans are tested separately.

Tier 3: isolated tenants. Regulated, very large, or noisy tenants get separate databases, clusters, or physical deployments. This is not only for security; it can be an operational fairness decision.

The architecture should make routing explicit:

flowchart TD
    Query[tenant retrieval query] --> Classify[tenant tier lookup]
    Classify --> Shared[shared table]
    Classify --> Heavy[dedicated partition or partial index]
    Classify --> Isolated[separate database boundary]
    Shared --> Auth[tenant and entitlement filter]
    Heavy --> Auth
    Isolated --> Auth
    Auth --> Results[authorized ranked chunks]

Keep a tenant catalog outside the vector query path that records tier, corpus size, index strategy, model version, and restore requirements. Without that catalog, partitioning becomes tribal knowledge.

Where It Breaks

Failure modeProduction symptomMitigation
Missing tenant filterCross-tenant data exposureQuery builders, tests, and database policies
Global index with selective filterToo few results for small tenantsTune search breadth, partial indexes, or partitions
Too many partitionsSlow planning and DDL complexityPartition by tier or hash, not every tenant by default
Large tenant in shared tableCache and index behavior degrade for allPromote tenant to heavier tier
Partial index sprawlMigrations become fragileUse explicit tenant promotion workflow
Tenant deletion scans huge tableCompliance request takes too longPartition or maintain tenant-local delete paths

Security and Tenancy Notes

Tenant isolation must be enforced before text leaves the database. Application-side filtering after vector retrieval is too late because unauthorized chunks may already be in memory, logs, traces, or prompts.

Use negative tests: ask for a document title from tenant A while authenticated as tenant B and prove no candidate crosses the boundary. Run those tests after index changes, partition changes, restore drills, and model-version migrations.

Row-level security may strengthen protection, but it should not be the only control if background workers, admin tools, and migration scripts bypass normal application roles — RLS policies apply per role, and a worker connecting as a superuser or with BYPASSRLS skips them entirely by design, which is exactly the gap that turns a “defense in depth” control into the only control.

Cost Notes

Partitioning and partial indexes can reduce query waste, but they increase operational cost. Each extra index consumes storage, backup space, build time, and maintenance attention. Separate clusters multiply baseline spend.

The cost decision should include fairness. A large tenant that forces every other tenant onto a larger database instance is already costing the platform money. Isolation can be cheaper than shared-resource interference.

Observability Notes

Track metrics by tenant tier:

  • Query latency and timeout rate.
  • Rows returned versus requested limit.
  • Empty-result and under-return rate.
  • Vector index size by table or partition.
  • Top tenants by query volume and storage.
  • Autovacuum lag on tenant-heavy partitions.
  • Plan changes for representative tenant queries.
  • Restore and delete time by tenant class.

Use EXPLAIN examples for small, medium, and large tenants. A single plan from a median tenant is not enough.

Backup, Restore, and DR Notes

Partitioning can improve tenant-level operations if the runbook is designed for it. A tenant-local partition may make export, deletion, restore testing, or archival easier. It can also complicate global restore because more physical objects need validation.

For shared tables, per-tenant restore is usually an application-level reconstruction problem, not a database restore problem. Decide whether the business needs tenant-level point-in-time recovery. If it does, shared-table design may need compensating audit logs or event replay.

Decision Checklist

  • Are tenant filters mandatory in every retrieval query?
  • How skewed is tenant corpus size and query rate?
  • Do small tenants under-return under the global index?
  • Which tenants need stronger isolation for compliance or fairness?
  • Does partitioning improve pruning or only add complexity?
  • How many partitions can the team operate comfortably?
  • Are partial indexes governed by a promotion process?
  • Can tenant deletion, export, and restore meet requirements?
  • Are query plans tested by tenant tier?
  • Is the design reversible if tenant distribution changes?

What to Do Next

Problem: A shared chunk table with a tenant_id filter works until tenant sizes diverge — a large tenant degrades cache and index behavior for everyone, and small tenants silently under-return against a global vector index.

Solution: Tier tenants explicitly — shared table for small/similar tenants, partial indexes or dedicated partitions for heavy tenants, separate databases for regulated or extreme tenants — and promote tenants between tiers deliberately, not by accident.

Proof: A negative test (request tenant A’s document while authenticated as tenant B) returns zero candidates after every index, partition, or restore change, and EXPLAIN plans are captured for small, medium, and large tenants, not just a median case.

Action: This week, run the cross-tenant negative test against production, and identify which tenant (if any) is large enough to warrant promotion out of the shared table.

Sources to Verify