Four Proven Multi‑Tenant Isolation Patterns and When to Use Them

Introduction

A medium‑sized e‑commerce platform’s reporting system crashed at night because an operator accidentally deleted a tenant column in a shared table, leading to a platform‑wide data mix‑up caused by an ALTER TABLE statement.

The incident highlights that every step of choosing a multi‑tenant solution is a trade‑off between security and cost.

This article discusses four common multi‑tenant isolation schemes.

1. Field Isolation

Low cost, high risk

Field isolation uses a single table with a tenant_id filter for logical separation.

Initial development cost is low, but data security relies entirely on code quality.

Critical checklist:

Any DAO query missing tenant_id condition leads to cross‑tenant data leakage.

Indexes must have tenant_id as the leftmost prefix to avoid performance bottlenecks.

Full‑table scans (e.g., reporting) cannot avoid cross‑tenant interference.

Code defense examples

(1) MyBatis interceptor automatically injects

tenant_id

@Intercepts({@Signature(type = Executor.class, method = "update")})
public class TenantInterceptor implements Interceptor {
    public Object intercept(Invocation iv) throws SQLException {
        MappedStatement ms = (MappedStatement) iv.getArgs()[0];
        Object param = iv.getArgs()[1];
        // 实体类自动填充tenant_id
        if (param instanceof BaseTenantEntity) {
            Field tenantIdField = param.getClass().getDeclaredField("tenantId");
            tenantIdField.setAccessible(true);
            if (tenantIdField.get(param) == null) {
                tenantIdField.set(param, TenantContext.get());
            }
        }
        return iv.proceed();
    }
}

(2) SQL firewall: enforce tenant_id in full‑table scans

/* Dangerous operation (may scan whole table) */
SELECT * FROM orders WHERE status = 'PAID';

/* Safe version (force tenant_id filter) */
SELECT * FROM orders
WHERE tenant_id = 'tenant_01'
AND status = 'PAID'
/* Must add LIMIT to prevent full fetch */
LIMIT 1000;

Suitable scenarios:

MVP products with few users.

Internal management systems with low data‑isolation requirements.

2. Schema Isolation

Database‑level isolation

Each tenant gets an independent schema within the same DB instance, achieving schema‑level isolation.

Operational warnings:

Backup and migration costs rise sharply after hundreds of schemas.

Cross‑schema joins require an aggregation layer.

Connection pool must be sized for the maximum tenant count, risking connection storms.

Dynamic routing implementation

(1) Spring dynamic datasource configuration

spring:
  datasource:
    dynamic:
      primary: master
      strict: true
      datasource:
        master:
          url: jdbc:mysql://... # main DB address
        tenant_001:
          url: jdbc:mysql://...# from address?currentSchema=tenant_001
        tenant_002:
          url: jdbc:mysql://...# from address?currentSchema=tenant_002

(2) AOP aspect for dynamic schema switching

@Aspect
@Component
public class SchemaAspect {
    @Before("@annotation(requireTenant)")
    public void switchSchema(JoinPoint joinPoint) {
        HttpServletRequest request = getCurrentRequest();
        String tenantId = request.getHeader("X-Tenant-ID");
        // validate tenant
        if (!tenantService.isValid(tenantId)) {
            throw new IllegalTenantException("租户身份异常！");
        }
        // dynamic switch datasource
        DynamicDataSourceContextHolder.push(tenantId);
    }

    @After("@annotation(requireTenant)")
    public void clearSchema() {
        DynamicDataSourceContextHolder.clear();
    }
}

Suitable scenarios:

Industries needing moderate security (education, retail).

Systems with less than 50 tenants and controllable data volume.

3. Independent Database

Ultimate data isolation

Each tenant has its own database instance.

Provides the highest security but cost grows linearly.

Financial warning checklist:

Each instance adds roughly ¥3000 per month (cloud RDS basic config).

Cross‑tenant aggregation requires extra ETL support.

DBA operational cost increases linearly with tenant count.

Core routing code

(1) Abstract routing controller

public class TenantDataSourceRouter extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TenantContextHolder.get();
    }

    @Override
    protected DataSource determineTargetDataSource() {
        String tenantId = (String) determineCurrentLookupKey();
        DataSource ds = dataSourceMap.get(tenantId);
        if (ds == null) {
            ds = createNewDataSource(tenantId); // dynamic create new tenant datasource
            dataSourceMap.put(tenantId, ds);
        }
        return ds;
    }
}

(2) Multi‑tenant transaction synchronizer

@Bean
public PlatformTransactionManager transactionManager() {
    return new DataSourceTransactionManager() {
        @Override
        protected void doBegin(Object transaction, TransactionDefinition definition) {
            TenantDataSourceRouter router = (TenantDataSourceRouter) getDataSource();
            router.initTenantDataSource(TenantContextHolder.get()); // ensure transaction bound to correct datasource
            super.doBegin(transaction, definition);
        }
    };
}

Suitable scenarios:

Highly regulated industries such as finance and healthcare.

Key accounts with strong payment ability that need isolated resource pools.

4. Hybrid Architecture

Balancing act without a silver bullet

Assign a tenant level and provide different isolation schemes accordingly.

Tenant level vs. isolation scheme table:

Tenant Level

Isolation Scheme

Resource Config

S

Independent Database

Dedicated RDS instance + read replica

A

Schema Isolation

Shared instance with separate schema

B

Field Filtering

Shared tables

Dynamic strategy selector using the strategy pattern:

public class IsolationStrategyFactory {
    public IsolationStrategy getStrategy(String tenantId) {
        TenantConfig config = configService.getConfig(tenantId);
        switch (config.getLevel()) {
            case VIP:
                return new IndependentDBStrategy();
            case STANDARD:
                return new SchemaStrategy();
            case BASIC:
            default:
                return new SharedTableStrategy();
        }
    }

    // example strategy interface
    public interface IsolationStrategy {
        DataSource getDataSource();
        void executeQuery(String sql);
    }
}

Operations pitfalls:

Metadata management : maintain tenant‑resource mapping to avoid configuration drift.

Migration toolchain : automate upgrades (e.g., move a VIP tenant from shared table to independent DB).

Layered monitoring : collect performance metrics separately for each isolation scheme.

Conclusion

The article lists four common multi‑tenant solutions. There is no perfect design, only the most suitable one for a given stage.

Multi‑tenant design is a trade‑off among resources, security, and cost.

Choosing the right scheme based on business phase yields practical architectural wisdom.