Which is more secure?

Both are safe when built correctly and both fail when built carelessly. Separate databases fail closed, which is a genuine advantage, but a shared schema with row level security has the same property because a missing predicate returns nothing rather than everything.

What is the real cost of database per tenant?

Migrations. Every schema change becomes a distributed job that will partially fail, which means it must be idempotent, resumable and tracked. At a hundred tenants that is a script. At a thousand it is a system somebody owns.

Can you start shared and move later?

Yes, and it is the usual path. Moving one tenant out to its own database is a data copy and a routing change. Going the other way, consolidating many databases into one schema, is considerably harder because of identifier collisions.

Solution

Database per tenant against a shared schema, decided properly

This decision gets made on a feeling about isolation and then lived with for a decade. The variable that actually decides it is what happens on the day you have to change the schema.

The short answer

Shared schema with row level security scales to many small tenants and makes migrations trivial. Database per tenant gives hard isolation and per tenant operations, and makes every schema change a distributed job. Choose by tenant count and by whether any customer will contractually require physical separation.

The question that actually decides it

Not isolation. Both models can be safe.

The question is: what does it cost you to change the schema?

In a shared schema, a migration is one migration. In database per tenant, a migration is a distributed job across every tenant, which will fail partway through at some point, leaving your estate in a mixed state that some part of the application has to tolerate.

Everything else follows from that.

What each model actually is

Shared schema. One database, one set of tables, a tenant_id column on every tenant owned table, and row level security enforcing the predicate below the application.

Database per tenant. One database per customer, identical schema in each, and a routing layer that resolves a request to a connection. There is usually also a shared control database holding the tenant directory.

There is a middle option, schema per tenant inside one database, which sounds like a compromise and in practice inherits the migration cost of separate databases while giving up much of the isolation benefit. It is rarely the right answer.

Choose shared schema when

You will have many tenants. Hundreds or thousands. Connection pools do not multiply well, and a thousand databases means a thousand sets of connections, backups and migration runs.

Tenants are small and similar. If the median tenant is a handful of users and a few thousand rows, a dedicated database is mostly overhead.

You deploy often. A migration you can run in one transaction, once, is a migration you will run without fear. That directly changes how willing you are to improve the schema, which compounds over years.

You need cross tenant queries routinely. Platform analytics, billing, usage reporting. In a shared schema that is a query. Across separate databases it is a data pipeline.

The cost you accept: isolation depends on getting row level security right, and one very large tenant can affect the performance of everyone else through shared table size and shared cache.

Choose database per tenant when

A customer contractually requires it. Enterprise, healthcare, government and finance procurement often demands physical separation, and no amount of explaining row level security will substitute. If you intend to sell into those markets, this can be the whole argument.

Tenants are few and large. Twenty customers with millions of rows each. The per tenant overhead is negligible relative to their size, and the benefits are real.

Per tenant operations matter. Restore one customer to a point in time without touching anyone else. Move a noisy customer to dedicated hardware. Delete a customer completely, provably, when they leave. All of those are simple here and awkward on a shared schema.

Blast radius must be bounded. A migration that corrupts data affects one tenant, not all of them. That is a genuine operational comfort when you are the only engineer.

The cost you accept: every schema change is a distributed job, and it needs the machinery below.

What database per tenant actually costs, in detail

This is the part that is usually underestimated, so it is worth being concrete.

Migrations become a system. The runner must be idempotent, resumable, and it must track per tenant state. It will fail partway. Requirements:

tenant_migrations
  tenant_id, migration_version, status, started_at, completed_at, error

run in waves     internal tenant first, then a canary, then the rest
resumable        re-running skips completed tenants
tolerant         the application must work while tenants are on mixed versions

That last line is the one people miss. Between the first and last tenant, your application is running against two schema versions simultaneously. Every migration therefore has to follow expand, migrate, contract anyway, which means you have the cost of that plus the cost of distribution.

Connections multiply. A pool per tenant does not scale. You need either a shared pool with the tenant set per checkout, or a proxy such as PgBouncer, and either way there is a real limit on how many tenants one application instance can serve.

Everything operational multiplies. Backups, restore testing, monitoring, extensions, version upgrades. Restore testing in particular: if you cannot restore a random tenant on demand, you do not have backups, and testing that across a thousand databases is a scheduled job somebody has to own.

The migration path between them

Almost everybody starts shared and extracts specific tenants later, and that direction is straightforward. Copy the tenant’s rows into a fresh database, switch the routing entry, verify, delete the originals after a holding period. It can be done live per tenant with a short read only window.

The other direction, consolidating many databases into one schema, is considerably harder because primary keys collide and every foreign key has to be remapped consistently. Plan the direction you might actually need.

How I would decide it today

Start with a shared schema and row level security unless a signed customer requires otherwise. It is faster to build, dramatically cheaper to operate, and it keeps schema change cheap during the period when you most need to change the schema.

Build the tenant resolution layer as an abstraction from day one, so that where the data lives is an implementation detail rather than an assumption spread through the codebase. Then extracting a tenant later is a routing change instead of a rewrite.

That abstraction is the actual insurance policy, and it costs about a day.

Questions people actually ask

Which is more secure?: Both are safe when built correctly and both fail when built carelessly. Separate databases fail closed, which is a genuine advantage, but a shared schema with row level security has the same property because a missing predicate returns nothing rather than everything.
What is the real cost of database per tenant?: Migrations. Every schema change becomes a distributed job that will partially fail, which means it must be idempotent, resumable and tracked. At a hundred tenants that is a script. At a thousand it is a system somebody owns.
Can you start shared and move later?: Yes, and it is the usual path. Moving one tenant out to its own database is a data copy and a routing change. Going the other way, consolidating many databases into one schema, is considerably harder because of identifier collisions.