Per-tenant databases vs shared schema — why we picked the harder path

If you ask 100 SaaS architects "how should I build multi-tenancy?", 90 will say "shared database, TenantId column on every table". It's cheaper, faster, and easier to operate. For most B2B SaaS, it's the right answer.

We didn't pick it. Every School Console tenant gets its own physical PostgreSQL database — separate connection string, separate backups, separate encryption keys, sometimes a separate region.

It costs us more. Here's why we still do it.

The shared-database temptation

In a shared-database design, all 100 schools share one Postgres instance. Every table has a TenantId column. Every query has WHERE TenantId = @currentTenantId. The application layer adds that filter automatically.

Why everyone loves it:

• One database to back up, monitor, alert on
• One schema migration to apply
• Easy cross-tenant queries (sales dashboards, support troubleshooting)
• Compute pooling — quiet tenants subsidise loud ones
• Cheap to onboard tenant #101 — no provisioning

Where it breaks

Three failure modes we've personally watched competitors hit:

Mode 1: One missing WHERE clause

A junior engineer adds a new admin endpoint. Forgets the tenant filter. Now the SuperAdminController.GetAllStudents() returns every student in every school.

Maybe someone notices in code review. Maybe not. The endpoint ships. Three months later, a curious customer figures it out. You're in front of regulators.

In a per-tenant-DB design, this bug is physically impossible. The wrong DB has the wrong students. There's no global query that can leak across tenants.

Mode 2: One bad backup restore

School A wants to restore a backup from last Tuesday. Your backup is for the whole shared database — so to restore School A, you either:

• Restore the whole DB into a separate environment, export School A's rows, import them back into prod (slow, error-prone, risk of partial state)
• Or restore in-place and clobber everyone else's recent data (catastrophic)

In a per-tenant-DB design, School A's backup is a 200 MB file. Restore it in 20 minutes, nobody else affected.

Mode 3: One tenant goes loud

School A imports 50 000 student photos. Their photo-upload job pegs Postgres CPU at 95% for an hour. Every other school's portal slows to a crawl. Your support inbox fills up.

In a per-tenant-DB design, School A's photos run on School A's database. Nobody else notices.

What it costs us

We're honest about the cost. The trade-offs:

• More expensive to operate. Each tenant is a separate Postgres instance (or at minimum a separate DB on a shared cluster). Storage, compute, backups all multiply.
• Schema migrations are loop, not single-shot. Updating to a new schema means iterating through every tenant DB. We have tooling for this (the Migrator project) but it's still slower than a single ALTER TABLE.
• Cross-tenant analytics are hard. Want to know "total students across all our customers"? You can't just run one query. We solved this with a separate analytics warehouse that ingests anonymised data from every tenant nightly.
• Onboarding a new tenant is provisioning. Not a row in a table. We've automated this, but it's still ~3 minutes per new tenant vs ~3 seconds in shared-DB.

Where it pays back

• Data residency — a Mumbai-based university can have their DB in Mumbai, an NCR school in NCR. Different regions, same software.
• Targeted backups + restores — 20-minute single-tenant restore is a real feature, not a quarterly fire drill.
• Blast-radius isolation — A bad migration on tenant 17 doesn't take down tenants 1-100.
• Compliance posture — DPDP-Act 2023 + state-specific data laws are dramatically easier when you can physically locate each tenant's data.
• Enterprise sales — "your data lives in its own database" is a single sentence that closes many deals. We've stopped counting.

When it doesn't pay back

If you're building a small-business CRM with 10 000 customers each paying ₹500/month, this architecture will kill you. Pay-back depends on:

• The data being valuable (school data is)
• Customers caring about isolation (schools do)
• Each tenant being large enough to justify the overhead (1 500-student schools are)
• Regulatory pressure favouring isolation (DPDP, state data laws — yes)

For school ERP, every one of these is yes.

If you're evaluating a school ERP, ask the architect: "What happens if you forget the WHERE TenantId clause?" If they answer "we have a code review checklist", they're shared-DB. If they answer "the wrong DB has the wrong data", they're per-tenant.

That's the difference. Request a demo — we'll show you the connection-string resolver in code.