Using Multiple Databases with Active Record

Talks

Julia López

Using Multiple Databases with Active Record

by Julia López

In the presentation titled 'Using Multiple Databases with Active Record' at Rails World 2023, Senior Software Engineer Julia López from Harvest discusses the benefits and implementation of using multiple databases within Rails applications, particularly focusing on the features introduced in Rails 6.0. López emphasizes that the concept of multiple databases involves having distinct database servers rather than simply multiple schemas in a single database.

Key points covered include:

- Introduction to Multiple Databases: López explains that the main focus is on enabling Active Record to connect with multiple database servers, which was made easier with Rails 6.0.
- High Availability: The importance of maintaining operational databases to minimize downtime is highlighted, discussing how multiple databases can help manage loads and prevent major failures.
- Data Sharding: This technique allows for partitioning large databases into smaller, manageable pieces by spreading user data across multiple databases, thus improving performance and scalability.
- Drawbacks of Multiple Databases: Challenges such as the complexity of migrations and the difficulty of managing transactions and joins between different databases are noted.
- Implementation at Harvest: López elaborates on how Harvest uses multiple databases, including configuration of Active Record to facilitate writing and reading operations across primary and replica databases.
- Code Examples: Simplified YAML configuration examples are shared, showcasing how to set up primary and replica database connections and manage manual connection switching.
- Automatic Switching: This feature allows for automatic redirection of read requests to replica databases, improving load management on the primary database.
- Custom Connection Strategies: Different applications within Harvest have tailored their database connection strategies based on specific needs, demonstrating flexibility in implementation.

The presentation concludes with an invitation for questions, indicating the complexity and evolving nature of database management in Rails applications, while encouraging attendees to share feedback and engage in discussions about best practices.

00:00:15.760 Okay, thanks for having me here. I am a bit overwhelmed; this is my first conference talk ever. I don’t think I knew what I was signing up for; it was supposed to be a cute new Rails conference and not like this display. It is also overwhelming because the people who implemented this functionality are speakers at the conference, Eileen Wiel and Adam Patterson. But they say, 'Go big or go home,' so that’s what I’m doing.

00:00:34.120 I also underestimated how long it takes to prepare a talk like this and how stressful it can be. You think it’s going to be a simple copy and paste of something you already know, but no one tells you about Keynote and all the animations it has. You have to decide whether to pick one or use them all. So let’s start with a fun fact: this is my second time in Amsterdam. The first time I came was for my first Ruby conference in 2012, which was Euro. So, over ten years later, I’m back and speaking at a conference! My name is Julia Lopez, I’m from Barcelona, and I’ve been doing Rails since 2011.

00:01:06.600 What I enjoy the most is refactoring and upgrades. If there’s ever a chance to do a Rails upgrade or any kind of refactor, I’m down for that. My dream would be to have a Gemfile with no pinned versions that you can bundle update every day! It’s like a dream that is not happening. Someone at my company called me the queen of YOLO, as in something good, you know? So, that’s what I do. I work for Harvest, a time tracking tool that has been in Rails since the very beginning, which is 2006. I was just a baby in 2006; I joined in 2016. Does anybody know Harvest, apart from my colleague over here? Does anybody use Harvest? Wow, nice!

00:01:40.440 So, you know we are like Rails and Harvest. We have a few different apps, but we have a monolith, which is the Harvest app. We currently have a small engineering team of thirteen developers, plus our site reliability engineers. I would like to thank my colleagues because they were the first ones to listen to this talk and helped me a lot by providing feedback.

00:03:03.640 What can you expect from this talk? This is not a walkthrough of the Rails guides, which by the way, are excellent. I feel that real experiences are the best way of learning something; it’s more informative and, hopefully, entertaining. I hope that you can also relate to some of the things that I’ll mention. It’s going to be a high-level overview of when setting up multiple databases could make sense for you. I’m not an expert, so bear with me.

00:03:35.239 I’ll give a quick intro to the feature and show how we use it at Harvest. This implementation was done three years ago with the release of Rails 6.0. I had to go back to all the pull requests I opened three years ago, back when we were upgrading Rails at the time. If there’s something you think could be improved, just let me know later, and we can do some code reviews.

00:04:02.760 So, I hope you learn something today. First, I would like to know if anyone here is already familiar with this feature? Okay, good. So, what is multiple databases? Well, multiple means many of one. Let’s dive into the concept.

