Shared tables vs schema per tenant vs database per tenant

The decision

A product that serves many business customers calls each customer a tenant. You must decide how to keep their data apart. With shared tables, all tenants use the same tables, and every row has a tenant_id column. With a schema per tenant, each tenant has its own set of tables inside one database. With a database per tenant, each tenant has a separate database. Moving down this list gives stronger isolation and an easier restore for one customer. A noisy neighbour, a tenant that uses too much, also does less harm, but only once the database itself is separate. The cost is a higher bill, slower migrations and more things to run. No option is right for every product.

Choose shared tables when

• You expect thousands of tenants, and most are small, with low revenue each.
• Every tenant uses the same schema.
• You want cheap sign-up and fast changes: one migration, one deploy.
• You can enforce the tenant filter in one place, in the database or in a shared data layer, not in every query.

Choose a schema per tenant when

• You have tens to a few hundred tenants, and each wants some custom fields or its own export.
• You want logical separation and easy per-tenant export, without many databases.
• Your migration tool can run a change across all schemas in reasonable time.

Choose a database per tenant when

• Tenants are large and pay enough, for example Rs 2 lakh a month each (one lakh is 100,000). Or a law or contract demands separate storage.
• A tenant needs its own restore point, its own encryption keys or a data location of its own.
• One tenant's load must not slow another's.
• You can automate creating and upgrading hundreds of databases.

The numbers that change the answer

All of these are Assumptions unless marked as calculation or cited.

Leak risk (calculation). In shared tables, each query must filter by tenant. Assume the code has 400 queries, and each has a 0.1 percent chance of missing the filter. The chance that at least one misses it is 1 - 0.999^400 = 33 percent. Now suppose a shared layer or a database rule adds the filter, and the chance per query is 0.01 percent. The total is 1 - 0.9999^400 = 3.9 percent. PostgreSQL has row-level security: policies that the database applies to every query. When it is on and no policy exists, no row is visible. Superusers, roles marked to bypass it, and table owners (unless forced) skip the rules. So the application must connect with a limited role (see Sources).

Migration time (calculation). Assumption: 60 tables per tenant, 5,000 tenants. A schema per tenant means 60 x 5,000 = 300,000 tables. A change takes 0.5 s for each schema. That is 5,000 x 0.5 = 2,500 s, or 41.7 minutes, if done one after another. If a few fail, two versions of the schema run at once. Shared tables need one migration, but on one very large table. Database per tenant has the same 5,000 steps, on 5,000 databases.

Cost (assumptions). Suppose 5,000 tenants: 4,950 small ones pay Rs 2,000 a month each, and 50 large ones pay Rs 2 lakh. Monthly revenue is 4,950 x 2,000 = Rs 99 lakh, plus 50 x 2 lakh = Rs 100 lakh.

• A dedicated small database costs Rs 8,000 a month. For all 5,000: 5,000 x 8,000 = Rs 4 crore (one crore is 10 million). That is twice the total monthly revenue of Rs 1.99 crore (4 / 1.99 = 2.0). It does not work for small tenants.
• Shared tables for all: say 20 servers at Rs 60,000, which is Rs 12 lakh a month.
• A dedicated database only for the 50 large tenants: 50 x 8,000 = Rs 4 lakh a month. That is 4 percent of their Rs 100 lakh of revenue. It works if a database at that price is big enough for them.

Noisy neighbour (assumption). One tenant sends 40 percent of the queries, and its heavy report slows all others. A per-tenant query limit, or a move to its own database, fixes it. A schema per tenant does not fix it, because all schemas share one database server. The Azure guide says separate databases with a shared application layer can contain this problem (see Sources).

Restore one tenant (calculation). A backup of the shared database is 500 GB. At 200 MB per second, restoring takes 500,000 / 200 = 2,500 s, which is 41.7 minutes. Then you must extract one tenant's rows and merge them back, which takes hours. With a database per tenant of 5 MB, the restore takes seconds.

What breaks with each choice

Shared tables.

