Back when I was developing a manufacturing ERP, the need arose to offer the same software to multiple customers. This inevitably brought multi-tenant architecture to the table. Although it seemed like a simple idea at first, making the right trade-offs was critical for both the technical and commercial success of the project. In this post, I will share the challenges I faced and the decisions I made while building a multi-tenant architecture in ERP systems, complete with concrete examples.
Why Do We Need Multi-Tenant Architecture?
Enterprise resource planning (ERP) systems are typically complex software suites where businesses manage their core operations. Monolithic structures developed specifically for a single customer can create maintenance and update challenges over time. Especially for service providers, setting up a separate server and database for each customer is a costly and operationally difficult scenario to manage. This is exactly where multi-tenant architecture comes into play.
By opening a single software instance to the access of multiple customers (tenants), we aim to use resources efficiently. This reduces both software development and maintenance costs and eases the operational burden. For example, while developing an ERP for a manufacturing company, if we want to serve 5 different customers, instead of dealing with a separate deployment process for each, we can serve them through a single system. This provides a huge advantage, especially in the early stages or for projects that need to scale.
Multi-Tenancy Approaches at the Database Level
One of the most fundamental and critical decisions in a multi-tenant architecture is what strategy to follow at the database layer. We generally encounter three main approaches:
- Single Database, Schema per Tenant: All tenants’ data is stored within a single database, but using a separate schema for each tenant. This provides a logical separation.
- Single Database, Single Schema (Shared): All tenants’ data is kept in the same database and the same schema. Data separation is handled with a
tenant_idcolumn in each table. - Separate Databases: A completely separate database instance is created for each tenant.
Each of these approaches has its own advantages and disadvantages.
Separate Databases: Isolated but Costly
In this approach, a completely independent database is created for each customer. This is the strongest option in terms of data isolation. The risk of one tenant’s data leaking into another’s is minimal. Moreover, a performance issue or error in one tenant’s database does not directly affect the other tenants. In a production ERP, this approach can be preferred for financial data that requires high security.
However, the biggest disadvantage of this approach is cost. Managing a separate database server, or at least a database instance, for each customer significantly increases resource consumption and operational costs. For example, when serving 100 customers, managing a separate PostgreSQL instance for each will produce a hefty bill in terms of both hardware and licensing costs (if a commercial database is used). In addition, database maintenance, backup, and update operations all have to be performed separately for each database, which increases operational complexity.
Single Database, Single Schema (Shared): Resource Efficiency and Risk
In this approach, all tenants’ data is stored in tables within the same database and the same schema. Data separation is provided by a tenant_id column in each table. For example, an orders table has a tenant_id field indicating which customer each row belongs to. This is the most efficient method in terms of resource usage. A single database instance can serve multiple customers. This provides a cost advantage, especially for early-stage SaaS (Software as a Service) products.
However, the biggest risk of this approach is weak data isolation. If sufficient security measures aren’t taken at the application layer, it becomes possible for one tenant to access another’s data. When a faulty WHERE tenant_id = X filter is forgotten, all customers’ data can be listed. This situation can end in disaster, especially in an ERP system dealing with financial data. Additionally, one tenant’s heavy database usage can negatively affect the performance of all other tenants. In a production ERP, one customer’s intensive reporting request can slow down other customers’ order entries.
-- Example: Querying Data in a Shared Schema
SELECT
order_id,
product_name,
quantity,
order_date
FROM
orders
WHERE
tenant_id = 'customer-abc-123'; -- You must always filter by tenant ID!In this approach, tenant_id filtering is critically important. A query accidentally run without this filter can cause all data to be exposed. For this reason, strict controls and tests must be implemented at the application layer.
Single Database, Different Schemas: A Balanced Approach
This approach is, in a sense, the middle ground between the two methods above. All tenants share a single database instance, but each tenant’s data is kept in its own dedicated schema. For example, while tables like orders and products exist under the customer_abc schema, tables with the same names exist under the customer_xyz schema. This both makes efficient use of resources and strengthens data isolation to some degree.
This method is better than the shared schema in terms of data security, because queries are made directly to the relevant tenant’s schema, and there is less need for additional filtering like tenant_id. However, database management complexity increases. The maintenance, backup, and update of each schema must be planned separately. In addition, managing multiple schemas can make some database operations (for example, performing a bulk update for all tenants) more complex.
Tenant Management at the Application Layer
Whatever the database strategy may be, correctly identifying and managing the tenant at the application layer is vital. When an HTTP request comes in, we need to know which tenant it belongs to. This information is usually taken from the request’s headers (for example, a custom header like X-Tenant-ID), from subdomains (for example, customer-abc.erp.com), or from user session information.
This tenant identity is then used to select the correct database connection or schema to use in database queries. If the shared schema approach is adopted, the incoming tenant identity must be automatically added to every query. This is usually done through a middleware or request interceptor. For example, if we are using FastAPI, we can take the tenant information from the request with a Depends function and pass it to our ORM (Object-Relational Mapper).
# FastAPI example: Getting tenant information from the request
from fastapi import FastAPI, Request, Depends
app = FastAPI()
async def get_current_tenant(request: Request) -> str:
tenant_id = request.headers.get("X-Tenant-ID")
if not tenant_id:
raise HTTPException(status_code=400, detail="X-Tenant-ID header is missing")
# Here we can verify the validity of the tenant ID
return tenant_id
@app.get("/orders")
async def read_orders(current_tenant: str = Depends(get_current_tenant)):
# Run the database query with current_tenant
orders = await db.fetch_all(f"SELECT * FROM orders WHERE tenant_id = '{current_tenant}'")
return ordersThis code snippet checks the X-Tenant-ID header of every incoming request and does not allow requests that arrive without this header. The tenant identity that is obtained is then used in database operations. This is one of the fundamental steps for ensuring tenant isolation at the application layer.
Scalability and Performance Trade-offs
When designing a multi-tenant architecture, scalability and performance should always be priority concerns. Especially in data-intensive ERP systems, one tenant’s performance must not affect the others.
In shared database or schema approaches, all tenants share the same database resources. This can cause others to slow down when one tenant consumes excessive resources. For example, when one customer runs a complex report that processes a large dataset, system-wide performance drops can occur. To manage situations like this, resource limiting and monitoring mechanisms are critically important.
The separate database approach naturally offers better scalability and performance isolation. However, in this case each database instance has to be scaled on its own. When you have thousands of tenants, scaling each one separately can turn into an operational nightmare. For this reason, the scaling strategy must be carefully planned according to the type of workload and cost constraints.
Security and Compliance
In multi-tenant systems, security is one of the most sensitive topics. Data breaches can lead not only to financial losses but also to reputational damage and legal problems. Since ERP systems generally hold sensitive financial, manufacturing, and customer data, security measures must be top-tier.
Data isolation is the cornerstone of the security strategy. The database approaches mentioned above provide this isolation at different levels. While separate databases offer the highest isolation, the shared schema offers the lowest. The correctness of the controls at the application layer determines how effective this isolation will be.
In addition, compliance with data privacy regulations such as GDPR and CCPA is also an important factor for multi-tenant architectures. Matters such as where each tenant’s data is stored, how it is processed, and who can access it need to be clearly defined. This becomes even more complex, especially when serving customers in different geographic regions.
Cost Analysis and Trade-offs
Finally, we shouldn’t ignore the impact of all these decisions on cost.
- Separate Databases: High initial and operating cost, but the best isolation and potentially the best performance.
- Single Database, Single Schema (Shared): Lowest initial and operating cost, but the lowest isolation and performance risks.
- Single Database, Different Schemas: A good balance between cost and isolation.
When developing a production ERP, we might initially consider starting with a shared schema in order to keep costs low. However, as the customer base grows and data sensitivity increases, it would be wise to plan a transition to more secure and isolated solutions, such as schema-based separation or even separate databases for some critical tenants. These kinds of transitions become possible thanks to the flexibility and modularity of the architecture.
For example, starting out with a shared schema while serving 5 small customers, then over time reaching 50 customers and having one of these customers be a large enterprise company, it can make sense to move that company to a separate database. This is a strategic decision and is shaped according to the growth stage of the project. Decisions like these must be aligned not only with technical considerations but also with business goals.