Designing for Multi-tenancy: Shared vs. Isolated Databases

Previously in this course, we covered Database Backup and Recovery, ensuring our SaaS data remains resilient. Now, we shift our focus to multi-tenancy, the architectural backbone of any SaaS application.

Multi-tenancy is the principle that a single instance of your software serves multiple "tenants" (customers or organizations). The challenge is not just serving them, but ensuring their data remains logically—and sometimes physically—separate. How you approach this at the database level dictates your security, scalability, and maintenance overhead for the life of your product.

Comparing Shared vs. Isolated Database Strategies

There are two primary ways to approach multi-tenancy. Choosing between them is a classic trade-off between operational simplicity and strict isolation.

1. The Shared Database (Shared Schema)

In this approach, all tenants live in the same tables. You distinguish them by adding a tenant_id column to every table that contains business data. This is the most cost-effective and common pattern for startups.

• Pros: Easy to deploy updates (one schema change touches everyone), efficient resource usage.
• Cons: High risk of "noisy neighbor" issues (one tenant's query slows down the system) and potential for data leakage if a WHERE clause is forgotten.

2. The Isolated Database (Database-per-Tenant)

Here, every new customer gets their own database instance or private schema.

• Pros: Absolute security (one tenant cannot query another's data), easy to back up or restore individual tenants, no noisy neighbor interference.
• Cons: Expensive to maintain at scale; schema migrations must be run across hundreds or thousands of databases.

Implementing Tenant Identification

If you choose the shared-database model—which is standard for most early-stage projects—the tenant_id is your most important column. Without it, your application is blind to who owns what.

To ensure consistency, every table representing a scoped entity (like projects, tasks, or invoices) must include a tenant_id (or account_id) that references your main accounts table.

Code Example: Adding Multi-tenancy to our Schema

We are building upon our work from Primary Keys and Identifiers. Let’s update our tasks table to enforce tenant isolation.

SQL
-- Creating a table with tenant-scoping
CREATE TABLE tasks (
    task_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL, -- The anchor for all queries
    title TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- Ensure the tenant exists
    CONSTRAINT fk_tenant 
        FOREIGN KEY (tenant_id) REFERENCES accounts(account_id) 
        ON DELETE CASCADE
);

-- Indexing for performance: crucial for shared-database queries
CREATE INDEX idx_tasks_tenant_id ON tasks(tenant_id);

By adding the tenant_id column and the corresponding index, we ensure that every query filtering by tenant_id is performant.

Hands-on Exercise

1. Review your current schema design from Logical vs Physical Schema.
2. Identify three tables that must be scoped to a tenant.
3. Write the ALTER TABLE statements to add the tenant_id column to these tables, ensuring you include a FOREIGN KEY constraint.
4. Run an EXPLAIN on a hypothetical SELECT query to ensure your new tenant_id index is being utilized.

Common Pitfalls

• Forgetting the WHERE clause: The most common disaster in shared-database design is a developer running a query that returns data for all tenants. Use Row-Level Security (RLS) features in PostgreSQL or database-level views to enforce tenant filtering at the driver level.
• Assuming isolation is easy: If you go with the "Database-per-tenant" route, ensure you have robust automation for migrations. Manually updating 50 databases is not a viable strategy.
• Ignoring Indexes: If you don't index your tenant_id column, your database will perform a "Full Table Scan" every time you try to fetch data for a specific user, leading to catastrophic performance degradation as your data grows.

FAQ

Q: Should I use RLS (Row-Level Security) in PostgreSQL? A: Yes, if your team is comfortable with it. RLS acts as a safety net, automatically appending WHERE tenant_id = current_user to your queries, preventing accidental data leaks.

Q: Is it possible to switch strategies later? A: It is very difficult. Start with a shared database and add physical isolation (like partitioning) only when specific tenants outgrow the shared infrastructure.

Recap

Multi-tenancy defines how your SaaS manages data for different customers. While isolated databases offer strict security, most teams benefit from the efficiency of a shared database combined with disciplined tenant_id usage and robust indexing. Always treat tenant_id as the most critical attribute in your schema.

Up next: Analyzing Query Complexity