Here's two reasons to use HStore:
1) It can turn O(N) operations into O(1)
Imagine trying to implement "Facebook-like" friend list: Every time a user hits their home page, the DB needs get their "friends". In a typical join table, this will take O(N) disk seeks.
So you think about storing the friend's names right on the user object, maybe as a string. But it will be slow -- you have to parse the string (server-side inside a transaction) to modify it.
So you think about a cache. But that just introduces new problems. ("There are two hard problems in Computer Science: Naming and Cache Invalidation.")
With a HStore, you don't need a cache -- It's O(1) to get friend list, and O(1) to modify it. Of course, the trade-off is duplicate data. (That's not a NoSQL thing. SQL people are always denormalizing for performance. (e.g. "total_order_price" or "num_friends")
2) It's a lot simpler. You have a user object (row) with an HStore friends column. You add friends to it, you subtract friends from it, you get all the friends. You don't need a fancy ORM to hide the complexity of messing about with multiple tables and extra IDs. (The trade-off is no FK checking. But MySQL proved we don't need it ;)
If you squint, HStore is really more like a Docstore than a Key-Value store. (A pure KV suffers from "last in wins" problems because you can't do sub-operations on a value. Only Get and Set.)
Copyright © 2017, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds