12 ms·

What if you didn't need relational queries and was dealing with a large volume of data that needed to be sharded?

sascha_sl 6y ago
If you just need a key-value store, a database like MongoDB is rather oversized. Redis or and a bunch of object storage system (now that they actually include locking) are much easier to operate. As soon as you're back to needing complex queries (with or without relations, Postgres it is again.
- petersellers 6y ago
  Redis is an in-memory datastore first and foremost (with optional persistence). With that said, it's probably not the right tool if you need to store and persist TB-worth of data even if it is key-value. Something like Cassandra is probably a better fit if you have a lot of non-relational data that needs to be persisted and you need it to scale.
  - funcDropShadow 6y ago
    But you need more than a few TiB of stored data for Cassandra to be a net win. I've been part of two inhouse software projects with database in the single digit TiB scale based on Cassandra. Both projects where switched to PostgreSQL and the performance improved for almost everything. Plus all the other advantages of mature relational database. Hosting Cassandra is a whole different league of complication compared to PostgreSQL. So you should be very sure that you actually gain something before choosing it over PostgreSQL.
    - petersellers 6y ago
      Absolutely - pick the right tool for the job. Generally speaking I'd go for PostgreSQL in most cases but there are definitely scenarios where Cassandra would perform better and be more appropriate.
bildung 6y ago
In what scenarios does one work on data without any relations? Honest question - perhaps I'm too used to think in SQL but even with e.g. a completely flat bunch of documents one usually needs at least access control (i.e. document owners, which is a relation).
- dheera 6y ago
  When you get piles of JSON data from somewhere else (e.g. an API response from a provider you subscribe to) and want to store all of it intact, query it, and that somewhere else may insert new fields at any time in the future without telling you. When you have multiple types of objects with differently populated fields that you want to store in a single collection and query across them in currently unknown ways, and where you need flexibility for the unknown.
  - drodgers 6y ago
    That sounds like a great use case for OLAP and fuzzy search solutions. Eg. Dump all of the data to S3 and search it in parallel via AWS Athena. If you want fast fuzzy search, then elasticsearch is built for this. If the data is small enough that you want B-tree indexes (with the accompanying slower writes and lower query latencies), then any SQL database with a JSON column will work.
    - dheera 6y ago
      > Eg. Dump all of the data to S3 and search it in parallel via AWS Athena. Too proprietary. Can't be run locally, on robots, etc. Can't be transferred to other cloud providers. Also S3 is horribly slow to write or delete thousands of records at once.
      - kornish 6y ago
        > Too proprietary. Every major cloud provider has a blob store. Most are S3-interface compatible, or can be fronted by something which is, like Minio. > Can't be run locally, on robots, etc. If the contents of your S3 keys are just line-delineated JSON files, you can easily download those files and run scripts or process them locally. > Can't be transferred to other cloud providers. Again, not true — a tool like rclone handles this case in a single command. Depends on the amount of data, but if anything it's easier to move a bunch of flat files between providers than copying a database backup around. You have to pay for egress bandwidth, though, of course. > Also S3 is horribly slow to write or delete thousands of records at once. If you're keeping one JSON record per S3 key, that is a blatant misuse of the tool and performance will be terrible. On the other hand, if you batch records into files of appropriate sizes, it's very cost-effective to store and query via Athena, Spark on EMR, etc. If you need individual record-level access as identifiable by a key, then you'd probably be better off with Redis or Memcached. (though, those will not be as good for bulk offline processing) It's all about your access patterns.
