Stop Splitting Databases: Spring Boot + PostgreSQL Schema Multi‑Tenant Best Practice for Performance

Problem with a naïve "one database per tenant" approach

Creating a separate database, deployment and duplicated configuration for each tenant quickly leads to an explosion of database instances, uncontrolled connection pools, difficult migrations and very low resource utilization. Many applications do not actually require full database‑level isolation.

PostgreSQL schema per tenant as the optimal solution

Using a PostgreSQL schema for each tenant provides:

Strong data isolation

High performance

Low resource consumption

Controllable operational cost

Strong scalability

Easy data migration

Compared with independent databases it avoids instance explosion; compared with a shared table it eliminates cross‑tenant data leakage.

Overall request flow

Client sends X‑TenantID header.

A Filter intercepts the request and stores the tenant ID in a ThreadLocal.

Hibernate’s CurrentTenantIdentifierResolver reads the tenant from the context.

The custom MultiTenantConnectionProvider executes SET search_path TO <tenant_schema> on the connection.

All subsequent SQL statements are automatically routed to the tenant’s schema.

Key implementation components

AppTenantContext.java – a Filter that extracts X‑TenantID, stores it in a ThreadLocal, and clears it in a finally block to avoid ThreadLocal leakage.

package com.icoderoad.context;

import jakarta.servlet.*;
import jakarta.servlet.http.HttpServletRequest;
import org.slf4j.MDC;
import org.springframework.stereotype.Component;
import java.io.IOException;
import java.util.Objects;

@Component
public class AppTenantContext implements Filter {
    private static final String LOGGER_TENANT_KEY = "tenant_id";
    public static final String TENANT_HEADER = "X-TenantID";
    private static final String DEFAULT_TENANT = "public";
    private static final ThreadLocal<String> CURRENT_TENANT = new ThreadLocal<>();

    public static String getCurrentTenant() {
        String tenant = CURRENT_TENANT.get();
        return Objects.requireNonNullElse(tenant, DEFAULT_TENANT);
    }

    public static void setCurrentTenant(String tenant) {
        MDC.put(LOGGER_TENANT_KEY, tenant);
        CURRENT_TENANT.set(tenant);
    }

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

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
            throws IOException, ServletException {
        HttpServletRequest req = (HttpServletRequest) request;
        String tenant = req.getHeader(TENANT_HEADER);
        try {
            if (tenant != null && !tenant.isBlank()) {
                setCurrentTenant(tenant);
            }
            chain.doFilter(request, response);
        } finally {
            clear();
        }
    }
}

CurrentTenantIdentifierResolverImpl.java – resolves the current tenant for Hibernate.

package com.icoderoad.config;

import com.icoderoad.context.AppTenantContext;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import java.util.Objects;

public class CurrentTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver<String> {
    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenant = AppTenantContext.getCurrentTenant();
        return Objects.requireNonNullElse(tenant, "public");
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}

MultiTenantConnectionProviderImpl.java – switches the schema on each connection and resets it on release.

package com.icoderoad.config;

import org.hibernate.engine.jdbc.connections.spi.AbstractDataSourceBasedMultiTenantConnectionProviderImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

@Component
public class MultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {
    private static final Logger log = LoggerFactory.getLogger(MultiTenantConnectionProviderImpl.class);
    private final DataSource dataSource;

    public MultiTenantConnectionProviderImpl(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    protected DataSource selectAnyDataSource() {
        return dataSource;
    }

    @Override
    protected DataSource selectDataSource(Object tenantIdentifier) {
        return dataSource;
    }

    @Override
    public Connection getConnection(Object tenantIdentifier) throws SQLException {
        String tenantId = tenantIdentifier != null ? tenantIdentifier.toString() : "public";
        log.info("Switching to tenant schema: {}", tenantId);
        Connection connection = getAnyConnection();
        try (Statement statement = connection.createStatement()) {
            statement.execute(String.format("SET search_path TO %s", tenantId));
        }
        return connection;
    }

    @Override
    public void releaseConnection(Object tenantIdentifier, Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.execute("SET search_path TO public");
        }
        releaseAnyConnection(connection);
    }
}

