5 ms·

The issue that I frequently run into is not that I'm looking for a fancy distributed/sharded database because of reasons of performance, but because I need to s

by heipei 7y ago

The issue that I frequently run into is not that I'm looking for a fancy distributed/sharded database because of reasons of performance, but because I need to store large amounts of data in a way that allows me to grow this datastore by "just adding boxes" while still retaining a few useful database features. I'd love to use Postgres but eventually my single server will run out of disk space. Now, one approach is to just dismiss this use-case by pointing at DynamoDB and similar offerings. But if for some reason you can't use these hosted platforms, what do you use instead? For search, ElasticSearch fortunately fits the bill, the "just keep adding boxes" concept works flawlessly, operating it is a breeze. But you probably don't want to use ElasticSearch as your primary datastore, so what do you use there? I had terrible experiences operating a sharded MongoDB cluster and my next attempt will be using something like ScyllaDB/Cassandra instead since operations seem to require much less work and planning. What other databases would offer that no-advance-planning scaling capability? Somewhat unrelated, by I often wonder what one were to use for a sharded/distributed blob store that offers basic operations like "grep across all blobs" with different query-performance than a real-time search index like ElasticSearch. Would one have to use Hadoop or are there any alternatives which require little operational effort?

momirlan 7y ago
yup, if you have infinite time hadoop will fit the bill nicely. but looking at the cost/skills to operate, maybe Elasticsearch is still the better offering
DelightOne 7y ago
What is the difference between one server with many TB vs multiple servers with less space?
- Drdrdrq 7y ago
  With multiple servers you can add space to each of them. With a single one there is a much lower limit to what you can do - that's the idea behind vertical/horizontal scalability. That, and the systems with multiple nodes can be made more reliable than single node servers.
- d_t_w 7y ago
  Availability is one, most distributed systems also replicate.
- cjp222 7y ago
  If the data is active, it's not enough to just throw more storage on the server. More storage means more of other things: Memory. I/O bandwidth. Processing power. Could keep adding those as well, but eventually it's faster and much cheaper to add additional servers.
- scurvy 7y ago
  Backups are easier/faster? A machine with a 5TB table will take forever to dump with a single thread, but 5 servers with 1TB shards will dump it more quickly.
- AkshatM 7y ago
  Vertical scaling (getting a beefier machine) is less preferable than horizontal scaling in the case of cloud providers, because a) it usually costs more to upgrade instance types than to run multiple smaller instance types, and b) eventually you hit a ceiling of available instance types as you grow. But you can still make this work. In our case, we'd ended up going the first route, and we ended up adding AWS EBS as the filesystem block store for our Postgres database, which is easy to resize dynamically without incurring downtime or other issues. The downside of a vertical scaling approach is, well, you don't get HA "for free". You have to manually configure followers and standby nodes for the sole machine. You have to worry about your own replication. If failover and management is abstracted out for you, as in RDS, then it's easy to live with - otherwise very painful. tl;dr go with managed DBs if you just need a DB and don't particularly have to optimise query performance outside of DB config.
- heipei 7y ago
  One server is still going to run out eventually. To give you a very concrete example: The dedicated boxes I use at Hetzner have 2x1TB NVMe SSD by default. I can order additional disks for sure, but even so you'll struggle to get above a few TB of NVMe per box. But, adding another box is cheap and easy. Plus, a single server with many TB is a big single-point-of-failure, and if you want to scale it (vertically) you still have to take it down.
bdcravens 7y ago
Clickhouse seems to be another great option for what you've described.
- MrBuddyCasino 7y ago
  He needs updates, and Clickhouse isn't really made for that. But otherwise I agree.
besus 7y ago
Checkout CockroachDB if you want to have the 'add a node' for additional storage option like MongoDB has. It's Postgres compliant, for the most part, and has a license that most of us can live with for the projects we build.
e12e 7y ago
> I need to store large amounts of data What kind of data and amounts do you frequently encounter that is a good fit for relational storage but difficult to fit in a single server? Honestly curious.
- heipei 7y ago
  It's not relational but key-value based, but I do need things like "update" operations, hence I can't use a simple blob store.
  - e12e 7y ago
    Thank you for taking the time to answer! IMNHO key-value can be a great fit for relational storage. But I'm still curious about what(why) and how much... Servers can grow pretty big these days. And you say update... Across keys, or some keys depend on others? Otherwise it sounds like a perfect fit for sharding with reduced constraints/requirements.
