In this post, we will share our experience implementing a hybrid and dynamic database multitenancy system for one of the projects we’re involved. If you’re unfamiliar with any of these concepts, don’t worry—we’ll cover them in detail.
Understanding Database Multitenancy
To better comprehend database multitenancy, let’s first take a look at single tenancy, its counterpart.
Single tenancy
Single tenancy is a software architecture in which each customer (tenant) has their separate instance of the software, along with its dedicated database. This approach provides isolation between customers but comes at the cost of higher resource consumption and maintenance overhead.
Multitenancy
Multitenancy, on the other hand, is an architecture where multiple customers (tenants) share the same software instance with their data logically separated, which could involve one or more databases. This approach is more efficient in terms of resource utilization and easier to maintain, but it may have potential security and performance risks.
Pros and Cons
Pros of multitenancy include cost-effectiveness, efficient resource utilization, easier maintenance, and scalability. Cons of multitenancy involve potential security risks and performance issues due to shared resources.
Hybrid Multitenancy: Granularity Levels
Different levels of granularity can be used to segregate tenant data in the data domain. It’s essential to remember that the same application instance is always shared. These levels include:
Database-level
Each tenant has a dedicated database. This approach offers the highest security and easier backups but comes with higher costs: more expensive, more developer time to onboard a new customer and higher resource consumption.
Schema-level
Tenants share the same database but have their own dedicated PostgreSQL schema. This level provides some degree of data isolation, but managing backups can be challenging.
Row-level
Tenants share the same database and schema, but their data is separated at the row level using a tenant identifier. This is the most cost-effective approach, but it may result in performance issues and weaker data isolation.
Hybrid
Allows the possibility to have either an exclusive database or a shared database, depending on the specific requirements of each tenant. This flexibility enables developers to strike a balance between cost, performance, and data isolation as needed.
Dynamic Multitenancy: Programmer Intervention and Runtime
Dynamic multitenancy allows for the addition of tenants to the system at runtime without the need for manual intervention by the programmer. In contrast, non-dynamic solutions require programmers to set up new tenants manually. For instance, when using Ruby on Rails 6.0 sharding, the programmer must modify the database.yml file and deploy the updated configuration for each new tenant.
Implementing dynamic multitenancy enables developers to automate the process of adding new tenants to the application, reducing overhead and potential errors associated with manual setup. This approach allows for faster onboarding of new customers and improves the overall scalability of the system, although it may not make sense if the system has only a few clients with a low rate of new client additions.
Multitenancy with Ruby on Rails and PostgreSQL: Popular Solutions
Two of the most popular solutions for implementing multitenancy in Rails applications using PostgreSQL are the Apartment gem and Ruby on Rails 6.0 built-in sharding. An honorable mention is the act_as_tenant gem, which provides row-level multitenancy.
Apartment Gem
The most popular multitenancy gem for Rails is called Apartment. It is well-built and feature-rich, but its official maintenance stopped three years ago, and it doesn’t officially support Rails 6.0. However, there is a fork that continues to maintain the gem. While it supports schema-level and database-level multitenancy, it does not allow both simultaneously, meaning it is not a “hybrid” solution. Nevertheless, the Apartment gem is dynamic.
Ruby on Rails 6.0 Built-in Sharding
Ruby on Rails 6.0 built-in sharding supports a hybrid model, as it requires defining the database connection, which includes information about both the database and the schema. However, it is not dynamic, as you must define the tenants in the database.yml file and deploy this configuration to add new tenants.
In summary, when choosing between the Apartment gem and Rails 6.0 built-in sharding for implementing multitenancy in Rails applications using PostgreSQL, consider the desired level of granularity and whether you need a dynamic or static solution.
Hybrid and Dynamic Database Multitenancy with Ruby on Rails and PostgreSQL: A practical solution
To implement a hybrid and dynamic multitenancy solution using Ruby on Rails and PostgreSQL, we build upon Ruby on Rails 6.0 sharding, which already provides a hybrid foundation. The challenge lies in making it dynamic by replacing the static configuration in the database.yml file with a runtime solution.
Our approach involves utilizing the public schema, where we store the database connection configurations for each tenant. When a worker starts, it loads these configurations from the database. With minimal effort, Rails creates a connection pool for each tenant, from which we can retrieve connections. Additionally, we need to hot restart the workers each time a new tenant is added, ensuring that it is accessible to all workers.
By adopting this practical solution, we have successfully integrated hybrid and dynamic database multitenancy with Ruby on Rails and PostgreSQL. The result is a flexible, efficient, and scalable architecture that caters to the unique requirements of each tenant while maximizing resource utilization.
In a future post, we will implement a hybrid and dynamic multitenancy solution built upon Ruby on Rails 6.0 sharing, stay tuned!