It's probably a bad idea to switch to read only replicas for reads pre-emptively, vs vertically scaling up the database. Doing so adds a lot of incidental complexity since you have to avoid read after writes, or ensure the reads come from the master.
The reason punting on this is a good idea is because you can get pretty far with vertical scaling, database optimization, and caching. And when push comes to shove, you are going to need to shard the data anyway to scale writes, reduce index depths, etc. So a re-architecture of your data layer will need to happen eventually, so it may turn out that you can avoid the intermediate "read from replica" overhaul by just punting the ball until sharding becomes necessary.
The problem with going to the "top of the vertical" scaling so to speak is that one day, if you're lucky, you'll have enough traffic that you'll reach the limit. And it will be like hitting a wall.
And then you have to rearchitect your data layer under extreme duress as your databases are constantly on fire.
So you really need to find the balance point and start doing it before your databases are on fire all the time.
Assuming you have a relatively stable growth curve, you should have some ability to predict how long your hardware upgrades will last.
With that, you can start planning your rearchitecture if you're running out of upgrades, and start implementing when your servers aren't yet on fire, but are likely to be.
Today's server hardware ecosystem isn't advancing as reliably as it was 8 years ago, but we're still seeing significant capacity upgrades every couple years. If you're CPU bound, the new Zen2 Epyc processors are pretty exciting, I think they also increased the amount of accessible ram, which is also a potential scaling bottleneck.
> Assuming you have a relatively stable growth curve, you should have some ability to predict how long your hardware upgrades will last.
But that's not how the real world works. The databases don't just slowly get bad. They hit a wall, and when they do it is pretty unpredictable. Unless you have your scaling story set ahead of time, you're gonna have a bad day (or week).
If you're lucky, the wall is at 95-100% cpu. Oftentimes, we're not that lucky, and when you approach 60%, everything gets clogged up, I've even worked on systems where it was closer to 30%.
Usually, databases are pretty good at running up to 100%, though. And if you started with small hardware, and have upgraded a few times already, you should have a pretty good idea of where your wall is going to hit. Some systems won't work much better on a two socket system than a one socket system, because the work isn't open to concurrency, but again, we're talking about scaling databases, and database authors spend a lot of time working on scaling, and do a pretty good job. Going vertically up to a two socket system makes a lot of sense on a database; four and eight socket systems could work too, but get a lot more expensive pretty fast.
Sometimes, the wall on a databases is from bad queries or bad tuning; sharding can help with that, because maybe you isolate the bad queries and they don't affect everyone at once, but fixing those queries would help you stay on a single database design.
The minute your RDBMS' hot dataset doesn't fit into memory its going to shit itself. I've seen it happen anywhere from 90% CPU down to around 10%. Queries that were instant can start to take 50ms.
It can be an easy fix (buy more memory), but the first time it happens it can be pretty mysterious.
There's no CPU wall. You have assets: CPU, memory, disk bandwidth/latency, and then structural decisions in your schema. The key is knowing your performance characteristics and where you will start queuing. That's hard to figure out. Practically speaking, you're right, and you're 100% right about unreplicated/unsharded data stores eventually hitting a wall and needing to have a strategy how/when to scale. I just noticed your username and feel silly for telling you stuff you already know far better than me, but posting it anyway in case it benefits others.
That's exactly how the real world works. Databases will get slow, then slower. Resources get used.
Unpredictable not really. Maybe you've run out of space or ram or processes are hanging. The database will never just start rending html or formatting your disk or email someone. It is pretty predictable.
The failure I've seen multiple times is that the database is returning data within normal latencies, and then there is a traffic tipping point and the latencies go up 1000x for all requests.
Capacity planning can't be solely linked to the growth curve. This assumes that the number and complexity of your SQL queries never evolve, which isn't true in most cases.
Your will implement new features, add new tables and columns, indexes, etc which will affect your data layer.
I actually implemented domain driven design WITH an Api-layer ( so core, application, Infrastructure + api). They also are split on Basket, catalog, checkout, shipping and pricing domain with seperate db's.
So just splitting up the heaviest part (eg. Catalog) into "a microservice" would be easy while I add nginx as load balancer. I already separated domain vs Integration Events.
Both now use events in memory in the application, I only need a message broker like NATS then for the integration events.
It would be a easy wall ;). I have multiple options like heavier hardware, splitting up the db from application server or splitting up a domain bound api to a seperate server.
As long as I don't need multimedia streaming, kubernetes or implement Kafka the future is clear.
Ps. Load balancing based on tenant and cookie would be a easy fix in extreme circomstances.
The thing I'm afraid for the most is hitting the identity server for authentication/token verification. Not sure if it's justified though.
Side note: one application has an insane amount of complex joins and will not scale :)
DID is an extremely important concept that is alien to a lot of developers: Deploy for 1.5X, Implement for 3X, Design for 10X (your numbers may vary slightly)
There are some cases where adding a read replica can be helpful at almost no extra overhead - for instance if your product has something like a stats dashboard you'll have some heavy queries that are never going to result in a write after read and don't matter if they are sometimes a few ms or even a few seconds or tens of seconds out of date. Similarly if you have analysts poking around running exploratory queries, a read replica can be the first step towards an analytics workflow/data warehouse.
For those who have reached vertical database write scaling limits and had to start sharding, I'm curious what kind of load that entails? Looking at RDS instances, the biggest one is db.r5.24xlarge with 48 cores and 768 gb ram. I imagine that can take you quite a long way--perhaps even into millions of users territory for a well-designed crud app that's read-heavy and doesn't do anything too fancy?
> the biggest one is db.r5.24xlarge with 48 cores and 768 gb ram. I imagine that can take you quite a long way--perhaps even into millions of users territory
That will run Stackoverflow's db by itself for reference, along with sensible caching (they're very read-heavy and cache like crazy). Here's their hardware for their SQL server for 2016:
Very far I would guess. 10 years ago we took a single bare metal database server running mysql with 8 cores and 64gb of memory to 8 million daily users. 15k requests per second of per user dynamic pages at peak load.
Yeah 10 years ago you could support millions of users on a high traffic site on a single box. (This was on postgres in my case.) Today, I'd guess at least a 10x increase due to both software optimizations and increased hardware capabilities, if not significantly more.
Truthfully, unless you're working on some kind of non-transactional problem like analytics, even assuming you will need to shard the data or scale out reads ever due to user activity is borderline irrational unless you have extremely robust projections. The database will be the last domino to fall after you've added sufficient caching and software optimization. It's so far down field for most projects (and the incidental complexity cost so high) that my personal bias is that even having the conversation about such things on most projects isn't even worth the opportunity cost vs talking about something else.
Even then, the first thing to fall over will probably be write heavy analytics-like tables that are usually append only due to index write load. Out of the box, you can often 'solve' this by partitioning the table (instead of sharding.) In modern DBs, this is a simple schema change.
The reason punting on this is a good idea is because you can get pretty far with vertical scaling, database optimization, and caching. And when push comes to shove, you are going to need to shard the data anyway to scale writes, reduce index depths, etc. So a re-architecture of your data layer will need to happen eventually, so it may turn out that you can avoid the intermediate "read from replica" overhaul by just punting the ball until sharding becomes necessary.