Hibernate Partition-Aware Routing for Multi-Tenant Java Apps

This walkthrough configures Hibernate 6 to route each tenant’s traffic to its own PostgreSQL shard and to keep the partition key in every generated query — one of the ORM integration & partition-aware routing patterns within partitioning implementation patterns & routing. You will build a MultiTenantConnectionProvider backed by one HikariCP pool per shard, a CurrentTenantIdentifierResolver that reads request context, a composite @IdClass key that always carries tenant_id, and an EXPLAIN-based check that list-partition pruning survives Hibernate’s SQL generation.

Prerequisites

• JDK 17+, Hibernate ORM 6.4+, HikariCP 5.x, and the PostgreSQL JDBC driver on the classpath
• Two or more PostgreSQL 14+ shard databases with identical schemas
• Hot tables LIST-partitioned by tenant_id on every shard, with the partition key in each primary key (see choosing a partition key for multi-tenant workloads)
• A tenant-to-shard assignment source (config map or lookup table) owned by one team
• pg_hba.conf entries allowing the app role to reach every shard from the app subnet
• A staging environment where you can capture Hibernate’s generated SQL safely

Step 1 — Declare one HikariCP pool per shard

DATABASE-mode multi-tenancy means every shard is a separate database with its own pool. Declare the pools declaratively so adding a shard is a config change, not a code change. The per-pool sizing follows the shard’s capacity, not the app’s thread count — a small shard hosting three tenants does not need the same pool as the shard carrying your largest customer.

# application.yml
app:
  tenancy:
    tenant-shard-map:
      acme: shard-a
      globex: shard-a
      initech: shard-b
      umbrella: shard-c
    shards:
      shard-a:
        jdbc-url: jdbc:postgresql://pg-shard-a:5432/orders_db
        username: app_rw
        maximum-pool-size: 20
        leak-detection-threshold: 30000
      shard-b:
        jdbc-url: jdbc:postgresql://pg-shard-b:5432/orders_db
        username: app_rw
        maximum-pool-size: 20
        leak-detection-threshold: 30000
      shard-c:
        jdbc-url: jdbc:postgresql://pg-shard-c:5432/orders_db
        username: app_rw
        maximum-pool-size: 10
        leak-detection-threshold: 30000

Operational note: Total connection load is the sum across pools times the number of app instances: three pools of 20+20+10 across 8 instances is 400 potential server connections. Budget against each shard’s max_connections individually, and prefer smaller pools plus queueing (connectionTimeout) over pools sized for peak burst.

DBA tip: Set leak-detection-threshold from day one. A connection checked out via getConnection(tenantId) and never released does not just leak — it pins one shard’s pool while the others stay healthy, which shows up as single-tenant timeouts that are maddening to diagnose after the fact.

Step 2 — Implement the shard-routing MultiTenantConnectionProvider

Hibernate 6 activates multi-tenancy as soon as a MultiTenantConnectionProvider is supplied. The provider’s contract is small: hand out a connection for a tenant identifier and take it back. Routing lives in one line — resolve the tenant to a shard, then borrow from that shard’s pool. This is the JVM’s single choke point for shard selection, the same role the chooser callbacks play in SQLAlchemy horizontal sharding.

public class ShardConnectionProvider
        implements MultiTenantConnectionProvider<String> {

    private final Map<String, HikariDataSource> shardPools;   // shard-a -> pool
    private final Map<String, String> tenantShardMap;         // acme -> shard-a

    public ShardConnectionProvider(Map<String, HikariDataSource> shardPools,
                                   Map<String, String> tenantShardMap) {
        this.shardPools = shardPools;
        this.tenantShardMap = tenantShardMap;
    }

    @Override
    public Connection getConnection(String tenantId) throws SQLException {
        String shard = tenantShardMap.get(tenantId);
        if (shard == null) {
            throw new SQLException("no shard mapping for tenant: " + tenantId);
        }
        return shardPools.get(shard).getConnection();
    }

    @Override
    public void releaseConnection(String tenantId, Connection conn)
            throws SQLException {
        conn.close();  // returns to the owning Hikari pool
    }

    @Override
    public Connection getAnyConnection() throws SQLException {
        return shardPools.get("shard-a").getConnection();  // bootstrap only
    }

    @Override
    public void releaseAnyConnection(Connection conn) throws SQLException {
        conn.close();
    }

    @Override public boolean supportsAggressiveRelease() { return false; }
    @Override public boolean isUnwrappableAs(Class<?> type) { return false; }
    @Override public <T> T unwrap(Class<T> type) { return null; }
}

