Your web application database isn't scaling, now what?
Don't panic, but read this
Good intentions, eventual sadness
As developers, when we create a new database table, we likely don’t have any super long-term vision of the life of the table. We grab a feature, recognize we need to store some data, then start to implement the update. A typical table comes to life (naively) like the following:
We evaluate how the change will affect any current relationships in the DB;
We work out a normalized schema;
Add some indexes, foreign keys and a primary key;
Code it up with our ORM;
Forget this table exists until we some future need
While this workflow will work for small datasets, once you get into larger datasets; CRUD operations begin to slow down. Eventually you run into timeouts, so now what?
Quick, let’s add an index
Adding indexes can indeed speed up slow queries, but what isn’t so obvious is the fact that indexes will slow down INSERT’s. The simple reason is an index entry must be created for each new row of data.
What ends up happening is that there are competing concerns on a single table. In order to optimize a table for INSERT’s, less indexes are desired. In order to optimize for read’s, we need to add indexes. Normalized tables are much friendlier to write’s than they are to read’s. Most times several JOIN’s are required in this situation to assemble a dataset for reporting and/business logic. Ideally, a read wants to have no JOIN’s.
Before we propose a solution, we first have to address the elephant in the room, the ORM.
ORM’s are Dark Magic
Object-relational-mappers (ORM’s) are both a blessing and a curse. Used properly, they make your interaction with the database a breeze. Used improperly, they enable misuse of the database in hidden and mysterious ways.
One such misuse is when deferred joins (lazy loads) are used in loops. This causes extra round-trips to the database, often with each iteration. This problem has been dubbed the N+1 problem. Lazy loading in my mind is something that should be the exception rather than the rule.
You of course could include additional data ahead of time (eager loading), but that is only useful if you can grab what you need and nothing more. Older ORM’s (NHibernate) encouraged the mapping all fields of a table row to an object instead of a selective approach by grabbing only the number of columns you need. When using a profiler, we can see that most calls to the database are grabbing all the columns (e.g. SELECT *) only to see the code uses one or two columns before throwing it all away (e.g. the web request ends).
Another smell is that ORM’s tend to enable is the lack of pagination on the server-side. For example, if we grab an entire table and return the dataset to the browser for client-side pagination, we’ve really done ourselves a dis-service b/c most datasets won’t need to be enumerated on the client (e.g. users don’t need to get past the first page often). While the browser is powerful, the database is literally a piece of hardware dedicated to sorting and filtering large datasets. Not using it and sending too much data to the browser is a mistake that won’t scale.
Finally many dev’s don’t understand the fundamental difference between an IQueryable and an IEnumerable in the context of ORM’s. The nuances of deferred query execution are outside the scope of this article, but we will return to it before we are finished.
ORM’s are not bad, in fact I use one in every app I make. It turns out ORM’s are so magical, it allows us to forget about what queries are actually being called by the code.
Bringing order to the chaos
As mentioned, reads and writes have competing concerns and ORM’s can enable poor use of the underlying DB. So where do we begin to claw back some sanity?
Split your reads and writes
The first thing we can and should do is have a read-only replica that is sync’d to a read/write server. In your app create two types of DB connections/contexts — one that can read/write and one that can read-only. In this way you can begin to load-balance requests and scale the reads especially.
While this sounds simple, it exposes one of the more insidious parts of an ORM. When you attempt to retrofit a large established repository, you’ll find that entities can be passed around a lot and you aren’t quite sure if at some point the entity was mutated or not. The way ORM’s work is that any entity that has been modified will be persisted when a `Save` on the DB context is invoked. Splitting the context will be tedious and if you have extensive integration tests, you’ll have a test to back you up when you’ve made something read-only when it has a hidden write in the request. I’d suggest starting with reporting and GET’s before trying to trace through your PUT and POSTs.
Throwing more servers at the problem will certainly help, but there’s more things we can do in the app itself.
Aggregates and projection tables
Data tables have different levels volatility. Some won’t change much (if at all) while other tables are high-churn with a lot of writes per second to existing rows.
For INSERT’s in high-churn tables, favor less indexes and avoid reading it. Since this will impact anything used to report on this table (queries), we need to come up with a way to read this same data quickly.
When I was learning database’s as a young dev, I got the impression that tables should be highly-normalized in all cases and that de-normalized tables were poorly designed. It turns out that that normalized tables are for INSERTS and de-normalized tables are for reads. A single table to do reads and writes have to balance the concerns of each — which is often not feasible.
We need to create a second table with similar data as the write table. But before we run out and create a new table, we should first figure out how this information is being read in the first place — in order to optimize for those use-cases. What you’ll likely find is that the data in the high-churn table is often JOIN’ed with one or more other tables.
Since a read is slowed down by a JOIN, create a table with all of the joined information already included, in fact let’s call it a projection. Whenever something is inserted or updated into the write table, add row to our new table with all of the joined information de-normalized into this projection. Be sure to add indexes to our new read table, then point your code at this new table for the reads. We’ve effectively delegated reading the information and providing it in a form that is “native” to what is consuming it.
When you have another highly used read use-case come along, create another projection and point other code at it as well. The best part is that code will often call aggregate functions for reporting (e.g. SUM/AVG/COUNT) which means you can create yet-another projection to have data rolled up and pre-computed.
This of course begs the question, if one of the rows in the write table updates, how will you know to update one of the read projections?
Cache invalidation is hard
What we’re really talking about is caching and all the nuances that come with invalidating it. You could of course setup native DB triggers and attempt to codify the business logic into a stored procedure (SPROC), but often times values need to be computed by imperative C# code or even a third-party API.
Unfortunately I can’t give you the answer that will definitely work in your system, rather I will attempt to provide you with a few options that may inspire a solution for your needs:
C# Events - Force writes to happen in a particular area of your application (e.g. a Save() method). Inside your method, raise a C# event that can be subscribed to by any code in your solution. Write some “de-normalizer” handlers that respond to an event and write to one or more projections.
Leverage eventual consistency (EC) - Similar to raising an event, EC usually involves publishing a message to a topic/exchange and something eventually reads the message and writes the de-normalized table records.
Developers don’t speak database as much as they should
ORM’s make DB usage so easy that we’ve abstracted away how a DB works for our younger dev’s. I’ve seen dev’s struggle to cobble together basic queries because of the magic of ActiveRecord and Entity Framework (EF). I’ve used Dapper over the years but often wanted to have a few nicer features that Entity Framework includes — specifically migration and unit of work functionality; so I get why we don’t wanna get too close to the metal with the database. In the end, we need developers to know how a DB works and then use an ORM to avoid repetitive work.
Make sure you avoid common pitfalls with ORM’s:
Implicit
SELECT *— grab only what you need, not everything just in case.Eager loading — often pitched as a benefit; I look at it as a way to easily hide death by a 1000 server calls.
Oh daddy!
Pardon the headline pun, but the other way to get more out of your database is to use proper pagination. Early on in an app, it’s easy to simply return the entire query result to the client and paginate there. Over time though, you’re sending way too much data to the client as most folks never even care what is past page one — so why load it?
OData is a protocol supported by EF and is a very popular way to handle server-side pagination that reaches down into the DB layer and leverages the power of Postgres/SQL Server/MySQL/etc to do the lowly tasks of ordering and fetching.
Bon voyage
There’ll never be a single solution that will solve all of your woes, so hopefully you are able to take a few things back to your database that might be in need of a tune-up.
Good luck and happy coding!