Pre-Filtering, Partial Indexes and Partitions

Match the design to the filter

Choose by how selective and how stable the filter is. Few huge tenants: a partial index per tenant (CREATE INDEX ... USING hnsw (...) WHERE tenant = 'acme') or a table partitioned by tenant gives each tenant its own small, accurate index, at the cost of more indexes to build and maintain. Many small tenants: one index with a tenant column and iterative scans or an exact scan per tenant (a few thousand rows scan quickly). Low-selectivity filters (for example language = en matching 80% of rows): post-filtering with some over-fetching works. Date ranges: partition by time and search only the relevant partitions. Always verify with EXPLAIN and a recall measurement, since the planner's choice depends on statistics. In a dedicated engine, create a payload index on every field you filter on and prefer engines that evaluate filters during graph traversal.

Filter design cheat sheet

Starting points; verify with EXPLAIN and measured recall.

Filter shape                                   Good design
few very large tenants                          partial index or partition per tenant
many small tenants (< ~10k rows each)           one index + iterative scan, or exact scan per tenant
low selectivity (matches most rows)             plain index + post-filter, over-fetch a little
range by time (recent data matters)             time partitions; search only the relevant ones
hard security boundary                          row-level security / mandatory tenant predicate in every query

Verify the plan, not the assumption

After changing indexes or partitions, run EXPLAIN and re-measure recall. Planner choices depend on statistics.

Quick check: For a few very large tenants, which design gives each tenant an accurate index?

• Filtering in the UI
• One index with no tenant column
• No indexes at all
• A partial index (or partition) per tenant