10 ms·

It is amazing how many large-scale applications run on a single or a few large RDBMS. It seems like a bad idea at first: surely a single point of failure must b

It is amazing how many large-scale applications run on a single or a few large RDBMS. It seems like a bad idea at first: surely a single point of failure must be bad for availability and scalability? But it turns out you can achieve excellent availability using simple replication and failover, and you can get huge database instances from the cloud providers. You can basically serve the entire world with a single supercomputer running Postgres and a small army of stateless app servers talking to it.

dijit 4y ago
Even when it’s not a cloud provider, in fact, especially when it’s not a cloud provider: you can achieve insane scale from single instances. Of course these systems have warm standbys, dedicated backup infrastructure and so it’s not really a “single machine”; but I’ve seen 80TiB Postgres instances back in 2011.
- cogman10 4y ago
  We are currently pushing close to 80tb mssql on prem instances. The biggest issue we have with these giant dbs is they require pretty massive amounts of RAM. That's currently our main bottle neck. But I agree. While our design is pretty bad in a few ways, the amount of data that we are able to serve from these big DBs is impressive. We have something like 6 dedicated servers for a company with something like 300 apps. A hand full of them hit dedicated dbs. Were I to redesign the system, I'd have more tiny dedicated dbs per app to avoid a lot of the noisy neighbor/scaling problems we've had. But at the same time, It's impressive how far this design has gotten us and appears to have a lot more legs on it.
  - Nathanba 4y ago
    Can I ask you how large tables can generally get before querying becomes slower? I just can't intuitively wrap my head around how tables can grow from 10gb to 100gb and why this wouldnt worsen query performance by x10. Surely you do table partitions or cycle data out into archive tables to keep up the query performance of the more recent table data, correct?
    - cogman10 4y ago
      > I just can't intuitively wrap my head around how tables can grow from 10gb to 100gb and why this wouldnt worsen query performance by x10 Sql server data is stored as a BTree structure. So a 10 -> 100gb growth ends up being roughly a 1/2 query performance slowdown (since it grows by a factor of log n) assuming good indexes are in place. Filtered indexes can work pretty well for improving query performance. But ultimately we do have some tables which are either archived if we can or partitioned if we can't. SQL Server native partitioning is rough if the query patterns are all over the board. The other thing that has helped is we've done a bit of application data shuffling. Moving heavy hitters onto new database servers that aren't as highly utilized. We are currently in the process of getting read only replicas (always on) setup and configured in our applications. That will allow for a lot more load distribution.
      - AtlasBarfed 4y ago
        The issue with b-tree scaling isn't really the lookup performance issues, it is the index update time issues, which is why log structured merge trees were created. EVENTUALLY, yes even read query performance also would degrade, but typically the insert / update load on a typical index is the first limiter.
        abraxas 4y ago
        If there is a natural key and updates are infrequent then table partitioning can help extend the capacity of a table almost indefinitely. There are limitations of course but even for non-insane time series workloads, Postgres with partitioned tables will work just fine.
    - cmckn 4y ago
      Well, any hot table should be indexed (with regards to your access patterns) and, thankfully, the data structures used to implement tables and indexes don't behave linearly :) Of course, if your application rarely makes use of older rows, it could still make sense to offload them to some kind of colder, cheaper storage.
    - 4y ago
strictfp 4y ago
I agree in principle. But one major headache for us has been upgrading the database software without downtime. Is there any solution that does this without major headaches? I would love some out-of-the-box solution.
- brentjanderson 4y ago
  Depends on the database - I know that CockroachDB supports rolling upgrades with zero downtime, as it is built with a multi-primary architecture. For PostgresQL or MySQL/MariaDB, your options are more limited. Here are two that come to mind, there may be more: # The "Dual Writer" approach 1. Spin up a new database cluster on the new version. 2. Get all your data into it (including dual writes to both the old and new version). 3. Once you're confident that the new version is 100% up to date, switch to using it as your primary database. 4. Shut down the old cluster. # The eventually consistent approach 1. Put a queue in front of each service for writes, where each service of your system has its own database. 2. When you need to upgrade the database, stop consuming from the queue, upgrade in place (bringing the DB down temporarily) and resume consumption once things are back online. 3. No service can directly read from another service's database. Eventually consistent caches/projections service reads during normal service operation and during the upgrade. A system like this is more flexible, but suffers from stale reads or temporary service degradation.
  - jtc331 4y ago
    Dual writing has huge downsides: namely you're now moving consistency into the application, and it's almost guaranteed that the databases won't match in any interesting application.
    - aeorgnoieang 4y ago
      I'd think using built-in replication (e.g. PostgreSQL 'logical replication') for 'dual writing' should mostly avoid inconsistencies between the two versions of the DB, no?
      - 4y ago
