Search code examples
javaspringpostgresqlspring-boothibernate

Multi-Tenant Spring boot web application

I'm new to developing multi-tenant web applications and have explored different approaches:

  1. Separate database for each tenant
  2. Same database with separate schemas per tenant
  3. Same database and schema, using a tenant identifier in tables

Option 2 seems ideal as it enables multi-tenancy without modifying all tables while providing isolation and requiring fewer connection pools compared to a separate database approach. However, a potential drawback is that database migrations may take longer as the number of tenants increases. Are there any other concerns?

I'm using Spring Boot and want to achieve the following, it is possible?:

  1. When a new tenant registers, the application should create a new schema and its associated tables dynamically without requiring a restart.
  2. Only the tenant table should exist in the public schema.

The public schema will contain a tenant table:

tenant
id
schema

Would this approach work efficiently in Spring Boot, and are there better ways to handle schema-based multi-tenancy or could you recommend other two options?

Additionally, a user_to_tenant table will map users to tenants, enabling tenant identification during login:

user_to_tenant
id
tenant_id
user_id [unique]

Note:

  1. You can assume the app is not public—only paid users can create accounts, so the number of schemas will remain limited.
Solution

  • It depends how often tenants are created, how much data is managed by a single tenant and what is the data isolation requirements. If we are speaking about several tenants, I would suggest to deploy multiple spring application, single application with an own database (or own schema) for every tenant.

    If you insist on using a single instance, I would recommend the option 3. Spring is not really designed for options 1 and 2. Sure, it will be somehow possible, but the price will be too high.

    If you have dozens or hundreds of tenants, option 3 is the only realistic option.

    If security is one of your requirements, take a look on Row-Level Security (RLS) feature. It allows you to define policies that control access to individual rows based on conditions. It is supported by bigger database vendors including Postgres.