Postgres Partial Indexes for Multi-Tenant RLS Performance

The Silent Performance Killer: Generic Indexes vs. Row-Level Security

In any mature multi-tenant SaaS application running on PostgreSQL, implementing data isolation via Row-Level Security (RLS) is a standard architectural pattern. It provides a robust, database-enforced barrier between tenants. However, what often follows this security enhancement is a gradual, then sudden, degradation of query performance that leaves teams baffled. The culprit is frequently a fundamental mismatch between how generic indexes are structured and how RLS policies operate.

Let's establish a concrete, production-style schema. We're building a project management tool where organizations are tenants.

-- Enable RLS on the table
CREATE TABLE organizations (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE users (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    organization_id BIGINT NOT NULL REFERENCES organizations(id),
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE tasks (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    organization_id BIGINT NOT NULL REFERENCES organizations(id),
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'todo', -- 'todo', 'in_progress', 'done', 'archived'
    priority TEXT, -- 'low', 'medium', 'high'
    due_date DATE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create a standard index, as one might initially do.
CREATE INDEX tasks_status_idx ON tasks (status);
CREATE INDEX tasks_organization_id_idx ON tasks (organization_id);

-- Populate with a significant amount of data for realism
-- (Imagine 1000 organizations with 10,000 tasks each = 10M tasks)
INSERT INTO organizations (name) SELECT 'Organization ' || i FROM generate_series(1, 1000) i;
INSERT INTO tasks (organization_id, title, status, priority, due_date)
SELECT
    floor(random() * 999) + 1,
    'Task ' || i,
    (ARRAY['todo', 'in_progress', 'done', 'archived'])[floor(random() * 4) + 1],
    (ARRAY['low', 'medium', 'high'])[floor(random() * 3) + 1],
    NOW()::DATE + (floor(random() * 90) - 30) * INTERVAL '1 day'
FROM generate_series(1, 10000000) i;

Now, we implement RLS. The standard pattern is to use a runtime configuration parameter (e.g., app.current_organization_id) to hold the current tenant's ID for the duration of a session.

-- Enable RLS for the tasks table
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see tasks belonging to their organization
CREATE POLICY tasks_isolation_policy ON tasks
FOR ALL
USING (organization_id = current_setting('app.current_organization_id')::BIGINT);

In your application's connection logic, you'd set this for every transaction:

// Example in Node.js with 'pg' library
async function queryAsTenant(organizationId, sql, params) {
  const client = await pool.connect();
  try {
    await client.query(`SET LOCAL app.current_organization_id = ${organizationId}`);
    const result = await client.query(sql, params);
    return result.rows;
  } finally {
    client.release();
  }
}

The Problem Unveiled with `EXPLAIN ANALYZE`

A typical query is to fetch all active, high-priority tasks for the current user's organization.

-- Set the context for our test
SET app.current_organization_id = '123';

EXPLAIN ANALYZE
SELECT * FROM tasks
WHERE status = 'in_progress' AND priority = 'high';

Let's analyze the likely EXPLAIN output for this query on our 10M row table:

Gather  (cost=1000.43..163351.45 rows=2083 width=61) (actual time=15.228..341.135 rows=2091 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Bitmap Heap Scan on tasks  (cost=0.43..162330.62 rows=868 width=61) (actual time=11.583..325.271 rows=697 loops=3)
        Recheck Cond: (status = 'in_progress'::text AND priority = 'high'::text)
        Rows Removed by Index Recheck: 1243560
        Filter: (organization_id = current_setting('app.current_organization_id')::bigint)
        Rows Removed by Filter: 8312
        Heap Blocks: exact=14328
        ->  Bitmap Index Scan on tasks_status_idx  (cost=0.00..0.43 rows=1 width=0) (actual time=10.452..10.453 rows=25011 loops=1)
              Index Cond: (status = 'in_progress'::text AND priority = 'high'::text) -- Assuming a composite index for this example
Planning Time: 0.215 ms
Execution Time: 341.567 ms

Dissecting the Inefficiency:

This problem compounds. As your table grows, the index scan and heap fetches become slower, and your query latency climbs, even though each tenant's individual dataset remains small.

The Advanced Solution: Tenant-Aware Partial Indexes

The naive solution might be to create an index for each tenant. This is a catastrophic anti-pattern. It leads to unmanageable DDL, massive index bloat, and performance degradation during writes and maintenance. Never do this.

The professional solution is to create indexes that align with your application's most frequent access patterns, acknowledging the multi-tenant reality.

Most SaaS applications exhibit a strong data temperature pattern: 95% of queries target a small, "hot" subset of data (e.g., active, non-archived, recent items). We can exploit this by creating partial indexes that cover only this hot data.

Strategy 1: Partial Index on Active Statuses

Let's assume most user-facing queries are for tasks that are 'todo' or 'in_progress'. Tasks that are 'done' or 'archived' are queried far less frequently.

First, let's drop the inefficient index and create a new, much smarter one.

DROP INDEX tasks_status_idx;

-- This is the key insight. The index includes the tenant ID as the *first* column.
CREATE INDEX tasks_active_by_org_idx
ON tasks (organization_id, priority, due_date)
WHERE status IN ('todo', 'in_progress');

Why this index is superior:

Rewriting the Query for Planner-Friendliness

To leverage this index, we must slightly modify our query pattern. We need to make the planner aware of the organization_id directly in the WHERE clause, rather than relying solely on the "hidden" RLS policy.

This doesn't defeat the purpose of RLS. RLS is our security backstop; it guarantees isolation. The explicit WHERE clause is a performance optimization to guide the planner.

-- The application must now provide the organization_id as a query parameter.
-- This is good practice anyway for clarity and testability.

-- Set context (as before)
SET app.current_organization_id = '123';

EXPLAIN ANALYZE
SELECT * FROM tasks
WHERE
  organization_id = '123' -- Explicit performance hint for the planner
  AND status = 'in_progress'
  AND priority = 'high';

Now, witness the new EXPLAIN plan:

Index Scan using tasks_active_by_org_idx on tasks  (cost=0.56..8.76 rows=1 width=61) (actual time=0.045..0.051 rows=5 loops=1)
  Index Cond: ((organization_id = 123) AND (priority = 'high'::text))
  Filter: (status = 'in_progress'::text)
Planning Time: 0.281 ms
Execution Time: 0.089 ms

Performance Deconstructed:

* Execution Time: Down from 341ms to 0.089ms. This is a ~3800x improvement.

* Index Scan: The planner immediately selects our new partial index.

* No Heap Scan: It's a pure Index Scan. The B-tree is traversed directly to the block for organization_id = 123, and then it efficiently finds the matching rows within that small section of the index.

* No RLS Filter step in the plan: The explicit WHERE organization_id = 123 clause is a superset of the RLS policy's condition. The planner is smart enough to see this and knows the RLS check will always pass, so it doesn't even show it as a separate filter step. The security is still enforced, but at zero performance cost for this query.

Strategy 2: Handling "Cold" Data with BRIN Indexes

Partial indexes are a scalpel, not a sledgehammer. Our tasks_active_by_org_idx is useless for querying archived tasks. For these less frequent, analytical-style queries, a different tool is required: the Block Range Index (BRIN).

BRIN indexes are ideal for large tables where data has a strong natural correlation with its physical storage order. Time-series data (like our tasks table with created_at) is a perfect use case.

A BRIN index stores the minimum and maximum value for a large block of pages (e.g., 128 pages). It's incredibly small but less precise than a B-tree. When you query a range, Postgres checks the BRIN index to see which blocks might contain matching rows, and then only scans those blocks.

Let's create a partial BRIN index for our archived data.

CREATE INDEX tasks_archived_by_date_brin_idx
ON tasks USING brin (created_at)
WHERE status = 'archived';

Now, consider a query for archived tasks from last year for a specific tenant.

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM tasks
WHERE organization_id = '123'
  AND status = 'archived'
  AND created_at >= '2023-01-01' AND created_at < '2024-01-01';

The plan would look something like this:

Bitmap Heap Scan on tasks  (cost=124.87..4361.98 rows=412 width=8) (actual time=2.345..15.812 rows=420 loops=1)
  Recheck Cond: (created_at >= '2023-01-01 00:00:00+00' AND created_at < '2024-01-01 00:00:00+00')
  Filter: (organization_id = 123 AND status = 'archived'::text)
  Rows Removed by Filter: 16500
  Heap Blocks: lossy=1536
  ->  Bitmap Index Scan on tasks_archived_by_date_brin_idx  (cost=0.00..124.77 rows=16667 width=0) (actual time=1.987..1.988 rows=16920 loops=1)
        Index Cond: (created_at >= '2023-01-01 00:00:00+00' AND created_at < '2024-01-01 00:00:00+00')
Planning Time: 0.312 ms
Execution Time: 16.011 ms

Without the BRIN index, this query would require a full parallel sequential scan of the entire tasks table, taking seconds or even minutes. With the BRIN index, Postgres can quickly eliminate the vast majority of table blocks from consideration, resulting in a reasonably fast query, perfectly acceptable for an occasional report or data export feature.

This hybrid approach gives you the best of both worlds: lightning-fast B-tree partial indexes for latency-sensitive UI queries and space-efficient BRIN indexes for analytical queries on cold data.

Advanced Edge Cases & Production Hardening

Implementing this pattern requires attention to detail.

1. The `NULL` Value Optimization

Partial indexes are exceptionally useful for enforcing uniqueness on columns that allow NULLs. A standard UNIQUE constraint doesn't work because NULL is not equal to NULL. But a partial unique index does:

-- Imagine users can optionally have a unique 'vanity_url_slug'
ALTER TABLE organizations ADD COLUMN vanity_url_slug TEXT;

-- Enforce uniqueness only for organizations that have set a slug.
CREATE UNIQUE INDEX organizations_vanity_url_slug_unique_idx
ON organizations (vanity_url_slug)
WHERE vanity_url_slug IS NOT NULL;

This is a common and powerful pattern that avoids application-level race conditions when checking for uniqueness.

2. Connection Pooling and `SET LOCAL`

The use of SET LOCAL app.current_organization_id = ... is critical. SET LOCAL scopes the setting to the current transaction. When the transaction commits or rolls back, the setting is reverted. This is essential for correctness in a concurrent application using a connection pool.

If you were to use SET (without LOCAL), the setting would persist for the entire session. When your application server returns that database connection to the pool, the next request (potentially for a different tenant) could pick it up and inherit the wrong organization_id, silently breaking your data isolation.

Your application framework's middleware must be structured to wrap requests in a transaction and set the local variable at the start.

# Example in Python with FastAPI and asyncpg

@app.middleware("http")
async def db_session_middleware(request: Request, call_next):
    # org_id would be derived from a JWT token, session, etc.
    org_id = get_org_id_from_request(request)
    
    async with pool.acquire() as connection:
        async with connection.transaction():
            # SET LOCAL is crucial here!
            await connection.execute(f"SET LOCAL app.current_organization_id = {org_id}")
            request.state.db_conn = connection
            response = await call_next(request)
    return response

3. Maintaining Planner Statistics

The PostgreSQL query planner relies on statistics about your data distribution. For partial indexes, it also maintains separate statistics for that subset of data. It's vital that ANALYZE is run regularly, especially after large data changes, to keep these statistics fresh. The standard autovacuum daemon usually handles this, but for tables with unusual write patterns, you may need to tune its configuration or run ANALYZE tasks; manually during maintenance windows.

4. Index Selection and Query Discipline

This strategy requires discipline. Your development team needs to understand that queries against tenant-specific data must include the explicit organization_id = $1 predicate to gain the performance benefit. This can be enforced through code reviews, ORM query builders that add it automatically, or even custom linting rules.

The RLS policy is your safety net, but performant queries are a result of deliberate, index-aware design.

Conclusion: From Reactive to Proactive Performance Tuning

Moving from generic indexes to a multi-layered strategy of tenant-aware partial B-tree indexes and partial BRIN indexes is a significant step in the maturity of a multi-tenant application. It's a shift from reactively fixing slow queries to proactively designing a database schema that is fundamentally aligned with the application's security model and access patterns.

By understanding the planner's limitations with RLS and guiding it with explicit predicates and purpose-built indexes, you can achieve orders-of-magnitude performance improvements. This ensures your application remains fast and responsive as you scale from ten tenants to ten thousand, transforming the database from a potential bottleneck into a robust and performant foundation for your service.