Comment by cfors

I can't speak for cogman10, but in my experience when you start to encounter issues of hitting the limit of "one big database" you are generally dealing with some really complicated shit and refactoring to dedicated read instances, shards, and other DB hacks are just short term solutions to buy time.

The long term solutions end up being difficult to implement and can be high risk because now you have real customers (maybe not so happy because now slow db) and probably not much in house experience for dealing with such large scale data; and an absolute lack of ability to hire existing talent as the few people that really can solve for it are up to their ears in job offers.

> I don't know what's the complexity of your project, but more often than not the feeling of doom coming from hitting that wall is bigger than the actual effort it takes to solve it.

We've spent and failed at multiple multi year projects to "solve" the problem. I'm sure there are more simple problems that are easier to disentangle. But not in our case.

One nice compromise is to migrate to using read-only database connections for read tasks from the moment you upgrade from medium sized DB hardware to big hardware. Keep talking to the one big DB with both connections.

Then when you are looking at the cost of upgrading from big DB hardware to huge DB hardware, you've got another option available to compare cost-wise: a RW main instance and one more read-only replicas, where your monolith talks to both: read/write to the master and read-only to the replicas via a load balancer.

I don’t see anyone mentioning memcached which can reduce the load on the db tremendously.

I worked at a mobile game company for years and years, and our #1 biggest scaling concern was DB write throughput. We used Percona's MySQL fork/patch/whatever, we tuned as best we could, but when it comes down to it, gaming is a write-heavy application rather than the read-heavy applications I'm used to from ecommerce etc.

Sharding things out and replicating worked for us, but only because we were microservices-y and we were able to split our schemas up between different services. Still, there was one service that required the most disk space, the most write throughput, the most everything.

(IIRC it was the 'property' service, which recorded everything anyone owned in our games and was updated every time someone gained, lost, or used any item, building, ally, etc).

We did have two read replicas and the service didn't do reads from the primary so that it could focus on writes, but it was still a heavy load that was only solved by adding hardware, improving disks, adding RAM, and so on.

Not without big compromises and a lot of extra work. If you want a truly horizontally scaling database, and not just multi-master for the purpose of availability, a good example solution is Spanner. You have to lay your data out differently, you're very restricted in what kinds of queries you can make, etc.

For what it's worth, I think distributing horizontally is also much easier if you're already limited your database to specific concerns by splitting it up in different ways. Sharding a very large database with lots of data deeply linked sounds like much more of a pain than something with a limited scope that isn't too deeply linked with data because it's already in other databases.

To some degree, sharding brings in a lot of the same complexities as different microservices with their own data store, in that you sometimes have to query across multiple sources and combine in the client.

We've done that (MSSQL Always on) and that's what keeps the lights on today. It's not, however, something that'll remain sustainable.

Finance. MSSQL.

There are multiple plans on how to fix this problem but they all end up boiling down to carving out domains and their owners and trying to pull apart the data from the database.

What's been keeping the lights on is "Always On" and read only replicas. New projects aren't adding load to the db and it's simply been a slow going getting stuff split apart.

What we've tried (and failed at) is sharding the data. The main issue we have is a bunch of systems reading directly from the db for common records rather than hitting other services. That means any change in structure requires a bunch of system wide updates.

What does it matter, in this context?

If it's about bare metal vs. virtual machines, know that Spectre affects virtual machines, too.

Here's the way it works for, say, Postgresql:

- you rsync or zfs send the database files from machine A to machine B. You would like the database to be off during this process, which will make it consistent. The big advantage of ZFS is that you can stop PG, snapshot the filesystem, and turn PG on again immediately, then send the snapshot. Machine B is now a cold backup replica of A. Your loss potential is limited to the time between backups.

- after the previous step is completed, you arrange for machine A to send WAL files to machine B. It's well documented. You could use rsync or scp here. It happens automatically and frequently. Machine B is now a warm replica of A -- if you need to turn it on in an emergency, you will only have lost one WAL file's worth of changes.

- after that step is completed, you give machine B credentials to login to A for live replication. Machine B is now a live, very slightly delayed read-only replica of A. Anything that A processes will be updated on B as soon as it is received.