icedchai 4y ago
Caches also help a ton (redis, memcache...)
- nesarkvechnep 4y ago
  Also HTTP caching. It's always funny to me why people, not you in particular, reach for Redis when they don't even use HTTP caching.
  - thedougd 4y ago
    They didn't build their APIs with an understanding of HTTP verbs (ala RESTful). Mistakes such as POST with a query in body to search for X.
    - nesarkvechnep 4y ago
      POST with a query as payload is not a problem if the search is a resource.
markandrewj 4y ago
Scaling databases vertically, like Oracle DB, in the past was the norm. It is possible to serve a large number of users, and data, from a single instance. There are some things worth considering though. First of all, no matter how reliable your database is, you will have to take it down eventually to do things like upgrades. The other consideration that isn't initially obvious, is how you may hit an upper bound for resources in most modern environments. If your database is sitting on top of a virtual or containerized environment, your single instance database will be limited in resources (CPU/memory/network) to a single node of the cluster. You could also eventually hit the same problem on bare metal. That said there are some very high density systems available. You may also not need the ability to scale as large as I am talking, or choose to shard and scale your database horizontally at later time. If your project gets big enough you might also start wanting to replicate your data to localize it closer to the user. Another strategy might be to cache the data locally to the user. There are positive and negatives with a single node or cluster. If retools database was clustered they would have been able to do a rolling upgrade though.
the8472 4y ago
> scalability You can scale quite far vertically and avoid all the clustering headaches for a long time these days. With EPYCs you can get 128C/256T, 128PCIe lanes (= 32 4x NVMes = ~half a petabyte of low-latency storage, minus whatever you need for your network cards), 4TB of RAM in a single machine. Of course that'll cost you an arm and a leg and maybe a kidney too, but so would renting the equivalent in the cloud.
- synicalx 4y ago
  It's all fun and games with the giant boxen until a faulty PSU blows up a backplane, you have to patch it, the DC catches on fire, support runs out of parts for it, network dies, someone misconfigures something etc etc. Not saying a single giant server won't work, but it does come with it's own set of very difficult-to-solve-once-you-build-it problems.
Vladimof 4y ago
I don't think that it's amazing.... I think that the new and shinny databases tried to make you think that it was not possible...
- eastbound 4y ago
  React makes us believe everything must have 1-2s response to clicks and the maximum table size is 10 rows. When I come back to web 1.0 apps, I’m often surprised that it does a round-trip to the server in less than 200ms, and reloads the page seamlessly, including a full 5ms SQL query for 5k rows and returned them in the page (=a full 1MB of data, with basically no JS).
  - Nextgrid 4y ago
    There’s shit tons of money to be made for both startups and developers if they convince us that problems solved decades ago aren’t actually solved so they can sell you their solution instead (which in most cases will have recurring costs and/or further maintenance).
  - Existenceblinks 4y ago
    Plus, HTML compression on wire is insane, easily 50%+.
lazide 4y ago
Well, it's always been that way. The big names started using no-sql type stuff because their instances got 2-3 orders of magnitude larger, and that didn't work. It adds a lot of other overhead and problems doing all the denormalization though, but if you literally have multi-PB metadata stores, not like you have a choice. Then everyone started copying them without knowing why.... and then everyone forgot how much you can actually do with a normal database. And hardware has been getting better and cheaper, which makes it only more so. Still not a good idea to store multi-PB metadata stores in a single DB though.
- jghn 4y ago
  > Then everyone started copying them without knowing why People tend to have a very bad sense of what constitutes large scale. It usually maps to "larger than the largest thing I've personally seen". So they hear "Use X instead of Y when operating at scale", and all of a sudden we have people implementing distributed datastore for a few MB of data. Having gone downward in scale over the last few years of my career it has been eye opening how many people tell me X won't work due to "our scale", and I point out I have already used X in prior jobs for scale that's much larger than what we have.
  - lazide 4y ago
    100% agree. I've also run across many cases where no-one bothered to even attempt any benchmarks or load tests on anything (either old or new solutions), compared latency, optimize anything, etc. Sometimes making 10+ million dollar decisions off that gut feel with literally zero data on what is actually going on. It rarely works out well, but hey, have to leave that opening for competition somehow I guess? And I'm not talking about 'why didn't they spend 6 months optimizing that one call which would save them $50 type stuff'. I mean literally zero idea what is going on, what actual performance issues are, etc.
  - dzhiurgis 4y ago
    Place I’ve recently left had 10M record MongoDB table without indexes which would take tens of seconds to query. Celery was running in cron mode every 2 second or so meaning jobs would just pile up and redis eventually ran out of memory. No one understood why this was happening so just restart everything after pagerduty alert…
