9 ms·
Hstore development for 9.4 release
- andreypopp 13y agoThis is impressive but not really surprising given the quality of PostgreSQL database. Quote: "We added performance comparison with MongoDB. MongoDB is very slow on loading data (slide 59) - 8 minutes vs 76s, seqscan speed is the same - about 1s, index scan is very fast - 1ms vs 17 ms with GIN fast-scan patch. But we managed to create new opclass (slides 61-62) for hstore using hashing of full-paths concatenated with values and got 0.6ms, which is faster than mongodb !"
- baudehlo 13y agoBut note it's at the expense of a very large index file - 800MB (vs Mongo's 100MB). Although Pg's index covers the entire JSON structure, whereas Mongo's index only covers the leafs being searched, so it will optimize a larger variety of queries.
- eCa 13y agoSpace is cheap. Time is expensive.
- wobbleblob 13y agoThat's why your index should fit in RAM
- falcolas 13y agoIt takes a non-0 amount of time to load large indices into memory (upwards of 5 seconds, depending on your choice of disks and arrays), and to iterate through large indices once they are in memory. Such large indices also limit how many of them can be stored in memory at the same time - making transactions against those indexes slower over time if the indices have to be loaded and unloaded from memory frequently. You should always choose indexes with care, because indexes not only cost space, they cost time. And as you said, Time is expensive.
- mason55 13y ago> It takes a non-0 amount of time to load large indices into memory (upwards of 5 seconds, depending on your choice of disks and arrays), and to iterate through large indices once they are in memory. This is why MongoDB recommends all indices fit in memory
- bsg75 13y agoIn space, no one can hear you scream.
- penguat 13y agoIs that 800Mb in RAM? That could get expensive.
- saosebastiao 13y agoSpace is cheap, but IOPS are not, and RAM always has an opportunity cost.
- eonil 13y agoThe real surprise is this comes with true multi-key ACID transaction.
- karavelov 13y agoIf you want to index on specific field with PostgreSQL you could use function indexes and bring the index size down
- integraton 13y agoSee slide 44 here: http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2013.pdf http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2... GIN indexes are big, but Mongo's indexes are bigger than all other PostgreSQL indexes, and Mongo also requires much more space for the data.
- garenp 13y ago800MB is for the old GIN index, the new experimental one is 350MB, and has not yet undergone more space optimizations. They are also not really comparable: Mongo only indexes a few select keys, whereas the new GIN index does many.
- desireco42 13y agoWhich is not surprising, as this is 'real' database. I read review of MongoDB internals and it sounded not very positive. While you can create NoSQL quickly, to do it well with any db, it takes time which PostgreSQL definitely has. Just to be clear, MongoDB is fine db for certain scenarios and I am using it in production.
- fennecfoxen 13y agoThe internals are so very slim... so I like this guy's advice for a Mongo replacement that's almost as slim: http://nyeggen.com/blog/2013/10/18/the-genius-and-folly-of-mongodb/ http://nyeggen.com/blog/2013/10/18/the-genius-and-folly-of-m... "ZFS has a heckuva caching strategy, and you know when it accepts a write." :P
- andrewcooke 13y agocontext: http://www.postgresql.org/docs/9.1/static/textsearch-indexes.html http://www.postgresql.org/docs/9.1/static/textsearch-indexes... (GIN and GiST are index types)
- gibybo 13y agoCan I index fields within a JSON document in PostreSQL? Can I query them? I.e. can I do something like SELECT json_field FROM data WHERE json_field.age > 15 ?
- andreypopp 13y agoYes, this is what index scan performance metrics is about — PostgreSQL outperforms MongoDB here.
- itodd 13y agoThis is how you do it in 9.2 using PLV8. http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html http://people.planetpostgresql.org/andrew/index.php?/archive... For 9.3, see more info at http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/ http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-...
- chrisfarms 13y agoyes. See the operators at: http://www.postgresql.org/docs/9.3/static/functions-json.html http://www.postgresql.org/docs/9.3/static/functions-json.htm...
- rpedela 13y agoThat query would be this: SELECT json_field FROM data WHERE json_field->'age' > 15 Part of the performance increase for hstore is improvements for GIN indexes, and according to the author can be applied to json. So yes you can use indexes on your hstore or json documents.
- jeffasinger 13y agoYes, for a project I'm working on, I had a JSON array of objects that each had a key called name. I have fast indexed queries that return any rows that have an object with a given name in it, and for full text search on that name along with a few other things.
- tete 13y agoWe actually use both of them in our system. I really wish PostgreSQL wasn't such an enormous challenge to scale horizontally.
- asdasf 13y agoWhat is challenging about it? If you make the same sacrifices you make to use mongo, it should be pretty straight forward.
- Herald_MJ 13y agoIn my experience, mongo doesn't even scale horizontally all that well...
- misiti3780 13y agoany more details? - im about to scale a large mongo database horizontally and wondering what I should be watching out for
- tete 13y agoIf you want to scale out using a replica-strategy you really want to have the most recent version of PostgreSQL. It made things like log shipping pretty much an automatic experience. It still has rough edges, but the latest version really makes a difference here. See the release notes of PostgreSQL. You will however find plenty of information on the wiki. Sometimes it is not completely up to date because of so much going on on that front. You can use third party solutions too, but they usually have a number of caveats and general problems. However it depends on what you do with your database. If you don't deal with writing your own functions, use extensions or have fancy transaction they will work just fine. If you do, the functionality of Postgres is a more safe strategy. If you are dealing with extensions, etc. you should really be ready to get dedicated support for these things. However, it really depends on what you do. For everything standard it won't make too much trouble once the first setup is done.
- 13y ago
- pstuart 13y agoAccording to the roadmap page, 9.4 is due out in the 3rd quarter of 2014.
- twic 13y agoThis is wonderful work, and it is the foundation for yet more wonderful work in the future. However, as much as i am a huge PostgreSQL fanboy and a dedicated MongoDB peanut-thrower, i worry that the caption applied to this post is misleading. Once this work is released, PostgreSQL will be faster than current versions of MongoDB at carrying out queries for documents which look for a given value at a given path (or perhaps more generally, values satisfying a given predicate at paths satisfying a given predicate). But that has never been one of MongoDB's important strengths, has it? The operations for which MongoDB is optimised are inserting documents, retrieving document by ID, and conducting map-reduces across the whole database. Yes, it has the ability to query by values in leaves, and it can use indices to make that faster, but that's a bit of a second-tier, bag-on-the-side feature, isn't it? If that was the main thing you needed to do, you wouldn't have chosen MongoDB, right?
- rpedela 13y agoYou can insert and retrieve by ID just as fast with PostgreSQL. Map/reduce? Isn't that slow in general? Can't PostgreSQL JOIN and aggregate functions do most of what map/reduce is for but faster?
- driverdan 13y agoMongo clusters distribute map reduce so it can be reasonably fast.
- sudhirj 13y agoWhen map/reduce comes into play, speed isn't usually the main issue. It's a way to do work in parallel - which may be done to increase speed, but more often than not it's done because there's too much work to be done any other way. Put another way, I see map/reduce as a background task - building indexes, reporting, etc. I don't think speed is as important there as it is for a foreground task like GET, PUT and DELETE. For these operations, especially by key, Postgres has always been right up there - what's happening now is that Postgres is getting really good at NoSQL features - documents, key value pairs, deep querying etc - good enough that if you have Postgres you can do pretty much whatever you could have done with Mongo - while still making use of everything else it has to offer.
- dkhenry 13y agoSo a database developer is saying that his database is faster then a competitors database. Shocking.....
- craigkerstiens 13y agoIf you want to find more of the detail and background around this which came from a talk by the author of the blog post then you check out his slides at http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2013.pdf?utm_source=postgresweekly&utm_medium=email http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2.... One of the key things not in the blog post but addressed in the slides is that there will be a new index type that really pushes the performance gains even further.
- gosukiwi 13y agoPostgres is quite scary, I feel that if I use it I'll miss 90% of the features @_@
- wmil 13y agoDon't worry about it, it's not like features have feelings. There's always a fight between sticking to what your ORM supports and trying to use every feature of the database. Either can simplify your code under certain circumstances.
- netghost 13y agoDon't worry, you don't use 90% of the words in the english language either, but it's still useful ;)
- rdtsc 13y agoAt least your data will be there because the features are set to sane defaults.
- andyl 13y agoPostgres Array/Hstore/JSON datatypes have made my Rails apps way simpler. Great combination.
- zen_boy 13y agoCare to expand how? Curious as a Rails developer myself.
- netghost 13y agoIf you just want some background, I actually gave a talk about hstore, arrays, and FTS in postgres for rails devs a few months ago. Links to the talk, slides, sample code, and some follow up articles: http://monkeyandcrow.com/blog/postgres_railsconf2013/ http://monkeyandcrow.com/blog/postgres_railsconf2013/
- dalyons 13y agoI was in the audience! It was a great session - you're a funny guy, explained the concepts well, and there was some really interesting real(or alternate :p) world use-cases for all this stuff.
- zen_boy 13y agoGreat stuff! Seems like the store_accessor works with the :json column type out of the box as well. Throw in a JSON schema validator and we have full-blown validations for :hstore and :json!
- eksith 13y agoI'd love to know how you've set things up [ if you don't mind me prying ;) ]. It would be interesting to see someone else's approach.
- andyl 13y agoWhat I did: 1) Use the latest Postgres (9.3) 2) use Rails4 - this provides String/Integer Arrays, HStore, UUIDs out of the box 3) use post_json (https://github.com/webnuts/post_json https://github.com/webnuts/post_json). These tools made it simpler to do things like tagging, user-defined data attributes, hierarchical data, flexible configuration data. It was always possible to store much of this data using text fields and JSON.parse (or some such), but the new Postgres stuff makes the flexible datatypes queryable. And as built-in support for flexible data types comes online, you can throw away your custom serializers. There's other Postgres datatypes that I haven't used - for geocoding/geosearching, network addresses, etc.
- roncohen 13y agoFantastic! No mention of arrays in the post, but in the slides: you can use {1, 2} syntax for arrays and hstore now eats it \o/
- Hande1967 13y agomy neighbor's step-sister makes $72/hr on the computer. She has been fired for 6 months but last month her payment was $16513 just working on the computer for a few hours. look at more info .... jobs37.com
- Hande1967 13y agossssssssss
- willvarfar 13y agoIt would be great if they could run these benchmarks with mongodb using TokuMX. I think tokutek's fractal trees would make an exciting data store for postgresql, but benchmarks are difficult to find... Who is perceived to be faster these days generally? Mysql+innodb, mysql+tokudb or postgresql?
- joshguthrie 13y ago$ brew remove mongodb Okay guys, now we're talking!
- eksith 13y agoImplementing our doc store on Postgres a while ago in lieu of switching to MongoDB suddenly seems like a very good call. We had nothing against Mongo (frankly haven't gone into deep analysis of how it would turn out). It was simply the db we already had at hand and we knew it well and trusted it.
- integraton 13y agoSee also the PostgreSQL as a Schemaless Database slides (includes several benchmarks vs MongoDB): http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2013.pdf http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2...
- jeffdavis 13y agoGreat work and research; horrible submission headline. The news here is that, in addition to the huge market for traditional DBs, postgres is going to compete in a serious way on MongoDB's home turf. As that becomes more apparent, it will validate postgres's flexability/adaptability and cast doubt over special-purpose database systems and NoSQL. MongoDB still has a story around clustering, of course. But that story is somewhat mixed (as all DB clustering stories are); and postgres is not standing still on that front, either. (Disclaimer: I'm a member of the postgres community.)
- rdtsc 13y ago> MongoDB still has a story around clustering, of course. Well, mostly. Except for when it doesn't, especially when it comes to partitions. http://aphyr.com/posts/284-call-me-maybe-mongodb http://aphyr.com/posts/284-call-me-maybe-mongodb
- craigching 13y agoHey Jeff, been a postgresql fan for a long time, but clustering is my number one issue. What is coming down the pipe for clustering/replication for PostgreSQL?
- jeffdavis 13y agoLogical replication will be a big deal over the next couple releases. I'm not sure exactly what will be available when, but I know that god foundations are being built now.
- craigching 13y agoJeff, first, thanks for the response and I'm heartened by what you say. If I could suggest, please look at ease of configuring replication in MongoDB. If you make it that easy, even if it's just for the JSON stores, I know that maybe you can't make it easy for relational tables, I will move my product to PostgreSQL in a heartbeat. I have used and promoted PostgreSQL throughout my career, but I'm currently stuck with having to deliver HA in a product for redistribution to customers and Slony can't cut it for our use cases, it's too complicated. I think the key is that we are a vendor and we need to provide a database with our products, so it's not me that's responsible for configuring HA, it's our users. And replication is what I'm concerned about 90% of the time, not necessarily horizontally scaling out (i.e. we're not using sharding in 90% of our cases, we just need HA).
- lucisferre 13y agoNow if I could only get Mongoid to work with Postgres instead of Mongo I would be happy.