You can go further and arrange to load balance requests between read-only replicas, while sending the write requests to the primary; you can look at Citus (now open source) to add multi-primary clustering.

Presumably it doesn't matter if you break your DB up into smaller DBs, you still have the same amount of data to back up no matter what. However, now you also have the problem of snapshot consistency to worry about.

If you need to backup/restore just one set of tables, you can do that with a single DB server without taking the rest offline.

Try out pg_probackup. It works on database files directly. Restore is as fast as you can write on your ssd.

I've setup a pgsql server with timescaledb recently. Continuing backup based on WAL takes seconds each hour and a complete restore takes 15 minutes for almost 300 GB of data because the 1 GBit connection to the backup server is the bottleneck.

On mariadb you can tell the replica to enter into a snapshotable state[1] and take a simple lvm snapshot, tell the the database it's over, backup your snapshot somewhere else and finally delete the snapshot.

1) https://mariadb.com/kb/en/storage-snapshots-and-backup-stage...

I found this approach pretty cool in that regard: https://github.com/pgbackrest/pgbackrest

Not a solution but using event sourcing would have prevented this.

That's fair - I added "are working on a specific problem which needs a more complicated setup" to my original comment as a nicer way of referring to edge cases like search engines. I still believe that 99% of applications would function perfectly fine with a single primary DB.

Depends what you mean by a database I guess. I take it to mean an RDBMS.

RDBMSs provide guarantees that web searching doesn't need. You can afford to lose a pieces of data, provide not-quite-perfect results for web stuff. It's just wrong for an RDBMS.

This is not typically going to be stored in an ACID-compliant RDBMS, which is where the most common scaling problem occurs. Search engines, document stores, adtech, eventing, etc. are likely going to have a different storage mechanism where consistency isn't as important.

a search engine won't need joins, but other things (ie text indexing) that can be split in a relatively easier way.

> outsource the problem to some vendor

At least that way you can be certain of failure.

Exactly. The HN crowd is obsessed with minimalism and reducing "bloat".

It has become a cult, where availability and scale requirements are apparently fiction. "You are not FAANG, you don't have these requirements."

Honestly from my perspective it feels like microservices arose strongly in popularity precisely when it was becoming less necessary. In particular the mass adoption of SSD storage massively changed the nature of the game, but awareness of that among regular developers seemed not as pervasive as it should have been.

To clarify, I think stateless microservices are good. It's when you have too many DBs (and sometimes too many queues) that you run into problems.

A single instance of PostgreSQL is, in most situations, almost miraculously effective at coordinating concurrent and parallel state mutations. To me that's one of the most important characteristic of an RDBMS. Storing data is a simpler secondary problem. Managing concurrency is the hard problem that I need most help with from my DB and having a monolithic DB enables the coordination of everything else including stateless peripheral services without resulting in race conditions, conflicts or data corruption.

SQL is the most popular mostly functional language. This might be because managing persistent state and keeping data organized and low entropy, is where you get the most benefit from using a functional approach that doesn't add more state. This adds to the effectiveness of using a single transactional DB.

I must admit that even distributed DBs, like Cockroach and Yugabyte have recognized this and use the PostgreSQL syntax and protocol. This is good though, it means that if you really need to scale beyond PostgreSQL, you have PostgreSQL compatible options.

> I'm seeing less 'balls of mud' with microservices.

The parallel to "balls of mud" with microservices is tiny services that seem almost devoid of any business logic and all the actual business logic is encapsulated in the calls between different services, lambda functions, and so on.

That's quite nightmarish from a maintenance perspective too, because now it's almost impossible to look at the system from the outside and understand what it's doing. It also means that conventional tooling can't help you anymore: you don't get compiler errors if your lambda function calls an endpoint that doesn't exist anymore.

Big balls of mud are horrible (I'm currently working with a big ball of mud monolith, I know what I'm talking about), but you can create a different kind of mess with microservices too. Then there all the other problems, such as operational complexity, or "I now need to update log4j across 30 services".

In the end, a well-engineered system needs disciple and architectural skills, as well as a healthy engineering culture where tech debt can be paid off, regardless of whether it's a monolith, a microservice architecture or something in between.

