9 ms·

NoSQL are super easy to start with. Then you have a pile of heaping unstructured data that's hard to query, hard to report and gain insight on. Every data task

NoSQL are super easy to start with. Then you have a pile of heaping unstructured data that's hard to query, hard to report and gain insight on. Every data task becomes a programming problem which needs a team to work on to get something that might be stale. With SQL there's a lot of work. But you have a DB that everyone knows. 3rd party tools work. Developer on-boarding is easy. Your boss asks you how many users used feature X in the past month you can actually tell him right away... Performance is actually good and consistent. Your data is also consistent and respects acid principles. The whole write performance of NoSQL DBs is a bit of a crock that doesn't stand the test of scaling. With proper cashing SQL is as performant when done right and the profiling tools are better. Screw no-SQL. They are just Object Oriented DBs (which were a disaster in the 90s) in new clothes.

YZF 5y ago
What's your SQL db of choice where you can scale it up seamlessly beyond a single instance? EDIT: to add ... and with HA/DR and where you keep all the good guarantees, queries etc.?
- FridgeSeal 5y ago
  If the data couldn’t be fully sharded (I.e. independent db’s and replicas), I’d personally choose Cockroach DB for this, I admittedly haven’t had the pleasure of using Cockroach, but I’ve seen alternative approaches and knowing what I wouldn’t do again…
- qaq 5y ago
  Seeing most people go for the cloud what percentage of projects will outgrow say Aurora? and beyond Aurora there is a ton of options like CockroachDB, TiDB, Yugobyte etc.
- belk 5y ago
  CockroachDB, I heard the dev experience of spinning up nodes can be dumbed down to 1 or 2 docker commands, and it's Postgres compatible, continues to have global transactions, although you wont get the same transaction performance as something GPS/clock hardware synced like Spanner, it's still a good self hosted solution. If you're interested, CRDB writes a lot about how they do their transactions on their blog, it's good to know what guarantees you actually get with your DB's transactions.
- nine_k 5y ago
  For what reason do you need to scale it beyond a single instance? If all you need is to alleviate the high read load (many selects), nearly every SQL database (even SQLite!) has a free and supported way to create read replicas, usually out of the box. If you need distributed transactional updates, MySQL / MariaDB has Galera (GPL), and Postgres has Citus (proprietary with a few AGPL parts). And, of course, there is CockroachDB that prioritizes reliability over speed, but is truly distributed out of the box.
  - YZF 5y ago
    Right now I (well, we, my team) uses Hbase and in a previous project I used Cassandra. These are very high scale systems where we need replicated writes (for durability and availability), we need horizontal scalability and performance (write heavy or mixed read/write workloads) trumps transactionality. There aren't a lot of writeups about performance benchmarks between NoSQL and SQL but as far as I know it's not even close to the same ballpark: https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-benchmark/#Benchmarking https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-be... With something like Cassandra you get read after write guarantees that I think you don't get with read replicas and SQL databases but I'm not too familiar with the latest there. Also once you start breaking up your SQL databases into shards you lose all SQL goodness across shards (like joins and other properties). So no free lunches basically. I'd love for there to be something fast, scalable, queryable, transactional etc. etc. Honestly I haven't looked in a while but every open source SQL solution I've seen in the past that claimed to be durable/reliable/scalable doesn't actually do that under failure modes like NoSQL does and pretty much loses either performance or other guarantees or both. Ofcourse NoSQL doesn't solve some of these problems, but at least you know what you're getting into ;)
    - nine_k 5y ago
      I agree! I've seen Cassandra in production, and learned why it was put there. With it, you still get some of the SQL goodness, but most guarantees are only within one shard. With that, you let go of the global ordering, because global ordering / single-point serialization just becomes technically infeasible. At that point you usually don't exactly need them, though. You only need local order within a transaction.
tharkun__ 5y ago
Actually they're even older. When I talked to my dad about RDBMS he was like "weeeelll, sure, there's things like DB2 UDB that do relational but performance wise, nothing beats reading the data straight by key in exactly the format you need.". DB2 UDB: "Initial release: 1987; 34 years ago". I.e. what he would rather use and is sort of the NoSQL equivalent is _even older than that_. Background: he started off with 360 assembler and worked all his working life on IBM Mainframes and the various technologies in and around it. They had it all and they had it before it came to "us". We're just re-inventing most of these things on much cheaper and more open hardware and software.
- xarope 5y ago
  I worked on DB2 UDB in the 90s, and learnt a lot about DBs through all the improvements in isolation levels. Those were the days when you'd actually have to make some judgement calls about setting specific isolation levels to improve performance. Nowadays you just chuck an SSD and several TBs of memory into the works (or talk to someone about some massively horizontally scaled solution, and distract from the real problem at hand).
