Multi-tenant SaaS architecture: shared tables or a database per tenant?

Multi-tenancy means one running application serves many customers, called tenants, while keeping each tenant’s data separate. It is how almost all B2B software is built, because you maintain one product instead of one copy per client. The hard part is deciding how “separate” the data should be, because that choice shapes your costs, your migrations, your backups and your security story for years.

I have made this decision on real products, including Appex Market, where every vendor gets their own bot and storefront, and Appointox, where each business has its own branded booking site. Here is the framework I use.

The four common tenancy patterns

1. Shared tables with a tenant ID

Every table carries a tenant ID, and every query filters by it. Migrations run once, connection pools stay simple, and you can scale to a very large number of tenants before you need to think about sharding. The risk is human: one missing filter leaks data across customers. That is why this pattern needs guardrails, covered below.

2. Schema per tenant

Each tenant gets a schema inside one database and the application switches the search path per request. It sounds like a tidy middle ground. In practice you multiply migrations by the number of tenants and put pressure on connection pooling, so schema drift becomes a real operational burden. Several engineering teams now advise against it for new products.

3. Database per tenant

Each customer has a dedicated database. You get independent backups and restores, no noisy neighbours at the storage layer and the strongest compliance story. You also take on fleet management: provisioning, credentials, connection pools and migration tracking for every database. It only works well when creating a tenant database is fully automated.

4. Hybrid tiers

Customer sizes follow a power law, so many products keep small customers on shared tables and move large or regulated customers to dedicated databases. It is the most complex option, but it matches cost to value.

How I choose for a real project

1. Who are the tenants? Thousands of small businesses point to shared tables. A handful of enterprises with security questionnaires point to dedicated databases.
2. What must a tenant be able to do alone? If a customer needs to restore only their own data, export it or have it deleted on request, per-tenant databases make that trivial. With shared tables you need selective-restore tooling.
3. How often does the schema change? If every tenant must run the same release, shared tables win. If tenants need different schema versions, you need separate databases.
4. What does your team operate? A single developer can run shared tables safely. A fleet of databases needs automation from day one.

Making shared tables safe

If you choose shared tables, treat isolation as a system-wide rule, not a database detail. The tenant must be resolved at the API boundary, then carried through the database, cache, file storage, background jobs and logs.

• Resolve the tenant from the authenticated session or a verified token, never from a value the client can freely change.
• Inject the tenant filter in one place, such as middleware, a repository layer or ORM scope, instead of repeating it by hand in every query.
• Include the tenant ID in unique constraints, so an invoice number can repeat across customers but never inside one.
• Namespace cache keys, file paths and queue messages by tenant.
• Write tests that try to read tenant B’s data while signed in as tenant A, and keep them in CI.

Row-level security as a second lock

PostgreSQL row-level security (RLS) lets the database itself refuse rows that belong to another tenant, even if a query forgets the filter. A safe setup uses a non-superuser application role, enables and forces RLS on tenant tables, and sets the tenant context inside each transaction so pooled connections never carry one tenant’s identity into the next request.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant')::bigint);

-- per request, inside one transaction
BEGIN;
SET LOCAL app.current_tenant = '42';
SELECT * FROM orders;
COMMIT;

What about MongoDB?

The same ideas apply. A shared collection with a tenant ID on every document is simple, and per-tenant databases are natural in MongoDB, which makes strong isolation easy for a smaller number of larger tenants. Pick based on how many tenants you expect and how independent they must be, not on the database brand.

Mistakes I see most often

• Starting with schema-per-tenant because it feels safer, then drowning in migrations.
• Trusting a tenant ID sent from the browser.
• Forgetting background jobs and reports, which run outside a request and so outside the usual filter.
• Having no way to see or support a single tenant, because the admin tooling was an afterthought.
• Postponing the decision. Moving tenants between models later means data migrations on live customers.

My default recommendation

Start with shared tables and strict, centralised tenant scoping. Add RLS where your database supports it. Design your code so a large customer can later be moved to a dedicated database. That keeps early costs low without closing the door on enterprise requirements.

If you are planning a SaaS and want a second opinion on the data model before you build, see how I approach SaaS development or get in touch.