00:04:34.520 From a high level, databases can mean many, many things. We are going to focus on databases that Active Record can connect to, which is an Object-Relational Mapping (ORM) tool. When I talk about databases, I’m not referring to many schemas in the same database; I’m talking about database servers. This is the use case for Harvest: we don’t have one MySQL with many schemas; we have many MySQL processes and each one holds one schema.

00:05:42.240 I wanted to use all the tools Keynote was giving me, so I thought it would be cool to add a quote. Wikipedia defines a database server as a server that uses database applications to provide database services to other computer programs, as defined by the client-server model. I should have asked ChatGPT, maybe! But hopefully, everyone knows what MySQL is.

00:06:08.440 Now, let’s consider when setting up multiple databases makes sense and what circumstances warrant this approach. I’m going to throw out some buzzwords you like to hear at conferences: high availability. So, high availability means the ability of a system to remain operational and accessible for users with minimal downtime. It doesn’t refer only to databases; it concerns how many servers you have, how many clusters, and how many locations.

00:06:39.600 However, we will focus on databases here, as they are critical components of your application. You might want to think about scaling your databases before scaling servers, as the databases may not be able to take the load if you only have one process, like a single database server running. This is not the same as having backups, but we will see the difference.

00:08:14.919 Imagine that you have many processes hitting just one database. If that database struggles, what happens if it goes offline for any reason? It could be anything; someone might have cut the cables, or you could be insecure in the cloud. The cloud is also a server located somewhere on this planet.

00:08:38.880 If that happens, it might not even be your fault. You may have backups and be able to restore things, but that can take time because you need to spin up a new server or contact your cloud provider. Users will be upset, the support team will be overwhelmed, and you will be very stressed. We don’t want that kind of situation!

00:10:03.720 Now, let’s envision a better scenario. You could have many database servers running simultaneously! You could have some processes hitting a primary database that handles all the writes, while another database contains the same information through replication to keep the data in sync. If the primary database goes down, some requests may fail, but it isn’t a problem.

00:10:40.720 With this setup, you also need to implement a failover process that directs all requests to the replica database, giving your team the time to recover the primary database without causing major disruptions. Your database may struggle, and requests might be a bit slower, but that’s a comfortable space for us as developers.

00:11:09.440 That was a broad overview of what having multiple databases means. You can complicate it as much as you want, but another reason for multiple databases lies in data sharding. When you partition large databases into smaller, manageable pieces called shards, you avoid keeping everything in one table with millions of rows.

00:11:51.760 For instance, assume your application has a hundred million users, and you don’t want that one table to get unwieldy. You could have their information partitioned across multiple databases. However, the process then needs to know where to go to find the data, and you will need to implement a discriminator strategy.

00:12:35.240 This is horizontal sharding, which Rails supports. There’s also the vertical strategy, which is about dividing users’ data in various libraries. You can find more information online about that. Other reasons could be security and compliance; perhaps your data must reside in very secure places or different geographical locations.

00:12:53.759 You can insert any other reason that makes your life as complicated as you want! Rails will assist you in managing this setup, but you should remember what they say about early optimization: it can lead to complications.

00:13:35.760 Keep in mind the drawbacks of having data stored in multiple databases. For instance, you cannot join information easily, and transactions become more complicated, if possible at all, depending on the tools you use. Managing migrations and replicas can be challenging, too.

00:14:17.640 As for how you handle high availability, I’ve already explained how we use this setup at Harvest, but I know very little about the backend processes involved. This is handled by my smart colleagues in the site reliability team. We have many tools for database management, and I promise that real-life setups can get complicated.

00:14:54.760 Let’s simplify this: imagine a Rails application that connects to a primary database where you can read and write while having a replica that only handles reading. The replication could have a one-second lag, which is okay. This feature was introduced in Rails 6.0, and four years later, it still holds relevance.

00:15:37.600 Harvest upgraded to Rails 6.0 in June 2020, and we’re still on track with that upgrade. I like to keep things visually engaging, so I’ve included a jellyfish image; it was a cute stock photo I found.

00:16:11.839 The features that Rails provides include the ability to have multiple writer databases and one replica, as well as manual connection switching and automatic switching between the writer and the replica. We’ll explore that in-depth shortly along with horizontal sharding and automatic shard switching, which were introduced in Rails 6.1.

00:16:46.160 Let’s discuss what it doesn’t do for you first. If you have more than one database, Rails won’t load balance between the replicas; you must handle that yourself. It won’t decide which replica to use based on load; the replication doesn’t do that either. You’ll need to use additional tools to manage all that functionality.

