Index strategy for org_id-style multi-tenant policies

The baseline: one b-tree led by the tenant column

Start every tenant-scoped table with a single composite b-tree index led by the tenant column, followed by the column your busiest screen sorts or ranges on. For a events table listed newest-first per organisation, that is (org_id, created_at DESC). The policy predicate then resolves through the index's leading column, and the same index serves the ORDER BY without a sort node. Build it concurrently on a live table, outside any transaction block:

CREATE INDEX CONCURRENTLY idx_events_org_id_created_at
  ON public.events (org_id, created_at DESC);

-- The policy it serves, wrapped per the InitPlan pattern:
ALTER POLICY "org_reads_events"
  ON public.events
  FOR SELECT
  TO authenticated
  USING ((SELECT auth.jwt() ->> 'org_id') = org_id);

Match composites to real query shapes

Ordering inside the composite follows the equality-first rule. Conditions joined by equality should occupy the earliest positions in any order among themselves; range and sort columns come after. A screen that filters org_id and status, then sorts by created_at, wants (org_id, status, created_at DESC). Resist creating one composite per screen — each extra index taxes every insert and update on the table — and instead let one well-chosen index serve several screens where the leading columns coincide. A second index earns its place only when a frequent query shares no usable prefix with the first.

Partials, uniqueness and restraint

Two refinements cover most remaining cases. Partial indexes shrink an index to the slice that queries actually traverse: a listing that only ever shows live rows justifies an index with a WHERE archived = false clause, buying smaller size and hotter cache residency for the common path. Unique constraints double as indexes — UNIQUE (org_id, slug) both enforces per-tenant uniqueness and serves the policy predicate on org_id, so a constraint you needed anyway may be the index you thought you lacked.

Restraint is the final discipline. Indexes on a write-heavy table cost maintenance on every insert and update, and a pile of overlapping composites slows the writes to speed up reads that one correct index would serve. When you add an index, retire the ones it obsoletes — the catalog will happily carry a dozen near-duplicates forever if nobody prunes.

-- Constraint doubling as an index:
ALTER TABLE public.projects
  ADD CONSTRAINT projects_org_slug_key UNIQUE (org_id, slug);

-- Partial index for the hot slice:
CREATE INDEX CONCURRENTLY idx_tasks_org_open
  ON public.tasks (org_id, due_date)
  WHERE completed_at IS NULL;

Verify the planner agrees

An index that exists is not an index that is used. Confirm with EXPLAIN (ANALYZE, BUFFERS) on representative queries that the plan descends through the expected index rather than scanning, and revisit after data volume changes — planners choose differently at different table sizes, correctly. For long-run confirmation rather than spot checks, the usage counters are informative: an index whose scan count never moves while the table's sequential-scan counter climbs is decorative, and decorative indexes still charge rent on every write.

SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY seq_scan DESC;

Where automation stops and starts

Choosing composite orders, partials and pruning duplicates is judgement work that RowShield does not automate — this page says so plainly. What it does continuously is guard the floor underneath those decisions: every column referenced by a policy must appear in some valid index, and UNINDEXED_RLS_PREDICATE enforces exactly that on every scheduled scan, reporting once per table and column with the concurrent CREATE INDEX statement generated from your schema. RowShield reads pg_catalog metadata only and never your rows, and is an independent product, not affiliated with or endorsed by Supabase. Run a free audit at rowshield.dev/audit — no account required.

Frequently asked

Should org_id lead every composite index on the table?

Usually, yes — nearly every query against a tenant-scoped table includes the tenant, and a leading position lets one index serve both the policy predicate and the query's own filters. Indexes not led by the tenant column earn their keep only for genuinely tenant-independent access paths.

Does a unique constraint on (org_id, slug) cover the policy?

Yes. Postgres implements unique constraints with a unique index, so the constraint you wanted for correctness simultaneously provides an index whose leading column satisfies a policy filtering org_id.

How many indexes per tenant table is reasonable?

As few as the real query shapes require — commonly one or two composites plus any uniqueness constraints. Judge additions by whether an existing index can serve the query through its leading columns, and prune indexes that the usage counters show are ignored.