- dd444fgdfg 5y ago
  what I fail to fathom today is, when I think back to the late 90s, I had various production rdms, SQL Server 6 (or 7, can't remember), running on something like a Pentium 4, with millions of rows of data in various tables... and the performance was... fine. not an issue.
  - foota 5y ago
    Single machine postgres is mostly fine when you're working with less than hundreds of million of rows, less than a few hundred gigs of data, don't need HA, and don't need more than a few thousand QPS. Outside of those constraints is why new things exist imo.
    - crooked-v 5y ago
      Of course, the overwhelming majority of all businesses (including web-centric businesses) work fine within those constraints.
felipeccastro 5y ago
I use Rails and Mongo at work. There is a schema, enforced declaratively at the application layer (mongoid). With Rails Console querying the database ad hoc is even easier than with SQL, thanks to mongoid api and Ruby’s expressiveness. There are no transactions, but single document writes are atomic. Performance is pretty good; the one hardship I run into is that the mongoid api is so high level that you don’t see when you’re firing way too many queries at the dB (but that’s an ODM issue, not mongo’s, other ODMs might not do that).
- foota 5y ago
  I feel like the issue with nosql is that it's largely unnecessary when newsql databases exist.
- erik_seaberg 5y ago
  > schema, enforced declaratively at the application layer Nobody has just one app forever. Any org that gets big enough is going to be uncertain about how many apps depend on a particular database, much less whether they’re all in the same language and bundle the same schema rules. Customer service builds weird support tools, accounting and compliance build weird reports …
  - lmm 5y ago
    Make sure each datastore has a clear owner, and access it via that API rather than directly. If you really must share a datastore, use a schema registry. Sharing between multiple applications doesn't really work for SQL database either (e.g. if you have multiple applications writing to your database in transactions then you're virtually guaranteed to get deadlocks).
    - tdrdt 5y ago
      This only works in theory. In reality sometimes access via an API is way too slow or is lacking flexibility. And I don't get this: "(e.g. if you have multiple applications writing to your database in transactions then you're virtually guaranteed to get deadlocks)" Isn't this why transactions exist in the first place?
lmm 5y ago
> But you have a DB that everyone knows. 3rd party tools work. Developer on-boarding is easy. Not reliably. All the different DBs have subtle misbehaviours and mismatches with the tools. > Performance is actually good and consistent. Lol no. Indexed joins and unindexed joins look exactly the same but have vastly different performance characteristics. And even if you have a query that currently performs well, you never know when or why the query planner will change its mind. > Your data is also consistent and respects acid principles. Only because you drop data on the floor if it's inconsistent, which is rarely a good enough solution in the real world. E.g. someone tries to add a customer but the city has been deleted in the meantime -> they get an error, and the system throws away everything they input about the new customer. You end up having to implement your own dead letter queues etc. and do all the work you would with a NoSQL datastore. > With proper cashing SQL is as performant when done right and the profiling tools are better. Caching makes your performance even more arbitrary and incomprehensible - it works great right until it doesn't. Profiling is great but it's a poor substitute for being able to reason about what the performance of your queries will be. > Screw no-SQL. They are just Object Oriented DBs (which were a disaster in the 90s) in new clothes. On the contrary - the problems with SQL databases are much the same as the problems with object databases.
- EdwardDiego 5y ago
  > Only because you drop data on the floor if it's inconsistent, which is rarely a good enough solution in the real world. E.g. someone tries to add a customer but the city has been deleted in the meantime -> they get an error, and the system throws away everything they input about the new customer. You end up having to implement your own dead letter queues etc. and do all the work you would with a NoSQL datastore. What. Who's letting people delete random shit out of the DB? Why is the origin of this data not validating against the actual data from the DB before sending data? "Hey we have to ensure that customers are selecting valid cities. We have a list of cities in the DB. Let's ensure we cache the cities in the api server so that we're not hitting the DB for every page load for data that will very rarely, if ever, change. Especially since we revoked Dave's DELETE permissions after that last time he deleted stuff from a production database."
  - 5y ago
