Composite Indexes: The Missing Piece in RLS Performance

You added an index on tenant_id. Your RLS policy still runs slow. That's not a fluke, and it's not because Row Level Security is inherently broken, it's because a single-column index can only ever solve half the problem your query is actually asking the planner to solve.

If you've been through reading your EXPLAIN ANALYZE output and spotted a large "Rows Removed by Filter" number sitting right next to a perfectly valid Index Cond, you've already found the symptom. This article is about the cure: composite indexes, built in the correct column order, that match your actual combined filter pattern instead of just the RLS policy in isolation.

Table of Contents

1. Why a Single-Column Index on tenant_id Isn't Enough
2. Index Cond vs. Filter: The Mechanical Difference That Matters
3. B-Tree Column Ordering: What Goes First?
4. Building the Composite Index Step by Step
5. Before and After: Reading the Query Plan Transformation
6. The Cascade Delete Gotcha Composite Indexes Don't Fix
7. When You Should NOT Add a Composite Index
8. Quick Answers About Composite Indexes and RLS
9. Frequently Asked Questions

Why a Single-Column Index on tenant_id Isn't Enough

Simply put, a single-column index on tenant_id only helps Postgres find rows belonging to the current tenant, it does nothing for whatever else your query is filtering, sorting, or joining on. RLS injects its own predicate underneath your query, but your application still asks its own questions on top of that, things like status = 'active' or ORDER BY created_at DESC.

Here's the thing: the planner has to satisfy both conditions, but an index on tenant_id alone can only narrow the search to "all rows for this tenant." Everything else gets checked row by row after the index has done its job. On a small table you won't notice. On a table with a few million rows per tenant, that gap becomes the entire performance problem.

This is exactly the pattern developers describe on r/PostgreSQL when they say RLS is "too slow, even on the simplest condition... with multiple indexes on fields used in the query." They've indexed the right columns individually. The planner just can't combine two separate single-column indexes as efficiently as one properly ordered composite index, especially once row estimates get large enough that the planner starts distrusting a bitmap AND of two indexes and falls back to a sequential scan instead.

In short: a single-column tenant index gets you into the neighborhood. A composite index gets you to the exact address.

Index Cond vs. Filter: The Mechanical Difference That Matters

Simply put, Index Cond is a condition the index itself resolves during the scan, while Filter is a condition Postgres checks after pulling a row out of the table heap. That distinction is the entire reason composite indexes matter for RLS.

When you run a query under an RLS policy with only a tenant_id index in place, a typical plan looks like this:

-- Simplified EXPLAIN ANALYZE output with a single-column index
Bitmap Heap Scan on orders
  Recheck Cond: (tenant_id = 482)
  Filter: (status = 'active'::text)
  Rows Removed by Filter: 48213
  Buffers: shared hit=1204
  ->  Bitmap Index Scan on idx_orders_tenant_id
        Index Cond: (tenant_id = 482)

Notice the shape: Index Cond resolves the tenant match, and Filter catches the status = 'active' condition after the fact. The Rows Removed by Filter: 48213 line is the smoking gun. Postgres pulled 48,213 rows off the heap that it then had to throw away because they didn't match the application's own filter. That's wasted I/O, wasted CPU, and it happens on every single execution of that query.

A high "Rows Removed by Filter" count next to a valid Index Cond is the definitive signal that you need a composite index, not a second single-column one.

B-Tree Column Ordering: What Goes First?

Simply put, the leftmost column in a composite B-tree index should be the one with an equality condition and the highest selectivity, and everything after it should follow the order your query actually filters or sorts by. PostgreSQL's official documentation is explicit that a multicolumn B-tree index is most efficient when constraints are applied to the leading columns, since equality constraints on leading columns define the boundaries of the scan, while constraints on columns further right are checked inside the index without needing extra heap visits, but they don't reduce how much of the index gets scanned.

For an RLS-protected table, that generally means:

1. tenant_id first: it's an equality match, and in a multi-tenant system it's usually your highest-cardinality, most selective column
2. The application's equality filter next (e.g. status): if the query also filters by an exact value
3. The sort or range column last (e.g. created_at): range and ORDER BY conditions belong at the end of the column list

So for a query filtering on tenant and status, then sorting by creation date, the index should be built as (tenant_id, status, created_at), not the other way around. Flip the order and Postgres can still use the index, but it loses the ability to satisfy the sort or the secondary filter as efficiently, and you'll see a Sort node or a Filter step reappear in the plan.

