PostgreSQL indexes for multi-tenant SaaS: tenant_id first

“Put tenant_id first in every index” is the advice every multi-tenant guide gives, and almost none of them show what happens if you don’t. In multi-tenant SaaS on PostgreSQL: six decisions the indexes are decision three, and the only one of the six you can still change comfortably later. That makes it worth knowing what the wrong choice costs before you have to find out in production.

So we measured it: five indexes on the same table, the same query, one small and one large tenant, with and without row-level security, and with and without partitioning. PostgreSQL 17.10, default settings, warm cache, median of five runs.

Where does tenant_id go in a composite index?

First. On a table of 2 million invoices spread over 1,000 tenants, the index (tenant_id, issued_on) answered “this tenant’s 50 most recent invoices” in 0.28 ms for a small tenant and 0.09 ms for the largest one, reading 51 and 7 pages. Every other index we tried was fast for one of the two tenants and slow for the other: (issued_on) needed 26 ms for the small tenant, (tenant_id) alone needed 82 ms for the large one.

The reason is that a multi-tenant table is never evenly filled. The index that works for your largest customer can be exactly the wrong one for the other 999, and a test with one tenant will not show it.

The setup

One invoices table with a uuid tenant_id, a date, a status and an amount. The tenants are deliberately unequal, because real ones are: the tenant with rank r gets roughly 2,000,000 / (7.49 × r) invoices. That produces 2,000,003 rows (146 MB) where the largest tenant has 267,183 invoices, the median tenant 534 and the smallest 267. The rows are inserted in random order, as they would be when a thousand customers are all invoicing at the same time.

create table invoices (
  id bigint generated always as identity primary key,
  tenant_id uuid not null references tenants(id),
  number int not null,
  status text not null,
  issued_on date not null,
  amount numeric(14,2) not null
);

The query is the one every SaaS screen starts with:

select id, number, issued_on, amount
from invoices
where tenant_id = $1
order by issued_on desc
limit 50;

We ran it for the median tenant (534 invoices) and the largest (267,183), each time with exactly one secondary index in place.

Five indexes, one query

A page is 8 kB; the count is the Buffers line from EXPLAIN (ANALYZE, BUFFERS).

What each row tells you:

• (issued_on) is perfect for the large tenant: walk the dates backwards and nearly every row is theirs. For the small tenant PostgreSQL walks the same dates and throws away 339,000 rows belonging to others before it has 50. The larger your largest customer, the worse this gets for everyone else.
• (tenant_id) is the mirror image. For the small tenant it finds 534 rows and sorts them. For the large tenant it finds 267,183 rows scattered over the whole table, fetches all of them and then sorts to keep 50 — slower than having no index at all.
• (issued_on, tenant_id) contains both columns and still reads almost nine times as many pages for the small tenant as the right order does, because tenant_id can only be checked inside the index, not used to jump to the right place.
• (tenant_id, issued_on) jumps to the tenant and then reads the dates in order. Fifty rows, and it stops.

That is the rule behind “tenant_id first”: equality on the tenant, then whatever the query sorts or ranges on. It also means the index that serves this screen is a 32 MB index, not a 14 MB one — you pay for it in storage and in every write.

Does row-level security change the plan?

No. With a policy using (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid), FORCE ROW LEVEL SECURITY and the query without a WHERE, the plan was the same Index Scan Backward on (tenant_id, issued_on) with the policy as the Index Cond: 51 pages and 0.28 ms for the small tenant, 7 pages and 0.11 ms for the large one. That matches what we measured earlier in row-level security in PostgreSQL: where tenants leak: with the right index the policy is not an extra filter, it is the index lookup.

The planner also estimates correctly under RLS. For the large tenant it expected 268,134 rows and found 267,183; for the small one it expected 674 and found 534. current_setting() is evaluated when the query is planned, so the statistics for that specific tenant are used. That is good news, and it is exactly what sets up the trap below.

Why does a query get slow for one tenant under RLS?

Because a prepared statement plans once, and under RLS the tenant is not a parameter. When the tenant comes from current_setting(), the statement has no parameters, and for that case the PostgreSQL documentation is explicit: “if the prepared statement has no parameters, then this is moot and a generic plan is always used”. The plan made for the first tenant is reused for every tenant after it on that connection.

