It looks like Rails 6 is going to include first class support for configuring your app to talk to multiple databases. I’m not going to get too deep into how the feature will work in this post. I think there will be plenty of blog posts and documentation discussing that once it’s released. I want to spend some time explaining why you’d want to access multiple databases from a single web app.
Read Only Connections and Follower databases
One common use case for connecting a web applications to two databases is to have one connection to the primary read/write instance of the database and another connection to a read-only replica of the same data. This is highly useful when trying to scale a system because it allows you to move complex read queries off of the primary database.
This reduces contention between reads and writes and leads to better performance. If you have a background job that computes some information about your user, you can have that background job use the follower for the read queries.
There will always be some level of lag between the primary db and its read-only follower, so systems have to be designed with that in mind to make this strategy usable. For example, you have to build things in such a way that it doesn’t create problems if a record has been added to the database but it hasn’t yet been copied to the read-only follower. That new record has to be able to be picked up on a subsequent run of the code.
ETL, Precomputation, and Other Data Warehousing Principals
Another set of designs that can be elagently implemented by having a web app that connects to multiple databases comes from the world of data warehousing.
One of the most common designs from this family is used when complex data transformations are computed ahead of time to simplify the computation that is needed in real time.
For example, imagine that we need to do a complex calculation to determine if the logged in user has a special gold status. Something like, “have they made 5 purchases in the last 60 days, written 3 product reviews, and completed 100% of their user profile”. That isn’t something that you’d want to query for on every page load.
Computing that value ahead of time is a good solution to this type of problem. If you combine this with the previous suggestion of connecting to a read-only follower of the database, you can end up with a great solution where a background process reads from the read only follower, makes this complex calculation, and writes it to a database specifically used to store precomputed values. This makes it so that your primary read-write database isn’t burdened in anyway by this relatively expensive computation.
It works similarly for business analysis and reporting use cases.
Monolith -> Microservice Transition
Another excellent time to consider connect your app to multiple database is when you are beginning to break a large Monolithic application up into smaller components.
Let’s say for example you are planning to pull all of your user information from the main monolith into a User Microservice. One way you could approach that is to first move the data from the primary database into one that will be private to the new service. That’s going to cause you to need to refactor all the places that you are relying on database joins to the user table. That approach is no longer going to be possible once you transition to the service. You can then make a Ruby class that provides an interface to that data. This class will read the data from the new database.
Then once you spin up the User Microservice, you can simply refactor that class to read from the JSON API instead of the new database. Then, you make it so that the User Microservice is the only thing that connects to the new user database.
This is a very common, safe, and powerful way to evolve your architecture.
Wrap up
Rails 6 is introducing a feature that may make all of the designs discussed in this post easier to setup. It’s a great time to do some thinking about how your system could benefit from these sorts of designs.
I’d love to hear how you use multiple database setups in your systems. Comment below!
Top comments (6)
We currently use a second database for our event tracking.
This feature is going to be very useful. The last time I needed to setup a read replica DB in a Rails project, I remember that was achievable using a ruby gem.
Way better if it's a default of the framework.
This is indeed really exciting. I definitely think this is something we'll eventually make use of with dev.to. Great writeup.
I've thought about getting to this point with DEV since we already emphasize this at other layers of the stack in terms of edge caching. But I thought that it seemed like overkill in terms of important choices at the time.
Thoughts on whether Rails 6 will make this use case a bit more straightforward?
I don't remember needing to use a gem for multi-db connection in the past or maybe they just didn't exist 7 years ago. I took over this codebase that had 1 database connection for every client, it was bizarre. I suppose every feature is a double edge sword.
Love this, thanks! This is definitely my favorite upcoming feature :D