rubyn00bie 7y ago
Try FoundationDB it's a distributed ACID key value store, can have databases scale up to 100TB, scales horizontally, and can handle an enormous amount of concurrent writes and reads while maintaining relatively low latency per read/write. You can even use the Foundation Document Layer which is API compatible with Mongo. It definitely takes some getting used to, but I think it's pretty fucking great, once you do.
- heipei 7y ago
  Yeah, I was super excited when FoundationDB was made Open Source, but since then I haven't really heard anything about it or anyone using it in production, even less so with the Document-DB layer (Mongo). Any experiences you'd like to share?
  - Graphguy 7y ago
    Lots of people using it in production. Snowflake, IBM Cloud, Goldman, VMWare. CouchDB is re-architecting onto it, https://youtu.be/SjXyVZZFkBg https://youtu.be/SjXyVZZFkBg
  - rubyn00bie 7y ago
    I love it personally. I think right now those who are using it are using it as their secret sauce. Its essentially what people want from BigTable or one of the other proprietary offerings from the cloud providers, except actually open and free. I think the biggest reason for a lack of noise about it, is the overhead of learning it is pretty high. You're not going to find folks writing their first "Nodejs + Express" applications using it. Additionally, you really have to know why most distributed databases suck ass to know why FoundationDB is so good. Example: I have a cluster of three VMs running FDB on my home server, and over the past week I've accidentally hit the power switch three or four times. At no point did I have data loss in the cluster, the cluster "immediately" comes back up, and is ready to go. Adding machines to the cluster is unbelievably easy, especially if you have ever even tried grokking how to horizontally scale PSQL. I'm close to releasing an Elixir-based Entity Layer which is pretty uhhh, ~~shitty~~ lacking, at this point feature wise, but it does make storing structured data a bit easier. I'm hoping it'll be more useful for helping people learn how to use FoundationDB than something folks are putting into production (though I'm dog-fooding it).
psds2 7y ago
Have you considered disaggregating your storage from your compute and using something like NVMe-oF or a traditional iscsi or fiber channel SAN to mount storage to your database server? With a block level storage fabric you can fail over instantly, run read only replicas and even support distributed writes in some cases.
- adev_ 7y ago
  > With a block level storage fabric you can fail over instantly, run read only replicas and even support distributed writes in some cases. Yes, but it's also a wonderful way to corrupt a DB as soon as anything goes wrong in your storage fabric system. And this type of corruption is generally not the recoverable one.
  - p_l 7y ago
    Combine it with a data-safety oriented systems like ZFS and your only issue will be downscaling storage without downtime (though that can also be worked around, if in slightly less safe manner, by putting ZVOLs on thin allocations on SAN)
  - pbowyer 7y ago
    > Yes, but it's also a wonderful way to corrupt a DB as soon as anything goes wrong in your storage fabric system. Why is this more likely to corrupt a DB than having it on local disks, when something goes wrong?
    - adev_ 7y ago
      > Why is this more likely to corrupt a DB than having it on local disks, when something goes wrong? Local filesystems (ext4, xfs) have been designed to run over reliable internal bus and over spinning disk. They are (almost) able to stand most of the outage happening in this context ( powercut, corrupted block, missing flush ). Put them over a non-reliable "normal" network, where you can get "savage" cable unplugged, faulty controller, paquet loss, out of order delivery, buggy middle box and you explode the number of scenario that can go wrong and will go wrong. Block level I/O virtualization is amazingly useful, but (in my mind) it should be used with care... I already heard of case in prod where the Master DB, the Slave DB and backup finished all in the same virtualized block-level storage.... try to guess what happened next.
saberience 7y ago
No one ever mentions Couchbase here, I guess it's a bit under the radar despite being used by so many big companies. I used it back when I was at Blizzard and thought it was pretty amazing. It's sort of like, what MongoDB could be if it was actually good. Ridiculous speed and scalability, and the new versions have included analytics and full-text search. Give it a look.
- jazoom 7y ago
  Not open source? Not even transparent pricing? https://www.couchbase.com/pricing https://www.couchbase.com/pricing
  - zgramana 7y ago
    The Community Edition is open source, though the Enterprise Edition has some proprietary extensions. It’s admittedly not at all obvious from the repo name, but the following is the top level repo for Couchbase Server (which uses Google’s `repo` tool): https://github.com/couchbase/manifest https://github.com/couchbase/manifest
    - jazoom 7y ago
      > proprietary extensions Geez, with all these limitations you may as well use another, proper open source database. https://www.couchbase.com/products/editions https://www.couchbase.com/products/editions
- bbulkow 7y ago
  the question with many databases using rotational based storage engines is not how much you want to store, but how much you want to query. Couch requires 99% cache hit rates for queries, they tell you to add memory if you are lower. which really makes it an in memory database with a disk based backing store, suitable for modest data sizes. i have also never seen a positive jepsen test, so i believe it is known to lose data in split brain modes.
