As to why you'd want a separate DB server process: long lived mutable structures tend to drift into unexpected states, which is why the standard computer troubleshooting procedure since forever is to restart. Database management systems specialize in not having that problem. You deploy one that's widely used and hardened over many years, change it infrequently, and operate it with great care. Or pay a cloud provider to do that.
Then everything else gets to outsource the burden of persistence to it, and the vast majority of the workloads you develop and operate are stateless. These are drastically more convenient and more resilient to mistakes. They're effectively "restarted" with each API request, and you can stand them up, tear them down, migrate them to new machines, etc. as much as you want without fear of data loss.
Why are those "tend to drift" ? As an example I wrote sort of like game server for one of my applications. Internally it has those exact forever lived mutable structures. I've never observed it to drift into any unpredictable state. Works like a charm and running for many month. I only reboot it when I need to update it to a new version.
The only catch here is that all data fit into RAM. With the amount of RAM modern computers can be stuffed with I do not really see if my server would ever run out of it.
Of course it backed up by database but it merely serves as a persistence layer for this particular application.
In the real world, let's say you have a shipping address and a billing address for a customer, and they are usually (but not always) the same.
Eventually, a customer moves, changing both of their addresses. But the user forgets to change their billing address with their delivery address.
A proper database would have a 'billing address is same as delivery address' logic, following the principle of DRY (don't repeat yourself).
----------
There are lots of examples here of what can go wrong when you repeat yourself in a database application. The user may have an error when repeating themselves over the dataset (delivery address is correct, but zip code on billing address has a typo).
Dealing with these issues at scale, with hundreds of thousands of customers, is certainly a problem. Normal forms can formalize these issues and help the business owner avoid the problems.
Where do you verify the existence of zip codes and cities? Where do you check for typos? How do you prevent contradictions on the submitted information?
Your human customers will make many mistakes. Your logic must hold up even in the presence of faulty data.
The database does not have logic like this. It has to be implemented by stored procedure. When I have application server all such logic (if applicable) is handled by code in much more performant way. No data goes to a database directly. Everything passes through the app server along with the validation data transformation etc, etc. As already said the database in this particular case is nothing more but persistence layer.
Again we can put all kind of theoretical speculations but as I already said, my particular server does not have data drifting to some faulty states.
> The database does not have logic like this.
Of course it wouldn't have 'logic', databases are just stores of data.
You'd have one 'address' table, with probably a int-primary/surrogate key. Then the 'delivery' and 'billing' address would be an int, pointing to the address table.
Furthermore, the billing and delivery address would be foreign keys, so the internal database logic would keep the tables in sync with no application code required.
With the data organized in this manner, the application code becomes logic free and braindead easy to write. Or at least, corner cases become easier to handle and more explicit. (Say two customers share the same address, do you allow repeats in the address table? Or do you allow customers to tie address information together? Either way, your decision rests on how you define the primary key)
> When I have application server all such logic (if applicable) is handled by code in much more performant way.
The most performant way is no logic at all. A proper database removes a lot of checks, the storage format itself naturally creates logic free code.
Some application logic is necessary of course. But you can minimize the logic needed by thinking about data layout.
Many years ago I happened to meet the creator of Prevayler, an open-source persistence framework that provided ACID guarantees and was thousands of times faster than a database as long as your data fit in RAM. I tried it out for a project and we loved it. Our hundreds and hundreds of unit tests ran in a few seconds. Our pages rendered in ~5 milliseconds. It was structured around a log of actions, so if you wanted to know how the data ended up like it did, every change was logged.
What people who haven't worked this way often miss is that a database doesn't make things simpler, it just makes certain things easier. Once you add a traditional database to your project, you're importing a million lines of mysterious code into your project, and demanding to pay a serialization/deserialization penalty any time your code wants to look at data. When it works, it can be swell, but when you have a problem, suddenly things can get hairy. Database performance optimization is a murky art in a way that just isn't true if all your data is right there in RAM.
Prevayler of course didn't catch on. It was just too weird for most people, who had grown up on databases and for whom data structures were something that they hadn't really thought about since their last CS exam. But I sometimes dream of the world where it did catch on. At the time, fitting in RAM was a big limitation. But now I can get an off-the-shelf desktop with 768 GB of RAM, and Amazon's servers go up to 24 TB of RAM. If you're going past working sets at those levels, traditional databases have anyhow fallen out of favor of big data tools. But it would have been a much better fit for today's world of microservices and distributed systems.
Given no firm deadline, I timeboxed to 12 hours so it’s not fully fleshed-out but I like to think it illustrates the concept well.
I’m a big fan of Redis, but I was bitten many years ago when I tried to use it as a replacement for an RDBMS. There were two reasons for this: 1) lack of development libraries and operational tools, and 2) lack of data integrity checks.
1 has changed these days, but 2 is still very much the case (and rightly so, IMHO). Perhaps Prevayler had this?
But yep, it was fast at a time when our competition’s software was slow and clunky. It made a difference.
With Prevayler, all data is kept in RAM, reachable from one root object, which Prevayler holds. All changes to the data model must be expressed as command objects. Each object is handed to Prevayler, which serializes it to a log and then executes it. Once in a while, you can snapshot the data out and start a new log. If there's a crash, you just load the latest snapshot and replay the log.
You got exactly as much data integrity as you wrote into your objects and your commands. Which without much work could be quite a bit, because you get a lot of data integrity by not writing things. E.g., if a kind of object should never be deleted, you just don't write any deletion code. If, say, account balances should never be changed directly, but only as part of properly structured credits and debits, then you just write your code like that.
Most databases, Redis I think included, are made for arbitrary operations on data. Developers add integrity and security later, hopefully. And that is often duplicative of the code base, so that one ends up having integrity checks both in the code and in the database.
I should say that made a lot of sense for the era databases came out of. Databases were a huge step forward in the 1970s and 1980s. My dad was a developer in that era and it was a big relief not to have to get a bunch of programmers to all follow the same conventions for exactly which record was stored in exactly which spot on their precious and expensive disk drives. Not having to know the minutia of the hardware let a lot of people just get in there and build business reports. But if we were starting fresh today, I don't think we would do anything like a SQL database. Redis was definitely a step away from that era, and I look forward to many more.
So if you didn't need persistence, you certainly didn't need it. Ditto if data integrity wasn't important.
It would also have made distribution much easier. Since each mutation was already serialized to disk before execution, you could also send it over the wire to read-only replicas and hot spares for the master.
Most file systems will start to get slow at some point with too many files in a folder.
I would go so far as to argue that SQLite could be used to store all of the assets for any large piece of software (I.e. a AAA game). This would probably wind up faster and more reliable than most alternative solutions out there today.
The killer feature of databases, including no-sql or older ones like BerkeleyDB, is multiprocess synchronization.
If two programs (or two copies of the same program) need to coordinate their data, a database is often far easier to use than writing your own transaction layer through files, mutexes, and other primitives.
Web applications, such as forums or discussion boards, have many users and processes adding data simultaneously. That's why databases are so popular in web backends.
If your state consists of a single data structure you could write it to disk into a new file and move-replace the old file. This works as long as you have a single instance of your application.
For anything more complex SQLite is a good way to keep your data in a consistent state. If you store it on a local drive, the performance is stellar.
Thinking about going multi-process or multi-user? SQLite can still give you an easy head-start because it performs OK in most situations with a database stored on a network drive.
You can get by with files, but they're slower. A DB is the right choice.
Structured data anytime you're hitting multiuser - web, networked games, collab programs, live maps, etc.
Anything that's massively stateful. A DB hands you a lot of guarantees. The filesystem has some of them, but is much slower.
When you want to persist data beyond the lifespan of your process?
as soon as you need any of these:
* a relational model -because you need to model your data that way or you are required to because someone wants to consume it with tableau-.
* concurrently read/read data between 1+ instances.
* a standard way of doing backups.
moving from that to a database would probably equal a rewrite.