5 ms·

I know there's quite some aversion to NoSQL around here, and generally I don't care much as I'm seldomly dealing directly with databases. But recently, I've be

I know there's quite some aversion to NoSQL around here, and generally I don't care much as I'm seldomly dealing directly with databases. But recently, I've been exposed to a fairly big and complex SQL one with several references between entities and lots, lots of X_has_Y tables. This makes me think that with growing complexity (which seems to be a general trend), NoSQL databases seem more practical at some point, or at least something less rigid than classic relational ones. I'm not saying SQL is obsolete, but it seems like its domain of usefulness is shrinking.

mi100hael 9y ago
I'd argue the opposite is true. NoSQL requires you to be more intentional with your schema design since you can only query keys. As a database grows with complexity, you can find yourself in a world of pain with NoSQL if you all of a sudden need to query something in a different manner. At least with SQL you have more options with WHERE clauses & indexes even if performance might not be top notch.
- HodGreeley 9y ago
  Not sure of others, but not true with Couchbase. You have queries just as flexible as with SQL.
  - philliphaydon 9y ago
    One of the reasons I love PostgreSQLs jsonb type is you get the power of relational database and power of (nosql?) Document database at the same time. i haven’t run into any query I couldn’t write against a jsonb doc. Granted dot notation on json would be nicer than ‘prop’ > ‘prop’ > ‘prop’, but I don’t feel limited.
andrewmutz 9y ago
I don't think the HN community is averse to NoSQL solutions, but is averse to MongoDB in particular.
- erik14th 9y ago
  Everyone here seems to love Postgres and hate on Mongo, I have no technical knowledge to compare the two, so IMO a lot of that love and hate is more towards the "project attitude", MongoDB is a suit, a sellout, with bullshit marketing and all Postgres is like some roots hippie, that cares a lot more about technical values and neglects marketing.
  - acdha 9y ago
    I think most of it was a reaction to the massive marketing push Mongo made. There were so many years of people hyping it as the answer to all of your data storage needs, followed by accounts of users hitting bugs or reimplementing most of a SQL database in code, and the promised performance benefits either didn't materialize or hadn't been necessary in the first place (“web scale” turned out to mean only dozens to hundreds of requests per second for many apps). Meanwhile, Postgres was quietly plugging away adding new features and continuing to deliver solid performance for a wider range of workloads, including better performance on JSON document storage.
  - paulmd 9y ago
    MongoDB accrued a lot of bad-will due to some extremely questionable defaults, which remain defaults to this day. There's no question that you can write a fast database when there's no guarantee that data ever hits the disk, but developers tend not to like it when a database accepts their write and then silently loses data. It's also great for toy problems and 15-minute-demos... but then you inevitably run into its limitations and end up re-implementing a database in your app. Even at its best, there is essentially no reason to choose MongoDB over Postgres with JSONB-type columns. They are essentially the same data model but Postgres gives you better guarantees of data consistency, plus a forward migration path to relational data when the day inevitably arrives when you need to model relationships between entities. At this point Postgres is where most open-source RDBMS development work is concentrated. It's not only a solid codebase, it's piling up features pretty quickly and there are relatively few niches it doesn't fill at least adequately. All of these niches are covered by some commercial products built on top of Postgres (eg EnterpriseDB or CitusDB). It's pretty much a one-stop shop for application development. You can use it for everything from GIS to machine learning [0] pretty efficiently, and it pretty much will just do the right thing without you watching. NoSQL really fits best around the margins, like as an auxiliary system for analytics. There is really almost no use-case where "user inputs data and we lose it" is an acceptable application behavior, so consistency is a business requirement for your master database whether you realize it or not. And consistency across a distributed system is hard so it almost always makes sense to sidestep clustering until the last possible moment. Buying more machine is cheap, replication/failover is a lot easier than consistency between distributed masters, and if you are really up against the wall there are those commercial products that can do this with Postgres. If you want to make an analogy... Oracle is the suit, Postgres is the hardworking small business that is slowly but surely eating up Oracle's lunch, and MongoDB is a trustafarian with a hot-dog detector app. And that's why there's a lot of resentment towards MongoDB. [0]: The 9.x series and 10.0 release have been absolutely jam-packed with new features, it's absurd how fast development is moving at the moment. One of my favorites... indexed cube queries. A cube is a data-cube type, an N-dimensional cube of data. One feature of this is distance queries, which have obvious applications in pattern recognition tasks (eg k-nearest-neighbor). One of the features in 9.6 is index functionality for these, so you can now do indexed KNN searches on your data... https://www.depesz.com/2016/01/10/waiting-for-9-6-cube-extension-knn-support/ https://www.depesz.com/2016/01/10/waiting-for-9-6-cube-exten...
