In this guide 6 sections
A shared database with a tenant column is cheapest and scales to thousands of accounts, while a schema for each customer separates data better but multiplies migrations. A database for each customer gives the strongest isolation and simplest per customer restore, but costs more per account in memory, connections and backup storage. Choose by customer count and restore needs.
What the three layouts actually are
In a shared database, every customer’s rows live in the same tables and a tenant_id column says who owns each row, so every query must filter on it. One missed filter leaks data.
Keeping one database but giving each customer a private set of tables, such as a PostgreSQL schema, is the middle option, with one server to patch. Queries run against one namespace chosen at login.
The third option gives each customer a whole database with its own connection string, credentials and backup schedule, so nothing is shared except the application servers.
- Shared: one set of tables, tenant_id on every row
- Separate schemas: one server, one set of tables per customer
- Own database: one database, one credential set per customer
Isolation and the cost of a mistake
The shared layout depends on discipline. Row-level security in PostgreSQL helps because the database enforces the filter instead of the developer, whereas a forgotten WHERE clause in an export can expose another customer’s invoices.
Separate schemas narrow the blast radius. A broken query hits one namespace. Full databases narrow it further, because a leaked password opens a single customer’s data and never the whole customer base, whichever way the credential was stolen.
Enterprise buyers often ask for this separation in a security questionnaire or a procurement call, so write your answer down before the first call.
Migrations, backups and restoring one customer
Shared tables migrate once. Add a column. Run one script. Finished. With a schema for each customer you run that script hundreds of times, and a failure halfway through the loop leaves customers on different table versions.
Backups reveal the real trade. With a shared database, restoring one customer means extracting their rows from a full dump into a scratch copy and merging them back by hand. That is slow. It invites mistakes.
Separate databases make it routine. Restore that customer’s backup to a chosen point in time and nobody else notices, so if customers demand this in their contract, weigh it heavily when you pick.
- Shared: one migration, hard single-customer restore
- Schema: many migrations, restore by schema dump
- Database: many migrations, simple point-in-time restore
- Offboarding is a row delete, a schema drop or a database drop
Noisy neighbours and cost per account
One heavy customer running a large export can slow everyone in a shared layout unless you add query timeouts, read replicas and per tenant rate limits in advance. Separate databases contain the problem by design.
Cost follows the same line. Shared tables serve ten thousand small accounts on modest hardware, whereas a database per customer carries a fixed overhead each, in memory, connections, monitoring and backup storage.
A mixed model is common. Small accounts share. Large or regulated customers get their own database on request, billed at a rate that covers the extra memory, connection slots and backup storage it consumes.
A rule of thumb for choosing
Under fifty customers with high contract values and strict data demands, database per tenant is affordable and easy to defend in a security review. Past a few hundred small accounts, shared tables with row-level security win on cost and operations, since one migration, one backup and one monitoring setup serve everybody.
Whatever you choose, build the routing in one place in the code so a later move from shared tables to separate databases touches one layer and not every query. Write down your isolation promise and your restore promise this week, then pick the layout that can keep both without a rewrite when your first large customer signs.
Tenant layout decision sheet
A one page table has three columns for shared, schema and database layouts, with rows for isolation strength, migration effort and single-customer restore time. Further rows cover noisy neighbour controls, monthly cost per account and offboarding steps, while a top block records expected customer count, largest customer size and contractual separation demands.
Open a blank worksheet to printQuestions owners ask
Is a shared database secure enough for multi tenant SaaS?
Yes, when the database enforces the tenant filter, for example with PostgreSQL row-level security, and automated tests prove that no query, report or export skips it. Some regulated buyers still demand separate databases.
Can I move from shared to database per tenant later?
It is possible but takes planning. Keep the tenant_id column clean and route connections through one layer, then copy each customer’s rows to a new database one at a time.
How do I restore one customer’s data by mistake?
In a shared layout, restore the full backup to a scratch database, extract that customer’s rows and merge them carefully after checking the row counts. A separate database restores on its own.
Which layout is cheapest to run?
Shared tables, by a wide margin. Separate databases cost more per account but can be justified for a few large customers whose contracts demand isolation or restore on request.