Short answer: for most early and mid-stage B2B products, a shared database with PostgreSQL row-level security is the simpler and cheaper place to start, and schema per tenant (or database per tenant) is worth its extra cost when customers demand hard separation, per-customer backups or very different data volumes. Neither choice is wrong in itself. What hurts is picking one without knowing the trade-offs, then discovering them with live customers.
This article compares the two approaches in plain terms so you can make the call before the first migration is written. If you want help applying it to your product, see our page on multi-tenant architecture.
What multi-tenant actually means
A tenant is one customer account, usually a company, with its own users and its own data. Multi-tenant means many tenants run on one deployment of your application. The central rule is simple to state and easy to break: a user from tenant A must never read or change data that belongs to tenant B. Every design decision below is a different way of enforcing that rule.
The common models are:
- Shared tables with a tenant column. Every table has a tenant_id and every query filters on it.
- Shared tables with row-level security. The same layout, but the database itself refuses to return rows from another tenant.
- Schema per tenant. Each tenant gets its own set of tables inside one database.
- Database per tenant. Each tenant gets a separate database, sometimes on separate servers.
A tenant column alone relies on every developer remembering the filter in every query forever. That is the setup behind most cross-tenant leaks, so we treat it as the minimum, not the design.
Row-level security: how it works and where it fits
With PostgreSQL row-level security, you attach a policy to each table that says which rows a session may see, usually by comparing the row's tenant_id to a value set for the current request. If application code forgets a filter, the database still returns nothing from other tenants. That moves the safety check from many places in the code to one place in the database.
The benefits are practical. There is one schema to migrate, one set of indexes to tune, one backup to run and one connection pool to size. Onboarding a new tenant is an insert, not an infrastructure task. Reporting across all tenants for your own internal use is a normal query. For a product that may have hundreds or thousands of small and mid-sized customers, this is usually the lowest-cost model to operate.
The costs are also real:
- Policies must be written, tested and kept in step with every new table. A table added without a policy is open.
- The application must set the tenant context reliably on every connection. With connection pooling this needs care, because a context left over from a previous request is a leak.
- Database roles matter. Policies do not apply to table owners or superusers by default, so the app must connect with a restricted role.
- One very large tenant can slow everyone else, since they share tables and resources.
- Restoring a single tenant to a point in time is awkward, because their rows sit among everyone else's.
Schema per tenant: what you gain and what you pay
In the schema-per-tenant model each customer has its own tables, and the application points each request at the right schema. Isolation is easier to explain to a security reviewer, because the data is physically separated by name and permissions. Per-tenant export, deletion and restore become much simpler. A customer that asks for all of its data, or for its data to be removed, is a contained operation.
The price shows up in operations. Every schema change must be applied to every tenant schema, and a failure halfway leaves tenants on different versions. With a few dozen tenants this is manageable. With thousands, migrations become long-running jobs that need tracking, retries and a clear rollback plan. Catalog size grows, tooling gets slower and connection pooling becomes harder. Cross-tenant reporting needs extra work, since there is no single table to query.
Database per tenant goes one step further. It gives the strongest separation and the easiest per-customer scaling, and it is the most expensive and the most work to run. It tends to appear in products with a small number of large customers who pay accordingly or who have contractual or regulatory reasons for it.
How to choose
Ask these questions in this order:
- How many tenants do you expect, and how different are they in size? Many small tenants point to shared tables. A handful of very large ones point to separate schemas or databases.
- Do customers require contractual isolation, data residency or per-tenant backups? If a signed agreement demands it, that decides the question. Do not promise it unless the architecture delivers it.
- How often will the schema change? Fast-moving early products are easier to evolve with one schema.
- Who runs operations? A small team is better served by fewer moving parts.
- What do you need to report across tenants? Product analytics and billing are easier on shared tables.
A common and sensible path is to start with shared tables and row-level security, keep every query tenant-aware from day one, and keep the option to move a large customer to its own schema or database later. That option is cheap to preserve if the tenant context is handled in one place in the code, and expensive to recover if it is scattered.
Mistakes we would plan for in either model
- Background jobs and exports. Workers often run outside the request path and skip the tenant context. Test them as carefully as the web app.
- Caches and search indexes. A cache key without a tenant prefix can serve one customer another customer's data.
- File storage. Uploaded files need tenant-scoped paths and access checks, not only the database rows that point to them.
- Admin and support tools. Internal staff access should be explicit, logged and limited, not an unfiltered back door.
- Tests. Write automated tests that sign in as tenant A and try to reach tenant B's records through every route. These tests catch regressions that code review misses.
What this does not guarantee
No isolation model makes a product secure by itself. Row-level security does not protect against a stolen password, a flawed permission check inside a tenant, or a misconfigured server. Schema separation does not stop an application bug that connects to the wrong schema. Treat isolation as one layer, back it with automated tests and access logging, and have it reviewed independently if your customers are in regulated industries. If you want a second opinion on an existing design, we are glad to read it with you.
If you would like to talk through your tenant model, chat on WhatsApp at +1 (929) 450-3261 or message us on Telegram and tell us how many customers you expect and what they will ask about data separation.