• A missing filter shows one tenant's data to another. Fallback: row-level security, a tested data layer and isolation tests.
• A noisy tenant slows others. Fallback: tenant limits and a path to move big tenants out.
• One big table is hard to change. Fallback: online migration, and partitions by tenant_id.

Schema per tenant.

• Catalog size and migration time grow with tenant count: 300,000 tables, 42 minutes per change. A noisy tenant still slows the others. Fallback: cap tenants per database, and run changes in parallel.
• Mixed versions across schemas. Fallback: a version table, and code that supports both.

Database per tenant.

• Cost per tenant and operations work grow with count. Fallback: use it only for tenants that pay for it, and automate creation.
• A fleet of databases drifts. Fallback: one template and one pipeline.

What it costs

• Shared tables: lowest bill (Rs 12 lakh a month). A safe data layer and row-level security take about 2 to 3 engineer-weeks (Assumption).
• Schema per tenant: a middle bill, and 3 to 4 engineer-weeks for a migration tool (Assumption).
• Database per tenant for 50 large tenants: Rs 4 lakh a month and 4 to 6 engineer-weeks of automation (Assumption).
• Changing your mind: moving a tenant to its own database means copying its rows and switching the mapping. It takes days per tenant if you built the mapping on day one, and months if you did not (Assumption).

How each level answers

SDE-1: Shared tables put all tenants in one table with a tenant ID. A database per tenant keeps each apart.

SDE-2: For many small customers I use shared tables with a tenant ID on every table and index. I add row-level security so a forgotten filter cannot leak data.

Senior: With 400 queries and a 0.1 percent miss each, a leak has a 33 percent chance. So the filter must be enforced in one place. A schema for each of 5,000 tenants means 300,000 tables and 42 minutes for each migration. A database for every tenant costs Rs 4 crore a month against Rs 1.99 crore of revenue. For the 50 large tenants it costs Rs 4 lakh. So I use shared tables for most tenants and a database for the large ones.

Staff: This is a pricing and trust decision. I ask sales which customers require isolation. I make it a paid tier. The first version has a mapping table from tenant to deployment. Then a move is a data copy and a switch, not a rewrite. Platform owns the tooling, and each team owns tenant filters in its code. I track isolation tests, noisy-neighbour incidents and cost per tenant. I move a tenant out for any of three reasons. Its revenue passes about Rs 1 lakh a month, a contract asks, or it uses over 10 percent of database load. These thresholds are assumptions. I revisit this each year.

Say it in the interview

I would start with shared tables and a tenant ID on every table. Row-level security means a forgotten filter cannot leak data. Most tenants are small, and a database for each would cost twice our revenue. I would keep a table that maps each tenant to a deployment, so big or regulated tenants can move to their own database. The risk is a noisy or leaking tenant, so I would limit queries for each tenant and test isolation in the pipeline.

Common mistakes

• Trusting every developer to add the filter. At 400 queries, even a small miss rate makes a leak likely.
• A schema per tenant for thousands of tenants. Catalogs and migrations grow with the count.
• A database for every tenant. Small tenants cost more than they pay.
• No tenant mapping from day one. Moving a big customer later becomes a rewrite.

Sources

• Microsoft Azure Architecture Center, "Tenancy models for a multitenant solution" (tenancy is a commercial as well as technical decision; vertically partitioned deployments mix shared and single-tenant; horizontally partitioned deployments with separate databases help with noisy neighbours; map tenants to deployments): https://learn.microsoft.com/en-us/azure/architecture/guide/multitenant/considerations/tenancy-models (opens in a new tab) (checked 26 Oct 2026).
• PostgreSQL documentation, "Row Security Policies" (default deny when enabled; superusers and BYPASSRLS roles bypass; table owners bypass unless forced): https://www.postgresql.org/docs/current/ddl-rowsecurity.html (opens in a new tab) (checked 26 Oct 2026).
• Tenant counts, table counts, prices, miss rates, migration time per schema, backup size, restore speed and effort are assumptions made for teaching. The leak, migration, cost and restore results are calculations.