PostgreSQL Partial Indexes for Hot Tenants in Multi-tenant SaaS

The Inevitable Bottleneck: Global Indexes in Multi-Tenant Architectures

In any mature multi-tenant SaaS application, you'll find a familiar schema pattern: core tables like invoices, projects, or documents all feature a tenant_id column. It's the cornerstone of data isolation. Naturally, the first step to optimizing queries is to create a composite index, typically (tenant_id, other_column).

Let's model this with a canonical documents table:

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    plan_type VARCHAR(20) DEFAULT 'standard',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    title TEXT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft', -- e.g., 'draft', 'published', 'archived'
    content TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

For a small application, this is perfectly adequate. But as the platform scales to thousands of tenants and hundreds of millions of rows, this single, monolithic index becomes a significant liability. The reasons are multifaceted:

Quantifying the Problem

Let's simulate a skewed data distribution. We'll create one "hot" enterprise tenant and 5,000 "cold" standard tenants.

-- Insert our 'hot' tenant
INSERT INTO tenants (id, name, plan_type) VALUES ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'MegaCorp Inc.', 'enterprise');

-- Generate 50 million documents for MegaCorp
INSERT INTO documents (tenant_id, title, status)
SELECT
    'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1',
    'MegaCorp Doc ' || g,
    CASE (g % 4) WHEN 0 THEN 'published' WHEN 1 THEN 'draft' ELSE 'archived' END
FROM generate_series(1, 50000000) g;

-- Generate 5,000 'cold' tenants with 2,000 docs each (10 million total)
INSERT INTO tenants (id, name, plan_type)
SELECT gen_random_uuid(), 'Standard Tenant ' || g, 'standard' FROM generate_series(1, 5000);

INSERT INTO documents (tenant_id, title, status)
SELECT
    t.id,
    'Standard Doc ' || g,
    CASE (g % 4) WHEN 0 THEN 'published' WHEN 1 THEN 'draft' ELSE 'archived' END
FROM tenants t, generate_series(1, 2000) g
WHERE t.plan_type = 'standard';

-- Analyze the table to update statistics for the query planner
ANALYZE documents;

Now, let's check the size of our global index:

-- Check the size of the global index
SELECT pg_size_pretty(pg_relation_size('idx_documents_tenant_id_status'));

-- Result on a sample run:
--  pg_size_pretty
-- ----------------
--  2315 MB

A 2.3 GB index is substantial. Now, let's examine a query for a small, cold tenant:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM documents
WHERE tenant_id = (SELECT id FROM tenants WHERE name = 'Standard Tenant 123')
  AND status = 'published';

----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_documents_tenant_id_status on documents  (cost=0.56..25.21 rows=1 width=38) (actual time=0.254..0.321 rows=500 loops=1)
   Index Cond: ((tenant_id = '...'::uuid) AND (status = 'published'::character varying))
   Buffers: shared hit=512
 Planning Time: 0.215 ms
 Execution Time: 0.388 ms

The execution time is fast (sub-millisecond) because the data is likely cached after our ANALYZE command. The key metric to watch here is Buffers: shared hit=512. This means PostgreSQL had to load 512 8KB blocks into memory to satisfy this query. For a query that returns only 500 small rows, this indicates the data is spread out across the index.

The Surgical Solution: The "Hot Tenant" Partial Index Pattern

Instead of treating all tenants equally within a single index, we can create dedicated, hyper-optimized indexes for only our largest, most active, or highest-paying tenants. This is where PostgreSQL's partial indexes (CREATE INDEX ... WHERE ...) shine.

A partial index, as its name implies, only includes entries for rows that satisfy its WHERE clause. The core of our pattern is to create indexes with a WHERE tenant_id = 'some_hot_tenant_id' clause.

Step 1: Identify and Flag Hot Tenants

This pattern is useless without a reliable way to determine which tenants are "hot". This is not a one-time decision. A tenant's activity can grow or shrink over time. Production-grade identification involves:

* Application Metrics: Track API calls, document counts, or active users per tenant.

* Database Monitoring: Use pg_stat_statements to find which tenant_id values appear most frequently in slow or high-frequency queries.

* Business Logic: An 'enterprise' plan might automatically qualify a tenant for dedicated indexes.

Let's add a flag to our tenants table to manage this state:

ALTER TABLE tenants ADD COLUMN has_dedicated_indexes BOOLEAN NOT NULL DEFAULT FALSE;

-- Flag our hot tenant
UPDATE tenants SET has_dedicated_indexes = TRUE WHERE name = 'MegaCorp Inc.';

Step 2: Create the Partial Index