omegalulw 5y ago
This is not the right way to approach this. NoSQL and SQL both have use cases they are suited for, and saying one is better than the other is wrong. Faster writes of NoSQL does help with scaling - but if your app needs transactions than obviously NoSQL won't work. For reporting, unstructured data is actually not bad. If your report requires you to scan your whole database, then SQL won't necessarily be faster, you have to think about disk performance as well as how complex of a join(s) do you need.
- kaba0 5y ago
  SQL databases can do several levels of transaction “strictness”, so their write speed can be just as fast.
pjmlp 5y ago
No love left for POET? :)

YZF 5y ago

What's your SQL db of choice where you can scale it up seamlessly beyond a single instance? EDIT: to add ... and with HA/DR and where you keep all the good guarantees, queries etc.?

FridgeSeal 5y ago
If the data couldn’t be fully sharded (I.e. independent db’s and replicas), I’d personally choose Cockroach DB for this, I admittedly haven’t had the pleasure of using Cockroach, but I’ve seen alternative approaches and knowing what I wouldn’t do again…
qaq 5y ago
Seeing most people go for the cloud what percentage of projects will outgrow say Aurora? and beyond Aurora there is a ton of options like CockroachDB, TiDB, Yugobyte etc.
belk 5y ago
CockroachDB, I heard the dev experience of spinning up nodes can be dumbed down to 1 or 2 docker commands, and it's Postgres compatible, continues to have global transactions, although you wont get the same transaction performance as something GPS/clock hardware synced like Spanner, it's still a good self hosted solution. If you're interested, CRDB writes a lot about how they do their transactions on their blog, it's good to know what guarantees you actually get with your DB's transactions.
nine_k 5y ago
For what reason do you need to scale it beyond a single instance? If all you need is to alleviate the high read load (many selects), nearly every SQL database (even SQLite!) has a free and supported way to create read replicas, usually out of the box. If you need distributed transactional updates, MySQL / MariaDB has Galera (GPL), and Postgres has Citus (proprietary with a few AGPL parts). And, of course, there is CockroachDB that prioritizes reliability over speed, but is truly distributed out of the box.
- YZF 5y ago
  Right now I (well, we, my team) uses Hbase and in a previous project I used Cassandra. These are very high scale systems where we need replicated writes (for durability and availability), we need horizontal scalability and performance (write heavy or mixed read/write workloads) trumps transactionality. There aren't a lot of writeups about performance benchmarks between NoSQL and SQL but as far as I know it's not even close to the same ballpark: https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-benchmark/#Benchmarking https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-be... With something like Cassandra you get read after write guarantees that I think you don't get with read replicas and SQL databases but I'm not too familiar with the latest there. Also once you start breaking up your SQL databases into shards you lose all SQL goodness across shards (like joins and other properties). So no free lunches basically. I'd love for there to be something fast, scalable, queryable, transactional etc. etc. Honestly I haven't looked in a while but every open source SQL solution I've seen in the past that claimed to be durable/reliable/scalable doesn't actually do that under failure modes like NoSQL does and pretty much loses either performance or other guarantees or both. Ofcourse NoSQL doesn't solve some of these problems, but at least you know what you're getting into ;)
  - nine_k 5y ago
    I agree! I've seen Cassandra in production, and learned why it was put there. With it, you still get some of the SQL goodness, but most guarantees are only within one shard. With that, you let go of the global ordering, because global ordering / single-point serialization just becomes technically infeasible. At that point you usually don't exactly need them, though. You only need local order within a transaction.

tharkun__ 5y ago

Actually they're even older. When I talked to my dad about RDBMS he was like "weeeelll, sure, there's things like DB2 UDB that do relational but performance wise, nothing beats reading the data straight by key in exactly the format you need.". DB2 UDB: "Initial release: 1987; 34 years ago". I.e. what he would rather use and is sort of the NoSQL equivalent is _even older than that_. Background: he started off with 360 assembler and worked all his working life on IBM Mainframes and the various technologies in and around it. They had it all and they had it before it came to "us". We're just re-inventing most of these things on much cheaper and more open hardware and software.

