11 ms·
PostgreSQL Outperforms MongoDB in New Round of Tests
- jvehent 12y agoI have been using JSON types in postgres 9.3 for several weeks now. It's nice. It just works. I love being able to mix relational logic on static columns with undefined documents on dynamic columns, all in the same tables.
- dang 12y agoUrl changed from http://developers-beta.slashdot.org/story/14/09/26/1330228/postgresql-outperforms-mongodb-in-new-round-of-tests http://developers-beta.slashdot.org/story/14/09/26/1330228/p..., which points to this.
- xtrumanx 12y agoOnly 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.
- mm_throwaway 12y agoPlease 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?
- bencollier49 12y agoJust tried to google this, but couldn't find anything. Why is Python on Postgres considered unsafe?
- JonnieCache 12y agoJust 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 of a function in untrusted PL/Python must take care that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator. Only superusers can create functions in untrusted languages such as plpythonu." http://www.postgresql.org/docs/current/static/plpython.html http://www.postgresql.org/docs/current/static/plpython.html Incidentally I've been merging my json in perl. It's automatically built into postgres, and the Hash::Merge library with it's configurable behaviour is very handy. Still, looking forward to not doing that. 9.4 is coming before xmas, right?
- yangyang 12y agoThat'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.html http://www.postgresql.org/docs/9.3/static/plperl-trusted.htm...
- Erwin 12y agoHuh. That's a little silly given the venerable HSTORE already has this capability: create table foo (d hstore); insert into foo (d) values(hstore(ARRAY['key', 'key2'], ARRAY['value', 'value2'])); -- "key"=>"value", "key2"=>"value2" update foo set d = d || hstore('key', UPPER(d->'key')); -- "key"=>"VALUE", "key2"=>"value2" Seems like JSON / JSONB need operators to merge two objects. In fact isn't JSONB sharing the data format with the new HSTORE2?
- willlll 12y agoYou can use PLV8 to do that in javascript on heroku.
- xtrumanx 12y agoYou're right [0]. Thanks for the heads up. [0] https://blog.heroku.com/archives/2013/6/5/javascript_in_your_postgres https://blog.heroku.com/archives/2013/6/5/javascript_in_your...
- dschiptsov 12y agoSince when MongoDB is considered as a database comparable with PostgreSQL?)
- Sanddancer 12y agoIt's a comparison on MongoDB's home turf, showing how well PostgreSQL works when used as a NoSQL document store.
- wmf 12y agoThat's not really relevant, since the point is that Postgres can do what MongoDB does but faster.
- threeseed 12y agoExcept it doesn't. It just has a JSON data type. As does many other databases e.g. Oracle, Teradata. People are switching to MongoDB because the developer and deployment experience is so good.
- vinceguidry 12y agoCapability is not the same thing as usability. Postgres is demonstratively more capable than Mongo, it's just not perceived as usable. There's a deep lesson here to be learned. One that can make you rich.
- Argorak 12y agoHate to say so, but many are switching to MongoDB because it's the current new kid on the block. I found that many companies have incredibly bad, not use-case driven reasons to pick their DB. Not that MongoDB is a bad database, but it has it's fair share of issues. Granted, this is the case for every database, but if you pick any database without being aware of those, you end up in a world of pain. (Context: I consult for backend systems in general)
- davidw 12y ago
- ranman 12y agoWhy does the graph read 2.4 when they say they're testing 2.6?
- jedisct1 12y agoHow does it compare to TokuMX? I am a huge fan of PostgreSQL, but in order to avoid having to rewrite an application designed for Mongo, I tried TokuMX and was pleasantly surprised by its performance.
- deleted 12y ago[deleted]
- deleted 12y ago[deleted]
- RyanZAG 12y agoGoing off [1] it looks like TokuMX is a similar multiple faster than Mongo than Postgre is from Mongo, so I'd guess Postgre and Toku would be fairly similar speed wise. Obviously depending on your data and search patterns one may still be significantly faster than the other as the usual caveat. [1] http://www.databasejournal.com/sqletc/tokumx-compared-to-mongodb-using-a-specific-common-use-case.html http://www.databasejournal.com/sqletc/tokumx-compared-to-mon...
- yangyang 12y agoNitpick: PostgreSQL, or Postgres for short. Never "Postgre".
- deleted 12y ago[deleted]
- tmcallaghan 12y agoPostgreSQL indexes are b-trees, and thus will suffer the same performance drop on insert/update/delete as all other b-tree based databases (InnoDB for example). I've been meaning to run the reported benchmark posted by EnterpriseDB myself, but haven't yet had the chance. My other concern with that benchmark is that it really doesn't cover much in the way of an interesting workload.
- threeseed 12y agoWill 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.
- jakozaur 12y agoHow about implementing MongoDB API on top of the PostgreSQL? Then sell it companies which started with MongoDB, but run into some scalability/reliability issues.
- Oculus 12y agoAs sad as it may be, with the use of schema-enforcing ORMs and the popularity of MongoDB, this will be a viable business in the near future.
- threeseed 12y agoPretty sure it's easier to add PostgreSQL as a storage engine: http://engineering.objectrocket.com/2014/07/18/experimental-pluggable-storage-engines-in-mongodb/ http://engineering.objectrocket.com/2014/07/18/experimental-... Than to fix up the sharding and clustering in PostgreSQL. It's been an issue for quite a few years now and still not mainlined. I think most PostgreSQL users are simply scaling them vertically.
- yangyang 12y agoSomeone's already done a proof of concept of this, see https://github.com/JerrySievert/mongolike https://github.com/JerrySievert/mongolike
- yangyang 12y agoThis is the thing I was actually thinking of when I posted the above. This is a background worker (runs within PostgreSQL) speaking the mongo wire protocol: https://github.com/umitanuki/mongres https://github.com/umitanuki/mongres Again, very basic PoC.
- bshimmin 12y agoI'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...).
- hnhipster 12y agoExactly! 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 details!
- mlvljr 12y agoNice benefit, bro (high five)!!
- hnhipster 12y agoThe approach to comments link doesn't indicate I should avoid sarcasm. But if that's how it is going to be then maybe one of the moderators can add that there? Link in question: https://news.ycombinator.com/newswelcome.html https://news.ycombinator.com/newswelcome.html.
- eksith 12y agoChanging databases is extremely painful if there's a fair amount of data already stored there. It doesn't matter how "database agnostic" the code was originally, there are too many edge cases that tend to become front cases over time that you have to adjust for your specific database. But just like with MySQL, I imagine those who can migrate to another DB would do so at the first opportunity or wait for the equivalent of MariaDB to make a "drop-in replacement" in lieu of messing with their application code.
- ExpiredLink 12y ago> and one of my clients uses DB2 ???
- deleted 12y ago[deleted]
- hnhipster 12y agoThis is very interesting. Around when MongoDB was quickly becoming the cool thing to do I'd ask people about why it is better than just storing things in Postgres. People would have answers that would be grammatically correct but would not make any sense. That being said, I find it weird that now it is cool to make fun of MongoDB. Some people on this thread have even said they want to know if a service is using MongoDB and they'd not use that service. I am pretty sure they'd be all over Stripe (who store your money related stuff in MongoDB) in a different thread.
- qwerta 12y agoDecade ago it was the same story with mysql.
- danohuiginn 12y agoyeah, mongodb has flipped from hype to hatred without much in between. The reason, I think, is that few people using it had a good sense of where the pain points would be. Many of us somehow imagined that because we didn't know where mongodb's limitations were, we wouldn't run into them. Cue frustration when you're bitten by some issue you didn't think about. I do believe there are good uses for it. But they are pretty specialised, and I'd rather use a relational db for most things.
- jacques_chester 12y ago> yeah, mongodb has flipped from hype to hatred without much in between. The hatred was always present, it's just that the hype has died down as the early adopters have (re)discovered the sharp corners in both architecture and implementation.
- xorcist 12y ago> Stripe (who store your money related stuff in MongoDB) That's a bit scary. There has been several successful attacks against virtual currency exchanges that use MongoDB, utilizing the eventual consistency to your advantage. If you handle money, you don't want any inconsistencies in your database, no matter how temporary. You can work around these of course but you really need to know what you're doing.
- tszming 12y agoMongoDB 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-ram/ http://docs.mongodb.org/manual/tutorial/ensure-indexes-fit-r...
- brey 12y agoonce you start page faulting and hitting spinning disk it's game over for any database's performance, postgres included.
- yangyang 12y agoThere 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.
- Nitramp 12y ago... 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.
- brey 12y agomongodb 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.
- collyw 12y agoThat sounds like bollocks. I have a script that grows a bit Perl hash. It fills up memory and starts paging. The application grinds to a halt (almost). I tie the hash to Berkly DB file, so it writes to disk. Performance is about a third of the in memory hash, BUT doesn't slow when it's too big for memory.
- krylon 12y agoI 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 though, I had to get familiar with the database schema of the ERP system we use at work, plus some modifications that have been done to it over the years, and it kind of feels to me like somebody was trying force a square peg through a round hole (i.e. trying to model data in relational terms, either not fully "getting" the relational model or using data that simply refuses to be modeled in that way). I sometimes think the people who wrote the ERP system might have enjoyed a NoSQL DBMS. Then again, with a multi-user ERP system, you <i>really</i> want transactions (personally, I feel that ACID-compliant transactions are single most useful benefit of RDBMS engines), and most NoSQL-engines seem to kind of not have them.
- sergiosgc 12y agoI see where you are coming from, and my opinion is mostly the same. However, I can see a use case for relaxing constraints in a distributed scenario, as described by the CAP theorem. In a distributed scenario, when a partition event occurs, relational databases opt for consistency, whereas nosql opts for availability. This is formally correct behaviour by relational dbs, but comes with a cost. The cost is a serial performance component, that can't be parallelized. Nosql DBs (most of them), in this scenario, go for availability, and may thus eschew some consistency guarantee tasks, with a cost in data consistency and an advantage in parallel performance. The trick, as ever, is to use each tool in its function. Nosql and relational dbs are wholly different tools, for wholly different problem classes. Using nosql where consistency is paramount irks me to no end, and that is the case 80% of the time I see people using nosql. On the other hand, in specific cases, nosql DBs are a new useful tool in my arsenal.
- romanovcode 12y agoIt 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 don't understand what's so hard to understand here. It's a simple solution to EAV/nulltable nightmare.
- tete 12y agoDid 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 so it's kinda slow. Many functions in MongoDB are actually implemented in JavaScript, not C. So that's also a factor, even when I guess it's not the big one here. MongoDB has a lot of limitations that can really bite yo (document size even though that's the smallest (gridfs), how you can do indices, even limitations in how your query can look like, etc The only thing that's good about MongoDB is that it's nice for getting something up and running quickly and that it's a charm to scale (in many different kinds), compared to PostgreSQL. If PostgreSQL had something built in(!) coming at least close to that (and development has a strong focus there) it would be perfect. For all these reasons many companies actually have hybrid systems, because sometimes one thing makes sense and sometimes the other. The benchmark seems strange, cause there are many SQL and NoSQL databases that are faster and that's a kinda well-known fact. I think everyone who ever had to decide on a database system has known that, even without a benchmark. This makes it kinda look like an advertisement (look at the company behind the blog). Using PostgreSQL 9.3 with JSON for a while now and it's great. Also I know it is possible to scale PostgreSQL and it's really nice. Still a lot more complexity involved (again, depending on the use case). Just use the right tool and please let's stop with such shallow comparisons, because I think it kinda harms the reputation of database engineers and system architects - and the authors of such comparison. When you look for real comparisons and example use cases, typical patterns or just some help one always stumbles across these things and they tend to quickly be out of date too, cause all well-known databases have a lot of active development going on.
- Nitramp 12y agoIs 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 http://aphyr.com/posts/284-call-me-maybe-mongodb
- jeremylevy 12y agoAny articles coming from a company which describes itself as "The Postgres Database Company" and sells "a relational database management system based on PostgreSQL..." [1][2] stating than "PostgreSQL outperforms X" is de facto suspicious. [1]: https://www.google.fr/?q=enterprisedb#q=enterprisedb https://www.google.fr/?q=enterprisedb#q=enterprisedb [2]: http://en.wikipedia.org/wiki/EnterpriseDB http://en.wikipedia.org/wiki/EnterpriseDB
- yangyang 12y agoThey employ several of the main developers of community PostgreSQL and also sell commercial versions of the database. Why does that make this article suspicious? Obviously they stand to benefit from more people using PostgreSQL, but they've been exceedingly clear with their methodology - their benchmark is available on Github: https://github.com/EnterpriseDB/pg_nosql_benchmark https://github.com/EnterpriseDB/pg_nosql_benchmark.
- gregwebs 12y agoThis 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 there is work underway to make it more competitive with MongoDB. So now they are also releasing a jsonb (b for binary) storage format, which looks promising, but I can't find any information on exactly what its features are. I would love to actually see a benchmark comparing field updates, but this benchmark is not it. MongoDB is a database with trade-offs, downsides, and more crappy edge cases then MySQL, but it does exist because at its core it allows data modeling that traditional SQL databases are lacking. MongoDB has first class arrays rather than forcing you to do joins. It supports schema-less data, which is rarely useful, but when you need it can be very useful. It can do inserts and count increments very quickly (yes the write lock means you eventually have to put collections in separate databases), which is also useful for certain use cases.
- yummyfajitas 12y agoThis 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 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.
- gregwebs 12y agoIt does have arrays, but they aren't first class to the same extent as MongoDB. * A Postgres Array can end up being stored elsewhere, whereas in MongoDB an array will be contained within the document * I am also not clear on what exactly can be stuck inside an array (In MongoDB it can be an object that contains more arrays) while maintaining first-class access and updates. I would love to find more detailed information on these points, but with jsonb they may be moot now. I will look into the increment issue in PostgreSQL more carefully. I just know that it wasn't feasible in MySQL when I was using it.
- yangyang 12y agoThese 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_schemaless.pdf http://www.sraoss.co.jp/event_seminar/2014/20140911_pg94_sch... Also contains some comparisons with MongoDB.
- larry_the_lover 12y agobored, bored, bored....good luck trying to shard postgres! idiots
- collyw 12y agoI'll keep the data types small, so it takes a fraction of the disk space and doesn't need sharding.