ddorian43 7y ago
See YugabyteDB. Reusing postgresql code and adding sharding with transactions & synchronous replication. Source: work there.
PeterCorless 7y ago
For the TSDB part you might use Scylla standalone, or Scylla+KairosDB. We also work well in tandem with Elastic if you need it for ad hoc queries. We have a two part blog series on the why and how: https://www.scylladb.com/2018/11/28/scylla-and-elasticsearch-part-one/ https://www.scylladb.com/2018/11/28/scylla-and-elasticsearch...

by heipei 7y ago

momirlan 7y ago

yup, if you have infinite time hadoop will fit the bill nicely. but looking at the cost/skills to operate, maybe Elasticsearch is still the better offering

DelightOne 7y ago

What is the difference between one server with many TB vs multiple servers with less space?

Drdrdrq 7y ago
With multiple servers you can add space to each of them. With a single one there is a much lower limit to what you can do - that's the idea behind vertical/horizontal scalability. That, and the systems with multiple nodes can be made more reliable than single node servers.
d_t_w 7y ago
Availability is one, most distributed systems also replicate.
cjp222 7y ago
If the data is active, it's not enough to just throw more storage on the server. More storage means more of other things: Memory. I/O bandwidth. Processing power. Could keep adding those as well, but eventually it's faster and much cheaper to add additional servers.
scurvy 7y ago
Backups are easier/faster? A machine with a 5TB table will take forever to dump with a single thread, but 5 servers with 1TB shards will dump it more quickly.
AkshatM 7y ago
Vertical scaling (getting a beefier machine) is less preferable than horizontal scaling in the case of cloud providers, because a) it usually costs more to upgrade instance types than to run multiple smaller instance types, and b) eventually you hit a ceiling of available instance types as you grow. But you can still make this work. In our case, we'd ended up going the first route, and we ended up adding AWS EBS as the filesystem block store for our Postgres database, which is easy to resize dynamically without incurring downtime or other issues. The downside of a vertical scaling approach is, well, you don't get HA "for free". You have to manually configure followers and standby nodes for the sole machine. You have to worry about your own replication. If failover and management is abstracted out for you, as in RDS, then it's easy to live with - otherwise very painful. tl;dr go with managed DBs if you just need a DB and don't particularly have to optimise query performance outside of DB config.
heipei 7y ago
One server is still going to run out eventually. To give you a very concrete example: The dedicated boxes I use at Hetzner have 2x1TB NVMe SSD by default. I can order additional disks for sure, but even so you'll struggle to get above a few TB of NVMe per box. But, adding another box is cheap and easy. Plus, a single server with many TB is a big single-point-of-failure, and if you want to scale it (vertically) you still have to take it down.

bdcravens 7y ago

Clickhouse seems to be another great option for what you've described.

MrBuddyCasino 7y ago
He needs updates, and Clickhouse isn't really made for that. But otherwise I agree.

besus 7y ago

Checkout CockroachDB if you want to have the 'add a node' for additional storage option like MongoDB has. It's Postgres compliant, for the most part, and has a license that most of us can live with for the projects we build.

e12e 7y ago

> I need to store large amounts of data What kind of data and amounts do you frequently encounter that is a good fit for relational storage but difficult to fit in a single server? Honestly curious.

heipei 7y ago
It's not relational but key-value based, but I do need things like "update" operations, hence I can't use a simple blob store.
- e12e 7y ago
  Thank you for taking the time to answer! IMNHO key-value can be a great fit for relational storage. But I'm still curious about what(why) and how much... Servers can grow pretty big these days. And you say update... Across keys, or some keys depend on others? Otherwise it sounds like a perfect fit for sharding with reduced constraints/requirements.

rubyn00bie 7y ago

Try FoundationDB it's a distributed ACID key value store, can have databases scale up to 100TB, scales horizontally, and can handle an enormous amount of concurrent writes and reads while maintaining relatively low latency per read/write. You can even use the Foundation Document Layer which is API compatible with Mongo. It definitely takes some getting used to, but I think it's pretty fucking great, once you do.

heipei 7y ago
Yeah, I was super excited when FoundationDB was made Open Source, but since then I haven't really heard anything about it or anyone using it in production, even less so with the Document-DB layer (Mongo). Any experiences you'd like to share?
- Graphguy 7y ago
  Lots of people using it in production. Snowflake, IBM Cloud, Goldman, VMWare. CouchDB is re-architecting onto it, https://youtu.be/SjXyVZZFkBg https://youtu.be/SjXyVZZFkBg
