RLS Performance Tuning with Partial Indexes

Row-Level Security is free until the planner stops using your indexes, at which point every query on a large shared table degrades at once — and this page shows how to keep the plans good. It is a focused part of Shared Database with Row-Level Security: how policies interact with the planner, which indexes actually help, and when a partial index is the right tool.

Problem Framing

A policy predicate is appended to every query against the table. USING (tenant_id = current_setting('app.current_tenant_id')::uuid) looks like a cheap equality filter, and usually is — but three things routinely turn it into a sequential scan.

First, the cast. current_setting(...)::uuid is stable rather than immutable, so PostgreSQL evaluates it once per query, which is fine — but if the column is text and the setting is cast to uuid, or vice versa, the types do not match the index and it is skipped entirely. Second, index order: an index on (created_at, tenant_id) cannot serve a tenant-equality filter efficiently, and a shared table almost always needs tenant_id first. Third, statistics: on a table where one tenant holds sixty percent of rows, the planner's default assumption of uniform distribution produces a plan tuned for the average tenant and terrible for both extremes.

Partial indexes address a fourth, narrower problem: a query pattern that only ever touches a small subset — open tickets, unbilled events, the last thirty days — where a full index is mostly wasted pages. Indexing only the rows the hot query touches shrinks the index enough to stay cached, which is where the real win comes from.

None of these is exotic. All three appear in almost every shared-table deployment that reports "RLS made us slow".

Step-by-Step Guide

1. Keep the policy predicate simple, typed and leakproof

-- Types match the column exactly; nothing to coerce at runtime.
ALTER TABLE ticket ALTER COLUMN tenant_id TYPE uuid USING tenant_id::uuid;

