News for hackers. Stuff that matters.
Re:The database ruins all good ideas by ahachete on Sunday July 18, 2021 @05:26AM (#27871690)
Before thinking of removing the RDBMS; replacing itwith a NewSQL/NoSQL; or trying to horizontally shard, ask yourself: what is the performance I really need?

* Read-only queries can easily be scaled out to read replicas.

* Write transactions.

As an example of numbers publicly available, GitLab.com runs more than 250K read-only txs and more than 60K write txs on a single Postgres cluster, with room for further vertical scalability [1].

Do you need millions of transactions per second? Then go ahead. Don't? Then ou can scale very very far with a properly tuned and administered relational database like Postgres.

[1]: https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/

Re:The database ruins all good ideas by diminish on Sunday July 18, 2021 @06:21AM (#27871872)
If the number of users, number of active users and all major tables grow 100x, would postgres be able to support the application, without major re-designs?
Re:The database ruins all good ideas by jmchuster on Sunday July 18, 2021 @06:35AM (#27871925)
At 100x, gitlab would be generating $15 billion in revenue each year. Having to redesign their database structure wouldn't be the thing that keeps them up at night.

I'll also add, that the point is that a devtools unicorn is able to run with a single postgres cluster, so maybe it isn't the rdbms that's going to be the limiting factor on your startup with a hundred users.

Re:The database ruins all good ideas by quonn on Sunday July 18, 2021 @06:32AM (#27871918)
Presumably they would then have 100x the cashflow? Enough for a redesign?
Re:The database ruins all good ideas by KronisLV on Sunday July 18, 2021 @06:31AM (#27871909)
But what about when there will be problems with your single point of failure?

Sometimes you'll run into situations where an instance of any software has degraded performance for reasons beyond either your control or understanding. That's one of the situations where horizontal scaling let's you deal with such circumstances better, instead of outright impacting everything and everyone connected to it.

A front end web server container crumbles under the load? No worries, just redirect the traffic to others. A back end API container has problems with the JVM reserved memory and/or the thread pool for processing connections has been filled and all new incoming requests just get queued up? Just have more instances to cope with the load and don't let one instance under load affect all others.

But what are you supposed to do if long running SQL exhausts the resources of your DBMS? What if a background process needs to run some really complicated writes, but your application needs write capability without slowing down?

That's not to say that bad configurations or bad code shouldn't be addressed, but any means of fighting it and minimizing the impact is worthwhile in my eyes.

Re:The database ruins all good ideas by ric2b on Monday July 19, 2021 @07:08PM (#27888314)
> A front end web server container crumbles under the load? No worries, just redirect the traffic to others.

You can do basically the same thing with read replicas, promote one of them to primary and shut down the machine causing issues.

Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @07:56AM (#27872203)
Why have the RDBMS as the default? Why assume you need ACID and transactions when they're overwhelmingly likely to bring you nothing but trouble (the number of web applications that make effective use of database-level transactions is approximately zero).

You're far better off starting with a system that does seamless active-active by default, and then figuring out what kind of transactional guarantees you need. An RDBMS might make sense for a few specialised use cases, but most of the time it doesn't.

Re:The database ruins all good ideas by ahachete on Sunday July 18, 2021 @11:43AM (#27873853)
> Why have the RDBMS as the default?

It's possibly the sanest default for a data persistence layer. I'd argue otherwise: use a RDBMS unless you know what you are doing.

> the number of web applications that make effective use of database-level transactions is approximately zero

Almost every driver and layer (ORMs, etc) that connect to databases use transactions. Transparently or not, but it uses them. So actually they are using transactions all the time.

> You're far better off starting with a system that does seamless active-active

Think about what means active-active. Either it means that you serialize everything in every active node (pointless); or you need to deal with concurrency and data synchronization. Which in turn means either supporting distributed transactions (e.g. via Paxos or RAFT protocols) or doing conflict resolution (which essentially is a nice way of saying "data loss"). Both problems are much, much harder than the (usually harmless) consequences of using transactions on a "classical" RDBMS.

The only drawbacks of transactions are that are limited to scale in write on a single node. But if this single node can scale to the numbers I mentioned ---and they do, and more-- most use cases will likely fit within this envelope.

Therefore being RDBMSs like Postgres the best and safest, the default, approach that should be taken for data persistence layers.

Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @11:21PM (#27878943)
> Almost every driver and layer (ORMs, etc) that connect to databases use transactions. Transparently or not, but it uses them. So actually they are using transactions all the time.

They're using them but they're not getting any useful effect out of them, since the database-level transactions don't actually align with business-level transactions. Fundamentally you can't actually use database-level transactions in a useful way in a web application because you can't share a transaction between multiple HTTP requests (without introducing much bigger problems).

> Either it means that you serialize everything in every active node (pointless)

Not pointless - you gain redundancy which is the whole point. Performance will be bad but for a low-traffic system it's often actually fine.

> Which in turn means either supporting distributed transactions (e.g. via Paxos or RAFT protocols) or doing conflict resolution (which essentially is a nice way of saying "data loss"). Both problems are much, much harder than the (usually harmless) consequences of using transactions on a "classical" RDBMS.

The consequence of using transactions on a classical RDBMS at scale without thinking carefully about it is deadlocks, which are just as bad as distributed transaction problems, and you still have to do conflict resolution in the case where a transaction gets aborted, which will happen sooner or later (most RDBMS fans just ignore it and lose data). If you want decent performance then eventually you'll have to model your data properly using CRDTs (this is true even in a classical RDBMS) and at that point you gain nothing from traditional RDBMS transactions.

> Therefore being RDBMSs like Postgres the best and safest, the default, approach that should be taken for data persistence layers.

RDBMSes are not safe. The way they achieve their integrity guarantees is by rejecting, and dropping, unexpected writes. This almost always loses data in practice.

Re:The database ruins all good ideas by ekidd on Sunday July 18, 2021 @09:16AM (#27872658)
> Why assume you need ACID and transactions when they're overwhelmingly likely to bring you nothing but trouble

Maybe this for consumer web applications, or for CRUD apps.

But my experience with B2B and SaaS applications is that sooner or later, I always need transactions (or locks) for something. Maybe it's for the billing code. Maybe it's for batch jobs or workflow logic. And that's when I'm really happy to have a transactional database.

The alternative to having transactions is setting up a Raft or Paxos server to handle distributed locks, and those require a lot more ops effort than a copy of PostgreSQL.

Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @09:26AM (#27872720)
Well if it's not distributed then it's a single point of failure, almost by definition. I hate maintaining a Zookeeper cluster as much as anyone, but anything that runs on a single server is not a real alternative (and if a single-server SPoF is acceptable, then just running the batch job / workflow processor / whatever it is on a single server works just as well - maybe with a pool of workers to scale out the actual processing, but a single coordinator, sort of like how Jenkins or Hadoop do it).
Re:The database ruins all good ideas by ekidd on Sunday July 18, 2021 @10:48AM (#27873402)
My most common batch/workflow usecases tend to involve a few hundred workers, and tolerance for brief downtime (especially if jobs don't fail outright).

PostgreSQL works surprisingly well for something like this. It has a rich set of coordination and locking primitives, and it can usually be recovered and restarted in under 10 minutes if the server fails. Yes, connection overhead from 300 workers is a real problem, so it doesn't hurt to put a thin layer in front of it.

One thing that an RDBMS buys me is flexible feature set and a solid ecosystem. Want write-ahead logs and automatic point-in-time recovery? It's out there. Need a specific locking primitive? PostgreSQL probably supports it. Etc.

If I were looking at 2,000 workers, yeah, it would be time to set up a Raft server. But a good RDBMS will do the job for a surprisingly long time.

Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @09:14PM (#27878244)
I wouldn't say "flexible" exactly - RDBMSes come with a lot of stuff, but it's all coupled in surprising ways and you have to use it the way the system expects. They remind me of those do-everything application frameworks that come with a built in web server and task queue and distributed transaction framework and what have you, and you can do these amazing demos in 10 lines of code but as soon as you want to customize the behaviour or step slightly off the beaten path you're in for a world of pain.

I do see where you're coming from, but I find they're more trouble than they're worth in the long term. YMMV I guess.

Re:The database ruins all good ideas by kayodelycaon on Sunday July 18, 2021 @08:03AM (#27872238)
Every app I’ve worked on has used transactions to maintain data integrity. And before you say I could have stored everything in a document, I had to move away from MongoDB because it couldn’t handle the ways I needed to query the data in a performant way.
Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @08:15AM (#27872281)
"Maintain data integrity" sounds good, but what does it actually mean? In practice with an RDBMS it means you drop a write on the floor (or worse, deadlock) if it violates a constraint. And in practice that's almost never good enough, so you end up having to build the same kind of application-level validation logic that you would have written in a non-RDBMS system anyway.
Re:The database ruins all good ideas by L0stLink on Sunday July 18, 2021 @08:58AM (#27872561)
RDBMS and Non-RDBMS both have there place, I have used both in the same system several times, all for things that they were good at. Transactions allow you to be confident while making complex changes that in case a failure occurs all partial changes will be rolled back. making use of database level validations and enforcing referential integrity is essential for keeping data consistent over the long term and making data migrations easier. Sure in trivial applications you can just dump data to a document store and have a validation soup handle the rest, that too can be implemented cleanly but Knowing when to leverage RDBMS over other DBMS is an essential skill for an engineer. Because that is how you build scalable systems. Not by throwing JSON stores at everything and calling it a day.
Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @09:19AM (#27872675)
If scalability is your concern then you can't use any of the supposedly core features of an RDBMS, since fundamentally there is no way to have a transaction across multiple nodes without solving a much bigger problem.

Validation is vital but the datastore is not the place to do it, because handling invalid data by dropping it on the floor is almost never the right behaviour.

There is no substitute for actually understanding your data model, but once you do 99% of the time you'll find using an RDBMS comes with minimal benefits and significant costs.

Re:The database ruins all good ideas by simonw on Sunday July 18, 2021 @01:00PM (#27874496)
My experience is the exact opposite: once you understand your data model, 99% of the time you will find that NOT using a RDBMS comes with minimal benefits and significant costs.
Re:The database ruins all good ideas by L0stLink on Sunday July 18, 2021 @05:10PM (#27876707)
It is extremely difficult to design a good RDBMS schema without understanding the data model, and once you do, it is there documented in its entirety with best in class tooling for anyone else to come along, pickup and be up to speed with it, additionally you don't have to forgo document storage, most if not all modern RDBMS suppord json(b) types.
Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @08:58PM (#27878163)
RDBMS tooling is a long way away from best-in-class, and the data model is extremely awkward in a way that actually distorts your modelling (no collection columns, no sum types...) and there's no real support for keeping track of schema evolution. I agree that recording your schema model explicitly and keeping track of it is very important (using something like Avro's schema registry), but RDBMS tools are not actually that great at it and using an RDBMS brings in a lot of other baggage.
Re:The database ruins all good ideas by tarsinge on Sunday July 18, 2021 @10:24AM (#27873160)
The point is that you can go pretty far on a single cluster (GitLab example). That 99% figure is trivially wrong in that case.
Re:The database ruins all good ideas by justinclift on Sunday July 18, 2021 @08:39AM (#27872420)
Why would you be input data in a way that violates referential integrity? That would be pretty bizarre.

That being said, if your code doesn't catch and handle errors, then there's a couple of deeper problems already.

Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @09:15AM (#27872654)
> Why would you be input data in a way that violates referential integrity? That would be pretty bizarre.

Well if you can't get input that violates that integrity then what are you gaining by enforcing that integrity?

> That being said, if your code doesn't catch and handle errors, then there's a couple of deeper problems already.

Sure, but how can you do error handling without data storage, given that your application itself is stateless? Maybe you retry the failure a few times, but ultimately your only option is to drop the data on the floor, which is almost always not what you want.

Re:The database ruins all good ideas by kayodelycaon on Sunday July 18, 2021 @09:29AM (#27872742)
If you allow invalid records and multiple versions of a schema in a collection, you will forever being paying for that mistake every time you read from the database.
Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @09:08PM (#27878210)
If you're fine with just dropping that invalid data, you can always just delete it at any point in the future. (Yes, that should horrify you - but so should the idea of just erroring and dropping it when someone first tried to write it, which is what an RDBMS would do). The overall effect is the same either way.

Fixing up data that was written wrongly takes work, there's no getting away from that. But if you have a record of the original write attempt then at least you can do that work and recover the data (if you decide the cost/benefit is worth it). Whereas if you have the datastore reject it at write time then you're SOL before you've even started.

Re:The database ruins all good ideas by CRConrad on Wednesday July 21, 2021 @12:30AM (#27902345)
> Yes, that should horrify you - but so should the idea of just erroring and dropping it when someone first tried to write it, which is what an RDBMS would do

Have you ever actually encountered any RDBMS that just silently "drops" your write without informing you, or is this just how you think they work?

Otherwise, isn't this just like any other data write erroring out -- MongoDB driver saying "Service down!", simple flat-file write saying "Disk full!", etc -- in that if your app doesn't react to this, it's not the data store but your code that sucks?

Re:The database ruins all good ideas by justinclift on Sunday July 18, 2021 @12:37PM (#27874298)
> Well if you can't get input that violates that integrity then what are you gaining by enforcing that integrity?

Perhaps I'm wording this badly?

Your application should _not_ be generating data which can't go into the database correctly. If it is, that's a different set of problems. ;)

Re:The database ruins all good ideas by lmm on Sunday July 18, 2021 @09:00PM (#27878174)
What are you claiming the purpose of integrity constraints in the database is? If your application doesn't generate data which can't go in the database correctly, what's the observable difference between a database that enforces constraints and a database that doesn't?
Re:The database ruins all good ideas by CRConrad on Wednesday July 21, 2021 @12:31AM (#27902358)
The difference is that one of them informs you when you've failed at generating valid data to save, and the other doesn't.