PostgreSQL Partial Indexes for Multi-Tenant RLS Performance

The Inefficiency of Generic Indexes in Segregated Architectures

In any mature multi-tenant SaaS application leveraging a shared database, single-table architecture, the tenant_id column is ubiquitous. It's the primary axis for data segregation, and nearly every query is anchored by a WHERE tenant_id = $1 clause. The conventional wisdom is to create composite indexes starting with tenant_id, such as CREATE INDEX ON documents (tenant_id, status, created_at). This is the correct foundational step.

However, as a table grows to hundreds of millions or billions of rows across tens of thousands of tenants, this pattern reveals its limitations. While the index efficiently locates a specific tenant's data block, the subsequent filtering within that block can still be inefficient. The B-tree for (tenant_id, status) contains entries for every status for every tenant, leading to a massive index that can strain memory and increase I/O, especially for queries that target a very small, specific subset of data.

Consider a tasks table where 95% of tasks are in an 'archived' state, but 99% of application queries are only interested in tasks with 'pending' or 'in_progress' statuses. The generic composite index is bloated with data that is almost never accessed by hot-path queries. The query planner must still traverse large sections of the index related to the current tenant to find the few rows matching the desired status.

This is where partial indexes, a standard but underutilized feature of PostgreSQL, become a surgical performance tool. When combined with the implicit filtering of Row-Level Security (RLS), they allow us to build incredibly small, efficient indexes that cater directly to the most frequent query patterns.

This article assumes you are already proficient with PostgreSQL, multi-tenancy concepts, RLS, and interpreting EXPLAIN ANALYZE output. We will not cover the basics. Instead, we will focus on the advanced interplay between these features to solve real-world performance bottlenecks.

The Baseline: A Typical Multi-Tenant Schema

Let's establish our working schema. We'll model a document management system where documents belong to tenants and have a lifecycle status.

-- Enable a UUID extension for primary keys
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Define a status enum type for clarity and storage efficiency
CREATE TYPE document_status AS ENUM ('draft', 'in_review', 'published', 'archived');

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    content TEXT,
    status document_status NOT NULL DEFAULT 'draft',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- The standard, generic composite index
CREATE INDEX idx_documents_tenant_id_status ON documents (tenant_id, status);

Now, let's populate this with a significant amount of data to simulate a production environment. We'll create 1,000 tenants and give each tenant 10,000 documents, for a total of 10 million documents. We'll skew the data distribution to reflect a realistic scenario where most documents are archived.

-- Generate sample data
-- NOTE: This may take a few minutes to run.
INSERT INTO tenants (id, name)
SELECT uuid_generate_v4(), 'tenant_' || i FROM generate_series(1, 1000) i;

INSERT INTO documents (tenant_id, title, status)
SELECT
    t.id,
    'Document ' || (random() * 100000)::int,
    CASE (random() * 100)::int % 20
        WHEN 0 THEN 'draft'       -- 5%
        WHEN 1 THEN 'in_review'   -- 5%
        WHEN 2 THEN 'published'   -- 5%
        ELSE 'archived'           -- 85%
    END
FROM tenants t, generate_series(1, 10000);

-- Ensure statistics are up-to-date
ANALYZE documents;

Let's pick a random tenant and examine the performance of a common query: fetching all draft documents.

-- Pick a tenant to work with
-- In a real app, this would be set via connection session
DO $$DECLARE 
    _tenant_id UUID;
