I spent two years building a SaaS product where I thought I was being clever with my database design. I used a single shared table for everything and just slapped a tenant_id column on every single row. It worked great during the beta. It worked great for the first 500 users. Then we hit a growth spurt, and the system started crawling.
If you are building a multi-tenant app, you probably started with the same pattern. It is the easiest way to get to market. But there are specific walls you will hit that no amount of "upgrading the server" will fix.
The Indexing Trap
When you have a tenant_id on every table, your instinct is to create a composite index on (tenant_id, id) or (tenant_id, created_at). For a while, this is fast. But as your table grows to millions of rows, those indexes become massive.
One specific problem I ran into was index fragmentation. Because data for different tenants is interleaved in the same physical storage, the database spends more time jumping around the disk than actually reading data. You end up with high IOPS and slow queries even when the result set is small.
The Danger of 'The Big Table'
In a SaaS, you usually have one or two tables that grow 100x faster than others. For me, it was the audit_logs table.
Because every single action by every single user in every single tenant went into one table, the index for that table eventually exceeded the available RAM. Once the index no longer fits in memory, every single query triggers a disk read. Your API response times go from 50ms to 2 seconds overnight.
If you are seeing this, stop trying to optimize the query. You need to partition your data. Whether you use PostgreSQL partitioning or move those logs to a different storage engine, you cannot keep a trillion rows of audit logs in a single flat table.
The 'Noisy Neighbor' Problem
This is the part they do not tell you in tutorials. In a shared schema, one huge client can kill the experience for everyone else.
I had one customer who uploaded 50,000 records via a CSV import. Their queries started scanning huge chunks of the index. Because the database was working so hard to serve that one tenant, the CPU spiked to 100 percent. Suddenly, the 50 small customers who only had 10 records each were seeing timeouts.
When you share a database, you share the resources. If you do not implement rate limiting at the application level or use database-level resource quotas, your biggest customer will accidentally become your biggest liability.
How to Fix It Before It Breaks
If you are still early, you do not need to move to a "database per tenant" model. That adds massive overhead for migrations and backups. Instead, try these three things:
Strict Query Scoping: Wrap your database calls in a way that makes it impossible to forget the
tenant_idfilter. If you forget it once, you have a massive security leak where one user sees another user's data.Early Partitioning: If you have a table that grows linearly with usage (like logs or events), partition it by time or by tenant ID from day one. It is much easier to start with partitions than to migrate a 500GB table later.
Read Replicas for Analytics: Never let your users run "Export all data to CSV" queries on your primary write database. Route those heavy, scanning queries to a read replica. This prevents a single heavy report from locking tables and freezing the app for everyone.
The Concrete Takeaway
Do not assume a shared schema scales linearly. The moment your indexes stop fitting in RAM, your performance will fall off a cliff. Monitor your buffer cache hit ratio and identify your fastest-growing tables now. If a table is growing by millions of rows a month, plan your partitioning strategy today, not when the site goes down.
Top comments (0)