rtpg 4y ago
I would love to have stats of real world companies on this front. Stuff like “CRUD enterprise app. 1 large-ish Postgres node. 10k tenants. 100 tables with lots of foreign key lookups, 100gb on disk. Db is… kinda slow, and final web requests take ~1 sec.” The toughest thing is knowing what is normal for multi tenant data with lots of relational info used (compared to more large and popular companies that tend to have relatively simple data models)
A1kmm 4y ago
And a lot of applications can be easily sharded (e.g. between customers). So you can have a read-heavy highly replicated database that says which customer is in which shard, and then most of your writes are easily sharded across RDBMS primaries. NewSQL technology promises to make this more automated, which is definitely a good thing, but unless you are Google or have a use case that needs it, it probably isn't worth adopting it yet until they are more mature.
closeparen 4y ago
I have been on so many interview loops where interviewers faulted the architecture skill or experience of candidates because they talked about having used relational databases or tried to use them in design questions. The attitude “our company = scale and scale = nosql” is prevalent enough that even if you know better, it’s probably in your interest to play the game. It’s the one “scalability fact” everyone knows, and a shortcut to sounding smart in front of management when you can’t grasp or haven’t taken the time to dig in on the details.
jhgb 4y ago
> It is amazing how many large-scale applications run on a single or a few large RDBMS. It seems like a bad idea at first: surely a single point of failure must be bad for availability and scalability? I'm pretty sure that was the whole idea of RDBMS, to separate application from data. You badly lose the very moment when some of your data is in a different place -- on transactions, query planning, security, etc. -- so Codd thought "what if even different applications could use a single company-wide database?" Hence the "have everything in a single database" part should be the last compromise you're forced to make, not the first one.
syngrog66 4y ago
plus caching, indexes and smart code algorithms go a looooooooong way a lot of "kids these days" dont seem to learn that by that I mean young folks born into this new world with endless cloud services and scaling-means-Google propaganda a single modern server-class machine is essentially a supercomputer by 80s standards and too many folks are confused about just how much it can achieve if the software is written correctly

dijit 4y ago

Even when it’s not a cloud provider, in fact, especially when it’s not a cloud provider: you can achieve insane scale from single instances. Of course these systems have warm standbys, dedicated backup infrastructure and so it’s not really a “single machine”; but I’ve seen 80TiB Postgres instances back in 2011.

cogman10 4y ago
We are currently pushing close to 80tb mssql on prem instances. The biggest issue we have with these giant dbs is they require pretty massive amounts of RAM. That's currently our main bottle neck. But I agree. While our design is pretty bad in a few ways, the amount of data that we are able to serve from these big DBs is impressive. We have something like 6 dedicated servers for a company with something like 300 apps. A hand full of them hit dedicated dbs. Were I to redesign the system, I'd have more tiny dedicated dbs per app to avoid a lot of the noisy neighbor/scaling problems we've had. But at the same time, It's impressive how far this design has gotten us and appears to have a lot more legs on it.
- Nathanba 4y ago
  Can I ask you how large tables can generally get before querying becomes slower? I just can't intuitively wrap my head around how tables can grow from 10gb to 100gb and why this wouldnt worsen query performance by x10. Surely you do table partitions or cycle data out into archive tables to keep up the query performance of the more recent table data, correct?
  - cogman10 4y ago
    > I just can't intuitively wrap my head around how tables can grow from 10gb to 100gb and why this wouldnt worsen query performance by x10 Sql server data is stored as a BTree structure. So a 10 -> 100gb growth ends up being roughly a 1/2 query performance slowdown (since it grows by a factor of log n) assuming good indexes are in place. Filtered indexes can work pretty well for improving query performance. But ultimately we do have some tables which are either archived if we can or partitioned if we can't. SQL Server native partitioning is rough if the query patterns are all over the board. The other thing that has helped is we've done a bit of application data shuffling. Moving heavy hitters onto new database servers that aren't as highly utilized. We are currently in the process of getting read only replicas (always on) setup and configured in our applications. That will allow for a lot more load distribution.
    - AtlasBarfed 4y ago
      The issue with b-tree scaling isn't really the lookup performance issues, it is the index update time issues, which is why log structured merge trees were created. EVENTUALLY, yes even read query performance also would degrade, but typically the insert / update load on a typical index is the first limiter.
      - abraxas 4y ago
        If there is a natural key and updates are infrequent then table partitioning can help extend the capacity of a table almost indefinitely. There are limitations of course but even for non-insane time series workloads, Postgres with partitioned tables will work just fine.
  - cmckn 4y ago
    Well, any hot table should be indexed (with regards to your access patterns) and, thankfully, the data structures used to implement tables and indexes don't behave linearly :) Of course, if your application rarely makes use of older rows, it could still make sense to offload them to some kind of colder, cheaper storage.
  - 4y ago

