I was a little hesitant to use hstore in my latest project, but it ended up being really cool.
I'm basically storing a trees of metadata that catalogs, in this case, a "season" of television as a root and then each individual episode as a leaf.
What I thought was really cool was that I can store the object references (as a self-referential foreign key) outside of the hstore: this means that the join I use when fetching the tree is still indexed and very inexpensive.
However the actual metadata itself is stored as an hstore payload -- which can be indexed in addition to being part of your query. The only downside is you lose a bit of typesafety since hstore is `text->text`.
It let me throw an application together without committing to a rigid schema, which was nice -- and it actually ends up being pretty easy to work with.
I just thought it was really cool how easy it was to separate the unstructured data, and the well-structured relation of that data.
I'm basically storing a trees of metadata that catalogs, in this case, a "season" of television as a root and then each individual episode as a leaf.
What I thought was really cool was that I can store the object references (as a self-referential foreign key) outside of the hstore: this means that the join I use when fetching the tree is still indexed and very inexpensive.
However the actual metadata itself is stored as an hstore payload -- which can be indexed in addition to being part of your query. The only downside is you lose a bit of typesafety since hstore is `text->text`.
It let me throw an application together without committing to a rigid schema, which was nice -- and it actually ends up being pretty easy to work with.
I just thought it was really cool how easy it was to separate the unstructured data, and the well-structured relation of that data.