autokad 9y ago
i got downvoted in another thread for saying nosql has a lot of advantages in reducing work like not having to worry about carefully creating indexes when joining billion row tables. someone replied you dont need indexes. i replied I dont know how you plan on running joins on billion row tables in postgress without indexes. got downvoted again. I bet they are still waiting for that join....
- parasubvert 9y ago
  Whether you need indexes really depends on what you’re trying to do. Full table scans are more efficient if you are doing analytics on most of those rows in a time series. If you need 1 row in a billion, yes, you need an index.
  - autokad 9y ago
    if your doing a join on two size N tables, your O(n) cost for each record is N, thus you are doing N^2 lookups. basically reading the entire DB into memory each record.
    - parasubvert 9y ago
      No. Worst case a nested join of two tables M and N would be O(MN). But most real databases would have a merge or hash join which could bring this down to O(M+N) if the tables are already stored in a sorted indexed order. It is also rare the cardinality of these databases will be similar in an analytical query - usually you have one time series “fact table” and a bunch of dimensions to enrich that data. If I’m doing analytics on a time series this gets even better with partition pruning and hash joins or bitmap indexes. And if I have a columnar database, that blows up this whole complexity argument. My point is that, layout and indexes should never be assumed to be one size fits all. Keep in mind if I’m doing analytics I want to bring my time series data into memory at least as a stream, as I need to calculate / filter / transform the records. Not everything is about rendering a page on a website.
- orf 9y ago
  If you're dealing with billion row tables and using mongodb you are way out of your league. Good luck.
matwood 9y ago
> less rigid than classic relational ones Less rigid than what? The schema is going to exist somewhere...
nwatson 9y ago
So you gonna do all those JOINs in code???
- HodGreeley 9y ago
  Part of the point is you don't need so many joins.
parasubvert 9y ago
The point is that the relational model is a fully general purpose mathematical model for managing data with integrity and with ad hock access. Most other models are for high performance of specific access paths and punt integrity management to code, which is a throwback to the 70s. They’re glorified file systems and data structure caches. Mongo has a reputation for losing your data. There are cases where scale and availability kills you and you need something like Cassandra or whatnot. But there are few as flexible and general as Oracle or Postgres.

mi100hael 9y ago

I'd argue the opposite is true. NoSQL requires you to be more intentional with your schema design since you can only query keys. As a database grows with complexity, you can find yourself in a world of pain with NoSQL if you all of a sudden need to query something in a different manner. At least with SQL you have more options with WHERE clauses & indexes even if performance might not be top notch.

HodGreeley 9y ago
Not sure of others, but not true with Couchbase. You have queries just as flexible as with SQL.
- philliphaydon 9y ago
  One of the reasons I love PostgreSQLs jsonb type is you get the power of relational database and power of (nosql?) Document database at the same time. i haven’t run into any query I couldn’t write against a jsonb doc. Granted dot notation on json would be nicer than ‘prop’ > ‘prop’ > ‘prop’, but I don’t feel limited.

andrewmutz 9y ago

I don't think the HN community is averse to NoSQL solutions, but is averse to MongoDB in particular.

erik14th 9y ago
Everyone here seems to love Postgres and hate on Mongo, I have no technical knowledge to compare the two, so IMO a lot of that love and hate is more towards the "project attitude", MongoDB is a suit, a sellout, with bullshit marketing and all Postgres is like some roots hippie, that cares a lot more about technical values and neglects marketing.
- acdha 9y ago
  I think most of it was a reaction to the massive marketing push Mongo made. There were so many years of people hyping it as the answer to all of your data storage needs, followed by accounts of users hitting bugs or reimplementing most of a SQL database in code, and the promised performance benefits either didn't materialize or hadn't been necessary in the first place (“web scale” turned out to mean only dozens to hundreds of requests per second for many apps). Meanwhile, Postgres was quietly plugging away adding new features and continuing to deliver solid performance for a wider range of workloads, including better performance on JSON document storage.
