Multi-tenancy: shared tables, schema per tenant or database per tenant

The interviewer says: "You build accounting software that many companies use. Each company must see only its own data. Some companies are huge, and most pay very little. How do you store the data?"

A weak answer says "one database for each customer" or "add a customer column", and stops. A strong answer compares the layouts on leaks, cost, schema changes, restore and delete. It also says how one big customer is kept from slowing the others, and how that customer can move to its own place when it grows.

At SDE-2 level you should know the layouts and how a leak happens. At SDE-3 level you should also handle the noisy neighbour, routing and moving a tenant.

Think of a building. In one layout, everyone's boxes sit on the same shelves in one big hall, and every box has a name label. In another, each family has its own locker room in the same hall. In the third, each family has its own house.

A tenant is one customer organisation that has many users, for example one company that uses your software. There are three layouts for tenant data.

• Shared tables. All tenants use the same tables. Every row has a tenant_id column.
• Schema per tenant. A schema is a named set of tables inside one database. Each tenant gets its own schema.
• Database per tenant. Each tenant gets its own database.

Three small pictures. With shared tables, the rows of tenants A, B and C are mixed in one table, each row with a tenant id. With a schema for each tenant, one database holds three separate sets of tables, one for each tenant. With a database for each tenant, A, B and C each have their own database. Tenant A is shown in blue in each picture.

Compare them on seven questions. A restore brings data back from a backup.

The mix is common. Small tenants share tables. Each large tenant, or each tenant with strict rules, gets its own database. A table that maps each tenant to its data keeps this simple.

Restore needs care. In Postgres, restoring to a point in time restores the whole database server, not one part of it (see Sources). So you restore a copy on the side and pull out one tenant. That is hard with shared tables, because the tenant's rows sit in many tables. With a schema or a database for each tenant, you copy one unit. A separate backup of each database makes this faster still. Deleting a tenant needs care too: a contract or a law may say the data must be gone. Old shared backups still hold it until they expire.

Keeping tenants apart in shared tables

Three rules, each one stops a different leak.

1. Tenant ID first in every index that serves tenant queries. An index on (tenant_id, created_at) finds one tenant's rows for a month at once (lesson 36). An index on created_at alone reads every tenant's rows for that month and drops the others after. Put tenant_id first in primary keys and unique rules too, so two tenants can both have invoice number 1.
2. Tenant ID in every cache key. A cache key is the name under which a cache stores a value. The key dashboard:2026-03 gives one tenant's dashboard to the next tenant who asks. Use t42:dashboard:2026-03.
3. Row-level security. The database adds the tenant filter to every query, so one forgotten filter cannot leak. In Postgres, once you switch it on for a table with no policy, nobody sees any row.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- the owner obeys it too
CREATE POLICY same_tenant ON invoices
  USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint);
-- At the start of each request, inside its transaction:
SELECT set_config('app.tenant_id', '42', true);  -- true: lasts for this transaction only

The policy does not apply to a superuser or to a role with the bypass attribute. It does not apply to the owner of the table unless you force it. So the application must connect as an ordinary role. Two more traps:

• Connection pools. A pool gives the same connection to different requests. A setting that stays on the connection (a plain SET) would give the next request the previous tenant. The setting above lasts for one transaction only. The NULLIF makes a request with no tenant see no rows, not an error.
• Views. A view can run with its owner's rights and skip the policy.

Add a test for every table and view: read as tenant A and expect zero rows of tenant B.

The noisy neighbour

A noisy neighbour is a tenant that uses so much of a shared resource that others slow down. A schema for each tenant does not fix it, because all schemas share one database server. A per-tenant limit does: use a token bucket (lesson 84) with the tenant ID as the key, and set its size by the tenant's plan. A request limit does not stop one very heavy query, so also set a time limit for each query. A tenant with strong needs can get its own database server, which separates the load.

Finding the tenant's data

Each request must reach the right data. The tenant name comes from the sub-domain (acme.example.com) or from the login token. A lookup table then maps the tenant to its database. Each app server keeps a copy in memory for a few seconds, so a moved tenant is found quickly.

