One partition per tenant in PostgreSQL, measured

Giving every customer their own partition of a shared table sounds like the best of both worlds: one schema, one set of migrations, and each tenant’s rows physically apart, ready to be detached and dropped when the customer leaves. It works — until the tenant filter lives only in a row-level security policy. Then the planner cannot see which customer a query is for, and it plans for all of them, every time.

We measured it. With one list partition per tenant and the tenant only in the RLS policy, one small query took 2.0 ms with 100 tenants, 77 ms with 1,000 and 241 ms with 3,000 — against 0.23–0.27 ms when the same query also said WHERE tenant_id = …. With 1,000 partitions and 32 concurrent clients, 20 or more of the 32 stopped with out of shared memory. The fix is one predicate per query, and a different command for adding a tenant.

The setup

PostgreSQL 17.10 in a throwaway container, default settings (max_locks_per_transaction 64, max_connections 100, two parallel workers per query), repeated on PostgreSQL 18.3. Measured on 4 October 2026.

• One table, invoices (tenant_id, id, amount_cents, issued), primary key (tenant_id, id), partitioned by list (tenant_id) with one partition per tenant: 100, 1,000 and 3,000 tenants, 200 invoices each. For comparison the same table unpartitioned (1,000 tenants) and with 16 hash partitions.
• Row-level security forced, with the tenant in a session variable: using (tenant_id = nullif(current_setting('app.tenant', true), '')::int). Queries run as an ordinary role, not the table owner.
• Each transaction sets the tenant with set_config('app.tenant', …, true) and adds up that tenant’s invoices — once as SELECT sum(amount_cents) FROM invoices, relying on the policy, and once with WHERE tenant_id = … added. pgbench, one client, 8 seconds per run, simple query protocol, at least two runs for each latency in the first table; the other results are single runs unless a range is given.

What does one query cost with a partition per tenant?

Average latency per transaction. The answer is the same in both columns — one tenant’s 200 invoices. With the predicate, the number of partitions made no difference. Without it, 1,000 partitions made the same query three hundred times slower than with it.

Where do the 77 milliseconds go?

Into three things, and only one of them is reading data.

Planning every partition. The value in the policy comes from current_setting(), which the planner does not know when it plans. The PostgreSQL documentation describes two moments at which partitions can be pruned: during planning, for values the planner knows, and “during initialization of the query plan” for values known only when execution starts. The policy’s tenant is the second kind. So the planner works through all 1,000 partitions and the executor removes 999 of them at the start: EXPLAIN ANALYZE showed Subplans Removed: 999 and 26 ms of planning (84 ms with 3,000 partitions, 1.4 ms with 100). With WHERE tenant_id = 7 the planning took 0.05 ms.

A parallel plan. Planned for 1,000 partitions, the query looked big enough to the planner for a parallel plan with two workers, while the actual work was two buffer hits. With max_parallel_workers_per_gather = 0 the same policy-only query took 29 ms instead of 77 (3,000 partitions: 101 ms instead of 241; 100 partitions: 1.85 instead of 1.98). Switching parallelism off is not the fix — it is a server-wide trade-off, and 29 ms is still a hundred times too slow.

Locks. Inside the transaction, after the query, we counted the relation locks the session held:

Two per partition — the partition and its primary-key index — plus the parent table, its index and the pg_locks view we counted with. The documentation is explicit that partitions removed at initialization “are still locked at the beginning of execution”. For one query on its own the locks were cheap; for many at once they were not — see below.

Do prepared statements help?

Partly, and they can hurt. A policy-only query has no parameters, so PostgreSQL reuses one generic plan and stops planning. That took the policy-only query to 0.18 ms with 100 partitions, 48 ms with 1,000 and 142 ms with 3,000 — and without parallel workers to 0.18, 0.43 and 1.07 ms. The 2,003 locks are still taken on every execution.

