Why every customer gets their own database

The usual way to build multi-tenant software is to add a tenant_id column to every table and filter on it. It is cheap, it scales, and it works right up until one query forgets the filter.

That query is the problem. Not because developers are careless, but because the failure is silent and total: a report that forgets its scope does not error, it returns somebody else's payroll.

What we do instead

Each workspace is a separate PostgreSQL database. A request arrives on yourcompany.example, we identify the workspace from the subdomain, and we point the connection at that database for the rest of the request. The application code has no idea tenancy exists — there is no scope to forget, because the other organisations' rows are not in the database being queried.

What it costs us

Plenty, honestly.

• Migrations run everywhere. A schema change has to be applied to every workspace, one at a time, and a failure part way through leaves workspaces on different versions.
• Background jobs need to know where they are. A queue worker has no request and no host name, so every job carries the workspace it was pushed for and the worker switches connection as it picks each one up. Get that wrong and a job writes one customer's payroll into another's database — the exact failure we were trying to design out.
• Connection management matters. A worker moving between customers would otherwise accumulate one connection per workspace, and PostgreSQL will not drop a database while any session holds it.
• Reporting across customers is harder. Our own revenue figures come from a separate registry database, not from a GROUP BY tenant_id.

What it buys you

• A query that forgets its scope reaches nothing, because there is nothing to reach.
• Your backup is a backup of your workspace. Restoring it does not involve extracting your rows from a shared table.
• Leaving is a pg_dump. You get a standard PostgreSQL custom-format file that any Postgres can restore — not an export format only we can read.
• Your workspace can be suspended, archived or restored without touching anybody else's.

The honest trade-off

Shared-schema tenancy is the right answer for a product with a hundred thousand small accounts. It is the wrong answer for a product that holds payroll and bank details for a few hundred organisations, where the cost of one leak is not a bug report but a business.

We would rather carry the operational weight than hope nobody ever writes the query.