6 ms·

This is absolutely true - when I was at Bitbucket (ages ago at this point) and we were having issues with our DB server (mostly due to scaling), almost everyone

by belak 4y ago

This is absolutely true - when I was at Bitbucket (ages ago at this point) and we were having issues with our DB server (mostly due to scaling), almost everyone we talked to said "buy a bigger box until you can't any more" because of how complex (and indirectly expensive) the alternatives are - sharding and microservices both have a ton more failure points than a single large box. I'm sure they eventually moved off that single primary box, but for many years Bitbucket was run off 1 primary in each datacenter (with a failover), and a few read-only copies. If you're getting to the point where one database isn't enough, you're either doing something pretty weird, are working on a specific problem which needs a more complicated setup, or have grown to the point where investing in a microservice architecture starts to make sense.

altdataseller 4y ago
What if your product simply stores a lot of data (ie a search engine) How is that weird?
- rmbyrro 4y ago
  a search engine won't need joins, but other things (ie text indexing) that can be split in a relatively easier way.
- zasdffaa 4y ago
  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.
  - altdataseller 4y ago
    What if you are using the database as a system of record to index into a real search engine like Elasticsearch? For a product where you have tons of data to search from (ie text from web pages)
    - IggleSniggle 4y ago
      In regards to Elasticsearch, you basically opt-in to which behavior you want/need. You end up in the same place: potentially losing some data points or introducing some "fuzziness" to the results in exchange for speed. When you ask Elasticsearch to behave in a guaranteed atomic manner across all records, performing locks on data, you end up with similar constraints as in a RDBMS. Elasticsearch is for search. If you're asking about "what if you use an RDBMS as a pointer to Elasticsearch" then I guess I would ask: why would you do this? Elasticsearch can be used as a system of record. You could use an RDBMS over top of Elasticsearch without configuring Elasticsearch as a system of record, but then you would be lying when you refer to your RDBMS as a "system of record." It's not a "system of record" for your actual data, just a record of where pointers to actual data were at one point in time. I feel like I must be missing what you're suggesting here.
      - altdataseller 4y ago
        Having just an Elasticsearch index without also having the data in a primary store like a RDMS is an anti-pattern and not recommended by almost all experts. Whether you want to call it a “system of record”, i wont argue semantics. But the point is, its recommended hacing your data in a primary store where you can index into elasticsearch.
        zasdffaa 4y ago
        Have you a link for this? Never heard of this requirement (but not an elastic user so no surprise).
- belak 4y ago
  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.
- skeeter2020 4y ago
  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.
thayne 4y ago
One issue I've seen with this is that if you have a single, very large database, it can take a very, very long time to restore from backups. Or for that matter just taking backups. I'd be interested to know if anyone has a good solution for that.
- Svenstaro 4y ago
  I found this approach pretty cool in that regard: https://github.com/pgbackrest/pgbackrest https://github.com/pgbackrest/pgbackrest
- rszorness 4y ago
  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.
  - xuki 4y ago
    For MySQL there is xtrabackup - https://www.percona.com/software/mysql-database/percona-xtrabackup https://www.percona.com/software/mysql-database/percona-xtra....
- mike_hearn 4y ago
  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.
  - thayne 4y ago
    > you still have the same amount of data to back up no matter what But you can restore/back up the databases in parallel. > 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. I'm not aware of a good way to restore just a few tables from a full db backup. At least that doesn't require copying over all the data (because the backup is stored over the network, not on a local disk). And that may be desirable to recover from say a bug corrupting or deleting a customer's data.

by belak 4y ago

altdataseller 4y ago

What if your product simply stores a lot of data (ie a search engine) How is that weird?

rmbyrro 4y ago
a search engine won't need joins, but other things (ie text indexing) that can be split in a relatively easier way.
zasdffaa 4y ago
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.
- altdataseller 4y ago
  What if you are using the database as a system of record to index into a real search engine like Elasticsearch? For a product where you have tons of data to search from (ie text from web pages)
  - IggleSniggle 4y ago
    In regards to Elasticsearch, you basically opt-in to which behavior you want/need. You end up in the same place: potentially losing some data points or introducing some "fuzziness" to the results in exchange for speed. When you ask Elasticsearch to behave in a guaranteed atomic manner across all records, performing locks on data, you end up with similar constraints as in a RDBMS. Elasticsearch is for search. If you're asking about "what if you use an RDBMS as a pointer to Elasticsearch" then I guess I would ask: why would you do this? Elasticsearch can be used as a system of record. You could use an RDBMS over top of Elasticsearch without configuring Elasticsearch as a system of record, but then you would be lying when you refer to your RDBMS as a "system of record." It's not a "system of record" for your actual data, just a record of where pointers to actual data were at one point in time. I feel like I must be missing what you're suggesting here.
    - altdataseller 4y ago
      Having just an Elasticsearch index without also having the data in a primary store like a RDMS is an anti-pattern and not recommended by almost all experts. Whether you want to call it a “system of record”, i wont argue semantics. But the point is, its recommended hacing your data in a primary store where you can index into elasticsearch.
      - zasdffaa 4y ago
        Have you a link for this? Never heard of this requirement (but not an elastic user so no surprise).
belak 4y ago
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.
skeeter2020 4y ago
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.

thayne 4y ago

One issue I've seen with this is that if you have a single, very large database, it can take a very, very long time to restore from backups. Or for that matter just taking backups. I'd be interested to know if anyone has a good solution for that.

Svenstaro 4y ago
I found this approach pretty cool in that regard: https://github.com/pgbackrest/pgbackrest https://github.com/pgbackrest/pgbackrest
rszorness 4y ago
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.
- xuki 4y ago
  For MySQL there is xtrabackup - https://www.percona.com/software/mysql-database/percona-xtrabackup https://www.percona.com/software/mysql-database/percona-xtra....
mike_hearn 4y ago
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.
- thayne 4y ago
  > you still have the same amount of data to back up no matter what But you can restore/back up the databases in parallel. > 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. I'm not aware of a good way to restore just a few tables from a full db backup. At least that doesn't require copying over all the data (because the backup is stored over the network, not on a local disk). And that may be desirable to recover from say a bug corrupting or deleting a customer's data.