Our Path from Monolith to Microservices
How our Lack of SQL Joins and Foreign Keys Aided Us in Our Journey
“We don’t have foreign keys in our databases at Hootsuite.”
My friend practically choked on his coffee. I gently patted him on his back as he bent over and coughed, his eyes nearly popping out of his head. I felt a strange surge of excitement at his reaction, much like the kind of excitement one gets upon telling a child Santa Claus is not real. Not that I would ever do that, of course.
“We also don’t do any SQL joins,” I said.
“What?!” he spat, “Okay sure fine yeah okay but why would you not want to enforce correct data?! This is so absurd!”
He gripped his head and looked at me, fear and confusion in his eyes (and a bit of coffee on his shirt, unfortunately). After believing something for so many years, how could anyone come along and say anything otherwise? How dare anyone deny the truth? Unless, maybe.. The truth has been a lie all along?
“Are you going to tell me why?”
“Nah, you’re gonna have to wait for my blog post.”
A Quick Preface: Monoliths and MicroservicesA monolith is a single application where all the guts and the logic live in a single place. For web services, the guts of a monolith usually reside in a single server-side application. A microservice, in contrast, is merely a component of the main application with its own business case. This component can communicate with other components, and it has its own datastore which it provides a consistent interface to through APIs. In other words, the monolith is its own entity.
A monolith is usually preferable if your project is small. The overhead of using a microservice architecture is often not worth it, as it is quite a bit more complex than a monolith. In contrast, a microservice architecture is better for larger projects. To name a few benefits, microservices are independently deployable, they are testable as individual units, they are independently scalable, your application will be less coupled, and many more things which I will not cover in this post.
At Hootsuite, we started as a monolith and transitioned into a microservice architecture. You can read more about our process here. In this post, I will focus on how the architectural decisions we made early on regarding the database have provided us with a very smooth monolith-to-microservices transition.
The Missing Link: Foreign Keys and SQL JoinsForeign keys are constraints in the database which prevent incorrect states of data when manipulating records. Therefore, one of the main reasons for using foreign keys in a database is to maintain data integrity. For example, if the Earth cannot exist without the existence of the Sun, then the deletion of the Sun will cascade and delete the Earth. In that sense, the foreign key serves as a security blanket: a layer between the application and the database which verifies that relationships stay consistent and correct.
On the other hand, SQL joins are used to combine and retrieve data from two different tables based on similarities. For example, if you had a table called Humans and a table called Animals and each animal has an entry for its Human owner, to find all humans who own pets using a SQL join you’d do something like this:
from HUMANS human
join ANIMALS animal
on human.ID = animal.OWNER_ID;
Why Not Have Foreign Keys and Joins in a Monolith?So if foreign keys and SQL joins are so useful, why did we decide not to have them in our database? The main reason is MSS, which stands for maintenance, scalability, and simplicity of the database.
MaintenanceAs with most companies in their early stages, our team was small and we had no database admin. In other words, we had no one to monitor the performance and security of our database. Without a deep understanding of these things we were hesitant to overwhelm our database with constraints. In addition, a lot of us have worked in places where way too much logic was put into the database itself, which caused more work to be put on the database instead of in the code and made the database very hard to reason about. This conveniently brings us to our next point:
SimplicityLooking up groups of things (i.e. lists) is very complicated because SQL joins can get extremely hairy. A simple Google search for “complicated SQL join” will show you plenty of examples of exactly that.
We decided very early on to place all logic traditionally belonging in SQL joins into code, which means we essentially need to retrieve all rows of all tables we need in order to perform our in-code “queries”. This would put tremendous strain on our database. To prevent this we used a caching system called Memcached, which is an in-memory key-value store. Memcached key-value lookup turns an operation which could take O(n) time into an operation which takes O(1) time, all while alleviating load off our our database. Having a successful caching system immensely helped us maintain logic in code instead of in the database.
We simplified things a bit more by deciding not to cache lists, and instead to cache objects using their IDs. For example, instead of caching all Teams in a specific Organization as a list with the Organization ID as its key, we cache each Team with its own ID as its key. This allows us to reuse these Teams for many different in-code “queries” and not just have them exist as members of a specific list. Evidently we still need to retrieve lists, and to do this we use Memcached multiget. Objects live on their own and are related through each other through simple collections and not through SQL joins. Using this strategy we were able to keep logic out of the database and in code in an effort to keep the database as simple as possible.
ScalabilityThe database is arguably the hardest to scale, and a lack of a database admin in the early days of Hootsuite meant there was no one to monitor development. Web servers and Memcached servers are much easier to scale than MySQL servers because they are horizontally scalable, while it’s very hard to scale MySQL horizontally. Because of this our goal was to make the database do very little, if any, heavy lifting.
Instead of relying on the functionality of the database we took advantage of caching and kept all the logic in the code. We now have 1 MySQL server and 6 Memcached servers. The database receives 500 queries per second while Memcached receives 80,000 queries per second. In addition, we have a 99% cache hit rate in Memcached, which shows how well our cache invalidation strategy works. If we used Memcached while having foreign keys and SQL joins and we would need to query the database a lot more to retrieve that information, which would definitely impact scalability.
The Flip Side of the Coin: Another ApproachOf course, stripping foreign keys and SQL joins from your database is not the only way to ensure that it follows MSS. Another option is to normalize your database. Repeated data creates confusion and often impacts maintenance in a big way, and normalization aims to eradicate this problem. In addition, normalization would simplify the set of error handling cases that need to be done and reduce the amount of code for dealing with incorrect data states.
Database normalization is a great approach that works for a lot of people, but it takes a team with a deep understanding of yay database design to be able to do it successfully. Where this approach falls down, however, is when you decide to migrate your monolithic application to a microservice architecture. I will explain why in the next section.
Moving towards a Microservice ArchitectureStripping the database to its bare bones as well as developing a good caching mechanism helped us tremendously when we started moving towards a microservice architecture. This is because we did not have to break apart any foreign key constraints in the database or untangle any SQL joins, which would have been extremely complicated. I will show you why with the following example.
Imagine we have a database (belonging to a monolith) which contains these tables: Human, Animal, and Food. An Animal has a foreign key constraint with a Human, its owner, and Food, what it eats. We write a SQL query to return all animals which have an owner and eat fish and display the list on a web page.
Let’s say that we decide to split off some logic into a microservice which just has the Animal table and some other tables, but not the Human and Food tables. First, we need to remove all the foreign key constraints, which isn’t too bad but now we have no reinforcement. If the logic was in the code instead of the database, we would be done, but now we will have to write that code anyway since we cannot rely on the foreign key anymore. Second, we cannot use the query anymore since we have deleted the Animal table in the original database and moved it over to a the microservices one. All code which relied on this query to return the right data will now be broken until we move the database logic into code. What if we had 10 SQL joins which used the Animal table? What if we had 50? We could have planned for this all along and saved ourselves the hassle.
One of the things you sacrifice when you decide to follow a microservice architecture for your project is consistent correctness of data. For example, many microservice projects don’t have transactions because they are so hard to implement between services (which you can read more about here). This means that it’s possible for data to be incorrect if a certain microservice fails after another one already manipulated data. So if people have been able to make their microservice projects succeed without the added layer of foreign keys and SQL joins, who says microservices can’t follow that approach too?
Instead of striving for perfection and data integrity, consider the opposite. Consider expecting bad data and putting in the appropriate measures to recover from it. Microservice projects are bound to have bad data at some point and implementing a recovery mechanism early on will only help. Through sacrificing data integrity we are forced to build more fault-tolerant code, which we excel at. This approach has worked very well for us at Hootsuite and has really helped us scale our service-oriented architecture.
ConclusionNot having foreign keys or SQL joins in your database is an interesting and rather controversial approach to database design. Having said that, it worked extremely well for us. That doesn’t mean it is the only correct approach, or that it is the best one, but it’s definitely something to consider when you’re thinking about how you want your database to look now and in the future, especially if microservices are somewhere down the horizon.
Big thanks to Bill Monkman, the architect of everything I mentioned in this blog post.
Ena is a Co-op on the #sofetch Platform team at Hootsuite. She enjoys playing drums and guitar and is currently learning Korean in her free time.