As software developers, we often face the challenge of designing databases that can efficiently support multi-tenant architecture. A multi tenant architecture is a software or service that runs on a single instance but serves multiple organizations (tenants), each with its unique user base and feature set. Designing a database that preserves the data integrity, security, and performance for all tenants within a single database instance requires careful consideration of the database structure.

There are several approaches to designing a database for a multi-tenant architecture, each with its advantages and trade-offs. Here, we explore the four most prominent ones:

1. Single Database, Shared Schema by All Tenants

In this approach, all tenants share the same database and schema. The data of each tenant is distinguished by a tenant identifier column in each table.

Advantages:

• Simplicity: Only one database to manage and maintain.
• Cost-Efficient: Lower operational costs since all tenants use the same database resources.
• Resource Utilization: Efficient use of database resources, as all tenants share the same instance.

Disadvantages:

• Complexity in Queries: All queries must include a tenant identifier filter, adding complexity to SQL statements.
• Security Risks: Higher risk of accidental data leaks between tenants if proper isolation is not enforced.
• Scaling Challenges: As the number of tenants increases, the database can become a bottleneck.

2. Single Database, Separate Schema per Tenant

In this approach, all tenants share the same database, but each tenant has its own schema. This means each tenant's data is stored in separate tables.

Advantages:

• Isolation: Better data isolation compared to a shared schema, reducing the risk of data leaks.
• Customization: Easier to customize schema for specific tenant requirements.
• Scalability: Easier to scale individual tenant schema if needed.

Disadvantages:

• Management Overhead: More complex database management due to multiple schema.
• Resource Usage: Increased resource usage as the number of schema grows.
• Deployment Complexity: More complex deployment process, as changes need to be applied to multiple schema.

3. Separate Database Instance per Tenant

In this approach, each tenant has its own separate database instance. This provides the highest level of isolation and customization.

Advantages:

• Isolation: Complete data and resource isolation between tenants, eliminating the risk of data leaks.
• Performance: Dedicated resources per tenant, which can improve performance.
• Customization: Full flexibility to customize the database structure for each tenant.

Disadvantages:

• Cost: Higher operational costs due to managing multiple database instances.
• Management Complexity: Increased complexity in managing backups, updates, and monitoring across multiple databases.
• Resource Utilization: Potentially inefficient use of resources if tenant databases are underutilized.

4. Hybrid Approach

Some companies employ a hybrid approach, combining elements of shared schema, separate schema, and separate databases to best meet the needs of different tenant sizes and requirements.

Advantages:

• Flexibility: Can tailor the database structure to the specific needs of different tenants, optimizing performance and cost.
• Scalability: Can scale efficiently by using shared schema for smaller tenants and dedicated resources for larger tenants.
• Resource Optimization: Balances resource utilization by mixing shared and isolated environments based on tenant needs.

Disadvantages:

• Complexity: Managing multiple types of database structures increases administrative complexity.
• Consistency Challenges: Ensuring consistent application behavior and performance across different database structures can be challenging.
• Deployment Complexity: More complex deployment and maintenance processes due to varied database setups.

Real-World Example:

• Atlassian JIRA: JIRA uses a hybrid approach to serve its diverse customer base. For smaller tenants, JIRA employs shared schema within a single database to maximize resource efficiency and minimize costs. For larger enterprise customers, JIRA provides separate schema or dedicated database instances to ensure necessary isolation, performance, and customization. This flexibility allows JIRA to meet the specific needs of each tenant while optimizing overall system performance and resource utilization, despite the added complexity in management and deployment.

Handling Custom Fields with JSONB in PostgreSQL

In multi-tenant architecture, managing customizable fields for each tenant is crucial for flexibility and scalability. PostgreSQL's JSONB datatype provides a robust solution for storing dynamic, tenant-specific data within the same table while maintaining a core set of default columns. This method allows each tenant to have unique fields without altering the database schema.

Example Project: Customizable Department Fields

In our project, we use PostgreSQL to implement a multi-tenant application where each tenant can define custom fields for their departments. These custom fields are stored in a JSONB column, and their configurations are managed in a separate configuration table.

Schema Design

Department Configuration Table:

• Columns: tenant_id, config_id, status, and fields_config.
• This table holds the configuration for custom fields, allowing each tenant to define their specific requirements.

We could also have implemented this by storing each fields config in sperate rows and all fields of a tenant being identified by tenant_id.

But we went with this JSONB approach because in this approach we don't need to query multiple rows to get the tables config, we find this more efficient for storing the tables config

{
  "fields_config": [
{
      "name": "department_name",
      "data_type": "STRING",
      "display_name": "Department name",
      "default_value": null,
      "is_mandatory": true
    },
    {
      "name": "dep_budget",
      "data_type": "DECIMAL",
      "display_name": "Budget",
      "default_value": null,
      "is_mandatory": true
    },
    {
      "name": "dep_location",
      "data_type": "STRING",
      "display_name": "Location",
      "default_value": null,
      "is_mandatory": true
    }
  ]
}

Department Table:

• Columns: tenant_id, department_id, department_name, and custom_fields (JSONB datatype).
• The tenant_id column ensures data segregation for each tenant.
• The custom_fields column stores JSON data specific to each tenant.

{
  "custom_fields": [
{
      "name": "department_name",
      "value": "Penguin Resources (PR)"
    },
    {
      "name": "dep_budget",
      "value": "5000000"
    },
    {
      "name": "dep_location",
      "value": "Antarctica"
    }
  ]
}

Benefits of Using JSONB for Custom Fields

1. Flexibility: The JSONB column can store any structure, allowing tenants to customize fields without requiring schema changes.
2. Efficiency: JSONB provides indexing and query capabilities, ensuring efficient data retrieval.
3. Simplified Schema Management: A single table structure can accommodate various tenant-specific data requirements, reducing the need for multiple schema versions.

How It Works in Practice

• Default Columns: Each department has default columns (tenant_id, department_id, department_name) that are consistent across all tenants.
• Custom Fields: The custom_fields column stores tenant-specific data in JSONB format, allowing for dynamic fields unique to each tenant.
• Configuration Management: The department_config_tbl table defines the structure and constraints of the custom fields for each tenant, ensuring data consistency and validation.

Real-World Application

By utilizing PostgreSQL's JSONB datatype, our multitenant application can efficiently manage dynamic, tenant-specific data within a unified database structure. This approach balances flexibility and performance, enabling each tenant to customize their data without compromising the integrity or scalability of the system.

Example Scenario:

• Tenant A may define a custom field budget for their departments, stored in the JSONB column as { "budget": 50000 }.
• Tenant B might have a different custom field location, stored as { "location": "New York" }.

Both tenants can store their unique data within the same department table, leveraging the power of JSONB to handle varying data structures seamlessly.

GitHub Repository Example:-