PostgreSQL RLS & Partial Indexes for Scalable Multi-Tenancy

October 5, 2025

13 min read

Goh Ling Yong

Technology enthusiast and software architect specializing in AI-driven development tools and modern software engineering practices. Passionate about the intersection of artificial intelligence and human creativity in building tomorrow's digital solutions.

The Inevitable Failure of Application-Layer Tenancy

In multi-tenant architectures, the most common pattern is to add a tenant_id column to every relevant table and meticulously append WHERE tenant_id = ? to every single database query. While simple to implement initially, this approach is a ticking time bomb for both security and performance as a system scales.

Security Pitfalls:

Human Error: A single forgotten WHERE clause in a complex join or a rushed bug fix can lead to catastrophic data leakage across tenants. This is not a matter of if, but when.

ORM Abstraction Leaks: Object-Relational Mappers (ORMs) can sometimes generate unexpected SQL. A seemingly innocuous change in the application code could lead to the ORM dropping the tenancy predicate, silently exposing data.

Performance Bottlenecks:

A standard B-tree index on (tenant_id, created_at) seems logical. However, it becomes progressively inefficient due to data skew, a common reality in SaaS:

The "Whale" Tenant Problem: One or two enterprise tenants might have 100 million rows, while a thousand smaller tenants have only 1,000 rows each. The index statistics become skewed, and the query planner may make suboptimal choices for the majority of tenants.

Inactive Data Bloat: A significant portion of your data might belong to inactive, churned, or archived tenants. The index must maintain pointers to this data, bloating its size and reducing cache efficiency, even though 99% of queries target active tenants.

Let's quantify this with a foundational schema.

sql

-- A simplified schema for a multi-tenant project management tool
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active', -- e.g., 'active', 'suspended', 'archived'
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    is_public BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- The standard, naive index
CREATE INDEX idx_projects_tenant_id_created_at ON projects(tenant_id, created_at DESC);

-- Let's populate with skewed data
-- Tenant 1: The "Whale"
INSERT INTO tenants (id, name, status) VALUES ('00000000-0000-0000-0000-000000000001', 'WhaleCorp', 'active');
INSERT INTO projects (tenant_id, name) SELECT '00000000-0000-0000-0000-000000000001', 'Project ' || i FROM generate_series(1, 2000000) i;

-- Tenant 2: The "Archived Whale"
INSERT INTO tenants (id, name, status) VALUES ('00000000-0000-0000-0000-000000000002', 'OldWhale', 'archived');
INSERT INTO projects (tenant_id, name) SELECT '00000000-0000-0000-0000-000000000002', 'Archived Project ' || i FROM generate_series(1, 3000000) i;

-- 1000 smaller, active tenants
INSERT INTO tenants (id, name, status)
SELECT gen_random_uuid(), 'Small Tenant ' || i, 'active' FROM generate_series(1, 1000) i;

INSERT INTO projects (tenant_id, name)
SELECT t.id, 'Small Project ' || s.i
FROM tenants t, generate_series(1, 1000) s(i)
WHERE t.name LIKE 'Small Tenant %';

ANALYZE projects;

The application-layer approach forces you to rely on developer discipline, which is a fragile foundation for security. The performance issues will manifest slowly, as queries that were once fast become sluggish due to index bloat and poor cardinality estimates. The solution is to delegate these critical concerns to the database itself.

Section 1: Database-Enforced Isolation with Row-Level Security (RLS)

RLS moves the tenancy check from the WHERE clause of every application query into a database policy attached directly to the table. Once enabled, this policy is automatically and transparently applied by PostgreSQL for any query accessing the table, regardless of its origin.

Production Pattern: Integrating RLS with JWT Authentication

Modern applications typically use stateless authentication via JSON Web Tokens (JWTs). We can create a robust system where the tenant_id from the JWT claim is used to enforce RLS policies. The flow is as follows:

User authenticates, receiving a JWT containing their user_id and tenant_id.

