Home · About · Services · Industries · Projects · Products · Process · Blog · FAQ · Contact
Multi-tenant SaaS database design: shared, schema or separate DB Shared tables, schema per tenant or a database per customer: how the three multi-tenant models compare, and how to make the shared model safe with Postgres…
A founder shows us the first version of their SaaS product. It works, customers are signing up, and every customer's data sits in the same tables with a company_id column. Then a larger prospect asks where their data is stored, whether it is separated from other customers, and whether it can live in a specific region. The database design that was fine for ten customers is now part of a sales conversation.
Short answer: in multi-tenant SaaS database design, most B2B products should start with a shared database and shared schema, with a tenant_id on every tenant-owned table and isolation enforced by the database, not only by application code. Add a separate database for the few customers who need it. Schema-per-tenant rarely pays off beyond a few hundred tenants.
The rest of this article explains why, what each model costs you in operations, and how to build the shared model so it stays safe as it grows. The examples use PostgreSQL, but the trade-offs apply to most relational databases and to MongoDB.
The three models in plain terms Shared schema (pool): one database, one set of tables, every row carries a tenant_id. Cheapest to run and simplest to migrate. Isolation depends on every query filtering correctly, which is why you want the database to enforce it. Schema per tenant (bridge): one database, one Postgres schema per customer, identical tables in each. Data is separated by namespace, but tenants still share CPU, memory, connections and backups. Database per tenant (silo): each customer gets their own database, sometimes their own server. Strongest isolation, easiest per-customer backup, restore and deletion, and the highest cost and operational load. These are not exclusive. Many mature products run a shared pool for most customers and a handful of dedicated databases for enterprise contracts, with the same code and the same migrations for both.
How the models compare in practice The decision usually turns on five things: how many tenants you expect, how uneven their sizes are, what isolation customers will ask for, how often you change the schema, and who will operate the system at 2am.
Migrations: shared schema runs each migration once. Schema-per-tenant and database-per-tenant run it hundreds or thousands of times, and you need tooling to track which tenants are on which version when one fails halfway. Catalogue size: every Postgres schema copies all its tables, indexes and sequences into the system catalogue. With 2,000 tenants and 80 tables each you have 160,000 tables, plus their indexes and sequences. pg_dump, autovacuum and some admin tools slow down noticeably at that scale. Connections: one database means one connection pool. Database-per-tenant needs a pool per database, or a pooler such as PgBouncer configured for many databases, and idle connections add up fast. Noisy neighbours: in shared models one heavy tenant's report can slow everyone. Dedicated databases contain that, at the price of paying for capacity each tenant mostly does not use. Per-tenant operations: restoring one customer's data, exporting it or deleting it on contract end is trivial with separate databases, workable with schemas, and needs careful scripts with a shared schema. Cross-tenant reporting: easy in a shared schema, awkward with schemas, and a data pipeline project with separate databases. Schema-per-tenant looks like a comfortable middle ground, and for tens of tenants it can be. It is often the model teams regret, because it carries most of the migration pain of separate databases without the isolation benefits customers actually ask for.
Building the shared schema properly The risk in a shared schema is a missing WHERE clause that shows one customer another customer's invoices. The fix is to make the tenant part of the data model and let the database refuse cross-tenant access.
Put tenant_id on every tenant-owned table, including child tables, even when it looks redundant. Make it part of primary and foreign keys, so a row can only reference a parent belonging to the same tenant.
CREATE TABLE tenants (
id uuid PRIMARY KEY DEFAULT uuidv7(), -- PostgreSQL 18; use gen_random_uuid() on older versions
name text NOT NULL
);
CREATE TABLE projects (
tenant_id uuid NOT NULL REFERENCES tenants(id),
id uuid NOT NULL DEFAULT uuidv7(),
name text NOT NULL,
PRIMARY KEY (tenant_id, id)
);
CREATE TABLE tasks (
tenant_id uuid NOT NULL,
id uuid NOT NULL DEFAULT uuidv7(),
project_id uuid NOT NULL,
title text NOT NULL,
PRIMARY KEY (tenant_id, id),
FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)
);The composite foreign key means a task cannot point at another tenant's project, whatever the application does. Leading indexes with tenant_id also keep each tenant's rows close together in the index, which helps almost every query, since almost every query filters by tenant. Unique constraints usually need the same treatment: an email should be unique per tenant, so the constraint is UNIQUE (tenant_id, email), not UNIQUE (email).
Let Postgres enforce isolation with row-level security Row-level security (RLS) adds a filter to every query on a table based on a policy. The application sets the current tenant once per transaction, and queries without a matching tenant return nothing instead of everything.
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tasks
USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);
-- Repeat for projects and every other tenant-owned table.
-- In the application, at the start of each transaction:
BEGIN;
SELECT set_config('app.tenant_id', '0199a3c2-6f1e-7c4b-9b1a-2f5e8d7c6b10', true);
SELECT * FROM tasks; -- only this tenant's rows
COMMIT;A few details decide whether this actually protects you:
Connect the application as a role that does not own the tables and is not a superuser. Superusers and roles with BYPASSRLS skip policies entirely. FORCE ROW LEVEL SECURITY makes the policy apply to the table owner too. Set the tenant with set_config(..., true) or SET LOCAL, which scopes it to the transaction. With PgBouncer in transaction mode a session-level SET can leak into the next request that borrows the same server connection. Passing true as the second argument to current_setting returns NULL when the setting has never been defined. Once a pooled connection has set it in an earlier transaction, it reverts to an empty string instead, and an empty string cast to uuid raises an error. The nullif(..., '') wrapper makes both cases behave the same: a forgotten tenant gives zero rows. Some teams prefer the error. Either is better than all rows. Keep the tenant_id = ... comparison simple so the planner can use your (tenant_id, ...) indexes. Policies that call complex functions per row can make queries much slower. Run migrations, background jobs that span tenants and support tooling under a separate, audited role, rather than giving the application role a bypass. Add an automated test that creates two tenants, writes data as one, and asserts the other sees nothing on every API endpoint. It is cheap to write and catches the regressions that code review misses.
When separate databases are the right call Dedicated databases are worth their cost when a customer's contract or regulator requires data in a specific region, when one tenant is far larger than the rest, when customers expect their own restore point or encryption keys, or when you sell a self-hosted edition anyway. Keep it a deployment option, not a different product:
One codebase and one migration set, applied to the shared pool and each dedicated database by the same pipeline. A tenant directory (a small control-plane database) that maps each tenant to its database, region and plan, so routing is a lookup rather than code. Keep tenant_id in the dedicated databases too. It costs little and lets you move a tenant between pool and silo with an export and import instead of a rewrite. Monitoring, backups and restore tests per database. Ten silos are ten things to watch. If a single shared database becomes too large for one server, sharding by tenant_id is the natural next step, because nearly every query already includes it. The Citus extension for Postgres distributes tables by a column such as tenant_id, and an application-level tenant directory can split tenants across several ordinary Postgres clusters. Both are much easier when the schema was tenant-keyed from day one.
What about MongoDB? The same reasoning applies. A tenantId field on every document, with tenantId as the first field of every compound index, is the shared model. Database-per-tenant works for small numbers of tenants, but each database adds collections and indexes, and WiredTiger keeps a file per collection and per index, so thousands of tenant databases strain a single deployment. MongoDB has no row-level security for application users, so enforce the tenant filter in one data-access layer that every query passes through, and test it the same way.
A short decision checklist Default to a shared schema with tenant_id on every tenant-owned table, in primary keys, foreign keys and unique constraints. Turn on row-level security with a non-owner application role and transaction-scoped tenant settings. Write a cross-tenant leak test and run it in CI. Script per-tenant export and deletion before the first customer asks for it. Offer dedicated databases only for customers with a concrete need, using the same code and migrations. Avoid schema-per-tenant unless you will stay under a few hundred tenants and have a clear reason for it. Get a second opinion before launch if you are unsure. Changing the tenancy model with live customers is a migration project, not a refactor.
Infigini IT Services · 11 Kanwali Road, Dehradun 248001, India · info@infigini.com · +91 95684 87789