Database Multitenancy Design: Pooled vs. Siloed Architectures

Table of contents

Designing for Multitenancy

When building a Software-as-a-Service (SaaS) application, one of the most critical early decisions is how you partition database resources across customers (tenants). Get it wrong, and you’ll either end up with runaway infrastructure bills, security isolation breaches, or schema migrations that take hours to run.

In this post, we’ll break down the architectural details of the three standard multitenancy models: Silo (Database-per-Tenant), Bridge (Schema-per-Tenant), and Pool (Shared-Database-Shared-Table). We will compare their operational tradeoffs, look at how to secure logical isolation using PostgreSQL Row-Level Security (RLS), and build a dynamic routing datasource in Spring Boot.

1. The Three Multitenancy Patterns

+---------------------------------------------------------------------------------+
|                               MULTITENANCY PATTERNS                             |
|                                                                                 |
|  1. SILO MODEL (DB per Tenant)                                                  |
|     [ Tenant A App ] ----> [ DB_Tenant_A (Isolated Server/Instance) ]           |
|     [ Tenant B App ] ----> [ DB_Tenant_B (Isolated Server/Instance) ]           |
|                                                                                 |
|  2. BRIDGE MODEL (Schema per Tenant)                                            |
|     [ Shared App ]   ----> [ DB Server ] -> Schema_A / Schema_B (Logical)       |
|                                                                                 |
|  3. POOL MODEL (Shared Table)                                                   |
|     [ Shared App ]   ----> [ DB Server ] -> Table `orders` [tenant_id column]  |
+---------------------------------------------------------------------------------+

Pattern A: The Silo Model (Database-per-Tenant)

In the Silo Model, each tenant has a completely isolated database instance or a separate database within a shared server.

• Pros:
  • Absolute Isolation: Zero risk of “cross-talk” or accidental data leaks. Perfect for highly regulated industries (e.g., healthcare, finance).
  • Independent Upgrades & Backups: A single tenant can be backed up, restored, or migrated to a different region without affecting others.
  • No Noisy Neighbor Effect: An expensive query executed by Tenant A cannot starve the database resources of Tenant B.
• Cons:
  • High Infrastructure Cost: Hard to utilize resources efficiently. You must provision capacity for peak loads on a per-database basis.
  • Connection Exhaustion: If you run $1,000$ tenants, your microservices need $1,000$ active connection pools, instantly exhausting database socket resources.
  • Operational Nightmare: Schema migrations must be orchestrated and run against $1,000$ separate databases.

Pattern B: The Bridge Model (Schema-per-Tenant)

In the Bridge Model, tenants share a database server, but their tables are separated logically into separate PostgreSQL schemas (tenant_a.orders, tenant_b.orders).

• Pros:
  • Logical Isolation: Database roles can be configured to lock down schema access.
  • Shared Resources: Reduces hardware footprint compared to the Silo model.
• Cons:
  • Schema Bloat (PostgreSQL Specific): PostgreSQL stores schema metadata in internal system catalog tables (like pg_class, pg_attribute). If you scale to $5,000$ schemas, the catalog size swells to millions of rows. Simple database operations, backups, and index scans slow down dramatically because Postgres has to scan massive catalog tables.
  • Migration Latency: A simple table alteration can take hours to execute as it must iterate sequentially through thousands of schemas.

Pattern C: The Pool Model (Shared Database, Shared Table)

In the Pool Model, all tenants share the same database, the same schemas, and the same tables. Data is partitioned logically by adding a foreign key column (e.g., tenant_id) to every table.

• Pros:
  • Maximum Resource Density: Hard infrastructure costs are minimized. Database instances run at high utilization rates.
  • Operational Simplicity: Schema migrations are executed once, globally.
• Cons:
  • Isolation Risks: A single developer mistake (forgetting a WHERE tenant_id = ? clause in a SQL query) can leak sensitive data.
  • Noisy Neighbors: A resource-heavy query by Tenant A will degrade performance for all other tenants.

2. Securing the Pool: PostgreSQL Row-Level Security (RLS)

If you select the high-density Pool Model, you must mitigate the data leak risk at the database level rather than relying on application code correctness. PostgreSQL provides a powerful feature for this: Row-Level Security (RLS).

With RLS, the database engine transparently filters rows returned by queries based on security policies, even if the application’s SQL query completely forgets the WHERE tenant_id clause.

-- 1. Create a tenant-partitioned table
CREATE TABLE customer_orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR(50) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

-- 2. Enable Row-Level Security
ALTER TABLE customer_orders ENABLE ROW LEVEL SECURITY;

-- 3. Define the tenant isolation policy
-- We use a session variable 'app.current_tenant_id' to store the active tenant
CREATE POLICY tenant_isolation_policy ON customer_orders
    USING (tenant_id = current_setting('app.current_tenant_id'));