The application backend receives a request with this JWT.
Before executing any business logic, the application sets a session-local configuration parameter in PostgreSQL with the tenant ID from the token.
All subsequent queries in that transaction/session are automatically filtered by RLS policies that read this parameter.

Let's implement this.

Step 1: Create a dedicated, non-login role for application access.

sql

-- This role will own the tables but not be used to connect.
CREATE ROLE service_owner NOLOGIN;

-- This role is what the application will use to connect to the database.
-- It has no special permissions initially.
CREATE ROLE service_user LOGIN PASSWORD 'your_secure_password';

-- Grant usage on schema and connect privileges
GRANT CONNECT ON DATABASE your_db TO service_user;
GRANT USAGE ON SCHEMA public TO service_user;

-- Change ownership of tables to the owner role
ALTER TABLE tenants OWNER TO service_owner;
ALTER TABLE projects OWNER TO service_owner;

-- Grant specific permissions to the application user role
GRANT SELECT, INSERT, UPDATE, DELETE ON tenants, projects TO service_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO service_user;

Step 2: Implement the RLS Policies

We define policies that check a runtime parameter, which we'll name app.current_tenant_id. The current_setting() function provides a secure way to access this.

sql

-- Enable RLS on the table. CRITICAL: This blocks all access by default
-- until a policy is created.
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Create the policy. The USING clause applies to SELECT, UPDATE, DELETE.
-- The WITH CHECK clause applies to INSERT and UPDATE (for the new row value).
CREATE POLICY tenant_isolation_policy ON projects
AS PERMISSIVE -- 'PERMISSIVE' allows multiple policies to be combined with OR
FOR ALL -- Applies to all commands: SELECT, INSERT, UPDATE, DELETE
TO service_user -- Only applies to our application user
USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

Now, any query on projects by service_user will have WHERE tenant_id = '...' implicitly and unavoidably appended.

Step 3: The Application-Side Logic

In your application's request middleware or database connection logic, you must set this parameter. Here's how it would look in a Node.js application using node-postgres (pg).

javascript

const { Pool } = require('pg');

const pool = new Pool({
    // ... your connection config
    user: 'service_user',
    password: 'your_secure_password',
});

// Middleware example (e.g., in Express.js)
async function getProjects(req, res) {
    // 1. JWT is decoded by a preceding auth middleware
    const tenantId = req.user.tenantId; // e.g., '00000000-0000-0000-0000-000000000001'

    const client = await pool.connect();
    try {
        // 2. Set the session variable for the current tenant
        // The 'true' flag makes it a local setting for just this transaction.
        await client.query(`SET LOCAL app.current_tenant_id = '${tenantId}'`);

        // 3. Execute the query WITHOUT a WHERE tenant_id clause
        // RLS handles the filtering automatically and securely.
        const result = await client.query('SELECT id, name FROM projects ORDER BY created_at DESC LIMIT 10');
        
        res.json(result.rows);
    } catch (err) {
        console.error(err);
        res.status(500).send('Server Error');
    } finally {
        client.release();
    }
}

