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

111–120 of 171 posts

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#111

Post by yummyfajitas »

krylon wrote: post #105
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…

Thanks for the explanation. Quite frankly, no application I have ever worked on has had to deal with "huge" amounts of data by any common definition (a couple of gigabytes at the most). And like I did say, looking at our ERP system's database I am beginning to understand the appeal of a database without a fixed schema. Some of the tables have dozens of columns, with most of the rows being full of NULL values. So I do…

Some of the tables have dozens of columns, with most of the rows being full of NULL values.

This is generally addressed in a relational design with a star schema. First create a dimension table:

    CREATE TABLE person (
      id BIGINT PRIMARY KEY NOT NULL
    )
Then create fact tables:

    CREATE TABLE person_name (
      person_id BIGINT REFERENCES person(id) UNIQUE,
      name VARCHAR(128) NOT NULL
    )

    CREATE TABLE person_bank_details (
      person_id BIGINT REFERENCES person(id) UNIQUE,
      bank_detail ....
    )
This avoids large numbers of rows containing nulls, but it violates a normal form. The mnemonic is that the table must contain the Key, the whole key, and nothing but the key, so help me Codd. Anytime you have a "REFERENCES table(pk) UNIQUE", you violate the "whole key" bit.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#112

Post by yummyfajitas »

gregwebs wrote: post #110

This benchmark misses the entire point of MongoDB: that you can atomically update individual fields in the document. Thas has not been possible with Postgres json storage type. Instead, the entire JSON blob must be read out, modified, and inserted back in. This reality is well known to those that understand Postgres, which is why they have HStore. HStore is limited though (particularly to the size of the store), so t…

This is an excellent post. You've answered a question I've had for a while - are there any circumstances where MongoDB is the right tool for the job? You seem to be on point that Mongo is currently the best for frequent updates of JSON blobs.

Thank you.

I'll take issue with a couple of points, though. Postgres has arrays:

http://www.postgresql.org/docs/9.3/static/arrays.html

Rapid counting of increments can be done fairly easily with triggers. You can even write the trigger function in javascript if you like.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#113

Post by jbuzbee »

jakozaur wrote: post #14
threeseed wrote: post #10

Will be great if PostgreSQL ends up as a storage engine option in MongoDB like InnoDB. Then you have the better engine of PostgreSQL with the superior clustering, sharding and end user experience of MongoDB. That said these articles are pretty pointless. Performance isn't the reason companies are switching to MongoDB.

How about implementing MongoDB API on top of the PostgreSQL? Then sell it companies which started with MongoDB, but run into some scalability/reliability issues.

I wonder how far one could go with this. I'm currently playing around with Meteor which requires MongoDB. I wonder if a MongoDB API on top of PostresSQL would be a path to start bringing in relational DB capabilities to Meteor? But perhaps Meteor's use of Mongo is too deeply intertwined and goes deeper than an application-level API.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#114

Post by jeffdavis »

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…

"Just use the right tool and please let's stop with such shallow comparisons"

A lot of people may have thought, prior to this benchmark, that MongoDB was the right tool for all high performance JSON-related tasks.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#115

Post by yangyang »

gregwebs wrote: post #110

This benchmark misses the entire point of MongoDB: that you can atomically update individual fields in the document. Thas has not been possible with Postgres json storage type. Instead, the entire JSON blob must be read out, modified, and inserted back in. This reality is well known to those that understand Postgres, which is why they have HStore. HStore is limited though (particularly to the size of the store), so t…

Note that on the Github repo for this benchmark, the README mentions that:

"... later versions will include a complete range of workloads (including deleting, updating, appending, and complex select operations) and they will also evaluate multi-server configurations."

Any update in PostgreSQL will result in a new tuple being inserted, whether it contains json, hstore or anything else; that's the basis of multi-version concurrency control. It'll be the same deal with jsonb.

Not only that, the delta to update the page to contain the new tuple, and a copy of the full page the tuple is being written to (if it's the first change to that page in that checkpoint cycle) are written to the write-ahead log.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#116

Post by yangyang »

These are the slides from an interesting presentation by one of the people that did a lot of the work for the new jsonb type in 9.4, and associated index improvements: http://www.sraoss.co.jp/event_seminar/2014/20140911_pg94_sch...

Also contains some comparisons with MongoDB.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#117

Post by MoOmer »

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…

Allow me to present Cassandra: http://planetcassandra.org/what-is-apache-cassandra/

Note that Cassandra scales linearly.

http://wiki.apache.org/cassandra/ArticlesAndPresentations

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#118

Post by Twirrim »

kennu wrote: post #104
yangyang wrote: post #102

Earlier quoted context omitted.

Streaming replication is really not hard to set up in PostgreSQL. It is cluster-level; if you want to replicate a single database you'll need to use a 3rd party solution like Slony or Burcado (logical replication built on features in 9.4 will improve this). 2ndQuadrant have developed Bi-Directional Replication for 9.4 ( http://2ndquadrant.com/en/resources/bdr/ , https://wiki.postgresql.org/wiki/BDR_User_Guide ) based…

I think your comment summarizes why this stuff so hard on traditional SQL; one doesn't even know where exactly to start. In MongoDB, you just turn on replication according to http://docs.mongodb.org/manual/replication/ , which means basically adding one directive to mongod.conf and typing in the IP addresses of the nodes. MongoDB handles everything else for you. I'm not claiming that it's better or reliabler, just th…

It only takes an hour or so to google and read up on what the different ways of replicating data are in PostgreSQL, their advantages and disadvantages.

Are we really that scared of 'research'?

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#119

Post by yangyang »

Twirrim wrote: post #118
kennu wrote: post #104

Earlier quoted context omitted.

I think your comment summarizes why this stuff so hard on traditional SQL; one doesn't even know where exactly to start. In MongoDB, you just turn on replication according to http://docs.mongodb.org/manual/replication/ , which means basically adding one directive to mongod.conf and typing in the IP addresses of the nodes. MongoDB handles everything else for you. I'm not claiming that it's better or reliabler, just th…

It only takes an hour or so to google and read up on what the different ways of replicating data are in PostgreSQL, their advantages and disadvantages. Are we really that scared of 'research'?

I get the feeling that a sizeable proportion of NoSQL users don't have much knowledge of relational databases at all, never mind replication. If you just want to persist some JSON data you already have, I suppose it is quite a large leap to start reading about relational algebra, ACID, undo/redo logs, SQL, and so on.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#120

Post by btbuilder »

gregwebs wrote: post #110

This benchmark misses the entire point of MongoDB: that you can atomically update individual fields in the document. Thas has not been possible with Postgres json storage type. Instead, the entire JSON blob must be read out, modified, and inserted back in. This reality is well known to those that understand Postgres, which is why they have HStore. HStore is limited though (particularly to the size of the store), so t…

Postgres has first-class arrays too:

http://www.postgresql.org/docs/9.3/static/arrays.html

Post reply on HN