Multi-Tenant SaaS Architecture on PostgreSQL: Silo, Bridge or Pool?

The first big architecture decision in any B2B SaaS is how to keep one customer's data away from another's. Get it right and it disappears into the background. Get it wrong and every new feature carries the risk of showing Company A's records to Company B.

We've built multi-tenant platforms where forms, contacts, supervisors and leads all belong to an organisation, so this guide comes from practice as much as theory. It uses the vocabulary from AWS's guidance on multi-tenant PostgreSQL: silo, bridge and pool.

Three ways to separate tenants

There's no universally right answer, but there is a sensible default. For a new product selling to many businesses, the pool model gives you the simplest operations and the lowest cost per tenant, as long as isolation is enforced by the database and not left to every developer's memory.

The pool model with row-level security, step by step

PostgreSQL's row-level security (RLS) lets you attach a policy to a table so that every query is filtered automatically, even one a developer forgets to filter. AWS's prescriptive guidance recommends comparing a tenant column with a runtime setting that your application sets, rather than creating a database user per tenant.

1. Tag and index every tenant-owned table

CREATE TABLE leads (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id  uuid NOT NULL REFERENCES tenants (id),
  email      text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, email)
);

CREATE INDEX leads_tenant_created_idx ON leads (tenant_id, created_at DESC);

Two details matter here. Unique constraints include tenant_id, so two customers can each have a lead with the same email. And indexes lead with tenant_id, because almost every query will filter by it.

2. Turn on row-level security and add a policy

ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
ALTER TABLE leads FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON leads
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

USING filters what can be read, updated or deleted; WITH CHECK stops anyone writing a row into another tenant. FORCE matters too: without it, the table's owner skips the policy.

3. Connect as a role that can't bypass the policy

Superusers and roles with the BYPASSRLS attribute ignore every policy. Run migrations as the owner, but let the application connect as a separate, unprivileged role:

CREATE ROLE app_user LOGIN PASSWORD '…' NOSUPERUSER NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON leads TO app_user;

4. Set the tenant for every request, inside a transaction

BEGIN;
SELECT set_config('app.tenant_id', '6f1c…', true);  -- true = local to this transaction
SELECT * FROM leads ORDER BY created_at DESC LIMIT 50;
COMMIT;

The third argument makes the setting local to the transaction, just like SET LOCAL. That's essential behind a connection pooler such as PgBouncer in transaction mode: a plain session-level SET would stay on the connection and could be picked up by the next request, from a different tenant. In ORMs like Prisma, Sequelize or TypeORM, open a transaction per request, set the tenant first, then run your queries inside it.

Row-level security turns "every developer must remember the WHERE clause" into "the database won't let anyone forget it". That's the whole point.

What the pool model costs you

• Noisy neighbours. One heavy tenant can slow everyone down. Mitigate with per-tenant rate limits, read replicas for reports and query timeouts.
• Per-tenant backup and restore. Restoring one customer's data to yesterday is harder when it's mixed with everyone else's. Plan for logical exports per tenant.
• Data residency requests. If a customer needs their data in a specific region, they need their own silo, at least for that region.
• Offboarding. Deleting a tenant means deleting across every table, so keep that logic in one tested place.

The hybrid most mature products end up with

Many SaaS products settle on a tiered model: small and medium customers share a pool, while the largest or most regulated customers get a silo with the same schema. Because the schema is identical, the application code doesn't change; only the connection it uses for that tenant does. Designing for this from the start (a tenants table that records where each tenant lives) costs almost nothing and keeps the door open.

Mistakes we've seen (and made)

1. Background jobs without tenant context. Queues and scheduled jobs don't come from a web request, so they need to set the tenant explicitly too.
2. Caches keyed without the tenant. A cache key like "dashboard-stats" will happily serve one company's numbers to another. Include the tenant in every key.
3. Shared file paths. Uploaded files need tenant-scoped paths and access checks, just like rows.
4. Analytics with a superuser. Reporting jobs that run as a privileged role skip RLS entirely. Give them their own role and policies.
5. Unique constraints without tenant_id. They leak information ("this email is taken") and block legitimate data in other tenants.

Which model should you choose?

Frequently asked questions

Planning a SaaS product and want a second opinion on its data model? We design and build multi-tenant SaaS platforms. Get in touch and we'll walk through it with you.