Have you ever wondered how large SaaS applications are built? Do they use a single database for all clients or multiple databases?
Let’s say you have a SaaS-based application that has data stored in a database and multiple customers, also known as tenants or clients, using the same application. Each tenant has their own set of data and customers.
How do you develop and manage an application like this?
The relationship between the tenants, application, and database is referred to as the multi-tenancy model. There are two main types of multi-tenancy models that are used for SaaS-based applications: Database and Table.
In Database Multi-tenancy, your application connects to a main database and has some logic for choosing which schema or database to connect to when serving a particular tenant.
On the other hand, Table multi-tenancy allows a single database to be used for all tenants and their data. However, it requires adding an associated tenant id in every table associated with a given tenant. The application doesn’t need to worry about which schema or database it is connecting to. Instead, the business logic queries for the tenant id within associated tables.
In this article, we’ll discuss the advantages and disadvantages of both database architectures.
Table multi-tenancy: Single Database for all clients
Throughout the years, hundreds of companies have opted to use a single database as a replacement for a range of top-performing database technologies. It has been discovered that in the vast majority of cases, separate database technology is not necessary to address the limitations of the primary database. Instead, all that is needed is for the main database to possess a few additional features and to have an overall improvement in speed.
Advantages of using a Single Database for all clients:
- Lower cost
- Simplified database management and administration
- Flexible data access and easier-to-add new features
- Easier to collect statistics on usage
Disadvantages of using a Single Database for all clients:
- All applications will be impacted if the database is lost or undergoing maintenance
- Slower connection speed
- Higher security risks
- More space is required for overhead
- More challenging application development and data management
- Less fault tolerant
- Harder to move a customer to a separate set of servers if one customer is using much of the server resources
- Its harder to remove non-existing clients records
Database Multi-tenancy: Multiple Databases
Having a separate database for each customer can indeed improve performance by reducing the load on a single database and allowing for more customization and fine-tuning of resources for each customer. This database can either be located on a separate server or simply reside as a separate database within a single database server. The ability to choose the preferred method provides versatility, making database multi-tenancy a desirable option.
This can also make it easier to move a customer to a separate set of servers if needed. Additionally, having only a single customer’s data in a database can increase fault tolerance by reducing the risk of data loss or corruption in the event of a failure. However, it is important to consider the added complexity and cost of maintaining separate databases for each customer.
Advantages of using a separate database for each client:
- Improved performance
- Easier data access for clients
- Enhanced security and data isolation
- Efficient database management and administration
- Quick implementation and good performance
- Easy to develop new features and modules
- Higher resistance against data crashes
Disadvantages of using a separate database for each client:
- More challenging to maintain multiple databases
- Difficult to backup data for each database
- Additional cost for database space
Personal Opinion
As a seasoned engineer with over decade years of experience in building enterprise-level applications, I can tell you that the choice of whether to use one database for all clients or one database for each client in building large SaaS applications is a crucial decision that requires careful consideration. Both options have their own advantages and disadvantages, and the choice depends on various factors such as licensing, hardware, hosting costs, security, scalability, and performance. Ultimately, the decision should be based on the specific requirements and goals of the SaaS application.
I have developed a few applications using Laravel and the Spatie Multi-tenancy package, which have worked well. The only challenge I have faced is setting it up in other developers’ development environments, as it requires the creation of subdomains using virtual hosts, such as client1.site.com, client2.site.com, client1.localhost, client2.localhost, etc.
Maintenance has been straightforward and the performance is excellent. However, I have not yet reached 10,000 tenants, which is my main concern as each tenant has their own database and this could result in a nightmare to manage multiple databases or schemas. Fortunately, Laravel has a fantastic migration script that makes the process very easy.
One of the key benefits of using Database Multi-tenancy is the ability to isolate, debug, and test data and changes without impacting tenants, especially in projects that involve messy data such as school, government, and health records.
In conclusion
The decision on which database model to using depends on specific requirements and considerations. Both options have their pros and cons, and it’s important to weigh them before making a final decision. However, I would recommend using multiple databases within a single server by using database schema due to the level of scalability it offers and the isolation of data.