How to Query Safely:

When your Spring Boot application borrows a connection from the pool, it must set the session-level variable before executing the actual business query:

-- 1. Start a transaction block
BEGIN;

-- 2. Set the session variable to the active tenant ID
SET LOCAL app.current_tenant_id = 'TENANT_ABC';

-- 3. Run the query. Notice there is no WHERE tenant_id filter!
SELECT * FROM customer_orders;

-- PostgreSQL transparently rewrites the query internally to:
-- SELECT * FROM customer_orders WHERE tenant_id = 'TENANT_ABC';

COMMIT;

3. Implementing Dynamic Datasource Routing in Spring Boot

To support the Silo or Bridge model dynamically without hardcoding connection arrays, Spring Boot provides a built-in abstract class called AbstractRoutingDataSource.

This class acts as a lookup router. It intercepts connection requests from Hibernate/JPA and routes them to a target pool based on a thread-local context key.

                  +----------------------------------------------+
                  |            ThreadLocal Context               |
                  |            (TenantContextHolder)             |
                  +----------------------+-----------------------+
                                         |
                                         v Read Active Tenant ID
                  +----------------------+-----------------------+
                  |         TenantRoutingDataSource              |
                  |      (extends AbstractRoutingDataSource)     |
                  +----------------------+-----------------------+
                                         |
                       +-----------------+-----------------+
                       |                                   |
                       v                                   v
             [ Connection Pool A ]               [ Connection Pool B ]
             (Tenant A Database)                 (Tenant B Database)

Step 1: Thread-Local Tenant Context

public class TenantContext {
    private static final ThreadLocal<String> CURRENT_TENANT = new ThreadLocal<>();

    public static void setTenantId(String tenantId) {
        CURRENT_TENANT.set(tenantId);
    }

    public static String getTenantId() {
        return CURRENT_TENANT.get();
    }

    public static void clear() {
        CURRENT_TENANT.remove();
    }
}

Step 2: The Routing DataSource Implementation

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class TenantRoutingDataSource extends AbstractRoutingDataSource {
    
    @Override
    protected Object determineCurrentLookupKey() {
        // Hibernate calls this method every time it borrows a connection.
        // We route based on the ThreadLocal active tenant ID.
        return TenantContext.getTenantId();
    }
}

Step 3: Configuration Class

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfiguration {

    @Bean
    public DataSource dataSource() {
        TenantRoutingDataSource routingDataSource = new TenantRoutingDataSource();
        
        // Define our dynamic targets
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("TENANT_A", createDataSource("jdbc:postgresql://db-srv:5432/tenant_a_db"));
        targetDataSources.put("TENANT_B", createDataSource("jdbc:postgresql://db-srv:5432/tenant_b_db"));
        
        routingDataSource.setTargetDataSources(targetDataSources);
        
        // Define a fallback default datasource
        routingDataSource.setDefaultTargetDataSource(createDataSource("jdbc:postgresql://db-srv:5432/default_db"));
        
        routingDataSource.afterPropertiesSet();
        return routingDataSource;
    }

    private DataSource createDataSource(String url) {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(url);
        dataSource.setUsername("svc_app");
        dataSource.setPassword("vault_secret_password");
        dataSource.setMaximumPoolSize(5);
        return dataSource;
    }
}

Step 4: Intercepting HTTP Requests via Servlet Filter

To populate the ThreadLocal context, register a servlet filter that extracts the tenant identifier from the incoming request headers (e.g., X-Tenant-ID):

import jakarta.servlet.*;
import jakarta.servlet.http.HttpServletRequest;
import java.io.IOException;

public class TenantContextFilter implements Filter {

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
            throws IOException, ServletException {
        
        HttpServletRequest httpRequest = (HttpServletRequest) request;
        String tenantId = httpRequest.getHeader("X-Tenant-ID");
        
        if (tenantId != null && !tenantId.isBlank()) {
            TenantContext.setTenantId(tenantId);
        }
        
        try {
            chain.doFilter(request, response);
        } finally {
            // CRITICAL: Clean up ThreadLocal context to prevent memory leaks 
            // and cross-tenant pollution on recycled servlet threads.
            TenantContext.clear();
        }
    }
}

Summary Checklist for SaaS Architects

• Silo Model: Best for enterprise tiers demanding physical data isolation, dedicated server configurations, and customized backup schedules.
• Bridge Model: Avoid for tables that scale past thousands of clients due to PostgreSQL catalog bloat bottlenecks.
• Pool Model: Pair with PostgreSQL Row-Level Security (RLS) and local session variable contexts (SET LOCAL) to build logical isolation safeguards at the database level.
• ThreadLocal Hygiene: Always execute context cleanup inside a servlet filter’s finally block to protect threads against data leak cross-contamination.