CREATE POLICY tenant_isolation ON ticket
  USING      (tenant_id = current_setting('app.current_tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

Avoid subqueries and function calls inside the policy. A policy of the form tenant_id IN (SELECT … FROM membership WHERE user_id = …) runs that subquery for every query against the table and blocks several planner optimisations; resolve membership once per request in the application and set a single tenant variable instead.

2. Put the tenant first in every composite index

-- Serves: tenant filter alone, tenant + recency, tenant + status + recency.
CREATE INDEX ticket_tenant_created_idx ON ticket (tenant_id, created_at DESC);
CREATE INDEX ticket_tenant_status_idx  ON ticket (tenant_id, status, created_at DESC);

-- Redundant once the above exist: a tenant-only index is a prefix of both.
DROP INDEX IF EXISTS ticket_tenant_idx;

Because a B-tree index can be used for any prefix of its columns, a (tenant_id, created_at) index also serves a plain tenant_id lookup — so single-column tenant indexes are usually dead weight that still costs write throughput.

3. Add a partial index where the hot query touches a fraction of rows

-- 4% of tickets are open, but they are 80% of the reads.
CREATE INDEX ticket_open_idx ON ticket (tenant_id, updated_at DESC)
  WHERE status IN ('open', 'pending');

-- Unbilled usage: the billing run only ever looks at rows not yet invoiced.
CREATE INDEX usage_unbilled_idx ON usage_event (tenant_id, occurred_at)
  WHERE invoiced_at IS NULL;

The predicate in the index must be implied by the query's own WHERE clause for the planner to use it — status IN ('open','pending') in the index matches a query filtering status = 'open', but a query filtering on a variable the planner cannot evaluate will not match. Partial indexes reward query patterns that are stable and explicit.

4. Fix the statistics for skewed tenants

-- Default target of 100 buckets cannot describe a distribution with one 60% tenant.
ALTER TABLE ticket ALTER COLUMN tenant_id SET STATISTICS 1000;
ANALYZE ticket;

-- Correlated columns: the planner assumes independence and underestimates badly.
CREATE STATISTICS ticket_tenant_status (dependencies, ndistinct)
  ON tenant_id, status FROM ticket;
ANALYZE ticket;

Extended statistics are the underused tool here. When status correlates with tenant_id — one tenant's workflow leaves everything in pending — the planner's independence assumption produces row estimates that are wrong by an order of magnitude, and a wrong estimate is what makes it choose a sequential scan.

5. Verify the plan, not the intention

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, subject, updated_at
FROM   ticket
WHERE  status = 'open'
ORDER  BY updated_at DESC
LIMIT  50;

--  Limit
--    ->  Index Scan using ticket_open_idx on ticket
--          Index Cond: (tenant_id = '…'::uuid)
--          Buffers: shared hit=7
-- The RLS predicate appears as the Index Cond — that is the outcome you want.

If the tenant predicate shows up as a Filter rather than an Index Cond, the index is not being used for it and every query is reading more rows than it returns.

The words to look for are `Index Cond`. A tenant predicate appearing under `Filter` means the whole table is being read to answer one tenant's question.

Verification

-- 1. Tables with RLS but no tenant-leading index — the highest-value list you can produce.
SELECT c.relname
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace AND n.nspname = 'public'
WHERE  c.relrowsecurity
  AND  NOT EXISTS (
         SELECT 1 FROM pg_index i
         JOIN   pg_attribute a ON a.attrelid = c.oid AND a.attnum = i.indkey[0]
         WHERE  i.indrelid = c.oid AND a.attname = 'tenant_id');

-- 2. Sequential scans on large RLS tables — the symptom, ranked.
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       seq_tup_read / nullif(seq_scan, 0) AS avg_rows_per_seq_scan
FROM   pg_stat_user_tables
WHERE  n_live_tup > 100000
ORDER  BY seq_tup_read DESC LIMIT 20;

-- 3. Partial indexes that are never used — pure write overhead.
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0 AND indexrelname LIKE '%_open_%' OR indexrelname LIKE '%_unbilled_%';

Run the first query in CI against a migrated schema. A new tenant-scoped table without a tenant-leading index is a latent performance incident, and catching it at review time costs nothing.

A partial index over four percent of the rows is small enough to stay resident, which is where the two-orders-of-magnitude latency difference actually comes from.

Failure Modes & Gotchas

Symptom: every query on a large table became slow when RLS was enabled. Cause: the tenant column type differs from the policy's cast, so no index matches. Fix: make the column and the cast the same type, then re-check the plan.
Symptom: the plan is good for most tenants and terrible for the biggest one. Cause: default statistics cannot represent a skewed distribution. Fix: raise the statistics target on the tenant column and add extended statistics for correlated columns.
Symptom: a partial index exists but is never used. Cause: the query's predicate does not imply the index predicate — often because the filter value is a parameter. Fix: make the constant explicit in the query, or widen the index predicate to something the planner can prove.
Symptom: write throughput dropped after adding indexes. Cause: several overlapping indexes now have to be maintained on every insert. Fix: drop single-column tenant indexes that are prefixes of composite ones, and remove partial indexes with zero scans.

FAQ

Does Row-Level Security have an inherent performance cost? The predicate itself is negligible — it is an equality comparison the planner folds into the scan. What costs is everything around it: a mismatched type, a missing tenant-leading index, or a policy containing a subquery. Deployments that report RLS as slow almost always have one of those three, and fixing it returns performance to the same level as an application-side WHERE tenant_id = $1.

Should very large tenants get their own partition instead? Partitioning by tenant hash helps when a handful of tenants dominate a table, because it bounds each scan to one partition and keeps per-partition statistics accurate. It is not a general answer: with thousands of tenants the partition count becomes a planning cost of its own. Reserve it for tables where a small number of tenants hold most of the rows, and reach for promotion to a dedicated tier before sub-partitioning by tenant across the board.

Is FORCE ROW LEVEL SECURITY expensive? No — it changes who the policy applies to, not how it is evaluated. It makes the policy apply to the table owner as well, which is a correctness property rather than a performance one, and without it a migration or an owner-run query silently reads every tenant. Enable it everywhere, and keep the application role NOBYPASSRLS.

How should index bloat be monitored on a shared multi-tenant table? Watch the ratio of index size to table size and the scan counts together, because either number alone is misleading. An index that has grown to a large fraction of the table and is never scanned is pure write overhead; one that is heavily scanned justifies its size regardless. Reindexing concurrently on a schedule addresses bloat from update-heavy workloads, and dropping unused indexes addresses the rest — both of which are easier to justify when the numbers are already on a dashboard rather than gathered during an incident.