8 ms·

I used both NoSQL and SQL databases. Proper replication topologies (e.g. multi-master configurations) and JSON columns killed the NoSQL movement. I'm glad the N

I used both NoSQL and SQL databases. Proper replication topologies (e.g. multi-master configurations) and JSON columns killed the NoSQL movement. I'm glad the NoSQL movement existed and forced the SQL camp into out-of-the-box thinking, but I wouldn't go for any NoSQL solution today on a greenfield project.

menzoic 6y ago
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.
YZF 6y ago
Is there a proper replication topology that guarantees all writes survive single node failures e.g. in a 3 node cluster and and reads also as expected? i.e. any sequence of reads and writes execute correctly in any single node failure scenario?
solidasparagus 6y ago
I'm less sure about this. One advantage of NoSQL is that the serverless offerings blow away any SQL DB-as-a-service offering that I've seen. I think that's because NoSQL databases give you minimal, but easy-to-reason-about scaling properties whereas SQL databases do more for you, but at the cost of greatly increased complexity. I think pretty highly of AWS Aurora Serverless and the UX is still garbage in comparison to S3 or DynamoDB. Now if you start trying to add transactions to a key-value store based architecture, NoSQL gets pretty ugly, but that's misusing the tool IMO. Most of my projects still rely on NoSQL DBs (usually S3) because they have really excellent UX and I have plenty of data where I don't need relational properties.
- remus 6y ago
  Frankly it sounds like you just don't need a relational database. Their whole selling point is the strong guarantees they provide about your data, and if you aren't using those then you're basically just writing files to disk in a very convoluted way.
  - solidasparagus 6y ago
    You're absolutely right, I don't need a relational database. There are many use cases that don't need relations and avoiding the complexity introduced by RDBMS features I don't need is where NoSQL shines.
- valzam 6y ago
  I think when most people say NoSQL they don't mean S3 (even though it is like a database). In general I think - S3 - Redis/Memcached - Elasticsearch aren't really NoSQL databases in the Marketing sense since they fulfil such a specific purpose.
schoolornot 6y ago
Postgres doesn't have multi-master. That's the problem.
- dijit 6y ago
  Pedantically; it does, but you pay for it: https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndquadrant/ https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndqua...
- vlod 6y ago
  I believe CockroachDB can do that. Would be a suitable as it's mostly postgres wire compatible.
collyw 6y ago
I am stuck maintaining a mongo monstrosity.
psychometry 6y ago
No SQL database has the anywhere near the search capabilities of Elasticsearch/Lucene. I also wouldn't choose Postgres for storing logs, either, but I would use a document store (i.e. ELK stack).
- citrin_ru 6y ago
  ClickHouse suits well for string logs, especially if a log message have some fixed structure which can be mapped to separate columns. And most logs have some number of predefined fields which are easy to map to columns (e. g. timestamp, IP, request time, e. t. c.). You can store free-form JSON which doesn't have per-defined schema in a string column, but ELK would be probably better in this case. And then you can query logs using all power of SQL.

menzoic 6y ago

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.

YZF 6y ago

Is there a proper replication topology that guarantees all writes survive single node failures e.g. in a 3 node cluster and and reads also as expected? i.e. any sequence of reads and writes execute correctly in any single node failure scenario?

solidasparagus 6y ago

I'm less sure about this. One advantage of NoSQL is that the serverless offerings blow away any SQL DB-as-a-service offering that I've seen. I think that's because NoSQL databases give you minimal, but easy-to-reason-about scaling properties whereas SQL databases do more for you, but at the cost of greatly increased complexity. I think pretty highly of AWS Aurora Serverless and the UX is still garbage in comparison to S3 or DynamoDB. Now if you start trying to add transactions to a key-value store based architecture, NoSQL gets pretty ugly, but that's misusing the tool IMO. Most of my projects still rely on NoSQL DBs (usually S3) because they have really excellent UX and I have plenty of data where I don't need relational properties.

remus 6y ago
Frankly it sounds like you just don't need a relational database. Their whole selling point is the strong guarantees they provide about your data, and if you aren't using those then you're basically just writing files to disk in a very convoluted way.
- solidasparagus 6y ago
  You're absolutely right, I don't need a relational database. There are many use cases that don't need relations and avoiding the complexity introduced by RDBMS features I don't need is where NoSQL shines.
valzam 6y ago
I think when most people say NoSQL they don't mean S3 (even though it is like a database). In general I think - S3 - Redis/Memcached - Elasticsearch aren't really NoSQL databases in the Marketing sense since they fulfil such a specific purpose.

schoolornot 6y ago

Postgres doesn't have multi-master. That's the problem.

dijit 6y ago
Pedantically; it does, but you pay for it: https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndquadrant/ https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndqua...
vlod 6y ago
I believe CockroachDB can do that. Would be a suitable as it's mostly postgres wire compatible.

collyw 6y ago

I am stuck maintaining a mongo monstrosity.

psychometry 6y ago

No SQL database has the anywhere near the search capabilities of Elasticsearch/Lucene. I also wouldn't choose Postgres for storing logs, either, but I would use a document store (i.e. ELK stack).

citrin_ru 6y ago
ClickHouse suits well for string logs, especially if a log message have some fixed structure which can be mapped to separate columns. And most logs have some number of predefined fields which are easy to map to columns (e. g. timestamp, IP, request time, e. t. c.). You can store free-form JSON which doesn't have per-defined schema in a string column, but ELK would be probably better in this case. And then you can query logs using all power of SQL.