Most developers start their first SaaS project with a simple tenant_id column on every table. It is the fastest way to get to market. You run a few queries with a WHERE tenant_id = X clause, and everything feels great. Then, six months later, you realize you have a massive problem: data leakage and performance degradation.
The Danger of the Manual Filter
When you rely on developers to manually add WHERE tenant_id = ? to every single SQL query, you are playing a dangerous game. It only takes one forgotten clause in a complex JOIN or a single misplaced variable in a delete statement to leak private data from one customer to another. In a B2B environment, this is a catastrophic failure.
I have seen this happen in production. A developer writes a cleanup script to remove old logs but forgets the tenant filter. Suddenly, the system wipes out the logs for every single customer instead of just one.
Moving Toward Row Level Security (RLS)
If you are using PostgreSQL, the best way to handle this is Row Level Security. Instead of trusting your application code to filter the data, you push that logic into the database itself.
With RLS, you define a policy that says: "A user can only see rows where the tenant_id matches their session variable." Once this is set up, you can run SELECT * FROM orders and the database will automatically filter the results based on the current user's identity. Your application code becomes cleaner, and the risk of accidental data leaks drops significantly.
The Performance Wall
Shared tables work well until one customer becomes a "whale."
Imagine you have 100 small customers and one giant enterprise client that generates 80 percent of your data. Because all the data lives in the same table, the indexes for the small customers get pushed out of memory by the whale's data. Your queries for the small clients start slowing down because the database is scanning massive indexes.
This is where the "Silo" approach comes in. For your biggest clients, you move them to their own dedicated database or schema. This prevents one noisy neighbor from ruining the experience for everyone else.
The Migration Nightmare
One thing people forget is the "Offboarding" process. When a customer cancels their subscription and asks for their data to be deleted, a shared table makes this hard. You cannot just drop a database. You have to run massive DELETE queries that can lock tables and slow down the site for other users.
If you use a schema-per-tenant approach, offboarding is as simple as DROP SCHEMA tenant_x. It is clean, fast, and leaves no remnants behind.
Practical Advice for Architecture
If you are starting today, do not over-engineer, but do not be naive. Here is the hierarchy of isolation I recommend:
- For MVP: Use a
tenant_idcolumn, but use a database wrapper or an ORM middleware that injects the filter automatically. Never write the filter manually in every controller. - For Growth: Implement PostgreSQL RLS to ensure hard isolation at the database level.
- For Enterprise: Build your system to support "sharding" or separate schemas from day one. Even if you start with one shared DB, ensure your code does not assume all data lives in a single place.
The Concrete Takeaway
Stop trusting your application code to keep tenant data separate. Move the isolation logic as close to the data as possible. Whether it is through RLS or separate schemas, the goal is to make it impossible for a developer to accidentally query another customer's data. Your future self, and your customers, will thank you.
Top comments (0)