00:17:48.440 Before Rails 6.0, multiple databases were handled via a gem. We owe a lot to that gem, but nothing compares to the feeling of removing a gem in favor of native support. No bundle update required; this was much better! Although we had to drop the gem when migrating to Rails 6.0, it was an educational experience to look into the source code and understand how Active Record operated.

00:18:18.920 Okay, let me show you the code; I know you came for that. Here’s our database YAML configuration, simplified. As you can see, we use the MySQL adapter because we use Percona—Percona’s implementation of MySQL. I’m quite proud of this one; we can have emojis in our database!

00:18:58.160 Migrating all tables took a while. While I don’t want to delve too deep into this aspect, I wanted to share an important configuration tip I learned when preparing this talk: your Active Record size must be equal to or greater than your process-level concurrency.

00:19:45.160 We use Sidekiq, so we have that configuration set up properly. The reason for this is that we have different database servers accommodating the same schema. The names of the schemas remain unchanged. We inherit all configuration from the production default, only changing the host; for the primary, it’s just one host, while for the replica, it connects to a proxy that balances the connections evenly.

00:20:23.600 With the database YAML configured, you can set up your application records by specifying which is the primary (writing) database and which is the replica (reading) database. It’s as straightforward as that. Now, how do you manage manual connection switching?

00:21:09.760 You can use blocks to manage connections, allowing you to direct all connections within that block to the reading database. If you have different abstract application records—like in the example of an animals database—you can specify that only models inheriting from the application record will connect to the reading database. This is particularly useful for preventing writes on the replica.

00:22:32.640 Ideally, in production, you’ll set up your replicas so they don’t take writes, which will cause a failure at the database level and not Active Record. For testing environments with incomplete setups, the prevent writes feature will cause an exception in your tests when you try to perform an insert, update, or destroy, helping you identify unexpected behavior.

00:23:16.080 Now, regarding automatic switching, this feature significantly reduces the load on your primary database. In our case, a substantial amount of our requests are reads from the database. We can simply redirect those reads to the replica databases instead of placing the entire load onto the primary database.

00:23:51.240 Let’s consider a quote that sums this up: it states that what Rails does is relatively primitive, and it’s on purpose. Automatic switching is basic and doesn’t do a whole lot. It primarily directs GET and HEAD requests to the replicas, with other requests going to the writer. It follows Rails conventions.

00:24:42.000 In a big enough web app or API scenario, you might find GET endpoints doing some inserts or similar actions that complicate this logic. It’s important to be aware of that. We have also experienced cases where search or index requests have too many parameters and hit URL character limits. However, it’s not enforced in the RFC, which is interesting!

00:25:12.240 Another key feature of automatic connection switching is that it prevents reading your own writes. That means if there’s a replication delay, the user may not see the data they just saved. However, the automatic connection switch ensures users don’t receive stale data.

00:25:46.760 This connection switch is primitive but very extensible. So, you can create an initializer or add everything to your application config file. The connection switching process occurs at the middleware level before it even reaches your controllers.

00:26:38.480 In this middleware, you can select which database to use based on the resolver you set up; you can decide beyond just read and write. If you configure the resolver wisely, you can customize the delay it takes for replication. The default resolver decides whether to read from the primary or the writer based on the user context.

00:27:24.480 For our applications at Harvest, we have three Rails apps. One is an API-only service, the second is a web app with an internal API, and the third is our main Harvest monolith. For each of these applications, we chose different strategies for configuring our automatic switch.

00:28:11.039 For Forecast, our API-only Rails app, we could not use sessions in a traditional manner, so we created a context that extends from the session context. This allows us to maintain a connection switch based on our header request.

00:28:59.439 For Harvest ID, our web app, we took a simpler approach. We always configure requests to use the writer connection for all authentication-related API calls. However, we allow other queries to use the automatic switching to take advantage of the underlying architecture without complicating the logic.

00:29:38.240 Our main Harvest application utilizes both internal and external APIs, with several different authentication styles. For this reason, we decided to create a custom concern for our application controller that conditionally directs traffic to either the writer or the replica when needed.

00:30:09.800 Thank you all for attending! If you’ve paid attention, I mentioned we released our implementation back in 2020. However, why am I showing you a graph from 2023? We experienced an issue after changing the order of includes in the application controller. Fortunately, we resolved it, but it took us a few days before we realized it broke our connections.

00:30:20.540 Thank you for your time, and I'd be happy to take any questions!

Rails World 2023