xarope 5y ago
I worked on DB2 UDB in the 90s, and learnt a lot about DBs through all the improvements in isolation levels. Those were the days when you'd actually have to make some judgement calls about setting specific isolation levels to improve performance. Nowadays you just chuck an SSD and several TBs of memory into the works (or talk to someone about some massively horizontally scaled solution, and distract from the real problem at hand).
dd444fgdfg 5y ago
what I fail to fathom today is, when I think back to the late 90s, I had various production rdms, SQL Server 6 (or 7, can't remember), running on something like a Pentium 4, with millions of rows of data in various tables... and the performance was... fine. not an issue.
- foota 5y ago
  Single machine postgres is mostly fine when you're working with less than hundreds of million of rows, less than a few hundred gigs of data, don't need HA, and don't need more than a few thousand QPS. Outside of those constraints is why new things exist imo.
  - crooked-v 5y ago
    Of course, the overwhelming majority of all businesses (including web-centric businesses) work fine within those constraints.

felipeccastro 5y ago

I use Rails and Mongo at work. There is a schema, enforced declaratively at the application layer (mongoid). With Rails Console querying the database ad hoc is even easier than with SQL, thanks to mongoid api and Ruby’s expressiveness. There are no transactions, but single document writes are atomic. Performance is pretty good; the one hardship I run into is that the mongoid api is so high level that you don’t see when you’re firing way too many queries at the dB (but that’s an ODM issue, not mongo’s, other ODMs might not do that).

foota 5y ago
I feel like the issue with nosql is that it's largely unnecessary when newsql databases exist.
erik_seaberg 5y ago
> schema, enforced declaratively at the application layer Nobody has just one app forever. Any org that gets big enough is going to be uncertain about how many apps depend on a particular database, much less whether they’re all in the same language and bundle the same schema rules. Customer service builds weird support tools, accounting and compliance build weird reports …
- lmm 5y ago
  Make sure each datastore has a clear owner, and access it via that API rather than directly. If you really must share a datastore, use a schema registry. Sharing between multiple applications doesn't really work for SQL database either (e.g. if you have multiple applications writing to your database in transactions then you're virtually guaranteed to get deadlocks).
  - tdrdt 5y ago
    This only works in theory. In reality sometimes access via an API is way too slow or is lacking flexibility. And I don't get this: "(e.g. if you have multiple applications writing to your database in transactions then you're virtually guaranteed to get deadlocks)" Isn't this why transactions exist in the first place?

lmm 5y ago

> But you have a DB that everyone knows. 3rd party tools work. Developer on-boarding is easy. Not reliably. All the different DBs have subtle misbehaviours and mismatches with the tools. > Performance is actually good and consistent. Lol no. Indexed joins and unindexed joins look exactly the same but have vastly different performance characteristics. And even if you have a query that currently performs well, you never know when or why the query planner will change its mind. > Your data is also consistent and respects acid principles. Only because you drop data on the floor if it's inconsistent, which is rarely a good enough solution in the real world. E.g. someone tries to add a customer but the city has been deleted in the meantime -> they get an error, and the system throws away everything they input about the new customer. You end up having to implement your own dead letter queues etc. and do all the work you would with a NoSQL datastore. > With proper cashing SQL is as performant when done right and the profiling tools are better. Caching makes your performance even more arbitrary and incomprehensible - it works great right until it doesn't. Profiling is great but it's a poor substitute for being able to reason about what the performance of your queries will be. > Screw no-SQL. They are just Object Oriented DBs (which were a disaster in the 90s) in new clothes. On the contrary - the problems with SQL databases are much the same as the problems with object databases.

EdwardDiego 5y ago
> Only because you drop data on the floor if it's inconsistent, which is rarely a good enough solution in the real world. E.g. someone tries to add a customer but the city has been deleted in the meantime -> they get an error, and the system throws away everything they input about the new customer. You end up having to implement your own dead letter queues etc. and do all the work you would with a NoSQL datastore. What. Who's letting people delete random shit out of the DB? Why is the origin of this data not validating against the actual data from the DB before sending data? "Hey we have to ensure that customers are selecting valid cities. We have a list of cities in the DB. Let's ensure we cache the cities in the api server so that we're not hitting the DB for every page load for data that will very rarely, if ever, change. Especially since we revoked Dave's DELETE permissions after that last time he deleted stuff from a production database."
- 5y ago

omegalulw 5y ago

This is not the right way to approach this. NoSQL and SQL both have use cases they are suited for, and saying one is better than the other is wrong. Faster writes of NoSQL does help with scaling - but if your app needs transactions than obviously NoSQL won't work. For reporting, unstructured data is actually not bad. If your report requires you to scan your whole database, then SQL won't necessarily be faster, you have to think about disk performance as well as how complex of a join(s) do you need.

kaba0 5y ago
SQL databases can do several levels of transaction “strictness”, so their write speed can be just as fast.

pjmlp 5y ago

No love left for POET? :)