Choosing a Multi-Tenant Architecture

There are four established ways to isolate tenant data in a Flask + PostgreSQL application. Flask RLS implements one of them. This page compares all four honestly — including the cases where you should not use RLS.

The four architectures

	Shared tables + RLS	Application-layer filtering	Schema-per-tenant	Database-per-tenant
Isolation enforced by	PostgreSQL policies	Your code, every query	Schema namespace	Physical separation
Can a code bug leak data?	No — the database filters	Yes — one missed filter	Only via search_path bugs	No
Migrations	One schema, one migration	One schema, one migration	One migration × N tenants	One migration × N databases
Cost of tenant #1000	One value in a column	One value in a column	1000 schemas, bloated catalog	1000 databases
Cross-tenant queries (admin, analytics)	Easy — BYPASSRLS role	Easy — omit the filter	Hard — union across schemas	Very hard — federate
Connection pooling	Fully shared pool	Fully shared pool	Shared, but search_path juggling	Pool per database
Per-tenant backup/restore	Row-level export	Row-level export	pg_dump --schema	Native
Compliance "physical isolation" requirements	No	No	Partially	Yes
Representative tools	flask-rls, hand-rolled RLS	plain SQLAlchemy filters	Flask-Tenants	custom infrastructure

Application-layer filtering: the default that fails quietly

Most Flask apps start here: every query carries .filter(Model.tenant_id == g.tenant_id). It needs no infrastructure and works — until the one query that forgets. The failure mode is silent, invisible in code review (the bug is the absence of a line), and typically discovered by a customer seeing someone else's data. Raw SQL, ORM relationship traversals, and background jobs multiply the surface.

Row-Level Security exists precisely to close this gap: the predicate lives in the database, where it applies to raw SQL, forgotten filters, and future code alike. If you are already filtering by a tenant_id column, RLS is not a rearchitecture — it is a hardening step over the same data model. See Migrating an existing app.

Schema-per-tenant: strong names, operational drag

Schema-per-tenant (the model implemented by Flask-Tenants, and by django-tenants in the Django world) gives each tenant its own PostgreSQL schema and switches search_path per request. Isolation is structural, and per-tenant restore is genuinely easier.

The costs arrive with scale and with change:

• Migrations multiply. Every Alembic migration runs once per tenant. At 500 tenants, a 30-second migration is a four-hour deploy, and a migration that fails on tenant #317 leaves you half-upgraded.
• The catalog bloats. Thousands of schemas × dozens of tables strain pg_catalog, slowing planning, autovacuum, and any tool that introspects the database.
• search_path is its own leak surface. A connection returned to the pool with the wrong search_path is the same class of bug RLS eliminates — enforced by convention, not by the database.
• Cross-tenant work gets hard. Product analytics across all tenants means a UNION over N schemas.

Schema-per-tenant fits best with a small, stable number of larger tenants, particularly when customers expect their data in "their own" namespace.

Database-per-tenant: maximum isolation, maximum overhead

One database per tenant is the right answer when a regulator or an enterprise contract demands physical separation, per-tenant encryption keys, or per-tenant point-in-time restore. Everything else about it is overhead: connection pools per database, migrations per database, monitoring per database, and no practical cross-tenant queries. Few teams need this for more than a handful of premium tenants — and it composes fine with RLS for the long tail (shared RLS database for most tenants, dedicated databases for the few that require it).

Shared tables + RLS: the default worth reaching for

With RLS, all tenants share tables, every row carries a tenant_id, and PostgreSQL enforces tenant_id = current_setting('rls.tenant_id') on every query — including raw SQL and the query someone writes two years from now. One schema, one migration path, one pool, one backup. Cross-tenant admin work uses a dedicated BYPASSRLS role, explicitly.

What Flask RLS adds over hand-rolling it:

• Transaction-scoped context via set_config(..., is_local=true) — pool-safe with no cleanup handlers (how it works).
• Fail-closed defaults — missing context yields zero rows, never all rows (security model).
• FORCE ROW LEVEL SECURITY emitted for every table, closing the owner-bypass gotcha.
• Policies as code — TenantPolicy, UserPolicy, ExpressionPolicy compile through SQLAlchemy's own PostgreSQL dialect, and ship as Alembic operations.

The honest costs: policies add a predicate to every query (mitigated by the InitPlan pattern and an index on tenant_id); RLS applies per database role, so your app must connect as a non-owner role; and "physical isolation" checkbox requirements are not satisfiable with any shared-table design.

Decision rubric

• Fewer than ~10 large tenants, customers demand namespace isolation → schema-per-tenant.
• Contractual/regulatory physical isolation → database-per-tenant (possibly hybrid).
• Everything else — SaaS with tens to millions of tenants on shared infrastructure → shared tables with RLS. It is the architecture PostgreSQL itself provides the primitives for, and the one that stays boring as you grow.

Start with the Quick Start, or follow the Complete Guide end-to-end.