PostgreSQL Partitioning vs Partial Indexing for Multitenant SaaS Performance

Every software engineer building a SaaS product eventually faces the same design crossroads. You start with a single database. Every table has a tenant_id column. You add a composite index on (tenant_id, created_at), and everything runs fast.

Then you sign your first enterprise customer.

Suddenly, a single tenant accounts for 85% of your database volume. The database pages that used to fit neatly into memory are now constantly being evicted. Your query times for smaller tenants start to spike because they are sharing the same index pages and buffer pool with a data giant.

This is the classic multitenant database scaling problem. When you search for solutions, the common advice is to partition your tables. But partitioning comes with massive operational complexity. There is another path that developers often overlook: partial indexing.

Comparing these two approaches reveals how they affect query planning, disk layout, and maintenance overhead.

The Core Problem: The Whale and the Minnows

In a typical multitenant SaaS database, data distribution is highly skewed. You have a few "whales" (enterprise customers with millions of rows) and thousands of "minnows" (small teams or trial accounts with a few hundred rows).

When you use a standard composite index like (tenant_id, created_at), PostgreSQL builds a single B-Tree index structure. For the query planner, searching this index is relatively cheap. But as the index grows to tens of gigabytes, Postgres can no longer keep the entire index in the shared buffer pool.

When a query for a small tenant runs, Postgres often has to read index pages from disk because the pages containing that tenant's keys were evicted to make room for the enterprise tenant's active data.

To solve this, we want to isolate the data access paths of our tenants without necessarily spinning up separate databases for each one.

Declarative Partitioning: Physical Isolation

PostgreSQL supports declarative partitioning, which allows you to split one logical table into multiple physical tables (partitions) based on a partition key. For multitenant apps, you typically partition by list using the tenant_id.

