Multi-tenant SaaS architecture in practice

A multi-tenant SaaS is the kind of system where a single codebase and a single infrastructure serve many independent customers. The load problems from the previous post gain an extra layer here: it is not enough for the system to be fast and secure, it also has to guarantee that no tenant can see or slow down another. Below we collect the decisions we made in last year’s SaaS projects.

Three isolation models

There are three basic models for separating tenant data, and each has its place.

Shared schema with a tenant_id column. Every table carries a tenant identifier and every query filters on it. This is the cheapest to operate, migrations are simple, and a backup covers a single database. The risk: one forgotten WHERE tenant_id = ... and you have a data leak.

Schema per tenant. Within one PostgreSQL database, each tenant gets its own schema. Isolation is stronger, and with search_path set the queries do not change. In exchange, migrations have to run per tenant, and above a few hundred schemas catalogue operations slow down noticeably.

Database per tenant. Full isolation, per-tenant backup and restore, optionally on separate servers. This is the most expensive option, and it is only justified when contractual or regulatory reasons demand it.

Most of our projects pick the shared-schema model and handle the risk of a forgotten filter not with discipline, but with a database-level mechanism.

Row-level security as a safety net

PostgreSQL row-level security (RLS) lets the database, rather than the application, enforce the filtering. The pattern we use:

• ENABLE ROW LEVEL SECURITY on every tenant-scoped table, plus a policy that filters on the value of current_setting('app.tenant_id'),
• the application sets the tenant at the start of the request, inside the transaction, with SET LOCAL app.tenant_id = ...,
• the application’s database user is not the owner of the table, so the policy applies to it as well.

With this in place, a missing WHERE clause produces an empty result set rather than a data leak, which is far more noticeable and far less painful. The price is a slight increase in query-plan complexity, and the fact that under PgBouncer in transaction mode SET LOCAL is the only safe form.

Per-tenant configuration in the database

A lesson we paid for more than once: tenant-specific settings (limits, enabled features, integration keys, branding) do not belong in environment variables or configuration files, they belong in the database, in a versioned tenant_settings-style table. There are several reasons:

• onboarding a new tenant does not require a deployment,
• changes to settings can be logged and rolled back,
• the admin interface sees exactly the same data as the application.

Secret values (API keys, passwords) are stored encrypted, with the key read from the application’s environment. Settings are cached in Redis with a short TTL, because they are needed on every request.

Migrations and the noisy neighbour

The big advantage of the shared-schema model is that a migration runs once. At the same time, modifying a large table affects every tenant at once, so we stick to a few rules: only additive changes during production hours, dropping a column in two steps (code first, then schema), and CREATE INDEX CONCURRENTLY for every index.

Against the “noisy neighbour” effect (one tenant’s load slowing down the others) we defend on several levels:

• per-tenant rate limits in Redis, not just a global one,
• heavy operations (exports, bulk imports, reports) go to a background queue with a per-tenant concurrency limit,
• statement_timeout is set on queries so that one bad query cannot hold a connection for minutes,
• the tenant ID appears as a label on every metric, so a problematic tenant is immediately visible.

Billing hooks and backups

Billing in a SaaS is not an afterthought, it is an architectural question. We build two things in from day one:

Usage events. Every billable operation (API call, message sent, record stored) writes an event into an append-only table, with tenant ID and timestamp. At the end of the billing period the summary is derived from this, and in case of a dispute it is what you can look things up in.

State transition hooks. The tenant lifecycle (trial, active, payment overdue, suspended, deleted) is an explicit state machine. Every transition runs the hooks that belong to it: notification, feature restriction, starting the data-retention timer. That way “what happens if they stop paying” has a documented answer.

On the backup side, the drawback of the shared-schema model is that restoring a single tenant is not trivial. We bridge this by producing, alongside the daily full backup, a per-tenant logical export (pg_dump with filtered data, or an application-level export), and by exercising the restore procedure regularly rather than only in theory.

In summary: multi-tenant architecture is not one decision but a dozen smaller, interrelated ones. A shared schema with RLS, tenant configuration stored in the database and per-tenant limits gives a good balance between operational simplicity and safety in most cases. If a tenant demands full isolation for contractual reasons, we solve that with a separate database, but we treat it as an exception, not the rule.