Multi-Tenant SaaS Architecture: Database, Schema, Row-Level

The most expensive architectural decision in a SaaS product is the one that's hardest to change later: how is tenant data separated? Every model works for the first ten customers; the problem appears at the five-hundredth customer, the first bank customer or the first "keep our data separate" request, and at that point changing the model is close to rewriting the product.

This article compares the three isolation models — database per tenant, schema per tenant and shared tables (row-level, tenant_id) — on isolation, cost and operations; then covers the decision criteria, carrying the tenant context with Spring Boot and Hibernate, PostgreSQL row-level security (RLS) as a second line of defense, the cross-cutting concerns (noisy neighbors, caching, batch jobs, backups) and the mistakes we see in the field.

The three models

Decision criteria

• Tenant count and distribution: A hundred large customers and ten thousand small ones are different models. In most B2B SaaS the distribution is skewed: a few giants, many small ones.

• Regulation and contracts: Some customers demand "our data in a separate database" and pay for it. If that demand isn't the norm, making the whole product a silo is unnecessary cost.

• Operational capacity: A three-person team can't manage migrations across two thousand databases; the silo model demands automation.

• Customization: Many tenant-specific fields and business rules point to schema or silo; few point to pool plus JSON columns.

In practice the most robust answer is the hybrid: shared tables (pool) by default, a separate database (silo) for large or regulated customers. Its precondition is that the application abstracted the tenant context and data access from the start; a tenant that can be moved from pool to silo is proof the design is right.

The tenant context: from identity to query

The tenant id is never taken from a value the client supplies (URL, body); it comes from the verified identity: the tenant_id claim in the JWT, or the subdomain resolved to a tenant on the server side. The context is established at the start of the request, carried through every layer and cleared at the end.

public final class TenantContext {
    private static final ThreadLocal<String> TENANT = new ThreadLocal<>();   // prefer ScopedValue with virtual threads
    public static void set(String id) { TENANT.set(id); }
    public static String current() { return Objects.requireNonNull(TENANT.get(), "no tenant context"); }
    public static void clear() { TENANT.remove(); }
}

@Component
public class TenantFilter extends OncePerRequestFilter {
    @Override
    protected void doFilterInternal(HttpServletRequest req, HttpServletResponse res, FilterChain chain) throws IOException, ServletException {
        var auth = (JwtAuthenticationToken) SecurityContextHolder.getContext().getAuthentication();
        String tenant = auth.getToken().getClaimAsString("tenant_id");                 // verified source
        TenantContext.set(tenant);
        try { chain.doFilter(req, res); } finally { TenantContext.clear(); }
    }
}

The Hibernate discriminator in shared tables

@Entity
public class Customer {
    @Id private Long id;
    @TenantId private String tenantId;        // Hibernate 6: automatic filter on every query, automatic value on every insert
    private String name;
}

@Component
public class TenantResolver implements CurrentTenantIdentifierResolver<String>, HibernatePropertiesCustomizer {
    @Override public String resolveCurrentTenantIdentifier() { return TenantContext.current(); }
    @Override public boolean validateExistingCurrentSessions() { return true; }
    @Override public void customize(Map<String, Object> props) { props.put(AvailableSettings.MULTI_TENANT_IDENTIFIER_RESOLVER, this); }
}

@TenantId adds the tenant condition to JPQL and Criteria queries automatically; but not to native SQL queries. Reporting queries, bulk updates and hand-written SQL are the most common leak points. That's why a second line of defense is built in the database.

Second line of defense: PostgreSQL RLS

ALTER TABLE customer ENABLE ROW LEVEL SECURITY;
ALTER TABLE customer FORCE ROW LEVEL SECURITY;             -- applies to the table owner too

CREATE POLICY tenant_isolation ON customer
    USING (tenant_id = current_setting('app.tenant', true))
    WITH CHECK (tenant_id = current_setting('app.tenant', true));