AmericanChopper 6y ago
As a former Oracle DBA, and general skeptic of most of the use cases the NoSQL ends up getting chosen for, I’ll gladly say that document databases are pretty good at storing documents. I’ve seen them used very well in content management applications, and the document-database-as-a-materialized-view use case can make a lot of sense. Just like graph databases can be very good at storing graphs. As far as the bandwagoning goes though, I think Mongo has taught a lot of people about the strengths of the relational model.
srich36 6y ago
There are plenty of modern, distributed RDBMSes that make sharding transparent to the user (E.g. cockroachDB, yugabyte, vitess, many cloud offerings, etc.). Most NoSQL databases end up adding transactions because they are important, and thus the scale advantages for NoSQL systems over relational databases are diminishing, if remaining at all.
- bushbaba 6y ago
  Latency at p99 is substantially better at scale with a key value lookup in nosql over postgresql. Thing is most people don’t have scale these days. You can get a single box with hundreds of logical cores and many hundreds of TiB of locally attached ssd. Until you exceed that you don’t necessarily have scale.
  - ZephyrBlu 6y ago
    Distributed databases like cockroachDB are KV stores.
    - collyw 6y ago
      MySQL had a Berkley DB engine underneath, that's a KV store too. I wouldn't say that makes /made MySQL a key value store.
  - threeseed 6y ago
    > You can get a single box with hundreds of logical cores and many hundreds of TiB of locally attached ssd And then that box falls over because the entire region fails like just happened yesterday with OVH. Or it just randomly fails like has happened to me with AWS dozens of times. Vertically scaling a database on your own cloud instances is amateur hour. Either use a cloud-managed database or one that is highly available.
    - bushbaba 6y ago
      Sure. And those services support some fairly beefy instance types. Such as https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-rds-for-sql-server-now-supports-x1-and-x1e-instance-types/ https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-rd...
anticristi 6y ago
To me, "warm" (not hot) data falls into one of two boxes: - schema on write: You know what future queries you will ask. Use SQL. - schema on read: You don't know what future queries you will ask. Use object storage. Of course, in practice, part of your data will be schema on write and part on read.
- aidos 6y ago
  Can you explain this more? I’d argue that if you don’t know the patterns you’ll use to access your data, relational is the way to go. That’s the point, no? Flexibility over how you access your data. With an object store you mostly have to have decided upfront which data is collocated together.
  - anticristi 6y ago
    Maybe I explained it badly. :) SQL essentially forces you to first define your data schema, then write data according to that schema. This works great for reading a user profile given an email; or read all comments of all users who are born before 1995. Since you have a schema on write, you can add indexes to optimise these queries. In contrast, if you need to do "anomaly detection" over an opaque dataset (where the definition of "anomaly" moves faster than you can rewrite your schemas), then it's better to just do a full dataset scan and create a schema on read. My 2 cents.
    - aidos 6y ago
      Still not sure I buy it. I can extract a document representation from my relational data that matches your document store at read time. I can effectively choose my schema when I read, there’s no need to rewrite it at the storage layer.
- rwoerz 6y ago
  Maybe I do not get your point but I have a complete opposite opinion. If you do not know about your clients' queries, you are better off with SQL since it enables clients to query exactly the data they need. If you know about future queries, you can shape your object or document storage or db beforehand to match these queries.
amenod 6y ago
I had a project where I just needed to be saving a constant flow of data (id + 100 bytes records) somewhere, no persistence needed, no sharding (single machine) - but lots of it. I first turned to Redis and it worked nicely, but since I had PostgreSQL running, I made a performance test with it too. To my surprise, once I optimized both (memory only, pg WAL disabled, persistent connections,...) there was almost no difference in insert rate, so I ended up using PgSQL only. This is anecdotal, of course, and it is possible that I simply didn't optimize Redis enough (though I did give it my best!). Let me also say that Redis is great otherwise, it was just in this specific case that PostgreSQL surprised me and fulfilled the need itself.
- HelloNurse 6y ago
  Your data-saving test was probably limited by how fast you could save to disk a roughly equal amount of data with the two databases; queries might work and perform more differently.
  - amenod 6y ago
    That's what I meant by "no persistence needed". In both cases, I wasn't actually saving anything to disk. I needed an in-memory storage for a never-ending stream of incoming data that would be read (and purged) by a separate process, no biggie if the data got lost occasionally. I also simply inserted and read the data, no complex queries or anything. Key-value store is all I needed and for this particular case PostgreSQL was as good at it as Redis.
    - kinos 6y ago
      Why did you use a database instead of just creating a global map and running in memory? Sharding?
      - amenod 6y ago
        No - different processes were reading and writing. And once you implement a global map which is accessible from multiple processes, you might as well use an existing solution, like Redis. Or, as I've learned, pgSQL - which gave me the same performance with less moving parts (because I was already using it) when I disabled WAL.

