When you start a multi-tenant SaaS, the standard playbook is shared-schema with a tenant_id
column on every table. It's cheap to operate, easy to query across tenants, and Rails / Prisma /
Drizzle all have well-trodden patterns for it.
LOAM does the opposite. Every store gets its own Postgres database, provisioned during onboarding, with its own schema, migrations, and connection pool. Here's why we made that call — and what we'd warn you about if you do the same.
Why we did it
Blast radius. A bad migration on a shared schema affects everyone. With one database per tenant, the worst case for a botched migration is one store goes offline for the duration of a rollback — not the entire fleet.
Data residency. A small but growing number of merchants want their data stored in
specific jurisdictions. Per-tenant databases mean we provision in the region the merchant
asks for, no WHERE region = query gymnastics required.
Custom extensions. A few high-traffic merchants asked for pg_cron, pgvector, and
specific timezone defaults. Per-tenant lets us flip those on for individuals without
touching anyone else's instance.
Backup and restore are linear. Restoring one tenant from yesterday's snapshot is a
straightforward pg_restore to a fresh database. Doing the same on a shared schema means
writing a tenant-scoped restore tool, which is its own multi-month project.
What we'd warn you about
- Connection pooling becomes the central concern. We use PgBouncer in transaction mode with one pool per tenant cluster, lazily attached. Without that, you'll hit Postgres connection limits before you finish onboarding 100 stores.
- Cross-tenant analytics is harder. We solved this with a separate analytics warehouse (DuckDB-on-S3) that ingests change data from each tenant nightly.
- Schema evolution discipline matters more. We treat the per-tenant schema like an external API — every migration is reversible, every release goes through dry-run.
The trade is real, but for LOAM — where merchants own their data and we host store infrastructure on their behalf — it was the right call.