Multi-Tenant CMS Architecture: Choosing a Tenancy Model

If you run more than a handful of websites on one platform, someone has had to answer a deceptively simple question: where does one customer's data end and the next customer's begin? That answer is the platform's tenancy model. It decides how safe the data is, how expensive the platform is to operate, how backups work and what happens when one busy site slows down the rest.

This guide compares the three common models for a multi-tenant CMS, with the trade-offs that matter to the people who run one, and to the agencies choosing one.

What a tenant is in a CMS

A tenant is one isolated customer space. In a CMS that usually means one website: its pages, posts, media, forms, submissions, users, settings and domains. Tenants share the same code and, to some degree, the same infrastructure. The question is how much of the database they share.

The three tenancy models

Shared database, shared schema

Every tenant's rows live in the same tables. Each tenant-owned table has a tenant_id column, and every query filters on it. This is the model most SaaS products start with and many never leave.

It is the cheapest to run. There is one set of tables to migrate, one connection pool and one backup. Adding a tenant is an INSERT, not a provisioning job. The risk is equally clear: isolation depends on every query including the right filter.

Schema per tenant

In PostgreSQL, each tenant gets its own schema: the same set of tables, repeated inside a namespace such as tenant_42. The application switches namespace with search_path per request.

Isolation is stronger, because a query without a tenant filter can only see the current schema. The cost moves into operations. A migration now runs once per tenant, so a deploy with a schema change scales with the number of tenants. Thousands of schemas also means thousands of copies of every table and index in the system catalog, which slows tools such as pg_dump. And search_path is session state, which needs care behind a transaction-pooling connection pooler: set it per transaction with SET LOCAL, or schema-qualify every query.

Database per tenant

Each tenant gets a separate database, sometimes on a separate server. This is the strongest isolation short of separate deployments, and it makes per-tenant decisions easy: one large customer can move to a dedicated server, or be restored, without touching anyone else.

It is also the most expensive. Every database needs its own connections, migrations, monitoring and backups. Reporting across tenants, such as "how many pages were published platform-wide this week", becomes a fan-out job across databases.

How the models compare

Isolation and blast radius

The real question is not "can tenants see each other's data?" All three models can be made safe. It is "how many mistakes does it take to leak data?" In a shared schema, one missing WHERE tenant_id = ? can be enough unless there are further layers. In a schema-per-tenant set-up, the mistake has to be in the connection setup. In a database-per-tenant set-up, it has to be in the connection routing.

That is why a shared-schema design should never rely on developers remembering a filter. It needs several independent layers, covered below.

Operational cost

Most CMS data models have dozens of small tables. With a shared schema you migrate them once. With a schema or database per tenant you migrate them N times, and a failure halfway through leaves tenants on different versions of the schema. You need tooling to track, retry and roll back per tenant. For a platform expecting many small tenants, this cost often outweighs the isolation benefit.

Backups and restoring a single tenant

Full-database backups are simple in every model. Restoring one tenant is where they differ. With a database per tenant, you restore that database. With a schema per tenant, pg_dump --schema and pg_restore handle one schema. With a shared schema, you restore the backup to a scratch database, extract that tenant's rows in foreign-key order, and copy them back. It is entirely doable, but it should be a scripted, rehearsed procedure rather than something you work out during an incident.

Noisy neighbors

On shared infrastructure, one tenant's traffic spike or slow query affects others. This is true of all three models when they share a server. Only database per tenant makes it easy to move a heavy tenant elsewhere. Whatever the model, the everyday mitigations are the same:

• Put tenant_id first in composite indexes, so each tenant's queries touch only their own slice of an index.
• Set a statement_timeout, so one runaway query cannot hold resources indefinitely.
• Rate-limit expensive endpoints per tenant, not only per IP address.
• Keep queues fair, so one tenant importing ten thousand images does not delay everyone else's form notifications.
• Cache per tenant, with the tenant in every cache key.

Making a shared schema safe

If you choose a shared schema, build isolation in layers so that no single forgotten line can leak data:

1. A scoped data layer. Every tenant-owned model applies the tenant filter automatically, and fills tenant_id on create. Better still, it refuses to run a tenant-owned query when no tenant is set, instead of silently returning everything.
2. Composite foreign keys. Reference other tenant-owned rows by (tenant_id, id), not just id. With a unique constraint on (tenant_id, id) in the parent table, PostgreSQL itself rejects a post that points at another tenant's image, even if the insert bypasses the application.
3. Tenant-scoped uniqueness. Slugs, form shortcodes and domain-specific identifiers are unique per tenant: UNIQUE (tenant_id, slug), never UNIQUE (slug).
4. Everything outside the database. Cache keys, file storage paths, queued jobs and scheduled tasks all need the tenant too. A background job that sends form notifications must restore the right tenant before it reads mail settings.
5. Isolation tests. For every tenant-owned model, a test proves that tenant A cannot list, read, update or delete tenant B's records through the API or the data layer.
6. Row-level security, optionally. PostgreSQL policies keyed on a per-transaction setting such as current_setting('app.tenant_id') add a database-enforced filter. Remember FORCE ROW LEVEL SECURITY, because table owners bypass policies otherwise, and plan how console commands, queue workers and migrations will set the tenant.

Which model should you choose?

• Many small tenants with the same data model, such as marketing sites, blogs and brochure sites: a shared schema with layered isolation. It keeps operations simple and makes per-tenant cost close to zero.
• Fewer, larger tenants with strict contractual separation: schema per tenant is a reasonable middle ground, provided you invest in migration tooling.
• A small number of very large or regulated tenants: database per tenant, or a hybrid where most tenants share and a few get dedicated databases.

Hybrids are common and sensible. Starting with a shared schema does not stop you moving one large tenant out later, as long as tenant_id is everywhere and your export path is scripted.

How OdysseyCMS approaches it

OdysseyCMS runs marketing websites, so tenants are numerous and share one data model. We use a shared PostgreSQL schema with a tenant_id on every tenant-owned table, an automatic tenant scope that refuses to run without a tenant, composite foreign keys between tenant-owned tables, tenant-scoped cache keys and storage paths, and an isolation test suite. Each site's content, media and settings stay separate, and the database has daily backups.

For agencies, the result is simple: one login can manage many sites, and each client sees only their own. If you are comparing platforms, ask each vendor which model they use and how they prove isolation. A good answer will be specific. Or book a demo and ask us the same questions; you can also compare plans first.