Operational note: getAnyConnection() is used by Hibernate for bootstrap tasks such as dialect resolution — never for tenant data. Point it at a designated shard and make sure schema management (hbm2ddl) is disabled in production, or bootstrap DDL will run against one shard only and leave the fleet inconsistent.

DBA tip: Fail loudly on an unmapped tenant, as above. A “default shard” fallback silently writes new tenants’ rows to whichever shard is the default, and untangling misplaced tenants later means row-level copies under traffic instead of a config fix at signup time.

Step 3 — Resolve the current tenant from request context

CurrentTenantIdentifierResolver tells Hibernate which tenant identifier to pass to the connection provider when a session opens. Store the tenant in a request-scoped holder populated by your authentication filter — from a JWT claim, subdomain, or API key — before any persistence call runs.

public final class TenantContext {
    private static final ThreadLocal<String> CURRENT = new ThreadLocal<>();
    public static void set(String tenantId) { CURRENT.set(tenantId); }
    public static String get() { return CURRENT.get(); }
    public static void clear() { CURRENT.remove(); }
}

public class RequestTenantResolver
        implements CurrentTenantIdentifierResolver<String> {

    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenantId = TenantContext.get();
        if (tenantId == null) {
            throw new IllegalStateException(
                "no tenant bound to this thread; refusing to open a session");
        }
        return tenantId;
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        return true;  // reject reuse of a session under a different tenant
    }
}

Operational note: ThreadLocal context breaks under virtual threads or reactive schedulers that hop threads mid-request. On Java 21+, carry the tenant in a ScopedValue (or your framework’s request context) instead, and always clear() in a finally block — thread pools recycle threads, and a stale tenant id is a cross-tenant data leak, not just a bug.

DBA tip: Keep validateExistingCurrentSessions() returning true. It makes Hibernate assert that a reused session still belongs to the resolver’s current tenant, converting a would-be silent cross-shard read into an immediate exception you can page on.

Step 4 — Register the providers with Hibernate

Wire both classes in via configuration properties. In Hibernate 6 there is no separate multi-tenancy “mode” switch — supplying the connection provider enables it, and routing tenants to distinct databases is what older documentation called DATABASE mode.

# hibernate.properties
hibernate.multi_tenant_connection_provider=com.example.tenancy.ShardConnectionProvider
hibernate.tenant_identifier_resolver=com.example.tenancy.RequestTenantResolver

# never let Hibernate manage schemas across shards
hibernate.hbm2ddl.auto=none

# surface the SQL we will EXPLAIN in verification
hibernate.show_sql=false
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.orm.jdbc.bind=trace

Operational note: Under multi-tenancy Hibernate cannot use its usual single-datasource assumptions: second-level cache regions must include the tenant in their keys (Hibernate does this automatically for entities, but verify any custom cache interactions), and connection-based observability tools see three pools, not one — update dashboards accordingly.

DBA tip: The org.hibernate.orm.jdbc.bind trace logging prints bound parameter values, including tenant_id. That is exactly what you need in staging to reconstruct runnable SQL for EXPLAIN, and exactly what you must disable in production log pipelines that are not scrubbed for customer identifiers.

Step 5 — Make tenant_id part of every identity with @IdClass

Routing puts the session on the right shard; the composite key keeps the partition key in the SQL. With tenant_id as half of an @IdClass composite key, every em.find(), every foreign-key join to orders, and every generated WHERE clause carries the column PostgreSQL needs to prune its list partitions — the schema-side pattern detailed in list partitioning for multi-tenant SaaS schemas.

public class OrderId implements Serializable {
    private String tenantId;
    private Long orderNo;

    public OrderId() {}
    public OrderId(String tenantId, Long orderNo) {
        this.tenantId = tenantId;
        this.orderNo = orderNo;
    }
    @Override public boolean equals(Object o) {
        return o instanceof OrderId other
            && Objects.equals(tenantId, other.tenantId)
            && Objects.equals(orderNo, other.orderNo);
    }
    @Override public int hashCode() { return Objects.hash(tenantId, orderNo); }
}