strictfp 4y ago

I agree in principle. But one major headache for us has been upgrading the database software without downtime. Is there any solution that does this without major headaches? I would love some out-of-the-box solution.

brentjanderson 4y ago
Depends on the database - I know that CockroachDB supports rolling upgrades with zero downtime, as it is built with a multi-primary architecture. For PostgresQL or MySQL/MariaDB, your options are more limited. Here are two that come to mind, there may be more: # The "Dual Writer" approach 1. Spin up a new database cluster on the new version. 2. Get all your data into it (including dual writes to both the old and new version). 3. Once you're confident that the new version is 100% up to date, switch to using it as your primary database. 4. Shut down the old cluster. # The eventually consistent approach 1. Put a queue in front of each service for writes, where each service of your system has its own database. 2. When you need to upgrade the database, stop consuming from the queue, upgrade in place (bringing the DB down temporarily) and resume consumption once things are back online. 3. No service can directly read from another service's database. Eventually consistent caches/projections service reads during normal service operation and during the upgrade. A system like this is more flexible, but suffers from stale reads or temporary service degradation.
- jtc331 4y ago
  Dual writing has huge downsides: namely you're now moving consistency into the application, and it's almost guaranteed that the databases won't match in any interesting application.
  - aeorgnoieang 4y ago
    I'd think using built-in replication (e.g. PostgreSQL 'logical replication') for 'dual writing' should mostly avoid inconsistencies between the two versions of the DB, no?
    - 4y ago

icedchai 4y ago

Caches also help a ton (redis, memcache...)

nesarkvechnep 4y ago
Also HTTP caching. It's always funny to me why people, not you in particular, reach for Redis when they don't even use HTTP caching.
- thedougd 4y ago
  They didn't build their APIs with an understanding of HTTP verbs (ala RESTful). Mistakes such as POST with a query in body to search for X.
  - nesarkvechnep 4y ago
    POST with a query as payload is not a problem if the search is a resource.

markandrewj 4y ago

Scaling databases vertically, like Oracle DB, in the past was the norm. It is possible to serve a large number of users, and data, from a single instance. There are some things worth considering though. First of all, no matter how reliable your database is, you will have to take it down eventually to do things like upgrades. The other consideration that isn't initially obvious, is how you may hit an upper bound for resources in most modern environments. If your database is sitting on top of a virtual or containerized environment, your single instance database will be limited in resources (CPU/memory/network) to a single node of the cluster. You could also eventually hit the same problem on bare metal. That said there are some very high density systems available. You may also not need the ability to scale as large as I am talking, or choose to shard and scale your database horizontally at later time. If your project gets big enough you might also start wanting to replicate your data to localize it closer to the user. Another strategy might be to cache the data locally to the user. There are positive and negatives with a single node or cluster. If retools database was clustered they would have been able to do a rolling upgrade though.

the8472 4y ago

> scalability You can scale quite far vertically and avoid all the clustering headaches for a long time these days. With EPYCs you can get 128C/256T, 128PCIe lanes (= 32 4x NVMes = ~half a petabyte of low-latency storage, minus whatever you need for your network cards), 4TB of RAM in a single machine. Of course that'll cost you an arm and a leg and maybe a kidney too, but so would renting the equivalent in the cloud.

synicalx 4y ago
It's all fun and games with the giant boxen until a faulty PSU blows up a backplane, you have to patch it, the DC catches on fire, support runs out of parts for it, network dies, someone misconfigures something etc etc. Not saying a single giant server won't work, but it does come with it's own set of very difficult-to-solve-once-you-build-it problems.

Vladimof 4y ago

I don't think that it's amazing.... I think that the new and shinny databases tried to make you think that it was not possible...