HibernateConfig.java – wires the multi‑tenant beans into Spring Boot.

package com.icoderoad.config;

import org.hibernate.cfg.Environment;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.orm.jpa.vendor.JpaVendorAdapter;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class HibernateConfig {
    private final JpaProperties jpaProperties;

    public HibernateConfig(JpaProperties jpaProperties) {
        this.jpaProperties = jpaProperties;
    }

    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        return new HibernateJpaVendorAdapter();
    }

    @Bean
    public CurrentTenantIdentifierResolver currentTenantIdentifierResolver() {
        return new CurrentTenantIdentifierResolverImpl();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            DataSource dataSource,
            MultiTenantConnectionProviderImpl provider,
            CurrentTenantIdentifierResolver resolver) {
        Map<String, Object> properties = new HashMap<>(jpaProperties.getProperties());
        properties.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, provider);
        properties.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, resolver);
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.icoderoad");
        em.setJpaVendorAdapter(jpaVendorAdapter());
        em.setJpaPropertyMap(properties);
        return em;
    }
}

application.yml – enables schema‑based multi‑tenancy.

server:
  port: 8082

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/testdb
    username: postgres
    password: 123456
    driver-class-name: org.postgresql.Driver
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: none
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    properties:
      hibernate:
        multiTenancy: SCHEMA

Database preparation

Create the database and tenant schemas:

CREATE DATABASE testdb;
CREATE SCHEMA tenanta;
CREATE SCHEMA tenantb;
GRANT USAGE ON SCHEMA tenanta TO postgres;
GRANT USAGE ON SCHEMA tenantb TO postgres;

Automatic tenant switching

When a request contains X‑TenantID: tenanta, the filter stores the tenant, and the connection provider runs SET search_path TO tenanta. Subsequent SQL such as SELECT * FROM users is automatically rewritten to SELECT * FROM tenanta.users without any business‑logic changes.

Dynamic tenant creation

Service that validates the tenant name and creates a schema if it does not exist:

package com.icoderoad.service;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class TenantService {
    private final JdbcTemplate jdbcTemplate;

    public TenantService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /** Create a tenant schema dynamically */
    public void createTenant(String tenantName) {
        if (!tenantName.matches("^[a-zA-Z0-9_]+$")) {
            throw new IllegalArgumentException("非法租户名称");
        }
        String sql = "CREATE SCHEMA IF NOT EXISTS " + tenantName;
        jdbcTemplate.execute(sql);
    }
}

Controller exposing a /tenants/create endpoint:

package com.icoderoad.controller;

import com.icoderoad.service.TenantService;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/tenants")
public class TenantController {
    private final TenantService tenantService;

    public TenantController(TenantService tenantService) {
        this.tenantService = tenantService;
    }

    @PostMapping("/create")
    public String createTenant(@RequestParam String tenantName) {
        tenantService.createTenant(tenantName);
        return "租户创建成功: " + tenantName;
    }
}

Performance advantages of schema‑based multi‑tenancy

PostgreSQL schemas are merely namespaces; they share the same buffer cache, connection pool, and optimizer. This leads to lower latency and higher throughput compared with separate databases and eliminates the need for multiple connection pools or instance restarts.

Production pitfalls and best practices

Always clear the ThreadLocal in a finally block to prevent tenant leakage across requests.

Validate tenant names against ^[a-zA-Z0-9_]+$ to avoid SQL injection via SET search_path.

Reset the schema to public before returning a connection to the pool.

Manage schema DDL with migration tools such as Flyway or Liquibase instead of manual scripts.

Suitable business scenarios

SaaS platforms, ERP, CRM, OA, multi‑merchant systems, AI‑agent platforms, enterprise back‑ends, API gateways.

Tenant count ranging from hundreds to thousands where strong data isolation is required without exploding the number of database instances.

Conclusion

Schema‑based multi‑tenancy combined with Spring Boot and Hibernate delivers strong isolation, high performance, low operational cost, and easy scalability, enabling teams to build enterprise‑grade SaaS systems with minimal development effort.