Multi-tenant SaaS on PostgreSQL: six decisions

A SaaS product with more than one customer in it locks six things into its database. Four of those we have written up separately before; two we went and measured for this article, because we could not find a measured answer anywhere — what it costs to restore a single tenant, and where schema-per-tenant actually breaks.

This is the overview page for those six. Per decision: what PostgreSQL gives you, whether you can still change it later, and where it is worked out in full.

Which decisions does a multi-tenant SaaS lock into PostgreSQL?

Six. One: the isolation model — a database per tenant, a schema per tenant, or shared tables with a tenant_id. Two: how you enforce that separation, and for shared tables that is row-level security with FORCE ROW LEVEL SECURITY on top. Three: the indexes, where tenant_id belongs at the front of every composite index. Four: the column type for money, numeric with an explicit scale or a whole number of cents. Five: how you delete or restore one tenant on its own, because pg_dump cannot filter rows. Six: how you migrate as the tenant count grows, and with schema-per-tenant that is the decision that hits a wall first.

Only decisions three and four are reasonably revisable later. The other four are schema decisions you have to convert with a migration over production data, and that is exactly why they belong in the first week rather than the second year.

The six in one table

The first four are decisions about what the data looks like. The last two are decisions about how you operate it, and they are almost always skipped — they do not hurt while there are ten customers.

1. The isolation model

Three models, and the choice nearly always lands on shared tables with a tenant_id: it is the cheapest to operate and it scales further than most teams expect. A database per tenant is the right answer when a customer contractually demands its own database or needs to be able to pick its own recovery point. Schema per tenant sounds like the middle ground and rarely is — see decision 6.

The full trade-off, with the three questions that settle it, is in multi-tenant architecture: which isolation model do you choose.

2. Enforcing separation

With shared tables, the tenant_id column is not the separation — the filter on it is, and a forgotten filter is a data breach. Row-level security moves that filter into the database, so that a raw query for a report, a background job with no logged-in user, or an import that bypasses the ORM cannot see another tenant’s rows.

That works, and it is easier to have off than on: the owner of a table bypasses its own policy without FORCE ROW LEVEL SECURITY, an empty tenant context is '' rather than NULL after a RESET, and a foreign key looks straight past the policy. The five ways RLS is silently not working are each measured in row-level security in PostgreSQL: where tenants leak.

3. The indexes

tenant_id belongs at the front of every composite index you read tenant data through. That is not a performance detail but the reason RLS costs nothing extra: the policy lands in the query plan as an Index Cond rather than as an additional filtering step. On 200,000 invoices of which 2,003 belong to one tenant, the policy condition becomes an Index Cond in a Bitmap Index Scan and the query’s own WHERE trails behind it as a Filter — measured, and consistent with what the documentation promises about evaluation order.

This is the only one of the six you can adjust on a running product without pain: CREATE INDEX CONCURRENTLY and done.

4. The column type for money

numeric with an explicit precision and scale for most products, a whole number of cents in bigint when amounts are mostly summed and travel back and forth to a payment provider. Not real or double precision, because those are not exact. Not money, because it cannot hold fractions of a cent and ties its decimals to the database setting lc_monetary.

The three candidates side by side, including the trap that a column with a scale rounds on write without saying anything, are in money in PostgreSQL: numeric, integer or money. Why a floating-point number cannot hold money at all is in money in a SaaS product: cents, not floats.

5. What does it cost to restore one tenant?

This is the question that actually settles the isolation model, and it almost never gets asked. A customer calls to say something was deleted, or a customer leaves and wants their data. What do you do?

pg_dump cannot filter rows. That is not a setting we missed: in PostgreSQL 17.11, pg_dump has --table, --exclude-table and --filter, and all three select objects, not rows. There is no --where. With shared tables, the backup of a single tenant does not exist as ready-made tooling; you write it yourself.

Writing it yourself is fast. In a database with 200,000 invoices and 200,000 invoice lines across 100 tenants, writing out every row belonging to one tenant (2,000 invoices, 2,000 lines) took 0.10 seconds with three COPY commands. Loading them back took 0.15 seconds. That is not the problem.

The problem is that you have to enumerate those commands yourself, and a table you forget silently does not come along. So let the database write the export script for you:

select format('\copy (select * from %I where tenant_id = :tenant) to ''/tmp/%s.csv'' csv',
              table_name, table_name)
from information_schema.columns
where column_name = 'tenant_id' and table_schema = 'public'
order by table_name;

And then ask the question that actually matters — which tables fall outside this:

select t.table_name
from information_schema.tables t
where t.table_schema = 'public' and t.table_type = 'BASE TABLE'
  and not exists (select 1 from information_schema.columns c
                  where c.table_schema = t.table_schema
                    and c.table_name = t.table_name
                    and c.column_name = 'tenant_id')
order by 1;

In our test database that second query returned exactly one table: tenants — the table that describes the tenant itself and therefore has no tenant_id. That is the correct outcome. Every other name that comes out is a table you need to be able to explain does not belong to a tenant.

The surprise: deleting is 182× more expensive than restoring

While measuring, one step fell completely out of line. Deleting that one tenant — 2,000 invoices and 2,000 invoice lines out of tables holding 200,000 rows — took 21.8 seconds, measured twice. Loading them back took 0.15 seconds.

The cause is not in the rows but in a foreign key without an index. invoice_lines.invoice_id references invoices.id, and the table had an index on tenant_id and on its own primary key — but not on invoice_id. For every invoice deleted, PostgreSQL then has to walk the whole invoice_lines table to check whether a line still references it. Two thousand times two hundred thousand rows.