// When a connection is taken from the pool, the tenant is written to a session variable (Hikari + JPA: a DataSource wrapper or a Hibernate connection provider)
try (var st = connection.createStatement()) {
    st.execute("SELECT set_config('app.tenant', '" + tenant + "', false)");   // a parameterized prepared statement in real code
}

With RLS, even if the application forgets the tenant filter, the database returns only that tenant's rows. The cost is small; the gain is closing the whole class of leaks that keep you up at night. Resetting the variable (RESET) when the connection returns to the pool is mandatory.

Schema per tenant: connection routing

In the schema model, Hibernate's MultiTenantConnectionProvider runs SET search_path TO tenant_x when acquiring a connection; migrations are applied in a loop over all schemas (Flyway.configure().schemas(tenant)). To know which schema is at which version if a migration stops halfway, Flyway's schema history table is kept per schema, and a deployment isn't considered complete until every schema reaches the same version.

Cross-cutting concerns

• Noisy neighbors: One tenant's bulk import must not slow the others; per-tenant rate limits, queue quotas for batch jobs, per-tenant bulkheads on external calls.

• Caching: Every cache key starts with the tenant id (t:{tenant}:product:{id}); a custom KeyGenerator in Spring Cache. A key without a tenant is the quietest data leak.

• Scheduled jobs: A batch job iterates over the tenant list and sets the context for each iteration; parallel per tenant and time-limited so one big tenant's job doesn't delay the rest.

• Messaging: The tenant id travels in the Kafka message header; the consumer rebuilds the context from the header.

• Observability: Tenant id in logs (MDC); as a metric tag only if the tenant count stays in the hundreds, otherwise it's a cardinality problem.

• Backup and offboarding: In the pool model, a per-tenant export job is written from the start to restore or export one tenant; deleting a departing customer's data (data-protection law) uses the same job.

• Tenant configuration: Plan, feature flags, limits and branding live on the tenant record; no if (tenant.equals("acme")) in code.

The isolation test: non-negotiable

An integration test that runs on every release verifies that, in tenant A's context, tenant B's data can't be read or written through any path — REST, reporting queries, batch jobs, cache. This test must break when a new native query is added; if it doesn't, the test is incomplete. It runs against real PostgreSQL with the RLS policies via Testcontainers.

Common mistakes in the field

1. Taking the tenant id from the client: Trusting the /tenants/42/… parameter in the URL; the id must come from the verified identity.

2. Forgetting the filter in a native query: Inevitable without RLS; harmless with it.

3. Shared sequences: Invoice numbers drawn from a sequence common to all tenants reveal the others' volume to each tenant; use per-tenant counters.

4. Losing the context at async boundaries: ThreadLocal doesn't cross @Async and thread pools; context propagation (TaskDecorator) is needed.

5. No automation in the silo model: If onboarding a tenant is ten manual steps, it doesn't scale; creating, migrating and deleting a tenant must be one command.

Frequently asked questions

Can we move from pool to silo later?

If the tenant context and data access are abstracted, yes: the tenant's rows are moved to a new database and the routing table is updated. Without the abstraction, the "move" is a rewrite project.

Do we need a separate application instance per tenant?

Usually not; the application layer is shared, isolation lives in the data layer. A separate instance only when regulation requires full separation or the tenant wants its own release schedule.

Does RLS hurt performance?

The policy condition is a WHERE clause added to every query; with tenant_id as the first column of composite indexes, the impact is negligible. Without the index, what's slow isn't RLS but the query that was never filtered in the first place.

Conclusion

Tenant isolation is decided once and lived with for a long time: silo for a few large customers, pool for many small ones, hybrid for most products. Whichever you choose, three things are indispensable: a tenant context that comes from a verified source and travels through every layer, a second line of defense in the database and an isolation test that runs on every release.

To design the multi-tenant architecture of your SaaS product or move an existing product to the hybrid model, see our software design and architecture and web and e-commerce development services.