> I'm seeing less 'balls of mud' with microservices. Thats not zero balls of mud.

They are probably younger. Give them time :P

I really don't understand how anything of what you wrote follows from the fact that you're building a web-app. Why do you lose user data when two users do anything at the same time? That has never happened to me with any RDBMS.

And why would HTTP requests prevent me from using transactional logic? If a user issues a command such as "copy this data (a forum thread, or a Confluence page, or whatever) to a different place" and that copy operation might actually involve a number of different tables, I can use a transaction and make sure that the action either succeeds fully or is rolled back in case of an error; no extra logic required.

I couldn't disagree more with your conclusion even if I wanted to. Relational databases are great. We should use more of them.

Http requests work great with relational dbs. This is not UDP. If the TCP connection is broken, an operation will either have finished or stopped and rolledback atomically and unless you've placed unneeded queues in there, you should know of success immediately.

When you get the http response, you will know the data is fully committed, data that uses it can be refreshed immediately and is accessible to all other systems immediately so you can perform next steps relying on those hard guarantees. Behind the http request, a transaction can be opened to do a bunch of stuff including API calls to other systems if needed and commit the results as an atomic transaction. There are tons of benefit using it with http.

This is a great resource but the RFC-style documentation says what you SHOULD and MUST do, not HOW to do it ...

Yeah, I can attest that even banks are really using best effort eventual consistency. However, I think it is very difficult to reason about with systems that try to use eventual consistency as an abstraction. It's a lot easier to think about explicitly when you have one data source/event that propagates outwards through systems with stronger individual guarantees than eventual consistency.

> It is bad practice to have your authentication database be the same as your app database.

No, this is resume-driven-development, Google-scale-wannabe FUD. Understand your requirements. Multiple databases is non-trivial overhead. The only reason to add multiple databases is if you need scale that can't be handled via simple caching.

Of course it's hard to anticipate what level of scale you'll have later, but I can tell you this: for every tiny startup that successfully anticipated their scaling requirements and built a brilliant microservices architecture that proactively paved the way to their success, there's a 100 burnt out husks of companies that never found product market fit because the engineering team was too busy fantasizing about "web-scale" and padding their resume by overengineering every tiny and unused feature they built.

If you want to get a job at FAANG and suckle at the teat of megacorporations who's trajectory was all based on work done in the early 2000s, by all means study up on "best practices" to recite at your system design interview. On the other hand, if you want to build the next great startup, you need to lose the big co mentality and start thinking critically from first principles about power to weight ratio and YAGNI.

> And yet developers do this every single day without any issue.

And users suffer through unresponsive interfaces and long load times every single day...

> splitting off services when it becomes apparent they need to change at a different pace from the main body

yes - this seems to get lost, but the microservice argument is no different to the bigger picture software design in general. When things change independently, separate and decouple them. It works in code and so there is no reason it shouldn't apply at the infrastructure layer.

If I am responsible for the FooBar and need to update it once a week and know I am not going to break the FroggleBot or the Bazlibee which are run by separate teams who don't care about my needs and update their code once a year, hell yeah I want to develop and deploy it as a separate service.

> What it fundamentally boils down to is that your org chart determines your architecture.

This is Conway’s law

https://en.wikipedia.org/wiki/Conway%27s_law

">Use One Big Database Server…

… and on it, use one software database per application.<"

FWIW that is how it is usually is done(and has been done for decades) on mainframes (IBM & UNISYS).

-----------------------

"Plus ça change, plus c'est la même chose."

English: "the more things change, the more they stay the same."

- old French expression.

OP mentioned joining, so they were definitely talking about a single database

I have to say I disagree with this ... you can only separate them if they are really, truly independent. Trying to separate things that are actually coupled will quickly take you on a path to hell.

The problem here is that most of the microservice architecture divisions are going to be driven by Conway's law, not what makes any technical sense. So if you insist on separate databases per microservice, you're at high risk of ending up with massive amounts of duplicated and incoherent state models and half the work of the team devoted to synchronizing between them.

I quite like an architecture where services are split except the database, which is considered a service of its own.

These are both true - but neither requires service-oriented-architecture.

