Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Outperforms MongoDB in New Round of Tests

blogs.enterprisedb.com

Post reply on HN
Print view View original

71–80 of 171 posts

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#71

Post by yangyang »

brey wrote: post #59
tszming wrote: post #44

MongoDB work well ONLY if indexes (and working set) fit in the memory. What are the indexes size in the benchmark? (I doubt as I see you are running a 145GB database on a 32GB instance) http://docs.mongodb.org/manual/tutorial/ensure-indexes-fit-r...

once you start page faulting and hitting spinning disk it's game over for any database's performance, postgres included.

There are plenty of database that don't fit into RAM completely and are perfectly usable. It depends on what you can fit in buffer cache / OS page cache.

The whole point of btree indexes is that they're efficient to query from disk.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#72

Post by yangyang »

JonnieCache wrote: post #60
bencollier49 wrote: post #34

Earlier quoted context omitted.

Just tried to google this, but couldn't find anything. Why is Python on Postgres considered unsafe?

Just because its a programming language. It can write to disk, open sockets, etc. "As of PostgreSQL 7.4, PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it. It has therefore been renamed to plpythonu. The trusted variant plpython might become available again in future, if a new secure execution mechanism is developed in Python. The writer o…

That's not strictly true; PL/Perl is (obviously) a programming language and is available in trusted (with certain operations disabled) and untrusted variants: see [1].

The issue with PL/Python is that it's nigh on impossible to properly sandbox Python.

[1] http://www.postgresql.org/docs/9.3/static/plperl-trusted.htm...

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#73

Post by Xylakant »

lgas wrote: post #57
gozzoo wrote: post #53

Earlier quoted context omitted.

> I've seen project that leveraged CouchDB to basically return all data for any given view with a single query from a pre-calculated view, something which would have impossible with an SQL database. This sounds interesting. I've been looking for a good example where NoSQL db is better than relation db. Could you provide more details.

Postgresql has materialized views now: http://www.postgresql.org/docs/9.3/static/sql-creatematerial... Unless I'm misunderstanding what Xylakant means, that's certainly doable in Postgresql and has been in other relational databases for a long time.

I didn't mean "view" in the sense of "database view" but rather in the sense of "page view". All data displayed on a page with multiple types of documents was returned in a single query. That's certainly doable in pg, but requires a lot of hacks and it's not efficient. Or you can use hstore and basically use pg as a nosql db.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#74

Post by mikegioia »

yangyang wrote: post #70
romanovcode wrote: post #63

Earlier quoted context omitted.

It is for storing huge number of data with dynamic keys. For example when site admin says "I want new archive that I can fill with items, items will have Id (automatically), Name (string), IsMale (bool)". He also want to do complex queries on this data as well. That's where NoSQL comes to help. And to answer why exactly MongoDb is so popular - it's because it has awesome driver support for every popular language. I d…

The nice thing about using an RDBMS with JSON support, rather than a NoSQL solution, is that you can store all the fixed-schema stuff in column as usual, and benefit from the performance, consistency, ease of joins and so on with that, but you can also store your JSON documents alongside that data in the same table, efficiently indexed.

Yes, but what happens if your JSON data size grows so large that it can't fit on a single machine? Multi-master replication or sharding is a terrible pain in any RDBMS (at least according to my research and trials).

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#75

Post by lgas »

Xylakant wrote: post #73
lgas wrote: post #57

Earlier quoted context omitted.

Postgresql has materialized views now: http://www.postgresql.org/docs/9.3/static/sql-creatematerial... Unless I'm misunderstanding what Xylakant means, that's certainly doable in Postgresql and has been in other relational databases for a long time.

I didn't mean "view" in the sense of "database view" but rather in the sense of "page view". All data displayed on a page with multiple types of documents was returned in a single query. That's certainly doable in pg, but requires a lot of hacks and it's not efficient. Or you can use hstore and basically use pg as a nosql db.

Ah, that makes sense, though couldn't you solve this by something like creating a table called 'pages' and storing the materialized JSON (the same that mongo would generate) in the table?

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#76

Post by mrweasel »

romanovcode wrote: post #63
krylon wrote: post #49

I never really "got" the new wave of NoSQL databases. Mongo seemed to be the one I could most easily wrap my head around, but still. I was never sure, though, if that meant I had never faced a problem suitable for one of these DBMSs or if my mind is just so warped by years of using relational engines (mostly Postgres, or SQLite for simple projects) that I could not think of modeling my data any other way. Recently th…