If a developer forgets to set app.current_tenant_id, the current_setting() call will raise an error (or return an empty string, which won't match any UUID), preventing any data from being returned. The system is secure by default.

Section 2: Supercharging Queries with Partial Indexes

With security solved, let's tackle performance. Our standard index on (tenant_id, created_at) is bloated with 3 million rows from our archived tenant, which are almost never queried. A partial index is an index built on a subset of a table's rows, defined by a WHERE clause.

Production Pattern 1: Indexing Only Active Tenants

This is the most impactful optimization for many SaaS applications. The vast majority of operational queries target active users. By creating an index that completely ignores archived or suspended tenants, we create a much smaller, more efficient index.

sql

-- Drop the old, inefficient index
DROP INDEX idx_projects_tenant_id_created_at;

-- Create a new index that ONLY includes projects from 'active' tenants.
-- This requires a JOIN in the index definition, which is a powerful feature.
CREATE INDEX idx_projects_active_tenants_created_at
ON projects (tenant_id, created_at DESC)
WHERE tenant_id IN (SELECT id FROM tenants WHERE status = 'active');

-- NOTE: The above syntax is not directly supported. PostgreSQL does not allow subqueries in
-- CREATE INDEX WHERE clauses. We must denormalize the status or use a function.
-- A more practical approach is to denormalize the tenant status onto the projects table,
-- which can be maintained with triggers.

-- Let's use a more direct and common partial index strategy.
-- Assume most queries are for 'active' projects within a tenant.
ALTER TABLE projects ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
UPDATE projects SET status = 'archived' WHERE tenant_id = '00000000-0000-0000-0000-000000000002';

-- Now, create the partial index on the project status
CREATE INDEX idx_projects_active_created_at
ON projects (tenant_id, created_at DESC)
WHERE status = 'active';

ANALYZE projects;

Performance Analysis:

Let's compare the size and effectiveness.

sql

-- Check index sizes
SELECT relname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname LIKE 'idx_projects%';

relname	index_size
idx_projects_tenant_id_created_at	250 MB
idx_projects_active_created_at	120 MB

(Sizes are illustrative). The partial index is less than half the size. This means it's more likely to fit in memory, leading to significantly faster lookups.

Now, let's examine the query plan for a typical query from an active tenant.

sql

-- Set the RLS parameter for an active tenant
SET app.current_tenant_id = '00000000-0000-0000-0000-000000000001';

EXPLAIN ANALYZE SELECT id, name FROM projects WHERE status = 'active' ORDER BY created_at DESC LIMIT 10;

Query Plan using Partial Index:

text

Limit  (cost=0.56..12.44 rows=10 width=45) (actual time=0.035..0.045 rows=10 loops=1)
  ->  Index Scan using idx_projects_active_created_at on projects  (cost=0.56..2489117.51 rows=2120000 width=45) (actual time=0.034..0.042 rows=10 loops=1)
        Index Cond: (tenant_id = '00000000-0000-0000-0000-000000000001'::uuid)
        Filter: (status = 'active'::text)
Planning Time: 0.150 ms
Execution Time: 0.065 ms

The planner correctly chooses our small, efficient partial index. The execution time is sub-millisecond.

The RLS policy is applied implicitly. Notice the EXPLAIN output doesn't show the RLS filter directly, but the Index Cond on tenant_id is a direct result of the RLS policy being pushed down into the query plan.

Production Pattern 2: Partial Indexes for Feature Flags

Another powerful use case is indexing data related to specific, opt-in features that only a subset of tenants use. Imagine a projects table with a jsonb metadata column, and we want to efficiently query for projects with a specific reporting feature enabled.

sql

ALTER TABLE projects ADD COLUMN metadata JSONB;

-- Enable a feature for a few tenants
UPDATE projects SET metadata = '{"enable_advanced_reporting": true}' 
WHERE tenant_id IN (SELECT id FROM tenants WHERE name LIKE 'Small Tenant %' LIMIT 5);

ANALYZE projects;

-- Create a partial index for this feature
CREATE INDEX idx_projects_advanced_reporting_enabled
ON projects (tenant_id, id)
WHERE (metadata->>'enable_advanced_reporting')::boolean IS TRUE;

A query to find these projects would be incredibly slow without this index, requiring a full table scan.

Query without partial index:

sql

EXPLAIN ANALYZE SELECT id FROM projects WHERE (metadata->>'enable_advanced_reporting')::boolean IS TRUE;

This would result in a Parallel Seq Scan on projects taking several seconds.

Query with partial index:

sql

EXPLAIN ANALYZE SELECT id FROM projects WHERE (metadata->>'enable_advanced_reporting')::boolean IS TRUE;

text

Bitmap Heap Scan on projects  (cost=105.73..13278.36 rows=5000 width=25) (actual time=0.352..1.251 rows=5000 loops=1)
  ->  Bitmap Index Scan on idx_projects_advanced_reporting_enabled  (cost=0.00..104.48 rows=5000 width=0) (actual time=0.301..0.301 rows=5000 loops=1)
Planning Time: 0.123 ms
Execution Time: 1.345 ms

The query time drops from seconds to just over a millisecond. The combination of RLS and this partial index allows for highly efficient queries that are both secure and feature-aware.

Section 3: Advanced Scenarios and Edge Cases

Implementing RLS and partial indexes is not without its complexities. Here are common production challenges and their solutions.

Edge Case 1: The "Super User" or Internal Tool Access

Your support team or internal admin tools need to view data across all tenants. How can they bypass RLS securely?

Solution: Create a dedicated role with the BYPASSRLS attribute.

sql

-- Create a role for internal tools or super-admins
CREATE ROLE internal_support NOLOGIN;

-- Grant this role to a specific user who needs this access
CREATE USER support_admin PASSWORD 'another_secure_password';
GRANT internal_support TO support_admin;

-- The critical privilege
ALTER ROLE internal_support SET BYPASSRLS = on;

-- Grant read-only access to this role
GRANT SELECT ON projects, tenants TO internal_support;

When support_admin connects, their session will have BYPASSRLS enabled, and they can query the tables without RLS policies being applied. This privilege should be granted with extreme caution and be heavily audited.

Edge Case 2: Cross-Tenant Analytics

Running analytical queries (e.g., calculating daily active projects across all tenants) is impossible for the standard service_user role due to RLS. Running them as a BYPASSRLS user on the primary database can cause significant performance degradation.

Solution: Use a read replica with a dedicated analytics role.

Set up a PostgreSQL streaming replica of your primary database.

Create a dedicated analytics_user on the primary that will be replicated.

Grant this user BYPASSRLS and SELECT privileges on the necessary tables.

Point your analytics jobs, data warehouse ETLs, and business intelligence tools to the read replica and connect as analytics_user.

This architecture completely isolates your production transactional workload from heavy analytical queries while still providing the necessary cross-tenant data access.

Edge Case 3: Performance Overhead of RLS Policies

RLS is not free. For every row access, PostgreSQL must execute the USING clause of your policy. If this clause contains complex logic or volatile functions, it can become a bottleneck.

Anti-Pattern: Using a complex function inside a policy.

sql

-- A function that might do its own DB lookups
CREATE FUNCTION check_tenant_subscription(tid uuid) RETURNS boolean AS $$
BEGIN
    -- This performs a sub-query, which will be executed repeatedly
    RETURN EXISTS (SELECT 1 FROM subscriptions WHERE tenant_id = tid AND status = 'active');
END;
$$ LANGUAGE plpgsql STABLE;

-- SLOW POLICY
CREATE POLICY subscription_policy ON projects
USING (check_tenant_subscription(tenant_id));

Best Practice: Keep policies simple. Use current_setting() and direct column comparisons. Any complex, session-level state should be pre-calculated by your application and set in a dedicated session variable.

sql

-- In application middleware:
const { tenantId, subscriptionStatus } = req.user;
await client.query(`SET LOCAL app.current_tenant_id = '${tenantId}'`);
await client.query(`SET LOCAL app.subscription_status = '${subscriptionStatus}'`);

-- FAST POLICY
CREATE POLICY tenant_isolation_policy ON projects
USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY active_subscription_policy ON projects
USING (current_setting('app.subscription_status') = 'active');

By using PERMISSIVE policies, both will be combined with OR, but the key is that the checks are simple and do not involve sub-queries, ensuring minimal overhead.

Conclusion: A Foundation for Scalable and Secure SaaS

Moving tenancy enforcement from the application layer to the database via Row-Level Security is a paradigm shift. It transforms data isolation from a recurring developer task fraught with risk into a foundational, guaranteed property of your data model.

When combined with partial indexes tailored to the specific query patterns of a multi-tenant environment—such as filtering by active status or feature flags—the result is a system that is not only more secure but also significantly more performant. The database query planner, armed with both RLS context and highly specific indexes, can make optimal decisions that are impossible with a generic, one-size-fits-all indexing strategy.

This architecture requires a deeper understanding of PostgreSQL's capabilities but pays substantial dividends in scalability, security, and maintainability. It is a hallmark of a mature engineering organization building for long-term success.