Why every tenant gets its own database
Schema-per-tenant, shared tables or database-per-tenant? At Avenit we chose the strictest option. Here's why — and what the trade-offs are.
Multi-tenancy is a question every SaaS asks itself once — and answers for the next five years. At Avenit we have one PostgreSQL database per tenant. Not schema-per-tenant. Not shared tables with a tenant_id column. A separate, full-fledged database.
It’s not a popular choice. Here’s why we made it.
Three options we had to choose from
1. Shared tables
All users of all customers in a single users table, separated by a tenant_id column. Standard in typical SaaS.
Pros: simple, cheap, easy to start with. Cons: one poorly written query and data leaks between customers. Indexes grow proportional to the sum of everyone’s data. Backing up anything means backing up everything.
2. Schema-per-tenant
One database, separate PostgreSQL schemas per customer. Logical isolation without physical isolation.
Pros: still one DB connection, cheaper than separate databases. Better isolation than shared tables.
Cons: pg_catalog grows linearly. Backing up one customer requires pg_dump --schema=.... PostgreSQL’s practical limits on schemas (~1000).
3. Database-per-tenant
A separate PostgreSQL database for each tenant. Physical and logical isolation.
Pros: full isolation. Per-customer backup and restore. Separate migrations, separate versions. An enterprise customer can get their own server. Cons: harder to manage connections. You need a pool on the application side (we use PgBouncer + LRU cache on the NestJS side).
Why we chose option 3
Enterprise sales. Our prospective enterprise customers ask before signing: where is my data, how is it isolated, can I export it and change region. Shared tables won’t pass security audit at any larger B2B customer.
Second reason: migrations without panic. When customer X has production orders and customer Y is testing a new version, we don’t want to stack up around a single ALTER TABLE on 50 million rows. With separate databases it’s like deploying to 50 separate environments — rolling, safely, without locks.
Third: the no-code builder. Our DDL generator creates real PostgreSQL columns when a customer adds a field to a form. In the shared-tables model, every field is either JSONB (slow, no indexes), or a column appearing on everyone’s rows. No compromise smells right.
What’s the cost?
Two real ones:
- Connection pooling — you can’t just open
pg.Poolat startup. We have an LRU cacheMap<dbName, postgres.Sql>inTenantConnectionService, closing unused connections after a TTL. - Cross-tenant operations — if you want to count “how many invoices were issued on the platform today”, you orchestrate queries from the application. Rarely needed, but the cost is there.
Today — 0 tenants in production, 3 in dev — we don’t feel the pain. But I know where it will be when we cross 200 databases. We have a plan to shard tenants across PostgreSQL nodes.
Conclusion
Database-per-tenant isn’t for everyone. For us — B2B with enterprise ambitions — it was the only sensible choice. We paid with architectural complexity at the connection layer. We gained a sales argument and peaceful operational sleep.
If you’re building a SaaS and wavering between options — ask yourself: “Will my biggest future customer sign with me when they see their data sits in the same table as their competitor’s?”