Every SaaS with more than one customer locks in six PostgreSQL decisions

A survey from the Now-Next blog measured what was missing from this debate: how much it costs to restore a single tenant on its own, and at exactly what point schema-per-tenant breaks backup.

Every SaaS product that starts serving more than one customer on the same database locks in, without realizing it, six design decisions in PostgreSQL. The market already discusses four of them (isolation, separation, indexes, the column type for money). The other two almost never show up in any article because they require measurement, not opinion: how much it costs to restore a single tenant, and at what point the schema-per-tenant model stops scaling. That is exactly what the Now-Next blog set out to measure, publishing the results on Planet PostgreSQL, with tests run on September 21, 2026 against PostgreSQL 17.11 in a default configuration.

This angle matters to anyone building multi-tenant SaaS from the very first migration, because of the six decisions, only two are reversible later without pain. The other four are schema decisions that require a migration over production data to undo, which is why they belong to the first week of the project, not the second year, when real customers already depend on the outcome.

Isolation: database, schema, or a tenant_id column

The first decision is the isolation model: one database per tenant, one schema per tenant, or shared tables with a tenant_id column. According to the analysis, the choice almost always falls on shared tables: it is the cheapest model to operate and scales further than most teams expect. Database per tenant is only justified when a customer contractually requires its own instance or needs to choose its own recovery point. Schema per tenant looks like the obvious middle ground, and rarely is; the reason shows up further ahead, in decision six, and it is the most counterintuitive part of the article.

This decision cannot be revised without a full migration over every tenant's data. Getting it wrong here is not a performance bug, it is rebuilding the entire schema in production.

Separation isn't the column, it's the filter

With shared tables, tenant_id is not the separation: the filter applied to it is. A forgotten filter is a data leak from one customer to another. The way out is to move that filter inside the database with row-level security, using ENABLE ROW LEVEL SECURITY and, crucially, FORCE ROW LEVEL SECURITY; without FORCE, the table owner ignores its own policy, and it is precisely the application account that tends to own the tables.

The point the analysis highlights is that RLS is easier to have turned off than turned on: an empty tenant context becomes '' instead of NULL after a RESET, and a foreign key simply does not pass through the policy. These are silent failures, not errors that show up in a log.

Index: tenant_id at the front of the composite

The third decision is about indexes, and it is the only one of the six that can be adjusted without pain on a production product, via CREATE INDEX CONCURRENTLY. The rule is simple: tenant_id goes at the front of every composite index used to read tenant data. The reason isn't just speed; it's what makes RLS cost nothing extra. In the test, over 200,000 invoices of which 2,003 belonged to one tenant, the policy condition shows up in the execution plan as Index Cond inside a Bitmap Index Scan, with the query's own WHERE running afterward, as Filter. In other words: the security policy disappears into the plan, it doesn't add an extra filter.

Money: numeric or bigint, never floating point

The fourth decision, partially reversible, is the column type for monetary values. The recommendation is numeric with explicit precision and scale for most products, or a whole number of cents in bigint when values are mostly summed and travel to and from a payment provider. Left out are real and double precision, which are not exact, and the money type, which doesn't store fractions of a cent and ties its decimal places to the server's lc_monetary setting, a detail that changes behavior depending on where the database is installed.

What it costs to restore a single tenant

Here is the part that most articles about multi-tenant never measure: what to do when a customer calls saying something was deleted, or when a customer leaves and asks for their data back. pg_dump, even in version 17.11, has --table, --exclude-table, and --filter, but all of them select objects, not rows; there is no --where. With shared tables, backing up a single tenant doesn't exist as a ready-made tool; it has to be written.

The good news is that writing that export is fast: in a database with 200,000 invoices and 200,000 invoice lines spread across 100 tenants, exporting the 2,000 invoices and 2,000 lines of a single tenant took 0.10 second with three COPY commands. Reloading took 0.15 second. The practical trick, suggested in the article, is to generate the export commands from the catalog:

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, to ask what really matters: which tables are left out of that export because they don't have tenant_id; in the test, the only table left out was the one describing the tenant itself, an expected result. Any other name that shows up on that list is a table someone needs to be able to explain.

The surprise: deleting costs 182 times more than restoring

The number that broke the authors' expectations was different: deleting that same tenant (2,000 invoices and 2,000 lines, within tables holding 200,000 rows) took 21.8 seconds, measured twice. Loading it back had taken 0.15 second.

The cause isn't the rows, it's 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 has to scan the entire invoice_lines table to check whether any row still references that id. Two thousand times, over two hundred thousand rows. PostgreSQL's documentation warns about this explicitly: declaring a foreign key does not automatically create an index on the referencing column, because it isn't always necessary and there are several ways to index it. In this case, it was necessary and nobody had noticed, because no read query in the product needed that index.

Creating the index took 0.14 second. The same delete afterward, 0.12 second: from 21.8 seconds to 0.12, a factor of 182 times, through an index that no read query ever asked for. This is exactly the kind of cost that shows up on the night a customer calls, and the check for foreign keys without a matching index can be done before that day, by querying pg_constraint and pg_index.

With database per tenant or schema per tenant, this entire question disappears: deleting a tenant is DROP DATABASE or DROP SCHEMA ... CASCADE, and in the test that took 0.10 second for a schema with ten tables. That is the real advantage of these two models, and the only one the analysis managed to measure honestly.

Where schema-per-tenant breaks

Schema per tenant is usually recommended on the idea that PostgreSQL handles "a few thousand schemas" well. That number circulates without any measurement behind it, and that's exactly what the authors tested: schemas with ten tables each, scaling up to 2,000 schemas (20,000 tables) on PostgreSQL 17.11 with a default configuration.

The result isn't that it gets slow; each step grew linearly, and a migration via ALTER TABLE over 2,000 tenants cost just over a second. The result is that backup stops working. At 1,200 schemas, pg_dump --schema-only still worked. At 1,300 schemas (somewhere between 12,068 and 13,068 tables), it failed with:

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

The reason is in the documentation: the shared lock table has room for max_locks_per_transaction objects per server process, and pg_dump locks every table it exports inside a single transaction. With the defaults (max_locks_per_transaction 64, max_connections 100), there is room for around 6,400 objects, a number that isn't exact because it depends on configuration and on whatever else the server is doing, which makes the real limit hard to plan for in advance.

And the fix is more expensive than it looks: max_locks_per_transaction can only be changed at server startup. In the test, an ALTER SYSTEM SET max_locks_per_transaction = 256 left pending_restart marked as true, and a pg_reload_conf() changed nothing; the same pg_dump failed again until the database was restarted. Fixing the backup, therefore, requires restarting the production database, which on a managed platform means negotiating a maintenance window with customers on the very night it was discovered there was no backup.

Four queries to run today

The analysis closes with a fifteen-minute checklist any team can run against its current database: which tables with a tenant column have no RLS policy; whether FORCE is active and the application role isn't the owner of the tables; which tables are left out of a per-tenant export; and which foreign keys have no index on the referencing column. The last two, according to the authors, are practically never done, and it was by running exactly this check that they found the missing index that stood between 0.12 second and 21.8 seconds.

For anyone designing a multi-tenant SaaS from scratch, the practical takeaway is this: of the six decisions, isolation and separation enforcement demand the right choice in the first migration, because fixing them later means rebuilding the schema over real data; index and monetary type tolerate incremental adjustment; and restoring or deleting a tenant, along with schema scale, are operations whose real cost is only revealed under measurement, not under the assumption that "PostgreSQL can handle it".