@Entity
@Table(name = "orders")
@IdClass(OrderId.class)
@FilterDef(name = "tenantFilter",
           parameters = @ParamDef(name = "tenantId", type = String.class))
@Filter(name = "tenantFilter", condition = "tenant_id = :tenantId")
public class Order {
    @Id @Column(name = "tenant_id", nullable = false, updatable = false)
    private String tenantId;

    @Id @Column(name = "order_no", nullable = false, updatable = false)
    private Long orderNo;

    @Column(name = "total", nullable = false)
    private BigDecimal total;
    // getters/setters omitted
}

Operational note: Loading a row now requires both halves of the key: em.find(Order.class, new OrderId("acme", 1001L)). Any repository method that accepts a bare orderNo is a design smell — it either scans all partitions or, worse, works today because the shard holds one tenant and breaks the day a second tenant lands there.

DBA tip: On the PostgreSQL side, declare PRIMARY KEY (tenant_id, order_no) with tenant_id first. Partitioned tables require the partition key inside every unique constraint, and leading with tenant_id means the primary-key index itself serves tenant-scoped scans without an extra index.

Step 6 — Enforce the tenant filter on every session

The @Filter from Step 5 is inert until enabled, and a forgotten enableFilter() call is the classic way tenant scoping silently disappears from HQL queries (JPQL find() by composite key is already safe). Enable it centrally in a session event listener so no request-path code can skip it.

public class TenantFilterEnabler implements SessionOpenedListener {
    // registered via hibernate.session_factory.session_scoped_interceptor
    // or your framework's session customizer

    public void sessionOpened(Session session) {
        String tenantId = TenantContext.get();
        session.enableFilter("tenantFilter")
               .setParameter("tenantId", tenantId)
               .validate();
    }
}

Operational note: Hibernate filters apply to HQL/JPQL and criteria queries but not to native SQL or to em.find(). Composite keys cover find(); for the handful of native queries, code-review them against a checklist requiring an explicit tenant_id = :tenantId predicate, and tag each with a comment marker you can grep in CI.

DBA tip: Add a nightly canary that runs one HQL query per entity through a test tenant and inspects pg_stat_user_tables on the other tenants’ partitions afterwards — seq_scan deltas on partitions the canary should never touch mean the filter or the composite key regressed.

Verification

Capture a generated query from the Step 4 SQL logging. A repository call like findByStatus("open") for tenant acme should log:

DEBUG org.hibernate.SQL: select o1_0.tenant_id,o1_0.order_no,o1_0.total,o1_0.status
    from orders o1_0 where o1_0.tenant_id=? and o1_0.status=?
TRACE org.hibernate.orm.jdbc.bind: binding parameter (1:VARCHAR) <- [acme]
TRACE org.hibernate.orm.jdbc.bind: binding parameter (2:VARCHAR) <- [open]

The tenant_id=? predicate is present without the repository mentioning it — the enabled filter injected it. Now replay that exact statement under EXPLAIN on the shard that owns acme (pg-shard-a):

EXPLAIN (COSTS OFF)
SELECT o1_0.tenant_id, o1_0.order_no, o1_0.total, o1_0.status
FROM orders o1_0
WHERE o1_0.tenant_id = 'acme' AND o1_0.status = 'open';

Expected output — the plan touches only the acme list partition, confirming pruning survived Hibernate’s SQL generation:

Index Scan using orders_acme_tenant_id_status_idx on orders_acme o1_0
  Index Cond: ((tenant_id = 'acme'::text) AND (status = 'open'::text))

If the plan instead shows an Append node over every orders_* child, pruning failed — check that the bound parameter type matches the column type (a varchar column compared against a parameter cast to text is fine, but a mismatch such as bigint vs numeric on numeric tenant keys blocks pruning). Finally, confirm isolation end to end: with TenantContext set to initech, the same repository call must log connections checked out from the shard-b pool, which you can see in HikariCP’s hikaricp_connections_active{pool="shard-b"} metric.

Failure mode table

FAQ

Related

• ORM Integration & Partition-Aware Routing — parent guide comparing routing hooks, session scoping, and partition-key enforcement across ORMs
• SQLAlchemy Horizontal Sharding & Partition-Aware Routing — the Python counterpart: chooser callbacks and a before_execute partition-key guard
• Django ORM with PostgreSQL Partitioned Tables — managing partitioned DDL and tenant-scoped managers from Django migrations