Schema-based sharding and multi-tenancy alternatives

Four multi-tenancy architectures

Each row in this table is a different bet you are making about your growth trajectory and your isolation obligations. When you see a deal with contractual data-residency requirements, that row tells you to skip RLS and row-based sharding and go straight to schema-based or database-per-tenant.

Citus 12 schema-based sharding

Introduced in July 2023, Citus 12 added schema-based sharding: each tenant gets a dedicated Postgres schema, and Citus distributes schemas across workers.

-- Tenant onboarding: create a schema, Citus assigns it to a worker
CREATE SCHEMA acme;
CREATE TABLE acme.orders (id BIGINT, ...);
CREATE TABLE acme.users  (id BIGINT, ...);

-- Application: scope connection to tenant's schema
SET search_path TO acme, public;
-- All subsequent table references resolve within acme's schema
-- Citus routes to acme's worker transparently

Benefits over row-based sharding:

1. No shared distribution key column required: any query within the schema runs on its worker — joins between any tables in the same schema are always local.
2. Schema is the deployment unit: CREATE SCHEMA for onboarding, DROP SCHEMA CASCADE for offboarding, move schema between workers for rebalancing.
3. Clean tenant isolation: table names are different per schema — one tenant cannot accidentally read another’s data.
4. Per-tenant migrations: ALTER TABLE acme.orders ADD COLUMN ... only touches that schema.

Limits:

• Tenant count ceiling: Citus recommends fewer than a few thousand schemas. Schema metadata is loaded into the planner on every query; thousands of schemas slow planning.
• Cross-tenant queries are still cross-shard: analytics across multiple tenants still require fan-out.
• Reference tables do not apply within schemas: lookup tables need to be in each schema or accessed from a shared schema.

Together, these four benefits mean schema-based sharding gives you structural isolation without the per-instance operational overhead of database-per-tenant — but only until the planner ceiling hits. Without the tenant-count ceiling awareness, teams often reach for schema-based sharding and then hit planning overhead at a few thousand tenants when row-based would have scaled further.

Sweet spot: B2B SaaS with 50–2000 enterprise tenants where contractual or regulatory data isolation is required.

Application-level routing

Skip Citus entirely: your application code maintains a shard map (a table or service mapping tenant_id → connection_string) and routes queries explicitly.

// Example: routing layer reads tenant from request context
const shard = await shardMap.get(req.tenantId);
const db = connectionPool(shard.connectionString);
return db.query('SELECT * FROM orders WHERE ...', [req.tenantId]);

Pros: explicit, no extra database product, maximum control, per-tenant routing logic (region, plan tier). Cons: every cross-shard concern becomes app code — joins, transactions, fan-out, rebalancing, failover. The routing layer is critical-tier-1 infrastructure that you now own.

Common when shard count is small (10–100) and queries are highly tenant-scoped. At 5000 tenants with complex queries, Citus is usually less engineering effort over a multi-year horizon.

Database-per-tenant

Each tenant gets a dedicated Postgres database (or instance). Maximum isolation: tenants literally cannot share any infrastructure at the database level.

Operational model:

• PgBouncer pool per (tenant, database)
• Backups per tenant database
• Postgres minor and major upgrades per tenant database
• Schema migrations deployed per tenant (or batched)

Practical ceiling: ~100–500 tenants before operational overhead dominates. Below that, database-per-tenant is often simpler than Citus for regulated industries (financial, healthcare) where contractual isolation is hard requirement.