Now, we create a new index specifically for MegaCorp that mirrors our global index's structure but is restricted to their tenant_id.

-- IMPORTANT: Use CONCURRENTLY in production to avoid locking the table!
CREATE INDEX CONCURRENTLY idx_documents_megacorp_status
ON documents (status)
WHERE tenant_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1';

Notice a subtle but critical detail: tenant_id is in the WHERE clause, not the list of indexed columns. Since every entry in this index will have the same tenant_id, including it in the B-tree key is redundant and wasteful. We only need to index (status). The query planner is smart enough to combine the query's WHERE tenant_id = ... with the index's WHERE tenant_id = ....

Let's check the size of this new index:

SELECT pg_size_pretty(pg_relation_size('idx_documents_megacorp_status'));

-- Result:
--  pg_size_pretty
-- ----------------
--  1850 MB

And the size of our original global index is still 2.3 GB. We haven't saved total disk space yet, but we've created a pathway for optimization.

Step 3: Verify the Performance Gain

Now, let's re-run a query for our hot tenant, MegaCorp.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM documents
WHERE tenant_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'
  AND status = 'published';

PostgreSQL's query planner will now see two potential indexes:

The planner will almost certainly choose the partial index because it's more specific and estimated to be cheaper to scan.

----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_documents_megacorp_status on documents  (cost=0.56..335805.56 rows=12500000 width=38) (actual time=0.045..285.441 rows=12500000 loops=1)
   Index Cond: (status = 'published'::character varying)
   Filter: (tenant_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'::uuid)
   Buffers: shared hit=1 read=84321
 Planning Time: 0.150 ms
 Execution Time: 342.112 ms

While the cold tenant query was already fast, the true benefit is seen in overall system stability. MegaCorp's queries are now interacting with an index structure that contains only their data. This isolates their performance from the noise of thousands of other tenants, leading to more predictable latency and dramatically improved cache efficiency for everyone else.

The small tenants still use the global index, but now that index is less frequently accessed by the most demanding tenant, it has a better chance of staying in cache for the smaller tenants, improving their performance as well.

Productionizing the Pattern: Automation and Migration

Creating indexes manually is not a scalable strategy. A production-grade implementation requires automation for promoting/demoting tenants and a robust migration process.

Automated Promotion/Demotion Logic

A background job (e.g., a cron job running a Python script or a scheduled job within your application framework) should run periodically (e.g., nightly or weekly) to manage this.

The Promotion Process:

    -- Find tenants with over 1 million documents who don't have dedicated indexes yet
    SELECT tenant_id, count(*)
    FROM documents
    GROUP BY tenant_id
    HAVING count(*) > 1000000
    INTERSECT
    SELECT id, 0
    FROM tenants
    WHERE has_dedicated_indexes = FALSE;

Example Pseudo-code (Python with psycopg2):

    import psycopg2
    import hashlib

    def create_migration_for_hot_tenant(tenant_id):
        # Sanitize tenant_id to prevent injection, though UUIDs are safe
        safe_tenant_id_str = str(tenant_id).replace('-', '')
        
        # Use a hash for a unique but deterministic index name
        index_name = f"idx_documents_tenant_{safe_tenant_id_str[:12]}_status"
        
        up_sql = f"""
        CREATE INDEX CONCURRENTLY {index_name}
        ON documents (status)
        WHERE tenant_id = '{tenant_id}';
        """
        
        down_sql = f"""
        DROP INDEX CONCURRENTLY IF EXISTS {index_name};
        """
        
        # Write these strings to a new migration file
        # ... file writing logic ...

    def update_tenant_flag(tenant_id):
        with psycopg2.connect(...) as conn:
            with conn.cursor() as cur:
                cur.execute("UPDATE tenants SET has_dedicated_indexes = TRUE WHERE id = %s", (tenant_id,))

The Demotion Process:

Similarly, if a tenant's activity drops significantly, you might want to reclaim the disk space and reduce maintenance overhead.

The Ultimate Optimization: Pruning the Global Index

Once you have a reliable system for creating dedicated indexes for your hot tenants, you can take this pattern a step further. You can modify the global index to explicitly exclude the tenants that have their own dedicated indexes.

-- 1. Drop the old global index
DROP INDEX CONCURRENTLY idx_documents_tenant_id_status;

-- 2. Create a new, smaller global index for 'cold' tenants only
CREATE INDEX CONCURRENTLY idx_documents_cold_tenants_status
ON documents (tenant_id, status)
WHERE tenant_id NOT IN (
    SELECT id FROM tenants WHERE has_dedicated_indexes = TRUE
);

This is a powerful but advanced step. The WHERE clause of this index is now dynamic based on the state of your tenants table. This has major implications:

* Pros: The global index becomes dramatically smaller and more efficient for the long tail of small tenants. It no longer contains any data for your largest tenants.

* Cons & Caveats:

* Planner Limitation: PostgreSQL cannot use a subquery (SELECT id FROM tenants ...) directly in an index definition. You would need to periodically regenerate this index with a hardcoded list of tenant IDs.

* A Better Way: A more manageable approach is to use a column. Add a is_hot_tenant boolean to the documents table itself, kept in sync via triggers on the tenants table. Then your index becomes simple:

        -- Add a denormalized flag to the documents table
        ALTER TABLE documents ADD COLUMN is_hot_tenant BOOLEAN NOT NULL DEFAULT FALSE;

        -- Create a trigger to keep it in sync
        CREATE OR REPLACE FUNCTION sync_hot_tenant_flag()
        RETURNS TRIGGER AS $$
        BEGIN
            NEW.is_hot_tenant := (SELECT has_dedicated_indexes FROM tenants WHERE id = NEW.tenant_id);
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;

        CREATE TRIGGER documents_sync_hot_tenant_flag
        BEFORE INSERT OR UPDATE ON documents
        FOR EACH ROW EXECUTE FUNCTION sync_hot_tenant_flag();

        -- Now, the 'cold' index is clean and simple
        CREATE INDEX idx_documents_cold_tenants_status 
        ON documents (tenant_id, status) 
        WHERE is_hot_tenant = FALSE;

This denormalization adds a small write overhead but makes the indexing logic incredibly clean and efficient for the planner. Now you have a true separation: hot tenant queries use their dedicated partial index, and cold tenant queries use the lean, dedicated cold_tenants index.

Advanced Edge Cases and Performance Considerations

This pattern is not a silver bullet. Senior engineers must consider the trade-offs and edge cases.

The Query Planner is Not Magic

The PostgreSQL planner will only consider a partial index if the query's WHERE clause mathematically proves that it can be used. The query's constraints must be a subset of the index's predicate.

* Match:

* Index: WHERE tenant_id = 'X'

* Query: WHERE tenant_id = 'X' AND status = 'Y' -> Planner uses it.

* No Match:

* Index: WHERE status = 'published'

* Query: WHERE status IN ('published', 'draft') -> Planner cannot use it.

* Subtle Mismatch:

* Index: WHERE some_column IS NULL

* Query: WHERE COALESCE(some_column, '') = '' -> Planner might not use it. The expressions must match.

Always verify the planner's choice with EXPLAIN. If it's not picking your index, ensure the WHERE clauses are compatible and run ANALYZE on the table to ensure statistics are up-to-date.

Index Proliferation and Maintenance

What if 20% of your tenants become "hot"? You could end up with thousands of indexes. Every index adds overhead:

* pg_locks Congestion: Each index is a relation that can be locked.

* Planner Overhead: The planner has more choices to evaluate for each query.

* Write Penalty: Every INSERT, UPDATE, or DELETE to the table must also update every relevant index. More indexes mean slower writes.

This pattern is most effective when a small percentage of tenants (e.g., <5%) generate a large percentage of the load (the classic Pareto principle). If your load is evenly distributed, other strategies like declarative partitioning by tenant_id might be more appropriate, though they come with their own significant complexity (e.g., re-partitioning, foreign key challenges).

Composite Partial Indexes

The pattern extends beautifully to more complex queries. If an enterprise tenant has a specific dashboard query that is slow, you can create a composite partial index just for them.

-- For a dashboard that frequently filters by date and status for MegaCorp
CREATE INDEX CONCURRENTLY idx_megacorp_dashboard_perf
ON documents (created_at DESC, status)
WHERE tenant_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1';

This is a surgical strike. You are adding a multi-column index that would be too expensive to justify globally, but for a single high-value tenant, it's a worthwhile investment.

Conclusion: A Pragmatic Approach to Scaling

The "Hot Tenant" partial index pattern is a powerful tool in the senior engineer's arsenal for scaling multi-tenant PostgreSQL databases. It strikes a pragmatic balance, offering significant performance isolation and efficiency gains without the operational upheaval of full-blown sharding or partitioning.

By moving beyond generic, one-size-fits-all indexing, you can cater to the reality of skewed data distributions inherent in SaaS platforms. The key to success lies in robust automation for identifying and managing the lifecycle of these dedicated indexes. When implemented correctly, this pattern allows your database to scale gracefully, ensuring that the success of your largest customers doesn't degrade the experience for everyone else.