If you just need a key-value store, a database like MongoDB is rather oversized. Redis or and a bunch of object storage system (now that they actually include locking) are much easier to operate. As soon as you're back to needing complex queries (with or without relations, Postgres it is again.

petersellers 6y ago
Redis is an in-memory datastore first and foremost (with optional persistence). With that said, it's probably not the right tool if you need to store and persist TB-worth of data even if it is key-value. Something like Cassandra is probably a better fit if you have a lot of non-relational data that needs to be persisted and you need it to scale.
- funcDropShadow 6y ago
  But you need more than a few TiB of stored data for Cassandra to be a net win. I've been part of two inhouse software projects with database in the single digit TiB scale based on Cassandra. Both projects where switched to PostgreSQL and the performance improved for almost everything. Plus all the other advantages of mature relational database. Hosting Cassandra is a whole different league of complication compared to PostgreSQL. So you should be very sure that you actually gain something before choosing it over PostgreSQL.
  - petersellers 6y ago
    Absolutely - pick the right tool for the job. Generally speaking I'd go for PostgreSQL in most cases but there are definitely scenarios where Cassandra would perform better and be more appropriate.

bildung 6y ago

In what scenarios does one work on data without any relations? Honest question - perhaps I'm too used to think in SQL but even with e.g. a completely flat bunch of documents one usually needs at least access control (i.e. document owners, which is a relation).

dheera 6y ago
When you get piles of JSON data from somewhere else (e.g. an API response from a provider you subscribe to) and want to store all of it intact, query it, and that somewhere else may insert new fields at any time in the future without telling you. When you have multiple types of objects with differently populated fields that you want to store in a single collection and query across them in currently unknown ways, and where you need flexibility for the unknown.
- drodgers 6y ago
  That sounds like a great use case for OLAP and fuzzy search solutions. Eg. Dump all of the data to S3 and search it in parallel via AWS Athena. If you want fast fuzzy search, then elasticsearch is built for this. If the data is small enough that you want B-tree indexes (with the accompanying slower writes and lower query latencies), then any SQL database with a JSON column will work.
  - dheera 6y ago
    > Eg. Dump all of the data to S3 and search it in parallel via AWS Athena. Too proprietary. Can't be run locally, on robots, etc. Can't be transferred to other cloud providers. Also S3 is horribly slow to write or delete thousands of records at once.
    - kornish 6y ago
      > Too proprietary. Every major cloud provider has a blob store. Most are S3-interface compatible, or can be fronted by something which is, like Minio. > Can't be run locally, on robots, etc. If the contents of your S3 keys are just line-delineated JSON files, you can easily download those files and run scripts or process them locally. > Can't be transferred to other cloud providers. Again, not true — a tool like rclone handles this case in a single command. Depends on the amount of data, but if anything it's easier to move a bunch of flat files between providers than copying a database backup around. You have to pay for egress bandwidth, though, of course. > Also S3 is horribly slow to write or delete thousands of records at once. If you're keeping one JSON record per S3 key, that is a blatant misuse of the tool and performance will be terrible. On the other hand, if you batch records into files of appropriate sizes, it's very cost-effective to store and query via Athena, Spark on EMR, etc. If you need individual record-level access as identifiable by a key, then you'd probably be better off with Redis or Memcached. (though, those will not be as good for bulk offline processing) It's all about your access patterns.

AmericanChopper 6y ago

As a former Oracle DBA, and general skeptic of most of the use cases the NoSQL ends up getting chosen for, I’ll gladly say that document databases are pretty good at storing documents. I’ve seen them used very well in content management applications, and the document-database-as-a-materialized-view use case can make a lot of sense. Just like graph databases can be very good at storing graphs. As far as the bandwagoning goes though, I think Mongo has taught a lot of people about the strengths of the relational model.

srich36 6y ago

There are plenty of modern, distributed RDBMSes that make sharding transparent to the user (E.g. cockroachDB, yugabyte, vitess, many cloud offerings, etc.). Most NoSQL databases end up adding transactions because they are important, and thus the scale advantages for NoSQL systems over relational databases are diminishing, if remaining at all.