CREATE TABLE orders (
    id UUID NOT NULL,
    tenant_id INT NOT NULL,
    amount NUMERIC,
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST (tenant_id);

When tenant 101 joins, you create a dedicated partition for them:

CREATE TABLE orders_tenant_101 
    PARTITION OF orders 
    FOR VALUES IN (101);

The Advantages of Partitioning

Partitioning physically separates your data on disk. When you query data for tenant 101, Postgres uses a process called partition pruning. The query planner looks at the query, sees WHERE tenant_id = 101, and ignores every other partition table in the database. It only scans the physical files associated with orders_tenant_101.

This isolation yields major benefits:

1. Autovacuum Isolation: Autovacuum runs on physical tables. In a non-partitioned database, a high-write tenant triggers vacuuming on the entire global table, consuming shared resources. With partitioning, only the active tenant's partition table gets vacuumed.
2. Easy Data Deletion: If a tenant churns, you do not run a slow DELETE query that generates massive write-ahead logs (WAL) and bloats the table. You simply run DROP TABLE orders_tenant_101. It is instantaneous and reclaims disk space immediately.
3. Index Size Control: Each partition has its own indexes. The index for a small tenant remains tiny and fits entirely in memory.

The Hidden Costs of Partitioning

Partitioning sounds perfect, but the operational trade-offs are steep.

First, primary keys and foreign keys must include the partition key. As shown in the SQL example above, your primary key must be (tenant_id, id), not just id. If you have an existing schema with relationships built on simple UUIDs or auto-incrementing integers, you have to rewrite your entire schema and update all your query logic. When refactoring these queries, keeping your codebase clean is essential, which is why following variable naming best practices for readable code is highly recommended during major database migrations.

Second, you have to manage partition creation. If a new user signs up, your application code must dynamically run DDL statements to create the new partition tables before the user can write any data. Before data even reaches these partitioned tables, it is critical to validate it at the application layer. For Python developers, this often involves designing resilient Pydantic v2 schemas to ensure incoming tenant payloads conform to strict business rules. Alternatively, you must route all new or small tenants into a default partition, which defeats the purpose of isolation for those tenants.

Third, catalog bloat is a real risk. If you have 5,000 tenants and partition 10 tables for each, you suddenly have 50,000 physical tables in Postgres. The query planner has to load metadata for these tables, which can degrade query planning performance and increase connection memory usage.

Partial Indexing: The Precision Tool

Partial indexes allow you to build an index on a subset of your table defined by a conditional clause.

Instead of partitioning the table physically, you keep all data in a single table but create targeted indexes for your active or large tenants.

CREATE INDEX idx_orders_tenant_101 
ON orders (created_at) 
WHERE tenant_id = 101;

When you query the orders table for tenant 101, the query planner recognizes that the filter in the index matching condition (WHERE tenant_id = 101) matches your query filter. It bypasses the global indexes and uses this specific, highly compact index.

The Advantages of Partial Indexes

1. No Schema Changes: You do not need to alter your primary keys or foreign keys. Your database schema remains simple, standard, and flat.
2. Zero Table Management Overhead: You do not need to create new physical tables dynamically. Small tenants can share a default global index, while you only spin up partial indexes for your top 5% highest-volume tenants.
3. Tiny Memory Footprint: The index only contains pointers to the rows matching the condition. An index for a tenant with 1,000 rows will be a few kilobytes, making it incredibly fast to scan and easy to keep cached in RAM.

The Drawbacks of Partial Indexes

Partial indexes do not solve the physical storage problem. Under the hood, all rows are still written to the same heap files on disk.

If tenant 101 has millions of rows, those rows are physically interleaved with the rows of your smaller tenants. If you need to run a sequential scan, or if Postgres needs to vacuum the table, it still has to process the entire massive table.

Additionally, dropping a tenant requires a standard DELETE query, which can cause table bloat and lock resources for a long time.

Performance Under the Hood: Query Planner Behavior

Let us look at how the Postgres query planner handles these two approaches when executing a simple query:

SELECT * FROM orders WHERE tenant_id = 101 AND created_at > '2026-01-01';

Under Partitioning

The query planner analyzes the query and applies partition pruning. It discards the root orders table and all other partitions.

It rewrites the query execution plan to run directly against orders_tenant_101. It then performs an index scan on the index defined on orders_tenant_101(created_at).

The planner only needs to look at the metadata for this single sub-table.

Under Partial Indexing

The query planner looks at the global orders table. It scans the available indexes and finds idx_orders_tenant_101, which is defined with the predicate WHERE tenant_id = 101.

Because the query contains tenant_id = 101, the planner knows this index is safe to use. It performs an index scan on this partial index.

The plan is fast because the index pages are small and likely already cached in memory.

But there is a catch. If you write a query that uses a variable, like this:

PREPARE get_orders(int, timestamp) AS
SELECT * FROM orders WHERE tenant_id = `1 AND created_at > `2;

The query planner may not be able to use your partial indexes. During the initial planning phase of a prepared statement, Postgres does not know the value of $1. It must generate a generic plan that works for any tenant ID.

A generic plan cannot use a partial index because the index only contains data for one specific tenant. If you rely on a TypeScript-based ORM to manage these queries, you can use TypeScript conditional and mapped types to build safer, strongly-typed query builders that handle these edge cases.

When to Use Which: A Decision Framework

Choosing between these two patterns depends on your scale, tenant distribution, and operational tolerance.

Choose Partial Indexing If:

• You have a standard SaaS distribution where 95% of your tenants are small, and you only have a dozen massive tenants causing performance bottlenecks. You can create partial indexes specifically for those dozen tenants.
• You cannot easily change your database schema to include tenant_id in all primary and foreign keys.
• You want to optimize read performance for key accounts quickly without running risky migrations on live production tables.

Choose Partitioning If:

• You have strict data residency or compliance requirements that demand physical separation of tenant data on disk.
• You need to delete tenant data frequently and want to avoid the write overhead and table bloat of bulk DELETE operations.
• Your tenants are relatively balanced in size, but the total volume of the database is too large for a single table to perform basic maintenance tasks like vacuuming and indexing.

The Hybrid Approach

You do not always have to choose one or the other. Many high-scale SaaS architectures use a hybrid pattern.

They partition their tables by time or tenant groups (e.g., hash partitioning or partitioning by region) to keep physical table sizes manageable. Then, within those partitions, they use partial indexes to optimize access patterns for highly active users or specific workflow states.

For example, you might partition your orders table by month to make historical data archiving easy. Inside each monthly partition, you can add partial indexes for your top tenants or for records that are still in an "active" status.

This keeps your indexes lean, your queries fast, and your database maintenance tasks predictable.