PostgreSQL's own documentation adds a caution worth taking seriously here: "Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time. Indexes with more than three columns are unlikely to be helpful." That's not a reason to avoid composite indexes for RLS, tenant_id plus one or two application columns is exactly the sparing use case they're describing, it's a reason not to bolt on a fourth or fifth column just because a query happens to touch it.

Summary: equality columns lead, range and sort columns trail, and three columns is a practical ceiling for most RLS composite indexes.

Building the Composite Index Step by Step

Here's the practical version. Say you have an orders table with an RLS policy scoped to tenant_id, and your application dashboard query filters by status and sorts by created_at.

-- The RLS policy itself, for context
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::int);

-- The application query this composite index targets
SELECT id, customer_name, total_amount, created_at
FROM orders
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 50;

-- The composite index: tenant_id first (RLS equality),
-- status second (app equality), created_at last (sort column)
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

A few details that matter in production:

• Always use CREATE INDEX CONCURRENTLY on a live table, it avoids locking writes while the index builds, though it does take longer and can't run inside a transaction block
• Match the sort direction in the index definition (DESC above) to your actual ORDER BY so Postgres can use the index for sorting instead of adding a separate Sort node
• Drop the now-redundant single-column tenant_id index once you've confirmed the composite index is being used and covers every query that relied on it, carrying both indexes forever just adds write overhead with no read benefit

Before and After: Reading the Query Plan Transformation

This is where the fix proves itself. Before the composite index, the plan shows a Bitmap Heap Scan doing real work to filter out rows the index couldn't rule out on its own:

-- BEFORE: single-column tenant_id index
Bitmap Heap Scan on orders  (cost=245.32..3891.07 rows=52 width=64)
  Recheck Cond: (tenant_id = 482)
  Filter: (status = 'active'::text)
  Rows Removed by Filter: 48213
  Buffers: shared hit=1204
  ->  Bitmap Index Scan on idx_orders_tenant_id  (cost=0.00..245.31 rows=51200 width=0)
        Index Cond: (tenant_id = 482)

After building the composite index that covers tenant_id, status, and created_at together, the same query resolves entirely inside the index structure:

-- AFTER: composite index (tenant_id, status, created_at)
Index Only Scan using idx_orders_tenant_status_created on orders
  (cost=0.42..8.55 rows=52 width=64) (actual time=0.031..0.124 rows=50 loops=1)
  Index Cond: ((tenant_id = 482) AND (status = 'active'::text))
  Heap Fetches: 0
  Buffers: shared hit=4

Both the tenant filter and the status filter now show up inside a single Index Cond, there's no separate Filter step at all, and Heap Fetches: 0 means Postgres never had to touch the table heap. Buffer usage dropped from 1,204 shared hits to 4. That's the mechanical difference a correctly ordered composite index makes, and it's the exact transformation documented in independent testing on multi-tenant RLS schemas, where switching from a single-column index to a matching composite index cut execution time to roughly 0.124 milliseconds with a full Index Only Scan.

The Cascade Delete Gotcha Composite Indexes Don't Fix

Simply put, composite indexes speed up reads but they don't help, and can actually get bypassed during, cascade deletes, because foreign key constraint enforcement runs at the system trigger level with elevated privileges that skip your RLS policy entirely.

When a parent row is deleted and ON DELETE CASCADE fires on a child table, Postgres checks referential integrity by looking up rows matching the foreign key column alone, not the RLS-filtered combination you built your composite index around. If your composite index is ordered (tenant_id, product_id), the cascade delete's lookup on product_id in isolation may not use that index efficiently at all, since product_id isn't the leading column.

This is a genuine trade-off, not a bug you can patch away. If a table sees heavy cascade delete activity (bulk cleanup jobs, tenant offboarding, retention policies), you may need a second, separate single-column index on the foreign key itself purely to keep delete performance acceptable, even though that index does nothing for your RLS-filtered reads. Weigh read frequency against delete frequency before deciding whether that second index is worth the extra write overhead.

Takeaway: a composite index that's excellent for reads can be quietly useless for cascade deletes, because the two operations don't share a column-order requirement.

When You Should NOT Add a Composite Index

Composite indexes aren't free. Simply put, skip one when the write overhead outweighs the read benefit, or when the query pattern doesn't justify a dedicated index at all.