This is sharding by tenant (lesson 69): the shard key is the tenant ID. A hash spreads the tenants evenly, but tenants differ a lot in size. So a lookup table (a directory) is better here, because you can place one huge tenant alone. To move a large tenant, use the four steps of lesson 69: copy its rows, keep sending new changes, switch its entry in the table (pause that tenant's writes for a moment), delete the old copy.

KhataDesk is accounting software. Assumptions (made up to teach with): 1,500 tenants. 1,470 are small and pay Rs 1,500 a month. 30 are large and pay Rs 100,000 a month. One table of invoice lines has 300,000,000 rows of 500 bytes, spread evenly over 36 months. The biggest tenant, Metro Traders, has 20,000,000 rows.

Step 1: the index (the predict box). Rows a month for all tenants: 300,000,000 / 36 = 8,333,333. For an average tenant: 300,000,000 / 1,500 = 200,000 rows, and 200,000 / 36 = 5,556 a month. Index on created_at alone: 8,333,333 entries read to keep 5,556, which is 1,500 times more (8,333,333 / 5,555.6). With tenant_id first: 5,556.

Step 2: cost. Assumption: the shared cluster costs Rs 300,000 (3 lakh) a month, which is 300,000 / 1,500 = Rs 200 for each tenant. We keep it in the mix (slightly high). A small database costs Rs 6,000 a month. For 1,500 tenants that is Rs 9,000,000, which is 30 times more. The 1,470 small tenants pay 1,470 x 1,500 = Rs 2,205,000, but their databases would cost 1,470 x 6,000 = Rs 8,820,000, four times their income. Assumption: a large database costs Rs 15,000. For 30 large tenants that is Rs 450,000, which is 15% of their Rs 3,000,000. The mix costs 300,000 + 450,000 = Rs 750,000.

Step 3: a schema change. With schemas, 1,500 x 40 tables = 60,000 tables. Assumption: one change takes 2 seconds in one schema. One after another: 1,500 x 2 = 3,000 seconds = 50 minutes. With 10 at a time: 5 minutes. If 1% fail, 15 tenants stay on the old shape, so the code must work with both shapes (lesson 41).

Step 4: restore one tenant. The shared database is 300,000,000 x 500 bytes = 150 GB. At 250 MB per second, copying the backup back on the side takes 150,000 / 250 = 600 seconds = 10 minutes (replaying the log adds time). Then you copy one tenant's rows back, table by table. An average tenant has 200,000 x 500 bytes = 100 MB, which loads in 100 / 250 = 0.4 seconds from the backup of its own database.

Step 5: delete Metro Traders. In shared tables: delete its 20,000,000 rows in this one table in batches of 10,000, which is 2,000 batches. At 0.2 seconds of work and 0.2 seconds of pause: 2,000 x 0.4 = 800 seconds = 13.3 minutes (lesson 41). With its own database: drop it.

Step 6: a noisy neighbour. Assumption: the database handles 3,000 queries per second, and normal load is 1,200. One tenant's script sends 2,500 per second. The total is 3,700, so everyone slows. A bucket of size 300 that refills 150 per second lets through at most 300 + 150 x 60 = 9,300 queries in a minute, not 150,000. The total stays near 1,350.

Step 7: move Metro Traders. Its rows in this table: 20,000,000 x 500 bytes = 10,000 MB. A slow copy at 20 MB per second takes 10,000 / 20 = 500 seconds = 8.3 minutes. Then the changes made during the copy are applied, and its entry in the lookup table is switched.

What companies wrote

• Netflix wrote in October 2026 (post listed 22 Oct 2026) that containers from many teams share its hosts. Here the tenants are teams, not customer companies. A few demanding real-time services run in a mode that gives each task its own virtual machine, with the same API and deployment path. The post says a shared host cannot separate every shared resource, such as memory bandwidth. Link: https://netflixtechblog.com/single-tenant-titus-vm-level-isolation-on-a-multi-tenant-container-platform-6d8b1909d163 (opens in a new tab)
• Notion wrote on 6 Oct 2021 that it splits data by workspace ID. It uses 480 logical shards, which are Postgres schemas, spread over 32 physical databases. A shard is not one customer: it holds the data of many workspaces. Link: https://www.notion.com/blog/sharding-postgres-at-notion (opens in a new tab)

Shared tables solve cost (Rs 200 a tenant, not Rs 6,000) and give one schema change. They cost leak risk, hard restore and delete (13.3 minutes of batches), and a noisy neighbour. A database for each tenant solves isolation and restore. It costs 30 times the bill if used for everyone, and one change many times. I would use shared tables for small tenants and a database for each large or regulated tenant. I would change a tenant's place when it uses a large share of the load, when a contract asks, or when its revenue pays for a database.

"A schema for each tenant fixes the noisy neighbour." All schemas share one server. In step 6, only a limit or a separate server helped.

"Row-level security means no tenant filter can leak." A superuser, a bypass role or the table owner skips it. The application role must not be one of them, and every table needs a test.

"A database for each tenant is the safest, so use it for all." Here it cost four times the income of the small tenants, and a schema change ran 1,500 times.

1. Name the three layouts. Why must tenant_id come first in an index? What is a noisy neighbour?
2. A shared table has 120,000,000 rows from 800 tenants over 24 months. (a) How many entries does a query for one tenant and one month read with an index on created_at alone, and with (tenant_id, created_at)? (b) A shared cluster costs Rs 200,000 a month and a database costs Rs 5,000. What is the cost for each of the 800 tenants in each layout?
3. A team turns on row-level security, and the app connects as the owner of the tables. A test with two tenants still passes. What is wrong?