Two weeks ago, while I was in a backend architecture discussion about a project, the concept of multi tenancy came up. The question was basically,
“How should we structure multi tenancy in our project?”
Honestly, this was the first time I had heard the concept in depth. Before trying to answer the architectural question, I wanted to understand the concept properly first. So I decided to explore it.
First, what is multi tenancy?
In SaaS systems, multiple customers or businesses use the same application, but their data still needs proper isolation.
For example, in a B2B SaaS application:
- Business A should never see Business B’s users
- Tenant specific migrations may become necessary
- Scaling one large customer should not affect everyone else
This is where multi tenant architecture decisions become important.
The Three Main Approaches
While exploring the topic, I found that multi tenancy mainly follows three approaches.
1. Shared Table
This is the simplest approach. One table is shared across all tenants, usually separated using a tenant_id.
It is easy to set up, migrations are simpler, and it is cost effective. This approach is mostly suitable for early stage MVPs and products because it is easy to build and requires fewer database resources.
But the tradeoff becomes tenant isolation and long term operational concerns. There is a higher chance of accidental data exposure if queries are not handled carefully. Also, the table size can become huge as the number of users grows.
2. Schema-per-tenant
In this approach, one database contains multiple tenant schemas.
Each tenant gets its own schema and tables, so tenant data gets separated naturally. This gives better isolation and cleaner organization.
But the main challenge becomes migration complexity. Every tenant schema must stay structurally consistent, which becomes operationally harder as the number of tenants increases.
3. Database-per-tenant
In this model, each tenant gets a completely separate database.
This gives strong isolation and better security. It is also better for scaling because issues in one database are less likely to affect others.
But the operational complexity becomes much higher. Managing migrations, backups, monitoring, and infrastructure across many databases can become expensive over time.
Exploring search_path
While exploring the concept, I came across search_path. This was the point where schema based multi tenancy started making practical sense to me.
When an application receives requests from different tenants, how does PostgreSQL know which schema to query?
That is exactly the problem search_path solves.
sql
SET search_path TO tenant1;
This automatically tells PostgreSQL to search the tenant1 schema first.
What I found interesting was that the application query itself could remain the same while the tenant context changes dynamically underneath.
Exploring PostgreSQL Row Level Security (RLS)
Another interesting part of the exploration was PostgreSQL Row Level Security (RLS).
What I found powerful was that tenant filtering could move into the database layer itself instead of depending entirely on application level WHERE clauses.
This made me realize how tenant isolation can become part of the database security model itself.
Exploring Citus
While exploring long term scaling discussions, I also came across Citus, a PostgreSQL extension designed for distributed scaling in large multi tenant SaaS systems.
I only explored it conceptually, but it was interesting to see how tenant based distribution can become part of scaling strategies as systems grow.
Final Thoughts
I’m not going deep into the concepts like a tutorial blog. This is mostly what I understood while exploring the topic for the first time.
What started as a simple exploration into multi tenancy became a much broader lesson in SaaS backend architecture, operational complexity, and long term scaling tradeoffs.
The most valuable realization for me was that backend architecture decisions are rarely about finding the best solution. They are usually about balancing tradeoffs between scalability, isolation, operational complexity, and future growth.