- rubyn00bie 7y ago
  I love it personally. I think right now those who are using it are using it as their secret sauce. Its essentially what people want from BigTable or one of the other proprietary offerings from the cloud providers, except actually open and free. I think the biggest reason for a lack of noise about it, is the overhead of learning it is pretty high. You're not going to find folks writing their first "Nodejs + Express" applications using it. Additionally, you really have to know why most distributed databases suck ass to know why FoundationDB is so good. Example: I have a cluster of three VMs running FDB on my home server, and over the past week I've accidentally hit the power switch three or four times. At no point did I have data loss in the cluster, the cluster "immediately" comes back up, and is ready to go. Adding machines to the cluster is unbelievably easy, especially if you have ever even tried grokking how to horizontally scale PSQL. I'm close to releasing an Elixir-based Entity Layer which is pretty uhhh, ~~shitty~~ lacking, at this point feature wise, but it does make storing structured data a bit easier. I'm hoping it'll be more useful for helping people learn how to use FoundationDB than something folks are putting into production (though I'm dog-fooding it).

psds2 7y ago

Have you considered disaggregating your storage from your compute and using something like NVMe-oF or a traditional iscsi or fiber channel SAN to mount storage to your database server? With a block level storage fabric you can fail over instantly, run read only replicas and even support distributed writes in some cases.

adev_ 7y ago
> With a block level storage fabric you can fail over instantly, run read only replicas and even support distributed writes in some cases. Yes, but it's also a wonderful way to corrupt a DB as soon as anything goes wrong in your storage fabric system. And this type of corruption is generally not the recoverable one.
- p_l 7y ago
  Combine it with a data-safety oriented systems like ZFS and your only issue will be downscaling storage without downtime (though that can also be worked around, if in slightly less safe manner, by putting ZVOLs on thin allocations on SAN)
- pbowyer 7y ago
  > Yes, but it's also a wonderful way to corrupt a DB as soon as anything goes wrong in your storage fabric system. Why is this more likely to corrupt a DB than having it on local disks, when something goes wrong?
  - adev_ 7y ago
    > Why is this more likely to corrupt a DB than having it on local disks, when something goes wrong? Local filesystems (ext4, xfs) have been designed to run over reliable internal bus and over spinning disk. They are (almost) able to stand most of the outage happening in this context ( powercut, corrupted block, missing flush ). Put them over a non-reliable "normal" network, where you can get "savage" cable unplugged, faulty controller, paquet loss, out of order delivery, buggy middle box and you explode the number of scenario that can go wrong and will go wrong. Block level I/O virtualization is amazingly useful, but (in my mind) it should be used with care... I already heard of case in prod where the Master DB, the Slave DB and backup finished all in the same virtualized block-level storage.... try to guess what happened next.

saberience 7y ago

No one ever mentions Couchbase here, I guess it's a bit under the radar despite being used by so many big companies. I used it back when I was at Blizzard and thought it was pretty amazing. It's sort of like, what MongoDB could be if it was actually good. Ridiculous speed and scalability, and the new versions have included analytics and full-text search. Give it a look.

jazoom 7y ago
Not open source? Not even transparent pricing? https://www.couchbase.com/pricing https://www.couchbase.com/pricing
- zgramana 7y ago
  The Community Edition is open source, though the Enterprise Edition has some proprietary extensions. It’s admittedly not at all obvious from the repo name, but the following is the top level repo for Couchbase Server (which uses Google’s `repo` tool): https://github.com/couchbase/manifest https://github.com/couchbase/manifest
  - jazoom 7y ago
    > proprietary extensions Geez, with all these limitations you may as well use another, proper open source database. https://www.couchbase.com/products/editions https://www.couchbase.com/products/editions
bbulkow 7y ago
the question with many databases using rotational based storage engines is not how much you want to store, but how much you want to query. Couch requires 99% cache hit rates for queries, they tell you to add memory if you are lower. which really makes it an in memory database with a disk based backing store, suitable for modest data sizes. i have also never seen a positive jepsen test, so i believe it is known to lose data in split brain modes.

ddorian43 7y ago

See YugabyteDB. Reusing postgresql code and adding sharding with transactions & synchronous replication. Source: work there.

PeterCorless 7y ago

For the TSDB part you might use Scylla standalone, or Scylla+KairosDB. We also work well in tandem with Elastic if you need it for ad hoc queries. We have a two part blog series on the why and how: https://www.scylladb.com/2018/11/28/scylla-and-elasticsearch-part-one/ https://www.scylladb.com/2018/11/28/scylla-and-elasticsearch...