The documentation says this outright, and it is one of the few places where PostgreSQL explicitly warns you that it is not doing something for you: “Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, the declaration of a foreign key constraint does not automatically create an index on the referencing columns.”

One extra index:

create index on invoice_lines (invoice_id);

Creating it: 0.14 seconds. The same delete afterwards: 0.12 seconds. From 21.8 down to 0.12 — a factor of 182, through an index that no read query in this product needed.

That is exactly the kind of cost you discover on the evening a customer calls. So walk through your schema once and find the referencing columns that have no index:

select c.conrelid::regclass as table_name,
       (select string_agg(a.attname, ', ')
        from unnest(c.conkey) k join pg_attribute a
          on a.attrelid = c.conrelid and a.attnum = k) as columns
from pg_constraint c
where c.contype = 'f'
  and not exists (
    select 1 from pg_index i
    where i.indrelid = c.conrelid
      and (i.indkey::smallint[])[0:array_length(c.conkey,1)-1] = c.conkey
  )
order by 1;

With a database per tenant or a schema per tenant, this whole question does not exist: deleting one tenant is DROP DATABASE or DROP SCHEMA … CASCADE, and in our test that took 0.10 seconds for a schema with ten tables. That is the honest win of those models, and the only one we have been able to measure.

6. Where does schema-per-tenant break?

Schema per tenant gets recommended with the argument that PostgreSQL handles “a few thousand schemas” fine. That number is everywhere and nowhere with a measurement under it, so we ran it ourselves: schemas with ten tables each, step by step up to 2,000 schemas, on PostgreSQL 17.11 with default settings.

The outcome is not that it gets slow. The outcome is that your backup stops working.

Everything in the first three columns grows perfectly linearly. A migration across 2,000 tenants costs a second; that is fine. But pg_dump --schema-only aborts:

pg_dump: error: query failed: ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".

In our setup the boundary sat between 12,068 and 13,068 tables — at 1,200 schemas the dump still succeeded, at 1,300 it did not. That is considerably lower than “a few thousand schemas”, and it is a hard stop rather than a slowdown: you have no schema dump, so you have no complete backup.

Why this happens is in the documentation: “The shared lock table has space for max_locks_per_transaction objects (e.g., tables) per server process or prepared transaction; hence, no more than this many distinct objects can be locked at any one time.” pg_dump takes a lock on every table it dumps, inside one transaction. With the defaults (max_locks_per_transaction 64, max_connections 100) there is room for roughly 6,400 objects — and because the documentation also states that individual transactions may lock more as long as all locks together fit in the table, the real boundary is higher and not exactly predictable. That last part is the unpleasant bit: the exact number depends on your settings and on whatever else the server is doing, so you cannot plan against it.

And the repair is more expensive than it looks. max_locks_per_transaction “can only be set at server start”. We measured that too: after ALTER SYSTEM SET max_locks_per_transaction = 256, pending_restart was true, a pg_reload_conf() changed nothing, and the same pg_dump failed again. After a restart it succeeded. Repairing your backup therefore requires restarting your database — on a managed platform that is a maintenance window you have to agree with your customers, on the evening you discovered you had no backup.

That is decision 6 in one sentence: with shared tables a migration is one ALTER TABLE and nothing grows along with the tenant list; with a schema per tenant the object count grows with your customer list, and the first thing to buckle under it is not your speed but your recovery plan.

Two answers, and that is deliberate

We build two SaaS products ourselves — booxx and Pilot-Next — and they do not land on the same answer to decision 2. That is not an inconsistency but a consequence of the question where do you want the mistake to show up.

One answer puts the separation in the database. Then the database itself refuses, including on a raw query for a report or a background job nobody anticipated — and you buy the five gaps described in where tenants leak along with it.

The other answer puts the separation in the application layer and the catch in architecture tests over the source code: a route may not take its tenant from the request, and a bulk mutation on a table without its own tenant column has to filter on its relation. That fails during the build rather than during the query, and it only covers what you thought to test for.

Which of the two fits a product depends on the three questions from the isolation model article — and on whether there is money or somebody else’s bookkeeping in the database. If there is, we want the database itself to refuse.

Check your own database in fifteen minutes

Four queries, and all four are worked out above or in the linked articles:

1. Which tables with a tenant column have no policy? The check query from gap 5 of the RLS article.
2. Is FORCE on, and is the application role not the owner? Gap 1 of the same article.
3. Which tables fall outside a tenant export? The second query from decision 5 above.
4. Which foreign keys have no index on the referencing column? The last query from decision 5.

Queries 3 and 4 cost five minutes together and are the two that essentially never get asked. If something comes out that you did not expect, that is not an incident but the normal outcome — while writing this article we found an index that no read query needs and that is the difference between 0.12 and 21.8 seconds.

---

If your product is running into one of these six, or the tenant separation still lives entirely in the application code of something that got bigger than planned — send us a mail. We build SaaS products where these six are settled from the first migration, and we take over and extend existing products where they are not.

Measured on 2026-09-21 on PostgreSQL 17.11 (Debian, in a container that was thrown away afterwards) with default settings: max_locks_per_transaction 64, max_connections 100, shared_buffers 128 MB. The scaling measurement used schemas with ten tables each; the export measurement a database with 100 tenants, 200,000 invoices and 200,000 invoice lines. The documentation quoted: Constraints and Lock Management, both read on 2026-09-21.