PostgreSQL Partial Index Strategies for Multi-tenant SaaS
The Inevitable Bloat of Multi-Tenant Indexes
In any mature multi-tenant SaaS application, a familiar pattern emerges: core tables like invoices, tasks, or documents grow to hundreds of millions or billions of rows. The standard approach to ensure data isolation and query performance is a composite B-Tree index, almost always starting with tenant_id.
CREATE INDEX idx_tasks_tenant_id_status ON tasks (tenant_id, status);This is the correct first step. It efficiently isolates a tenant's data for queries filtering by status. However, as the table grows and data distribution skews—one enterprise tenant with 100 million tasks, and ten thousand SMB tenants with 1,000 each—this index becomes a significant performance liability.
Why? Because this single index must catalog every single task for every single tenant, regardless of its state. If 95% of tasks are in a 'completed' state and are rarely accessed by hot operational queries, you are paying a steep price in storage, cache memory, and write overhead to maintain index entries that provide little value to your application's most frequent, performance-sensitive code paths. Every INSERT, UPDATE, and DELETE on the tasks table pays the tax of updating this monolithic index.
This article dissects a more surgical, potent solution: partial indexes. We will move beyond the theory and implement production-ready patterns that address specific, high-impact query scenarios in a multi-tenant environment.
Section 1: A Concrete Scenario - The 'Actionable Items' Dashboard
Let's model a realistic system. We have a project management tool where tenants manage tasks. The most performance-critical part of the application is the user's dashboard, which shows pending and overdue tasks.
Schema:
CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL
);
CREATE TYPE task_status AS ENUM ('pending', 'in_progress', 'completed', 'archived');
CREATE TABLE tasks (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES tenants(id),
assignee_id BIGINT, -- Can be NULL
title TEXT NOT NULL,
status task_status NOT NULL DEFAULT 'pending',
due_date TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);The Standard Indexing Approach:
A senior developer's first instinct is to create a comprehensive index to cover the dashboard query.
-- The common, but bloated, approach
CREATE INDEX idx_tasks_tenant_assignee_status ON tasks (tenant_id, assignee_id, status);This index will serve a query like:
SELECT id, title, due_date
FROM tasks
WHERE tenant_id = '...'
AND assignee_id = 123
AND status IN ('pending', 'in_progress');Simulating Production Data Skew:
Let's populate the database to reflect a real-world scenario. We'll have one massive enterprise tenant and thousands of smaller ones.
-- Generate one massive tenant and 5000 small tenants
INSERT INTO tenants (id, name) VALUES ('a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6', 'Enterprise Tenant');
INSERT INTO tenants (name) SELECT 'Small Tenant ' || i FROM generate_series(1, 5000) i;
-- Populate tasks with heavy skew
-- Enterprise Tenant: 10 million tasks, 95% completed/archived
INSERT INTO tasks (tenant_id, assignee_id, status, title, due_date)
SELECT
'a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6',
(random() * 1000)::int + 1,
CASE
WHEN random() < 0.025 THEN 'pending'
WHEN random() < 0.05 THEN 'in_progress'
WHEN random() < 0.85 THEN 'completed'
ELSE 'archived'
END,
'Task ' || i,
NOW() + (random() * 30 - 15) * '1 day'::interval
FROM generate_series(1, 10000000) i;
-- Small Tenants: 1000 tasks each, similar distribution
INSERT INTO tasks (tenant_id, assignee_id, status, title, due_date)
SELECT
t.id,
(random() * 20)::int + 1,
CASE
WHEN random() < 0.05 THEN 'pending'
WHEN random() < 0.10 THEN 'in_progress'
ELSE 'completed'
END,
'Task ' || i,
NOW() + (random() * 30 - 15) * '1 day'::interval
FROM tenants t, generate_series(1, 1000) i
WHERE t.name LIKE 'Small Tenant %';
ANALYZE tasks;Now, let's check the size of our standard index:
SELECT pg_size_pretty(pg_relation_size('idx_tasks_tenant_assignee_status'));
-- Result might be: ~550 MBWhen we run our dashboard query for the large tenant, the planner uses this index.
EXPLAIN ANALYZE
SELECT id, title, due_date
FROM tasks
WHERE tenant_id = 'a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6'
AND assignee_id = 42
AND status IN ('pending', 'in_progress');QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tasks_tenant_assignee_status on tasks (cost=0.56..549.28 rows=50 width=22) (actual time=0.215..0.291 rows=51 loops=1)
Index Cond: ((tenant_id = 'a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6'::uuid) AND (assignee_id = 42) AND (status = ANY ('{pending,in_progress}'::task_status[])))
Planning Time: 0.211 ms
Execution Time: 0.312 msThe performance is excellent. So what's the problem? The problem is the 550 MB of disk and memory this index consumes. It's bloated with entries for the 9.5 million completed and archived tasks that our hot-path query will never need. This cache pressure starves other, more deserving queries and data.
Section 2: The Partial Index Solution
A partial index is simply an index with a WHERE clause. It instructs PostgreSQL to only include rows in the index that satisfy the predicate. This is our precision tool.
Let's create an index specifically for our dashboard query.
-- Drop the old one for a fair comparison
DROP INDEX idx_tasks_tenant_assignee_status;
-- The surgical, partial index
CREATE INDEX idx_tasks_actionable_by_assignee ON tasks (tenant_id, assignee_id, due_date)
WHERE status IN ('pending', 'in_progress');Notice two key changes:
WHERE clause: This is the magic. It limits the index to only the 5% of tasks that are 'actionable'.due_date to the index. Since this new index is so much smaller, we can afford to include more columns to support sorting or even create index-only scans, further boosting performance.Let's check the size of this new index:
SELECT pg_size_pretty(pg_relation_size('idx_tasks_actionable_by_assignee'));
-- Result might be: ~30 MBWe've achieved a ~95% reduction in index size. This is a monumental win for memory usage, cache efficiency, and write performance.
Now, let's re-run our query. The query planner is smart enough to match the query's WHERE clause to the partial index's predicate.
EXPLAIN ANALYZE
SELECT id, title, due_date
FROM tasks
WHERE tenant_id = 'a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6'
AND assignee_id = 42
AND status IN ('pending', 'in_progress')
ORDER BY due_date ASC;QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tasks_actionable_by_assignee on tasks (cost=0.43..26.13 rows=50 width=22) (actual time=0.061..0.082 rows=51 loops=1)
Index Cond: ((tenant_id = 'a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6'::uuid) AND (assignee_id = 42))
Planning Time: 0.145 ms
Execution Time: 0.103 msThe performance is even better. The cost estimate is much lower because the planner knows this index is smaller and more targeted. The execution time is faster because the index is more compact, requiring fewer page reads from disk/cache.
Section 3: Production Implementation Patterns
Partial indexes are not just for status enums. They are a versatile tool for a variety of common SaaS engineering problems.
Pattern 1: Conditional Uniqueness
Problem: In our system, a tenant can configure webhooks. They should only be allowed to have one active webhook per URL, but they can have many inactive ones for historical purposes.
A standard UNIQUE (tenant_id, target_url) constraint is too restrictive. It would prevent creating a new, active webhook if an inactive one for the same URL already exists.
Solution: A unique partial index acts as a conditional unique constraint.
CREATE TABLE webhooks (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES tenants(id),
target_url TEXT NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT true
);
CREATE UNIQUE INDEX idx_webhooks_one_active_url_per_tenant
ON webhooks (tenant_id, target_url)
WHERE is_active = true;Let's test it:
-- This succeeds
INSERT INTO webhooks (tenant_id, target_url, is_active)
VALUES ('a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6', 'https://api.example.com/hook', true);
-- This fails with a unique constraint violation, as expected
INSERT INTO webhooks (tenant_id, target_url, is_active)
VALUES ('a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6', 'https://api.example.com/hook', true);
-- ERROR: duplicate key value violates unique constraint "idx_webhooks_one_active_url_per_tenant"
-- This succeeds, because it doesn't meet the index's WHERE clause
INSERT INTO webhooks (tenant_id, target_url, is_active)
VALUES ('a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6', 'https://api.example.com/hook', false);This pattern is incredibly powerful for enforcing complex business rules directly at the database layer, ensuring data integrity without convoluted application-level locking.
Pattern 2: Optimizing for Soft Deletes
Problem: Soft deletes, typically using a deleted_at IS NULL flag, are common practice. However, this means that 99%+ of your data matches the IS NULL condition. A standard index on (tenant_id, ...) is almost entirely composed of pointers to non-deleted rows.
Solution: Create two highly specialized, small indexes.
-- A compact, efficient index for the 99.9% of queries that access live data.
CREATE INDEX idx_tasks_live ON tasks (tenant_id, assignee_id)
WHERE deleted_at IS NULL;
-- A tiny index for the rare administrative query that needs to find deleted items.
CREATE INDEX idx_tasks_deleted ON tasks (tenant_id, deleted_at)
WHERE deleted_at IS NOT NULL;This strategy keeps the index for your most common access pattern as small and fast as possible. The performance difference is most noticeable in tables with a high churn rate of soft-deleted records.
Pattern 3: Focusing on Non-NULL Foreign Keys
Problem: Consider our tasks table. The assignee_id can be NULL. Often, we have queries that specifically look for assigned tasks. An index on (tenant_id, assignee_id) will include entries for all the unassigned tasks, which can be a significant portion of the table.
Solution: Index only the rows that have an assignee.
CREATE INDEX idx_tasks_assigned ON tasks (tenant_id, assignee_id)
WHERE assignee_id IS NOT NULL;Any query like ... WHERE tenant_id = ? AND assignee_id = ? will perfectly match this index. Queries looking for unassigned tasks (... WHERE assignee_id IS NULL) will likely perform a sequential scan on the tenant's data, which is often efficient if the unassigned task count is low, or could be served by another partial index: ... WHERE assignee_id IS NULL if needed.
Section 4: Advanced Considerations and Edge Cases
While powerful, partial indexes require a deeper understanding of the query planner and ORM integration.
The Planner Matching Rule:
For a partial index to be used, the query's WHERE clause must be a logical superset of the index's WHERE predicate. PostgreSQL must be able to prove that all rows returned by the query are guaranteed to be in the index.
* Will use index ... WHERE status = 'pending':
* ... WHERE status = 'pending' AND tenant_id = ? (Exact match)
* Will use index ... WHERE status IN ('a', 'b'):
* ... WHERE status = 'a' AND tenant_id = ? (Query predicate is a subset)
* Will NOT use index ... WHERE status = 'pending':
* ... WHERE status IN ('pending', 'in_progress') (Query predicate is a superset)
* ... WHERE status != 'completed' (Planner cannot prove this is equivalent)
* ... WHERE COALESCE(status, 'pending') = 'pending' (Using functions on the column)
This is the most common pitfall. You must ensure your application code generates queries that precisely match your index definitions.
ORM and Migration Integration:
Most modern ORMs do not have a first-class, declarative syntax for partial indexes. You must typically drop down to raw SQL or use special annotations within your migration files.
Example with TypeORM (in a migration file):
import { MigrationInterface, QueryRunner } from 'typeorm';
export class CreateActionableTasksIndex1678886400000 implements MigrationInterface {
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(
`CREATE INDEX "idx_tasks_actionable_by_assignee" ON "tasks" ("tenant_id", "assignee_id", "due_date") WHERE status IN ('pending', 'in_progress')`,
);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`DROP INDEX "idx_tasks_actionable_by_assignee"`);
}
}Example with SQLAlchemy and Alembic:
# In an Alembic migration script
from alembic import op
import sqlalchemy as sa
def upgrade():
op.create_index(
'idx_tasks_actionable_by_assignee',
'tasks',
['tenant_id', 'assignee_id', 'due_date'],
unique=False,
postgresql_where=sa.text("status IN ('pending', 'in_progress')")
)
def downgrade():
op.drop_index('idx_tasks_actionable_by_assignee', table_name='tasks')Write Overhead and Maintenance:
Every index adds overhead to write operations. However, the overhead is proportional to the size and complexity of the index. Because partial indexes are significantly smaller, the write penalty is often drastically lower than their full-table counterparts. The cost of updating a 30 MB index is trivial compared to updating a 550 MB one.
Furthermore, maintenance operations like VACUUM and REINDEX are much faster on smaller indexes, contributing to better overall database health.
Section 5: Full-Scale Benchmarking
Talk is cheap. Let's prove the impact with a reproducible benchmark.
Setup:
We'll use Docker Compose to spin up a PostgreSQL instance and a Python script to populate data and run tests.
docker-compose.yml
version: '3.8'
services:
postgres:
image: postgres:15
environment:
POSTGRES_USER: user
POSTGRES_PASSWORD: password
POSTGRES_DB: saas_db
ports:
- "5432:5432"
volumes:
- postgres_data:/var/lib/postgresql/data
volumes:
postgres_data:benchmark.py
import psycopg2
import time
import uuid
# --- Connection Details ---
DB_CONN_STR = "postgresql://user:password@localhost:5432/saas_db"
# --- Test Parameters ---
ENTERPRISE_TENANT_ID = str(uuid.uuid4())
ENTERPRISE_TASK_COUNT = 5_000_000
SMALL_TENANT_COUNT = 1000
SMALL_TENANT_TASK_COUNT = 1000
QUERY_ITERATIONS = 500
def setup_schema(conn):
with conn.cursor() as cur:
print("Setting up schema...")
cur.execute("DROP TABLE IF EXISTS tasks, tenants;")
cur.execute("DROP TYPE IF EXISTS task_status;")
cur.execute("CREATE TYPE task_status AS ENUM ('pending', 'in_progress', 'completed', 'archived');")
cur.execute("""
CREATE TABLE tenants (
id UUID PRIMARY KEY,
name TEXT NOT NULL
);
""")
cur.execute("""
CREATE TABLE tasks (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL,
status task_status NOT NULL
);
""")
conn.commit()
def populate_data(conn):
with conn.cursor() as cur:
print(f"Populating data for enterprise tenant ({ENTERPRISE_TASK_COUNT} tasks)...")
cur.execute("INSERT INTO tenants (id, name) VALUES (%s, 'Enterprise Tenant');", (ENTERPRISE_TENANT_ID,))
cur.execute("""
INSERT INTO tasks (tenant_id, status)
SELECT %s,
CASE
WHEN random() < 0.05 THEN 'pending'
ELSE 'completed'
END
FROM generate_series(1, %s);
""", (ENTERPRISE_TENANT_ID, ENTERPRISE_TASK_COUNT))
conn.commit()
def run_benchmark(conn, index_name, index_ddl):
with conn.cursor() as cur:
print(f"\n--- Benchmarking with index: {index_name} ---")
# Create index and analyze
cur.execute(f"DROP INDEX IF EXISTS {index_name}; DROP INDEX IF EXISTS idx_full;")
start_time = time.time()
cur.execute(index_ddl)
conn.commit()
print(f"Index creation time: {time.time() - start_time:.2f}s")
cur.execute("ANALYZE tasks;")
# Measure index size
cur.execute("SELECT pg_size_pretty(pg_relation_size(%s));", (index_name,))
size = cur.fetchone()[0]
print(f"Index size: {size}")
# Measure read performance
query = """
SELECT COUNT(*) FROM tasks
WHERE tenant_id = %s AND status = 'pending';
"""
# Warm-up
for _ in range(10):
cur.execute(query, (ENTERPRISE_TENANT_ID,))
cur.fetchone()
latencies = []
for _ in range(QUERY_ITERATIONS):
start_time = time.time()
cur.execute(query, (ENTERPRISE_TENANT_ID,))
cur.fetchone()
latencies.append((time.time() - start_time) * 1000) # ms
avg_latency = sum(latencies) / len(latencies)
latencies.sort()
p95_latency = latencies[int(QUERY_ITERATIONS * 0.95)]
print(f"Query Latency (avg): {avg_latency:.4f} ms")
print(f"Query Latency (p95): {p95_latency:.4f} ms")
def main():
conn = psycopg2.connect(DB_CONN_STR)
setup_schema(conn)
populate_data(conn)
# Benchmark 1: Standard full composite index
run_benchmark(conn, "idx_full",
"CREATE INDEX idx_full ON tasks (tenant_id, status);")
# Benchmark 2: Partial index
run_benchmark(conn, "idx_partial",
"CREATE INDEX idx_partial ON tasks (tenant_id, status) WHERE status = 'pending';")
conn.close()
if __name__ == "__main__":
main()To run:
docker-compose up -dpip install psycopg2-binarypython benchmark.pyExpected Results (will vary based on hardware):
--- Benchmarking with index: idx_full ---
Index creation time: 10.51s
Index size: 125 MB
Query Latency (avg): 0.3512 ms
Query Latency (p95): 0.5120 ms
--- Benchmarking with index: idx_partial ---
Index creation time: 0.48s
Index size: 6384 kB
Query Latency (avg): 0.1425 ms
Query Latency (p95): 0.2250 msAnalysis of Results:
* Index Size: The partial index is ~5% of the size of the full index (6.4 MB vs 125 MB). This is a direct translation to saved disk space and, more importantly, RAM for caching.
* Creation Time: Building the index was over 20x faster. This matters during deployments and migrations.
* Query Performance: The query latency was more than 2x better with the partial index. The database had to scan a much smaller, denser data structure to find the required rows.
This benchmark isolates and proves the core value proposition: for targeted, high-frequency queries, a partial index offers a dramatic improvement in efficiency across multiple vectors.
Conclusion: A Tool for Surgical Precision
Partial indexes are not a replacement for well-designed, general-purpose composite indexes. A broad index like (tenant_id, created_at) will always have its place. Instead, partial indexes are a specialized tool for senior engineers to surgically resolve performance bottlenecks in mature, large-scale systems.
By profiling your application and identifying the most frequent, performance-sensitive queries that operate on a small, predictable subset of your data, you can apply these patterns to:
INSERT and UPDATE operations need to touch.The next time you see a status column or a deleted_at flag in a WHERE clause for a critical path query on a massive multi-tenant table, don't just add it to the end of your existing composite index. Ask yourself: can I solve this more elegantly and efficiently with a partial index?