eastbound 4y ago
React makes us believe everything must have 1-2s response to clicks and the maximum table size is 10 rows. When I come back to web 1.0 apps, I’m often surprised that it does a round-trip to the server in less than 200ms, and reloads the page seamlessly, including a full 5ms SQL query for 5k rows and returned them in the page (=a full 1MB of data, with basically no JS).
- Nextgrid 4y ago
  There’s shit tons of money to be made for both startups and developers if they convince us that problems solved decades ago aren’t actually solved so they can sell you their solution instead (which in most cases will have recurring costs and/or further maintenance).
- Existenceblinks 4y ago
  Plus, HTML compression on wire is insane, easily 50%+.

lazide 4y ago

Well, it's always been that way. The big names started using no-sql type stuff because their instances got 2-3 orders of magnitude larger, and that didn't work. It adds a lot of other overhead and problems doing all the denormalization though, but if you literally have multi-PB metadata stores, not like you have a choice. Then everyone started copying them without knowing why.... and then everyone forgot how much you can actually do with a normal database. And hardware has been getting better and cheaper, which makes it only more so. Still not a good idea to store multi-PB metadata stores in a single DB though.

jghn 4y ago
> Then everyone started copying them without knowing why People tend to have a very bad sense of what constitutes large scale. It usually maps to "larger than the largest thing I've personally seen". So they hear "Use X instead of Y when operating at scale", and all of a sudden we have people implementing distributed datastore for a few MB of data. Having gone downward in scale over the last few years of my career it has been eye opening how many people tell me X won't work due to "our scale", and I point out I have already used X in prior jobs for scale that's much larger than what we have.
- lazide 4y ago
  100% agree. I've also run across many cases where no-one bothered to even attempt any benchmarks or load tests on anything (either old or new solutions), compared latency, optimize anything, etc. Sometimes making 10+ million dollar decisions off that gut feel with literally zero data on what is actually going on. It rarely works out well, but hey, have to leave that opening for competition somehow I guess? And I'm not talking about 'why didn't they spend 6 months optimizing that one call which would save them $50 type stuff'. I mean literally zero idea what is going on, what actual performance issues are, etc.
- dzhiurgis 4y ago
  Place I’ve recently left had 10M record MongoDB table without indexes which would take tens of seconds to query. Celery was running in cron mode every 2 second or so meaning jobs would just pile up and redis eventually ran out of memory. No one understood why this was happening so just restart everything after pagerduty alert…

rtpg 4y ago

I would love to have stats of real world companies on this front. Stuff like “CRUD enterprise app. 1 large-ish Postgres node. 10k tenants. 100 tables with lots of foreign key lookups, 100gb on disk. Db is… kinda slow, and final web requests take ~1 sec.” The toughest thing is knowing what is normal for multi tenant data with lots of relational info used (compared to more large and popular companies that tend to have relatively simple data models)

A1kmm 4y ago

And a lot of applications can be easily sharded (e.g. between customers). So you can have a read-heavy highly replicated database that says which customer is in which shard, and then most of your writes are easily sharded across RDBMS primaries. NewSQL technology promises to make this more automated, which is definitely a good thing, but unless you are Google or have a use case that needs it, it probably isn't worth adopting it yet until they are more mature.

closeparen 4y ago

I have been on so many interview loops where interviewers faulted the architecture skill or experience of candidates because they talked about having used relational databases or tried to use them in design questions. The attitude “our company = scale and scale = nosql” is prevalent enough that even if you know better, it’s probably in your interest to play the game. It’s the one “scalability fact” everyone knows, and a shortcut to sounding smart in front of management when you can’t grasp or haven’t taken the time to dig in on the details.

jhgb 4y ago

> It is amazing how many large-scale applications run on a single or a few large RDBMS. It seems like a bad idea at first: surely a single point of failure must be bad for availability and scalability? I'm pretty sure that was the whole idea of RDBMS, to separate application from data. You badly lose the very moment when some of your data is in a different place -- on transactions, query planning, security, etc. -- so Codd thought "what if even different applications could use a single company-wide database?" Hence the "have everything in a single database" part should be the last compromise you're forced to make, not the first one.

syngrog66 4y ago

plus caching, indexes and smart code algorithms go a looooooooong way a lot of "kids these days" dont seem to learn that by that I mean young folks born into this new world with endless cloud services and scaling-means-Google propaganda a single modern server-class machine is essentially a supercomputer by 80s standards and too many folks are confused about just how much it can achieve if the software is written correctly