bushbaba 6y ago
Latency at p99 is substantially better at scale with a key value lookup in nosql over postgresql. Thing is most people don’t have scale these days. You can get a single box with hundreds of logical cores and many hundreds of TiB of locally attached ssd. Until you exceed that you don’t necessarily have scale.
- ZephyrBlu 6y ago
  Distributed databases like cockroachDB are KV stores.
  - collyw 6y ago
    MySQL had a Berkley DB engine underneath, that's a KV store too. I wouldn't say that makes /made MySQL a key value store.
- threeseed 6y ago
  > You can get a single box with hundreds of logical cores and many hundreds of TiB of locally attached ssd And then that box falls over because the entire region fails like just happened yesterday with OVH. Or it just randomly fails like has happened to me with AWS dozens of times. Vertically scaling a database on your own cloud instances is amateur hour. Either use a cloud-managed database or one that is highly available.
  - bushbaba 6y ago
    Sure. And those services support some fairly beefy instance types. Such as https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-rds-for-sql-server-now-supports-x1-and-x1e-instance-types/ https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-rd...

anticristi 6y ago

To me, "warm" (not hot) data falls into one of two boxes: - schema on write: You know what future queries you will ask. Use SQL. - schema on read: You don't know what future queries you will ask. Use object storage. Of course, in practice, part of your data will be schema on write and part on read.

aidos 6y ago
Can you explain this more? I’d argue that if you don’t know the patterns you’ll use to access your data, relational is the way to go. That’s the point, no? Flexibility over how you access your data. With an object store you mostly have to have decided upfront which data is collocated together.
- anticristi 6y ago
  Maybe I explained it badly. :) SQL essentially forces you to first define your data schema, then write data according to that schema. This works great for reading a user profile given an email; or read all comments of all users who are born before 1995. Since you have a schema on write, you can add indexes to optimise these queries. In contrast, if you need to do "anomaly detection" over an opaque dataset (where the definition of "anomaly" moves faster than you can rewrite your schemas), then it's better to just do a full dataset scan and create a schema on read. My 2 cents.
  - aidos 6y ago
    Still not sure I buy it. I can extract a document representation from my relational data that matches your document store at read time. I can effectively choose my schema when I read, there’s no need to rewrite it at the storage layer.
rwoerz 6y ago
Maybe I do not get your point but I have a complete opposite opinion. If you do not know about your clients' queries, you are better off with SQL since it enables clients to query exactly the data they need. If you know about future queries, you can shape your object or document storage or db beforehand to match these queries.

amenod 6y ago

I had a project where I just needed to be saving a constant flow of data (id + 100 bytes records) somewhere, no persistence needed, no sharding (single machine) - but lots of it. I first turned to Redis and it worked nicely, but since I had PostgreSQL running, I made a performance test with it too. To my surprise, once I optimized both (memory only, pg WAL disabled, persistent connections,...) there was almost no difference in insert rate, so I ended up using PgSQL only. This is anecdotal, of course, and it is possible that I simply didn't optimize Redis enough (though I did give it my best!). Let me also say that Redis is great otherwise, it was just in this specific case that PostgreSQL surprised me and fulfilled the need itself.

HelloNurse 6y ago
Your data-saving test was probably limited by how fast you could save to disk a roughly equal amount of data with the two databases; queries might work and perform more differently.
- amenod 6y ago
  That's what I meant by "no persistence needed". In both cases, I wasn't actually saving anything to disk. I needed an in-memory storage for a never-ending stream of incoming data that would be read (and purged) by a separate process, no biggie if the data got lost occasionally. I also simply inserted and read the data, no complex queries or anything. Key-value store is all I needed and for this particular case PostgreSQL was as good at it as Redis.
  - kinos 6y ago
    Why did you use a database instead of just creating a global map and running in memory? Sharding?
    - amenod 6y ago
      No - different processes were reading and writing. And once you implement a global map which is accessible from multiple processes, you might as well use an existing solution, like Redis. Or, as I've learned, pgSQL - which gave me the same performance with less moving parts (because I was already using it) when I disabled WAL.