1. Low-cardinality tenant columns: if tenant_id only has a handful of distinct values across a small table, the planner may reasonably prefer a sequential scan regardless of what indexes exist
2. Write-heavy tables with rare reads: every additional index adds overhead to every INSERT, UPDATE, and DELETE, if a table is written to constantly but queried rarely, a composite index may cost more than it saves
3. Query patterns that vary too much: if your application filters on a different combination of columns on almost every call, you'll end up needing several composite indexes to cover them all, at which point it's worth reconsidering the query design itself rather than indexing your way out of it
4. You already have four or more columns in mind: per the official guidance above, indexes beyond three columns rarely pay for themselves, that's usually a sign the query needs restructuring, not a wider index

Quick Answers About Composite Indexes and RLS

What Causes RLS Queries to Stay Slow Even With an Index?

Simply put, a single-column index on the RLS policy column only resolves the tenant match, leaving every other application-level filter to be checked row by row after the heap fetch. This shows up in EXPLAIN ANALYZE as a large "Rows Removed by Filter" value next to a valid Index Cond. It matters most on large multi-tenant tables where individual tenants hold hundreds of thousands of rows or more.

Composite Indexes at a Glance

When Does This Apply?

This applies once a table holds enough rows per tenant that a single-column index leaves a meaningful number of rows to filter after the heap fetch, typically tens of thousands of rows per tenant or more. Small tables, low-traffic tenants, or tables with only the RLS filter and no additional WHERE/ORDER BY clauses don't need this.

Pros and Cons of Composite RLS Indexes

• Pro: Eliminates the post-index Filter step entirely for matching queries
• Pro: Enables Index Only Scans, cutting heap fetches to zero
• Pro: Scales the same query pattern to much larger per-tenant row counts

• Con: Adds write overhead on every INSERT/UPDATE/DELETE touching indexed columns
• Con: Doesn't help, and can hurt, cascade delete performance on the same table
• Con: Only helps the specific column combination and order it was built for

Frequently Asked Questions

Can row level security use composite indexes?

Yes. PostgreSQL's RLS policies are evaluated as regular query predicates, so any index, including a composite index covering the policy column and application filters, can be used to satisfy them when the column order matches the query.

Does RLS bypass indexes in Postgres?

No, RLS doesn't bypass indexes. It adds a mandatory filter condition to the query, and the planner chooses indexes the same way it would for any other WHERE clause, based on selectivity and cost estimates.

Why is my PostgreSQL query not using an index despite having one on tenant_id?

Usually because the index only covers the RLS column, leaving your application's other filters or sort to run as a post-scan Filter step, or because the planner estimates too many matching rows and prefers a sequential scan instead.

How do I optimize row level security in PostgreSQL?

Build composite indexes matching your actual combined filter pattern, keep policy functions STABLE and wrapped in a SELECT subquery for caching, avoid joins inside policy expressions, and confirm the fix with EXPLAIN ANALYZE rather than assuming it worked.

What order should columns go in a composite RLS index?

Equality conditions first, starting with the highest-selectivity column (usually tenant_id), followed by other equality filters, with range or ORDER BY columns placed last.

Does adding a composite index slow down writes?

Yes, every index adds overhead to inserts, updates, and deletes on the columns it covers. The read performance gain on large multi-tenant tables usually outweighs this, but it's worth measuring on write-heavy tables.

Will a composite index fix slow cascade deletes too?

Not necessarily. Cascade deletes check foreign key columns directly and bypass RLS policies at the trigger level, so a composite index ordered around your RLS column may not be the leading column the delete needs.

How many columns should a composite index have?

Two or three is the practical ceiling for most RLS use cases. PostgreSQL's own documentation notes that indexes beyond three columns are unlikely to help and usually signal the query itself needs restructuring.

Composite indexes are the fix that turns "I indexed tenant_id and it's still slow" into a query the planner can resolve entirely inside the B-tree. The pattern is straightforward once you see it in an execution plan: lead with your highest-selectivity equality column, follow with the rest of your query's filters and sort order, and confirm with EXPLAIN ANALYZE rather than assuming it worked. If you haven't already, it's worth revisiting how to read your own query plans so you can spot this pattern the next time a table grows past the point a single-column index can handle. This fits into the broader picture laid out in our complete guide to multi-tenant Supabase architecture, indexing decisions like this one are part of the same schema design choices that determine whether RLS scales cleanly or becomes a bottleneck as your tenant count grows.