Four Common Multi‑Tenant Isolation Strategies and Their Trade‑offs

Preface

A medium‑size e‑commerce platform’s reporting system once crashed at night because an operations mistake deleted a tenant column in a shared table, leading to a platform‑wide ALTER TABLE disaster.

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 schemes and their practical implications.

1. Field Isolation Scheme

Low Cost, High Risk

Field isolation achieves logical separation by using a single table together with a tenant‑ID filter.

Initial development cost is minimal, but data‑security pressure shifts entirely to code quality.

Critical Defect Checklist:

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

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

Full‑table scans (e.g., reporting) inevitably cause cross‑tenant interference.

Code Defense Example

(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];
        // Entity auto‑fill 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: force tenant filter on full‑table scans

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

/* Safe version (force tenant_id) */
SELECT * FROM orders WHERE tenant_id = 'tenant_01' AND status = 'PAID' LIMIT 1000;

Applicable Scenarios

Early‑stage MVP products with few users.

Internal management systems with low data‑isolation requirements.

2. Schema Isolation

Database‑Level Isolation (Separate Schemas)

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

Operations Warning Checklist:

When schema count reaches hundreds, backup and migration costs rise sharply.

Cross‑schema joins require an intermediate aggregation layer.

Connection pools must be sized for the maximum tenant count to avoid connection storms.

Dynamic Routing Code Implementation

(1) Spring dynamic datasource configuration

spring:
  datasource:
    dynamic:
      primary: master
      strict: true
      datasource:
        master:
          url: jdbc:mysql://
master‑address
tenant_001:
          url: jdbc:mysql://
slave‑address?currentSchema=tenant_001
tenant_002:
          url: jdbc:mysql://
slave‑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("Invalid tenant!");
        }
        // Switch datasource dynamically
        DynamicDataSourceContextHolder.push(tenantId);
    }

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

Applicable Scenarios

Industries requiring moderate security (education, retail).

Systems with fewer than 50 tenants and controllable data volume.

3. Independent Database

Ultimate Data Isolation

Each tenant enjoys a completely separate database instance.

Security is highest, but cost grows linearly with the number of tenants.

Financial Warning Checklist:

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

Cross‑tenant aggregation requires an extra ETL system.

DBA operational cost increases linearly with tenant count.

Core DataSource 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); // Dynamically 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 binds correct datasource
            super.doBegin(transaction, definition);
        }
    };
}

Applicable Scenarios

Highly regulated industries such as finance and healthcare.

Key‑account customers with strong paying power and a need for isolated resource pools.

4. Hybrid Architecture

No Silver Bullet – Balancing Act

Core Principle: Provide different isolation schemes based on tenant tier.

When a tenant is created, assign it a level (S, A, B) and select the corresponding isolation method.

Tenant Level

Isolation Scheme

Resource Allocation

S

Independent Database

Dedicated RDS + read replica

A

Schema Isolation

Shared instance, separate schema

B

Field Filtering

Shared tables

Dynamic Strategy Selector

Using the Strategy pattern, the system picks the appropriate database access method according to tenant level.

Code example:

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 (Must‑Read)

Metadata Management: Build a tenant‑resource mapping table to avoid configuration drift.

Migration Toolchain: Develop automated upgrade/downgrade tools (e.g., migrate a VIP tenant from shared tables to an independent DB).

Layered Monitoring: Collect performance metrics separately for each isolation scheme.

Conclusion

The article lists four common multi‑tenant solutions; none is perfect, but each can be the best fit for a given business stage.

The essence of multi‑tenant design is a three‑way trade‑off among resources, security, and cost.

Choosing the most suitable scheme for the current phase yields real architectural wisdom.