You can split up your applicaiton into chunks that are deployed on seperate hardware, and use different languages, without composing your whole architecture into microservices.

A monolith can still have a seperate database server and a web server, or even many different functions split across different servers which are horizontally scalable, and be written in both java and python.

Monoliths have had seperate database servers since the 80s (and probably before that!). In fact, part of these applications defining characteristics at the enterprise level is that they often shared one big central database, as often they were composed of lots of small applications that would all make changes to the central database, which would often end up in a right mess of software that was incredibly hard to de-pick! (And all the software writing to that database would, as you described, be written in lots of different languages). People would then come along and cake these central databases full of stored procedures to make magic changes to implement functionality that wasn't available in the legacy applications that they can't change because of the risk and then you have even more of a mess!

if you have multiple micro services updating the database you need to have a database access layer service as well.

there's some real value with abstraction and microservices but you can try to run them against a monolithic database service

> 99% of apps are best fit as monolithic apps and databases and should focus on business value rather than scale they'll never see

You incorrectly assume that 99% of apps are building these architectures for scalability reasons.

When in reality it's far more for development productivity, security, use of third party services, different languages etc.

Totally agree.

I guess I just don't see the value in having a monolith made up of microservices - you might as well just build a monolith if you are going down that route.

And if your application fits the microservices pattern better, then you might as well go down the microservices pattern properly and not give them a big central DB.

Where I work we are looking at it because we are starting to exceed the capabilities of one big database. Several tables are reaching the billions of rows mark and just plain inserts are starting to become too much.

You've not really built microservices then in the purest sense though - i.e. all the microservices aren't independently deployable components.

I'm not saying what you are proposing isn't a perfectly valid architectural approach - it's just usually considered an anti-pattern with microservices (because if all the services depend on a single monolith, and a change to a microservice functionality also mandates a change to the shared monolith which then can impact/break the other services, we have lost the 'independence' benefit that microservices supposedly gives us where changes to one microservice does not impact another).

Monoliths can still have layers to support business logic that are seperate to the database anyway.

> I know we're all hot and bothered about getting our apps to scale up to be the next unicorn, but most apps never need to scale past the limit of a single very high-performance database. For most people, this single huge DB is sufficient.

True if the reliability is good enough. I agree that many organisations will never get to the scale where they need it as a performance/data size measure, but you often will grow past the reliability level that's possible to achieve on a single node. And it's worth saying that the various things that people do to mitigate these problems - read replicas, WAL shipping, and all that - can have a pretty high operational cost. Whereas if you just slap in a horizontal autoscaling datastore with true master-master HA from day 1, you bypass all of that trouble and just never worry about it.

> Also, for many (maybe even most) applications, designated outages for maintenance are not only acceptable, but industry standard. Banks have had, and continue to have designated outages all the time, usually on weekends when the impact is reduced.

IME those are a minority of applications. Anything consumer-facing, you absolutely do lose out (and even if it's not a serious issue in itself, it makes you look bush-league) if someone can't log into your system at 5AM on Sunday. Even if you're B2B, if your clients are serving customers then they want you to be online whenever their customers are.

I don't know the characteristics of bikesheddb's upstream in detail (if there's ever a production-quality release of bikesheddb I'll take another look), but in general using something that can scale horizontally (like Cassandra or Riak, or even - for all its downsides - MongoDB) is a great approach - I guess it's a question of terminology whether you call that "sharding" or not. Personally I prefer that kind of datastore over an SQL database.

Cassandra was just an example, but most databases can be scaled either vertically or horizontally via sharding. You are right if misconfigured performance can be hindered, but this is also true for a database which is being scaled vertically. Generally speaking you will get better performance if you have a large dataset by growing horizontally then you would by growing vertically.

https://stackoverflow.blog/2022/03/14/how-sharding-a-databas...

> The problem with this DB system is that you have to know all the queries before you can define the schema

Not true.

You just need to optimise your schema if you want the best performance. Exactly the same as an RDBMS.

Replace "people" with "features" and my comment still holds. As software, features, and organizations become more complex the core feature data becomes a smaller and smaller proportion of the overall state and that's when microservices and separate data stores become necessary.

Your scenario is data recovery, not backup restoration. Wildly different things.