Hstore development for 9.4 release
obartunov.livejournal.com
obartunov.livejournal.com
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?
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?
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.
How can postgres possible be faster on insert? Unlike Mongo, Postgres actually needs to write bytes to a disk (with a WAL!).
Not with unlogged tables it does not!
> Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client.
> This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits.
http://www.postgresql.org/docs/9.3/static/runtime-config-wal...
It also means that you can be sure that the data you read from an unlogged table is that data that you put there. I much prefer this over the possibility of maybe reading corrupt data without any possibility of detection.
In my case, the use-case of an unlogged table is a table that practically works like a materialized view (until postgres learns to refresh them without an exclusive lock and maybe even automatically as the underlying data changes). If the table is empty, I can easily recreate the data, so I don't care about any kind of WAL logging or replication. If the table's there, perfect. I'll use it instead of a ton of joins and an equally big amount of application logic.
If the data isn't there, well. Then I run that application logic which will cause the joins to happen. So that's way, way slower, but it'll work the same.
The advantage of using an unlogged table here is that it's cheaper to update it and it doesn't use replication traffic (unlogged tables are not replicated).
I'm now immune to MongoDB bashing now but jesus keep up with updates. This isn't true as of maybe two years ago.
That same blog notes Postgres is susceptible to the same issue: http://aphyr.com/posts/282-call-me-maybe-postgres
We do want Jesus to keep up with the updates, for sure, but this isn't just about a random slip-up, goes a bit deeper than that. This wasn't an unfortunate bug, "oh we meant to do all we could to keep your data safe" it was a deliberate design decision.
Dishonesty and shadiness was the problem. When finally they started shipping with better defaults, did we see an apology? Was anyone responsible terminated? Nope. Don't remember.
Now, it could all be a statistics fluke and people randomly all decided to bash MongoDB, say, more than Basho, Cassandra, PostgreSQL, HBase and other database products? Odd isn't it. Or, isn't it more rational, that it isn't quite random, there is a reason for it.
When developers sell to developers, it is expected a certain level of honesty. If I claim I am building a credit card processing API but I deliberately add a feature that randomly double charges to gain extra performance, you should be very upset about. Yes, it is in the documentation, on page 156, that says in fine print "oh yeah enable safe transaction, should have read that". And you as a result should probably never buy or use my product and stop trusting me with credit card processing from them on.
The bottom line is, they are probably good guys to have beers with but I wouldn't trust them with storing data.
Shady? Yes. Dishonest? No. To be sure, both of these attributes suck, but beyond that there is an absolute world of difference between the two. One passively leaves knowledge gaps that even novice consumer programmers would uncover (the source is wide open after all, and speaking from personal experience, any claims of hyper performance should arouse enough suspicion to inspire some basic research), while the other asserts no knowledge gaps and actively inhibits knowledge discovery.
That said, they fell very far form the ideal, and for that they should be faulted. They're now paying the price by living in a dense fog of distaste and disgust. I don't think anyone believes it's a "statistics fluke" but it's a likely that a mob/tribal mentality plays some role (this is an industry notoriously susceptible to cargo cults) and that many individuals on the burn-MongoDB-at-the-stake bandwagon do not actually have deep--or moderate!--experience with it and competing products. Also, would you chalk it up to statistical anamoly that they were able to close funding and that many large scale systems use their product?
Isn't it possible that the company had shameful marketing practices that repel the programmer psyche, but that the product itself has a unique enough featureset (schemaless yet semi-structured querying, indexing, dead simple partitioning and replication, non-proprietary management language, simple backups, all at the cost of RAM/SSD) to make it compelling for some problem classes?
Mongo locks. Postgres rocks. [1]
1: http://www.postgresql.org/docs/current/static/mvcc-intro.htm...
http://www.sai.msu.su/~megera/postgres/talks/Next%20generati...
This makes me very, very happy.
Edit:
I should point out why: -my document structures pretty much match 1:1 with hstore now (we have nested arrays of hashes) -i can query & report on the data with normal tools -i can update individual fields on my data (currently impossible) -i can do relational joins & such-like in the db (currently impossible) -i can have sensible transactions
That said, if Postgres matches/exceeds enough Mongo features, it could certainly outshine Mongo for those scenarios.
AFAIK, map-reduce is relatively expensive on Mongo... typically recommended only for background execution, nothing synchronous.
Easy replication ... that's why I use MongoDB.
"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 !"
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.
This is why MongoDB recommends all indices fit in memory
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.
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.)
Well, mostly. Except for when it doesn't, especially when it comes to partitions.
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).
Have you looked at streaming replication in postgres?
http://www.postgresql.org/docs/9.3/static/high-availability.... http://www.postgresql.org/docs/9.3/static/warm-standby.html#...
I'm not sure what level of expertise your customers are at, so this might not work for you. But it seems like a better fit than slony at least.
I.e. can I do something like SELECT json_field FROM data WHERE json_field.age > 15 ?
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-...
http://www.postgresql.org/docs/9.3/static/functions-json.htm...
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.
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.
Just to be clear, MongoDB is fine db for certain scenarios and I am using it in production.
"ZFS has a heckuva caching strategy, and you know when it accepts a write."
:P
I really wish PostgreSQL wasn't such an enormous challenge to scale horizontally.
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.
No, but I really like the recent enhancements in PostgreSQL. Failover is nearly as easy as in MongoDB, however all this doesn't play so nicely yet, if you are using stuff like extensions (PostGIS), your own functions and still isn't really an out-of-the box experience.
I agree, if you use it with the same limits and the performance gain is worth it, you can as well be using Postgres. However, a lot of this actually only changed in the recent releases. It's all still fairly new.
Also there are still a number of things that are basically missing, like out of the box upserts (we are using a function for this, but it more a hack) and if you are still somewhat in development a lot of little changes get really hard in PostgreSQL. Converting your data structure, even with stuff like CTE and surrounding functionality can become really challenging, especially when you think there must be an easier way.
Where it is easier to modify structures in MongoDB it is actually harder to aggregate it sometimes. Using stuff like Map Reduce (even the lightweight version called aggregate) frequently appears sort of an overkill.
I think however it really depends on the kind of data you are dealing with. That's why we are using a hybrid system right now. Both systems are actually evolving really quickly and if you have the joy of using their most recent versions one is always excited about new releases.
MongoDB marketing sloppy and promises "webscale" while hand waving partition-tolerance way.
After the whole "let's disable durable write to make our benchmarks faster" I just can't see trusting them with data I want to actually read back from the database. It might be good for probabilistic storage or stats reporting. It sort of because an issue of trust more than anything.
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.
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.
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/
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?
Links to the talk, slides, sample code, and some follow up articles: http://monkeyandcrow.com/blog/postgres_railsconf2013/
Throw in a JSON schema validator and we have full-blown validations for :hstore and :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.
$ brew remove mongodb