Understanding Multitenant Database Index Tuning Strategies in PostgreSQL

When you build a software-as-a-service (SaaS) application, you usually start with a single database. It is cheap, easy to manage, and keeps your deployment pipeline simple. You put a tenant_id column on every table, write your queries with a WHERE tenant_id = ? clause, and push to production. For the first few months, everything runs fast.

But SaaS growth has a weird way of breaking databases. One day, a large enterprise customer signs up. They import five million records into a table where your average customer has five hundred. Suddenly, queries for every other tenant on that database start slowing down. The database server thrashes its disks, CPU usage spikes to 90%, and your monitoring alerts start screaming.

This is the classic shared-database, shared-schema multitenancy problem. When multiple tenants share the same physical tables, their indexes share the same physical storage. If you do not tune your indexing strategy for this pattern, the data of your largest customers will push the index pages of your smaller customers out of memory.

To keep your application fast, you need to understand how Postgres manages indexes under the hood and how to structure them for multitenant workloads.

How Postgres B-Trees Handle Tenant Data

By default, Postgres uses B-Tree indexes for most queries. When you run CREATE INDEX ON orders (tenant_id), Postgres builds a balanced tree structure. The leaf nodes of this tree contain the actual index keys and pointers to the physical rows on disk (called Tuple IDs or TIDs).

When a query requests data for a specific tenant, the database engine starts at the root node of the B-Tree and traverses down to the leaf nodes. In a small database, this traversal takes three or four page reads. If the index fits entirely in RAM, this lookup takes microseconds.

The system breaks down when the index grows too large for the Postgres buffer cache (shared_buffers). If a single tenant owns 80% of the rows in a table, their index entries are scattered throughout the B-Tree. When Postgres needs to read index pages for a smaller tenant, it often has to evict pages belonging to the large tenant from memory, read the small tenant's pages from disk, and then reverse the process when the large tenant queries again. This constant swapping is called cache thrashing.

To prevent this, your index strategy must keep the active portion of your indexes small enough to fit into memory.

Composite Indexes and the Rule of Leftmost Columns

The first line of defense in a multitenant database is the composite index. If your queries look for specific rows belonging to a tenant-such as finding a pending order-you need an index that covers both fields.

CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status);

The order of columns in a composite index is not arbitrary. Postgres organizes the B-Tree using the columns from left to right. In the index above, Postgres sorts the index entries by tenant_id first. Within each tenant_id, it sorts the entries by status.

This structure allows the query planner to use the index for two types of queries:

1. Queries filtering by both tenant_id and status.
2. Queries filtering only by tenant_id.

If you wrote the index as (status, tenant_id), Postgres would sort the index by status first. If you queried for a specific tenant without specifying a status, Postgres would have to scan the entire index because tenant 42's records would be scattered across the different status branches.

You should almost always put tenant_id as the leftmost column in your composite indexes. This groups all data for a single tenant together in the physical index layout. When you query for tenant 42, Postgres jumps directly to the start of tenant 42's index block and reads only the pages relevant to that tenant.

There is a trade-off here. If you have ten different tables that all filter by tenant_id and another column, you will end up with ten composite indexes. Each index increases the time it takes to insert or update rows, which can impact performance on critical paths like API idempotency keys.

Partial Indexes for Skewed Tenant Workloads

In many SaaS applications, data distribution is highly skewed. You might have thousands of sign-ups who created an account, added two records, and never logged in again. At the same time, you have ten power users generating millions of rows.

If you build a global composite index on (tenant_id, created_at), you are paying the storage and write cost to index millions of rows of dead data. You can solve this with partial indexes.

A partial index uses a WHERE clause to limit the rows included in the index. For example, if your application only queries active orders, you can exclude archived orders from the index:

CREATE INDEX idx_orders_active_tenant ON orders (tenant_id, created_at)
WHERE status != 'archived';

This index is smaller than a full index. It only stores pointers to rows that match the filter condition. When your application queries active orders, the query planner recognizes that the query filter is a subset of the index filter and uses the smaller index.

You can also use partial indexes to isolate giant tenants. If tenant 999 is causing performance issues because of their sheer volume of data, you can build an index specifically for them:

CREATE INDEX idx_orders_tenant_999_reporting ON orders (created_at)
WHERE tenant_id = 999;

For queries targeting tenant 999, Postgres will use this specialized index. For all other tenants, the database can use a general index or even perform a sequential scan if their data volume is tiny. This keeps the index footprint for your typical tenants small and clean.

The challenge with partial indexes is maintenance. If you hardcode tenant IDs into your index definitions, your database schema becomes coupled to your customer list. You will need automated scripts or migration pipelines to create and drop these indexes as large tenants onboard or offboard.

Partitioning Schemes for Physical Index Isolation

When your tables grow to tens of millions of rows, composite and partial indexes might not be enough. The physical table itself becomes too large to manage. Operations like VACUUM take hours, Postgres backups slow down, and index bloat becomes difficult to control.

Declarative partitioning allows you to split one logical table into multiple physical tables (partitions) based on a partition key. In a multitenant database, the natural choice for the partition key is tenant_id.

You can set up list partitioning to group tenants explicitly:

CREATE TABLE orders (
    id uuid NOT NULL,
    tenant_id integer NOT NULL,
    status text NOT NULL,
    created_at timestamp with time zone NOT NULL,
    PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST (tenant_id);

Note that Postgres requires the partition key (tenant_id) to be part of the primary key constraint. This ensures that unique constraints can be enforced locally within each partition.

Next, you create the actual partitions:

CREATE TABLE orders_tenant_group_1 PARTITION OF orders
    FOR VALUES IN (1, 2, 3, 4, 5);
 
CREATE TABLE orders_tenant_large PARTITION OF orders
    FOR VALUES IN (999);

When you partition a table, Postgres does not create a single global index. Instead, it creates a separate index for each partition.

When you run a query like SELECT * FROM orders WHERE tenant_id = 3 AND status = 'pending', the query planner performs partition pruning. It analyzes the query, determines that tenant 3 lives in orders_tenant_group_1, and completely ignores the other partitions.

This isolation means the database only searches the index for orders_tenant_group_1. Because that index only contains data for five tenants, it is small and fits easily in RAM. The massive index for tenant 999 lives in a separate physical file and does not compete for memory space during queries for tenant 3.

If you have thousands of small tenants, creating a partition for each one is a bad idea. Too many partitions will slow down the query planner because it has to analyze metadata for hundreds of tables before executing a query. For small tenants, you should use hash partitioning to distribute them across a fixed number of buckets (for example, 64 or 128 partitions):

CREATE TABLE orders_hash (
    id uuid NOT NULL,
    tenant_id integer NOT NULL,
    status text NOT NULL,
    created_at timestamp with time zone NOT NULL,
    PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);

You can then isolate your largest enterprise tenants into their own dedicated list partitions, while the long tail of smaller tenants shares the hashed partitions.

Write Amplification and Index Maintenance

Every index you add to a table slows down writes. When you insert a row, Postgres must write the data to the table (the heap) and then insert a new entry into every index on that table. If you have five indexes, one insert results in six write operations.

In high-throughput SaaS systems, this write amplification can saturate your disk I/O. You must regularly audit your indexes to ensure they are actually being used.

Postgres tracks index usage in the pg_stat_user_indexes system view. You can find unused indexes with this query:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0
    AND schemaname = 'public';

If an index has zero scans after running in production for a few weeks, it is safe to drop.

Another issue is index bloat. When you update a row in Postgres, the database does not overwrite the existing data. Instead, it writes a new version of the row and marks the old version as dead (this is Multi-Version Concurrency Control, or MVCC). The index entries continue to point to both the live and dead rows until the VACUUM process cleans them up.

If you have a high volume of updates, your indexes will grow bloated with dead pointers. This makes the index larger on disk and slower to search.

To reclaim this space without locking your application, use the REINDEX CONCURRENTLY command:

REINDEX INDEX CONCURRENTLY idx_orders_tenant_status;

This command builds a new index in the background, swaps it with the old one, and drops the bloated version. It requires more disk space during the build process, but it prevents table locks that would otherwise take your SaaS offline.

Balancing read performance against write latency requires constant monitoring. Start with composite indexes that put tenant_id first, use partial indexes to exclude cold data, and migrate to partitioning when your tables grow too large for a single index structure to fit in memory.