- paulmd 9y ago
  MongoDB accrued a lot of bad-will due to some extremely questionable defaults, which remain defaults to this day. There's no question that you can write a fast database when there's no guarantee that data ever hits the disk, but developers tend not to like it when a database accepts their write and then silently loses data. It's also great for toy problems and 15-minute-demos... but then you inevitably run into its limitations and end up re-implementing a database in your app. Even at its best, there is essentially no reason to choose MongoDB over Postgres with JSONB-type columns. They are essentially the same data model but Postgres gives you better guarantees of data consistency, plus a forward migration path to relational data when the day inevitably arrives when you need to model relationships between entities. At this point Postgres is where most open-source RDBMS development work is concentrated. It's not only a solid codebase, it's piling up features pretty quickly and there are relatively few niches it doesn't fill at least adequately. All of these niches are covered by some commercial products built on top of Postgres (eg EnterpriseDB or CitusDB). It's pretty much a one-stop shop for application development. You can use it for everything from GIS to machine learning [0] pretty efficiently, and it pretty much will just do the right thing without you watching. NoSQL really fits best around the margins, like as an auxiliary system for analytics. There is really almost no use-case where "user inputs data and we lose it" is an acceptable application behavior, so consistency is a business requirement for your master database whether you realize it or not. And consistency across a distributed system is hard so it almost always makes sense to sidestep clustering until the last possible moment. Buying more machine is cheap, replication/failover is a lot easier than consistency between distributed masters, and if you are really up against the wall there are those commercial products that can do this with Postgres. If you want to make an analogy... Oracle is the suit, Postgres is the hardworking small business that is slowly but surely eating up Oracle's lunch, and MongoDB is a trustafarian with a hot-dog detector app. And that's why there's a lot of resentment towards MongoDB. [0]: The 9.x series and 10.0 release have been absolutely jam-packed with new features, it's absurd how fast development is moving at the moment. One of my favorites... indexed cube queries. A cube is a data-cube type, an N-dimensional cube of data. One feature of this is distance queries, which have obvious applications in pattern recognition tasks (eg k-nearest-neighbor). One of the features in 9.6 is index functionality for these, so you can now do indexed KNN searches on your data... https://www.depesz.com/2016/01/10/waiting-for-9-6-cube-extension-knn-support/ https://www.depesz.com/2016/01/10/waiting-for-9-6-cube-exten...

autokad 9y ago

i got downvoted in another thread for saying nosql has a lot of advantages in reducing work like not having to worry about carefully creating indexes when joining billion row tables. someone replied you dont need indexes. i replied I dont know how you plan on running joins on billion row tables in postgress without indexes. got downvoted again. I bet they are still waiting for that join....

parasubvert 9y ago
Whether you need indexes really depends on what you’re trying to do. Full table scans are more efficient if you are doing analytics on most of those rows in a time series. If you need 1 row in a billion, yes, you need an index.
- autokad 9y ago
  if your doing a join on two size N tables, your O(n) cost for each record is N, thus you are doing N^2 lookups. basically reading the entire DB into memory each record.
  - parasubvert 9y ago
    No. Worst case a nested join of two tables M and N would be O(MN). But most real databases would have a merge or hash join which could bring this down to O(M+N) if the tables are already stored in a sorted indexed order. It is also rare the cardinality of these databases will be similar in an analytical query - usually you have one time series “fact table” and a bunch of dimensions to enrich that data. If I’m doing analytics on a time series this gets even better with partition pruning and hash joins or bitmap indexes. And if I have a columnar database, that blows up this whole complexity argument. My point is that, layout and indexes should never be assumed to be one size fits all. Keep in mind if I’m doing analytics I want to bring my time series data into memory at least as a stream, as I need to calculate / filter / transform the records. Not everything is about rendering a page on a website.
orf 9y ago
If you're dealing with billion row tables and using mongodb you are way out of your league. Good luck.

matwood 9y ago

> less rigid than classic relational ones Less rigid than what? The schema is going to exist somewhere...

nwatson 9y ago

So you gonna do all those JOINs in code???

HodGreeley 9y ago
Part of the point is you don't need so many joins.

parasubvert 9y ago

The point is that the relational model is a fully general purpose mathematical model for managing data with integrity and with ad hock access. Most other models are for high performance of specific access paths and punt integrity management to code, which is a throwback to the 70s. They’re glorified file systems and data structure caches. Mongo has a reputation for losing your data. There are cases where scale and availability kills you and you need something like Cassandra or whatnot. But there are few as flexible and general as Oracle or Postgres.