We measured it with a per-status total, select status, count(*), sum(amount) from invoices group by status, prepared once and then executed for a different tenant:

The first case reuses a plan built for 674 rows on 267,183 rows, without parallel workers. The second is worse in relative terms: a parallel plan built for the large tenant, reused for a tenant with 534 rows, is eleven times slower than it needs to be. On a connection pool, which tenant was first on a connection is chance.

Setting plan_cache_mode = force_custom_plan does not help: that setting only applies to statements with parameters, and we measured 151 ms for the frozen case with it switched on.

What does help is giving the planner a parameter. Keep the policy as the guard, and pass the tenant in the query as well:

prepare tenant_totals(uuid) as
  select status, count(*), sum(amount)
  from invoices
  where tenant_id = $1
  group by status;

Prepared for the small tenant and executed for the large one, this took 90 ms, with the parallel plan the large tenant needs. The policy still blocks any row of another tenant if the parameter is ever wrong; the parameter only tells the planner which tenant it is planning for. Whether your driver prepares statements behind your back differs per driver and per setting, so check yours rather than assuming it does not.

Does uuid or integer matter for tenant_id?

For the index size, yes. The same (tenant_id, issued_on) index on 2 million rows is 32 MB with a uuid and 23 MB with an integer, a quarter smaller. We would not switch an existing product for that — the key type is part of decision one and changing it is a migration over every table — but for a new product it is a real cost to weigh against the reasons for choosing uuid.

When should you partition by tenant?

Not for query speed at this size. We copied the same 2 million rows into two partitioned tables, 16 hash partitions on tenant_id and a list partition that gives the largest tenant its own table, with the same index and the same RLS policy.

Partition pruning works under RLS: the policy’s current_setting() is resolved when the query starts, and EXPLAIN showed Subplans Removed: 15 for the hash table. The large tenant’s total read 6.3 and 7.5 times fewer pages. It was not faster, because adding up 267,183 rows costs the same wherever they are and the 146 MB table fitted in memory. The documentation’s own rule of thumb is that partitioning pays off when “the size of the table should exceed the physical memory of the database server”.

Where partitioning did make a difference is operations. Removing the large tenant took 141 ms as a DELETE and 4.4 ms as DETACH PARTITION plus DROP TABLE, and a detached partition can be dumped with pg_dump --table on its own. That is the case for giving one very large customer its own partition: leaving, restoring and vacuuming that customer — not reading their invoices faster.

How do you check your own schema?

This query lists every index on a table with a tenant_id column where tenant_id is not the first column. We tested it on the table above with two deliberately wrong indexes, (issued_on) and (status, tenant_id); it returned exactly those two and not the correct ones.

select i.indrelid::regclass  as table_name,
       i.indexrelid::regclass as index_name,
       pg_get_indexdef(i.indexrelid) as definition
from pg_index i
join pg_attribute t
  on t.attrelid = i.indrelid
 and t.attname = 'tenant_id'
 and not t.attisdropped
where i.indkey[0] <> t.attnum
  and not i.indisprimary
order by 1, 2;

Not every hit is wrong. An index that only serves an internal job across all tenants may rightly start with something else. But every hit that sits under a screen your customers use is a candidate, and a unique constraint in the list — unique (number) rather than unique (tenant_id, number) — is usually a bug rather than a performance issue.

Changing an index is the cheap part: CREATE INDEX CONCURRENTLY builds the new one without blocking writes, after which you drop the old one.

What we did not measure

A cold cache, a table larger than memory, PostgreSQL 18, and the write cost of the larger index. All numbers above come from one machine with default settings (shared_buffers 128 MB), so read them as proportions rather than as times you will see in production. The proportions are the point: in every test the index with tenant_id first was the only one that was fast for both tenants.

Sources: the PostgreSQL 17 documentation on PREPARE (generic and custom plans) and on table partitioning (pruning and when partitioning pays off), both read on 27 September 2026. All measurements are our own, on PostgreSQL 17.10, on 27 September 2026.