It is for storing huge number of data with dynamic keys. For example when site admin says "I want new archive that I can fill with items, items will have Id (automatically), Name (string), IsMale (bool)". He also want to do complex queries on this data as well. That's where NoSQL comes to help. And to answer why exactly MongoDb is so popular - it's because it has awesome driver support for every popular language. I d…

We've redone our product catalog for a website using a NoSQL solution ( not MongoDB, but we did look at it). Our products are in multiple different categories, and have vastly differently attributes depending on categories. NoSQL solutions are perfect of this. As you point out it's a simply alternative/solution to deploying an EAV model.

I've only seen EAV used in one system, Magento, but was a disaster. It's complex and slow to the point that very product in stored both in the EAV model and as a "flattened product".

For systems dealing with sales and economy in general I would almost alway pick a RBDMS, it's seems a much more natural fit. The ability to do ad-hoc queries in SQL, rather that map-reduce is a huge advantage.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#77

Post by Nitramp »

krylon wrote: post #49

I never really "got" the new wave of NoSQL databases. Mongo seemed to be the one I could most easily wrap my head around, but still. I was never sure, though, if that meant I had never faced a problem suitable for one of these DBMSs or if my mind is just so warped by years of using relational engines (mostly Postgres, or SQLite for simple projects) that I could not think of modeling my data any other way. Recently th…

I think people mix up three things:

(1) Transactional model. Many NoSQL databases are non-ACID, but others are (Google's stores all have some transactional guarantees). Some databases try to gain efficiency by relaxing their transactional guarantees, some probably just didn't get around to implementing a proper transactional system yet.

(2) Data model. The relational models can be overly restrictive as you cannot easily represent contained, repeated elements in an object without ending up in a crazy join smorgasbord. Note that that doesn't mean you have to be dynamically typed.

(3) Distribution/sharding/clustering. RDBMSes are traditionally single machine, and getting them to cluster is usually a huge source of pain. NoSQL databases are often built from the ground for sharding.

I think people go for MongoDB mostly for (2) and ease of use. Very few people have an actual big data problem where you really need (3), and for reliability there are simpler solutions (hot standby). (1) makes it so much easier to build reliable systems that it'd be a real deal breaker for me.

I personally don't understand why so many people go for NoSQL, seems to me like that creates a substantial cost, both for performance, but more importantly missing the transaction guarantees, with no real benefit, at least none that's obvious to me. MongoDB with its unapplied writes, no real transactions, but no real distribution story seems like an odd choice in particular.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#78

Post by mlvljr »

hnhipster wrote: post #47
bshimmin wrote: post #39

I'm sort of struggling to imagine anyone really using MongoDB at all in a couple of years. But then again, plenty of shops still use MySQL (and one of my clients uses DB2...).

Exactly! I can't imagine anyone using MongoDB at all in a couple of years either. Everything will be in Node! But then again, I am going to say the opposite of what I just said to hedge my bet and point out plenty of shops still use MySQL. No serious company like YouTube, Facebook, or until recently Google Ads would use it. I love sitting in my armchair and passing technical judgements without providing any technical…

Nice benefit, bro (high five)!!

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#79

Post by Nitramp »

tete wrote: post #65

Did I miss something? MongoDB was never ever faster than Postgres. That's nothing new. Most of these things are clear when one reads the MongoDB docs: MongoDB stores Metadata, (nearly) uncompressed on a per document basis, so of course it uses way more diskspace. It doesn't store the data in any efficient way either. Also it's pretty much unoptimized, compared to Postgres which has been around for a really long time…

Is MongoDB's distribution and scaling story really nicer? A cluster story that's easy to set up but then doesn't actually work (loses data, fails in potentially catastrophic ways) sounds not all that useful.

http://aphyr.com/posts/284-call-me-maybe-mongodb

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#80

Post by vertex-four »

mikegioia wrote: post #74
yangyang wrote: post #70

Earlier quoted context omitted.

The nice thing about using an RDBMS with JSON support, rather than a NoSQL solution, is that you can store all the fixed-schema stuff in column as usual, and benefit from the performance, consistency, ease of joins and so on with that, but you can also store your JSON documents alongside that data in the same table, efficiently indexed.

Yes, but what happens if your JSON data size grows so large that it can't fit on a single machine? Multi-master replication or sharding is a terrible pain in any RDBMS (at least according to my research and trials).

At the end of 2013, Stack Overflow worked on one SQL server (plus a redis server for caching). The rest of Stack Exchange runs on another SQL server.[0]

For the most part, for most projects, worrying about multi-master replication is going to be pointless. You can always put some data in a distributed K/V (or document) store and point to that from your SQL if you need to.

[0] http://nickcraver.com/blog/2013/11/22/what-it-takes-to-run-s...

Post reply on HN