News for hackers. Stuff that matters.
Re:Hstore development for 9.4 release by andreypopp on Thursday November 07, 2013 @10:13AM (#6689713)
This 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 !"

Re:Hstore development for 9.4 release by baudehlo on Thursday November 07, 2013 @10:36AM (#6689896)
But 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.
Re:Hstore development for 9.4 release by integraton on Thursday November 07, 2013 @12:08PM (#6690560)
See slide 44 here: 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.

Re:Hstore development for 9.4 release by garenp on Thursday November 07, 2013 @02:40PM (#6691887)
800MB 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.
Re:Hstore development for 9.4 release by eCa on Thursday November 07, 2013 @10:44AM (#6689960)
Space is cheap. Time is expensive.
Re:Hstore development for 9.4 release by falcolas on Thursday November 07, 2013 @10:51AM (#6689998)
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.

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.

Re:Hstore development for 9.4 release by mason55 on Thursday November 07, 2013 @11:16AM (#6690187)
> 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

Re:Hstore development for 9.4 release by saosebastiao on Thursday November 07, 2013 @12:19PM (#6690669)
Space is cheap, but IOPS are not, and RAM always has an opportunity cost.
Re:Hstore development for 9.4 release by wobbleblob on Thursday November 07, 2013 @10:49AM (#6689979)
That's why your index should fit in RAM
Re:Hstore development for 9.4 release by penguat on Thursday November 07, 2013 @11:08AM (#6690121)
Is that 800Mb in RAM? That could get expensive.
Re:Hstore development for 9.4 release by bsg75 on Thursday November 07, 2013 @10:56AM (#6690044)
In space, no one can hear you scream.
Re:Hstore development for 9.4 release by eonil on Thursday November 07, 2013 @11:01AM (#6690081)
The real surprise is this comes with true multi-key ACID transaction.
Re:Hstore development for 9.4 release by karavelov on Thursday November 07, 2013 @11:46AM (#6690418)
If you want to index on specific field with PostgreSQL you could use function indexes and bring the index size down