A prepared query with the tenant as a parameter, WHERE tenant_id = $1, stayed at 0.22 ms with 1,000 partitions in the default plan_cache_mode = auto: EXPLAIN EXECUTE after six executions still showed invoices_t7 and tenant_id = 7, a custom plan with the value filled in. Forced to a generic plan with plan_cache_mode = force_generic_plan, the same query took 57 ms — the planner no longer sees the value, and we are back at the policy-only case.

What happens with more than one client?

The locks have to fit somewhere. The shared lock table has room for max_locks_per_transaction locks per server process, and transactions can borrow from each other “as long as the locks of all transactions fit in the lock table”. With 3,000 partitions, four clients taking 6,003 locks each still ran. With 32 clients, 29 of 32 aborted within six seconds on:

ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".

Without parallel workers it was 31 of 32; with prepared statements and no parallel workers, all 32. With 1,000 partitions — 2,003 locks per query — 32 clients still lost 20 to 32 of them per run, in each of four runs. The same 32 clients on 3,000 partitions with WHERE tenant_id = … in the query: 20,470 transactions per second, 1.6 ms each, no errors. Raising max_locks_per_transaction treats the symptom; the query still locks every partition.

Does PostgreSQL 18 change this?

Not the shape. On PostgreSQL 18.3 the policy-only query took 55 ms with 1,000 partitions and 160 ms with 3,000 (without parallel workers 22 and 83 ms), against 77 and 241 ms on 17 — lower, and still 2,003 and 6,003 locks for one tenant’s invoices. With the predicate, planning stayed at 0.04–0.06 ms and execution under 0.1 ms on both versions.

Is adding a tenant a problem?

It can be. Onboarding a customer now means creating a partition, and CREATE TABLE … PARTITION OF invoices takes an ACCESS EXCLUSIVE lock on the whole partitioned table. With one other tenant reading their own invoices in an open transaction, our CREATE TABLE … PARTITION OF waited until its lock_timeout of 2 seconds ran out, and failed.

The alternative from the documentation went through while the reader was still there: create the table on its own (CREATE TABLE … (LIKE invoices INCLUDING ALL)), add a CHECK (tenant_id = …) constraint, and ALTER TABLE invoices ATTACH PARTITION …. That takes only a SHARE UPDATE EXCLUSIVE lock on the parent; it took 23 ms. The CHECK constraint spares the attach a scan of the new table — irrelevant for a new, empty tenant, useful when you attach a filled one — and the documentation advises dropping it afterwards. The new table gets no grants of its own; leave it that way, so the application only ever reaches it through the parent and its policy.

Removing a tenant has the mirror-image problem — detaching and dropping a partition is measured in deleting one tenant in PostgreSQL.

What we would build

The PostgreSQL documentation gives the same warning without the RLS angle: if you choose one partition per customer and the number of customers grows, “it may be better to choose to partition by HASH and choose a reasonable number of partitions”. Under RLS the planner never sees the customer, so in our measurement 100 customers already made the query eight times slower, and 1,000 three hundred times.

Why partition at all, and what it does and does not speed up for a single large customer, is in indexes for a multi-tenant table: tenant_id first. The policy itself, and the gaps it leaves, are in row-level security in PostgreSQL: tenant isolation.

What we did not measure

Tables of real size — 200 invoices per tenant fits in memory, so these are the fixed costs of planning, parallel start-up and locking, not of reading data. A list partition for a few very large customers with a default partition for the rest: adding a partition then also has to check the default partition. Memory per session: the documentation warns that each partition’s metadata is loaded into “the local memory of each session that touches it”. Joins between two tables partitioned the same way, sub-partitioning, more than 3,000 partitions, and INSERT and UPDATE routing. The timings come from one machine and are for comparison, not for capacity planning.

This is one of the decisions that is cheap early and expensive later; the rest of that list is in multi-tenant SaaS on PostgreSQL: six decisions.

Sources: the PostgreSQL 17 documentation on table partitioning (pruning during planning and initialization, locks on pruned partitions, ATTACH PARTITION versus CREATE TABLE … PARTITION OF, best practices) and on lock management (max_locks_per_transaction), read on 4 October 2026. All measurements are our own, on PostgreSQL 17.10 and 18.3, on 4 October 2026.