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

81–90 of 171 posts

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#81

Post by [deleted] »

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).

[deleted]

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#82

Post by collyw »

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).

Sure, but how many people genuinely have data that big?

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#83

Post by Nitramp »

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.

... but that usually doesn't mean you have to have your full data set in memory, only the hot parts. And even if so, one or two ~10 ms seeks during a query aren't that terrible.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#84

Post by Xylakant »

lgas wrote: post #75
Xylakant wrote: post #73

Earlier quoted context omitted.

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?

Sure, you could. But CouchDB just makes that very easy to do while it is very hard and hacky in PG. On the flip side, the use case did not require the guarantees that PG has to offer, so why go through the pain for no gain :)

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#85

Post by collyw »

yangyang wrote: post #54
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...).

What's wrong with DB2? I know it's commercial and a bit arcane in areas, but it's a far more powerful database than MySQL.

Big banks still use it.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#87

Post by rpedela »

mm_throwaway wrote: post #33
xtrumanx wrote: post #4

Only problem I have with JSON on Postgres is you can't update a property of a JSON object like so: update table set jsonCol->propertyA = 42; You need to write an extension for that. Easiest to do so using Python but sadly Heroku doesn't support python on postgres since its unsafe.

Please correct me if I'm wrong, but isn't updating JSON properties one the big improvements of the soon-to-be-released 9.4 version?

Not until 9.5 at the earliest.

http://www.postgresql.org/message-id/534C4225.6020600@agliod...

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#88

Post by mikegioia »

collyw wrote: post #82
mikegioia wrote: post #74

Earlier quoted context omitted.

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).

Sure, but how many people genuinely have data that big?

Well one thing we store is HTML content and "MS Word" like document data. We also store hundreds of revisions for all of those documents. I wouldn't want to use an RDMBS for this because (a) its not relational, but also (b) backup/replication/load distribution would be too painful. A system like CouchDB can be spread out over n-machines, any of them write-capable.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#89

Post by mikegioia »

vertex-four wrote: post #80
mikegioia wrote: post #74

Earlier quoted context omitted.

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/wha…

What I meant was a data-size that was too large for a machine. Adding arbitrarily large JSON to your table could expand the data-size to be too big for one machine, or even too big for block storage. Plus you might not want it all in block storage. My point is that an RDBMS can be better served storing the relational data alone with a separate DB Engine for the potentially massive JSON data.

Re: PostgreSQL Outperforms MongoDB in New Round of Tests

#90

Post by brey »

Nitramp wrote: post #83
brey wrote: post #59

Earlier quoted context omitted.

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

... but that usually doesn't mean you have to have your full data set in memory, only the hot parts. And even if so, one or two ~10 ms seeks during a query aren't that terrible.

mongodb recommends that your working set lives in memory, not your entire database - it's generally much cheaper to add more RAM than code in any case.

a few seeks aren't terrible for small/medium applications, but when you're asking for thousands of queries a second any disk access is bad news.

Post reply on HN