6 ms·

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? *

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/ https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/

diminish 5y ago
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?
- quonn 5y ago
  Presumably they would then have 100x the cashflow? Enough for a redesign?
- jmchuster 5y ago
  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.
KronisLV 5y ago
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.
- ric2b 5y ago
  > 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.
lmm 5y ago
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.
- kayodelycaon 5y ago
  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.
  - lmm 5y ago
    "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.
    - justinclift 5y ago
      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.
      - lmm 5y ago
        > 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.

diminish 5y ago

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?

quonn 5y ago
Presumably they would then have 100x the cashflow? Enough for a redesign?
jmchuster 5y ago
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.

KronisLV 5y ago

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.

ric2b 5y ago
> 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.

lmm 5y ago

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.

kayodelycaon 5y ago
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.
- lmm 5y ago
  "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.
  - justinclift 5y ago
    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.
    - lmm 5y ago
      > 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.