BEGIN
    SELECT id INTO _tenant_id FROM tenants LIMIT 1;
    -- Set a custom GUC (Grand Unified Configuration) variable for the session
    EXECUTE 'SET app.tenant_id = ''' || _tenant_id || '''';
END$$;

-- Check the query plan for fetching 'draft' documents
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM documents
WHERE tenant_id = current_setting('app.tenant_id')::uuid AND status = 'draft';

Typical EXPLAIN Output (Generic Index):

Index Scan using idx_documents_tenant_id_status on documents  (cost=0.56..55.93 rows=13 width=38) (actual time=0.065..0.219 rows=500 loops=1)
  Index Cond: ((tenant_id = '...'::uuid) AND (status = 'draft'::document_status))
  Buffers: shared hit=21
Planning Time: 0.150 ms
Execution Time: 0.252 ms

At first glance, this looks fine. An index scan is used, and it's fast. But look closer at the Buffers: shared hit=21. The database had to read 21 pages (likely 8KB each, so ~168KB) from memory to find our 500 rows. Why? Because the index contains entries for all statuses (draft, in_review, published, archived). The planner has to navigate the B-tree for our tenant, then scan through all the index entries for that tenant until it finds the draft ones. In a table with billions of rows, this number of buffer hits can easily climb into the thousands, resulting in significant I/O and latency.

Pattern 1: Partial Indexes on Low-Cardinality Statuses

The solution is to create an index that only contains entries for the rows we care about in our hot-path queries. Let's create a partial index specifically for draft documents.

-- Create a hyper-specific index for only draft documents
CREATE INDEX idx_documents_draft ON documents (tenant_id, created_at)
WHERE status = 'draft';

Note that I included created_at in the index. This is a common pattern. If you're fetching a list of items, you're almost certainly going to sort them, typically by creation date. Including it in the index allows for an index-only scan if the query planner decides it's worthwhile, avoiding a heap fetch entirely for the sort operation.

Now, let's re-run our query. PostgreSQL's planner is smart enough to see that a query with WHERE ... status = 'draft' can be satisfied by this new, much smaller index.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM documents
WHERE tenant_id = current_setting('app.tenant_id')::uuid AND status = 'draft'
ORDER BY created_at DESC;

EXPLAIN Output (Partial Index):

Index Scan Backward using idx_documents_draft on documents  (cost=0.42..15.45 rows=13 width=38) (actual time=0.035..0.095 rows=500 loops=1)
  Index Cond: (tenant_id = '...'::uuid)
  Buffers: shared hit=6
Planning Time: 0.180 ms
Execution Time: 0.125 ms

The results are immediate and clear:

The Size Difference

Let's quantify the storage savings, which directly correlates with I/O efficiency.

-- Check the size of the indexes
SELECT pg_size_pretty(pg_relation_size('idx_documents_tenant_id_status')) AS generic_index_size,
       pg_size_pretty(pg_relation_size('idx_documents_draft')) AS partial_index_size;

Our partial index is ~5% of the size of the generic composite index. This is a monumental difference. It's smaller on disk, smaller in memory (PostgreSQL's buffer cache), faster to scan, and faster to maintain during INSERT, UPDATE, and DELETE operations on non-draft rows.

The Interplay with Row-Level Security (RLS)

Now, let's introduce the complexity of RLS. RLS is the gold standard for enforcing tenant isolation at the database layer. It works by implicitly appending a WHERE clause to every query against the protected table.

-- Enable RLS on the documents table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Create a policy that ensures users can only access their own tenant's data
-- This policy uses the session variable we set earlier
CREATE POLICY tenant_isolation_policy ON documents
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);

With RLS enabled, our application code no longer needs to explicitly add WHERE tenant_id = .... The database handles it automatically and securely.

Our query now simplifies to:

-- The application query is now simpler and safer
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM documents
WHERE status = 'draft'
ORDER BY created_at DESC;

How does the planner handle this? It effectively combines the RLS policy's USING clause with the query's WHERE clause. The effective query becomes:

... WHERE (status = 'draft') AND (tenant_id = current_setting('app.tenant_id')::uuid)

PostgreSQL is intelligent enough to recognize that this combined set of conditions is a perfect match for our partial index, idx_documents_draft. The query plan will be identical to the one we saw in the previous section.

This is the critical synergy: RLS provides the security guarantee, and the partial index provides the performance guarantee. You can write clean, tenant-agnostic application code, confident that the database will not only enforce isolation but also use the most efficient data access path available.

Edge Case: Partial Indexes and `NULL` Values

A common pattern in many applications is using NULL to signify a state. For example, a tasks table might have a completed_at timestamp that is NULL for all open tasks. Querying for WHERE completed_at IS NULL is a frequent operation.

Let's model this. Imagine our documents can be optionally assigned to a user for review.

-- Add a nullable foreign key to a (hypothetical) users table
ALTER TABLE documents ADD COLUMN reviewer_id UUID;

-- Create an index to find unassigned documents needing review
CREATE INDEX idx_documents_unassigned_for_review ON documents (tenant_id)
WHERE status = 'in_review' AND reviewer_id IS NULL;

-- Populate some data for this scenario
UPDATE documents SET status = 'in_review' WHERE random() < 0.05;
ANALYZE documents;

This index is highly specific: it only includes rows for documents that are in_review AND are unassigned. This is perfect for building a dashboard of work to be claimed.

Let's query it, relying on our RLS policy for tenant isolation:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM documents
WHERE status = 'in_review' AND reviewer_id IS NULL;

EXPLAIN Output (NULL-aware Partial Index):

Index Scan using idx_documents_unassigned_for_review on documents  (cost=0.42..30.15 rows=25 width=26) (actual time=0.041..0.150 rows=249 loops=1)
  Buffers: shared hit=10
Planning Time: 0.211 ms
Execution Time: 0.178 ms

Again, the planner correctly identifies and uses our specialized index. Without it, it would have to use the generic idx_documents_tenant_id_status, find all in_review documents for the tenant, and then perform a secondary filter in memory to discard the ones that have a reviewer_id. The partial index approach is vastly more efficient as the dataset scales.

Performance Benchmarking: The Hard Numbers

Let's formalize our performance comparison with a more rigorous benchmark. We will compare three scenarios for fetching draft documents for a random tenant:

We'll use a plpgsql script to simulate 1000 queries for each scenario and record the average execution time.

-- Ensure we're starting clean
DROP INDEX IF EXISTS idx_documents_tenant_id_status;
DROP INDEX IF EXISTS idx_documents_draft;

-- Scenario 1: Sequential Scan (No Index)
DO $$ 
DECLARE
    _tenant_id UUID;
    start_time TIMESTAMPTZ;
    total_duration INTERVAL := '0 ms';
BEGIN
    FOR i IN 1..1000 LOOP
        SELECT id INTO _tenant_id FROM tenants ORDER BY random() LIMIT 1;
        PERFORM set_config('app.tenant_id', _tenant_id::text, false);
        start_time := clock_timestamp();
        PERFORM id FROM documents WHERE status = 'draft';
        total_duration := total_duration + (clock_timestamp() - start_time);
    END LOOP;
    RAISE NOTICE 'Seq Scan Avg. Duration: %', total_duration / 1000;
END $$;

-- Scenario 2: Generic Composite Index
CREATE INDEX idx_documents_tenant_id_status ON documents (tenant_id, status);
ANALYZE documents;
DO $$ 
DECLARE
    _tenant_id UUID;
    start_time TIMESTAMPTZ;
    total_duration INTERVAL := '0 ms';
BEGIN
    FOR i IN 1..1000 LOOP
        SELECT id INTO _tenant_id FROM tenants ORDER BY random() LIMIT 1;
        PERFORM set_config('app.tenant_id', _tenant_id::text, false);
        start_time := clock_timestamp();
        PERFORM id FROM documents WHERE status = 'draft';
        total_duration := total_duration + (clock_timestamp() - start_time);
    END LOOP;
    RAISE NOTICE 'Generic Index Avg. Duration: %', total_duration / 1000;
END $$;
DROP INDEX idx_documents_tenant_id_status;

-- Scenario 3: Partial Index
CREATE INDEX idx_documents_draft ON documents (tenant_id) WHERE status = 'draft';
ANALYZE documents;
DO $$ 
DECLARE
    _tenant_id UUID;
    start_time TIMESTAMPTZ;
    total_duration INTERVAL := '0 ms';
BEGIN
    FOR i IN 1..1000 LOOP
        SELECT id INTO _tenant_id FROM tenants ORDER BY random() LIMIT 1;
        PERFORM set_config('app.tenant_id', _tenant_id::text, false);
        start_time := clock_timestamp();
        PERFORM id FROM documents WHERE status = 'draft';
        total_duration := total_duration + (clock_timestamp() - start_time);
    END LOOP;
    RAISE NOTICE 'Partial Index Avg. Duration: %', total_duration / 1000;
END $$;
DROP INDEX idx_documents_draft;

Benchmark Results (on a test machine):

While the jump from sequential scan to any index is obviously massive, the improvement from a generic index to a partial index is still significant—nearly 3x faster. In a system handling thousands of requests per second, this is the difference between a stable system and one that is constantly on the verge of latency-induced failure.

Write Performance Considerations

Indexes are not free; they come with a write penalty. Every INSERT, UPDATE, or DELETE on the documents table requires updating all relevant indexes.

This reduced write amplification is a crucial secondary benefit, leading to faster write operations and less database load overall.

Production Strategy: How to Identify and Deploy Partial Indexes

You should not start by creating partial indexes for every possible query. This leads to index bloat and maintenance headaches. The correct approach is data-driven and iterative.

    -- You may need to run: CREATE EXTENSION pg_stat_statements;
    -- And configure postgresql.conf to load it.

    SELECT
        (total_exec_time / 1000 / 60) as total_minutes,
        calls,
        mean_exec_time,
        query
    FROM pg_stat_statements
    ORDER BY total_exec_time DESC
    LIMIT 10;

* High in total_exec_time (meaning they contribute significantly to overall database load).

* High in calls (meaning they are on a hot path).

* Have a mean_exec_time that is higher than you'd like.

* Contain a WHERE clause with a highly selective, low-cardinality filter (e.g., WHERE status = 'active', WHERE is_deleted = false, WHERE type = 'invoice'). These are prime candidates for a partial index.

    -- This will build the index without locking the table against writes.
    -- It will take longer and consume more resources, but it's safe for production.
    CREATE INDEX CONCURRENTLY idx_documents_draft ON documents (tenant_id)
    WHERE status = 'draft';

Be aware that CREATE INDEX CONCURRENTLY cannot be run inside a transaction block.

Conclusion

Partial indexes are a powerful tool in the arsenal of a senior engineer tasked with scaling a multi-tenant PostgreSQL database. They are not a replacement for fundamental indexing strategies but rather a precision instrument for targeted optimization.

By moving beyond generic composite indexes and creating small, specialized indexes for high-frequency, selective query patterns, you can achieve significant reductions in I/O, query latency, and index maintenance overhead. When combined with the security and convenience of Row-Level Security, this pattern allows you to build systems that are simultaneously secure, scalable, and highly performant. The key is to let performance data from tools like pg_stat_statements guide your optimization efforts, ensuring you're applying this powerful technique where it will have the most impact.