Timeline for Use case for hstore
Current License: CC BY-SA 3.0
17 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 19, 2019 at 7:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Feb 3, 2019 at 23:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jun 12, 2017 at 18:41 | answer | added | Evan Carroll | timeline score: 2 | |
| Dec 5, 2015 at 14:32 | history | tweeted | twitter.com/StackDBAs/status/673148061254819840 | ||
| Dec 5, 2015 at 13:08 | comment | added | s.m. | @CraigRinger thank you, I'm reading your post from May on 2ndquadrant right now. Very interesting. The XML I have is not really nested, but I will have to make a few tests, because there's surely something obvious I'm missing. Also, even though I tagged the question as Postgres-9.4, I just remembered we'll be stuck with 9.3 for some more time, so jsonb might actually not be viable at all :/ | |
| Dec 5, 2015 at 13:00 | comment | added | s.m. |
@ErwinBrandstetter not sure I understand, but for sure there will be queries in which, for instance, I want to retrieve data only for employees who have a value of foo for tag bar for May 2015, and value baz for July. Actually, I can't foresee exactly which kind of queries we'll be doing, because once we have this kind of information handy, the possible scenarios will suddenly become many more than I can possibly imagine now.
|
|
| Dec 5, 2015 at 10:54 | comment | added | s.m. | @CraigRinger thank you, I will try to dig up some discussion and see what has been said. | |
| Dec 5, 2015 at 10:52 | comment | added | Craig Ringer | @s.m. That has been widely discussed and written about so won't go into detail. Short version: in core not an extension; supports data typing; supports nested objects and arrays (important for you) | |
| Dec 5, 2015 at 10:52 | comment | added | s.m. | @CraigRinger can I ask what the advantages of storing it as json would be compared to hstore? I was thinking of going with hstore simply because iterating over the XML and extracting the tags looked like the easiest thing to do. | |
| Dec 5, 2015 at 5:35 | comment | added | Erwin Brandstetter |
XML can't be indexed as efficiently as hstore .. Why do you care about that? Are there even going to be queries looking inside the value? If that's not the case, given that you mostly just store the data and never change and hardly read, I would run a quick test and pick the data type resulting in the smallest size on disk. Since you also don't care about data integrity inside the value, maybe even just text - unless there are going to be queries looking at tags inside the value.
|
|
| Dec 5, 2015 at 5:24 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
minor edits
|
| Dec 5, 2015 at 5:00 | comment | added | Craig Ringer | If you can't use the original XML and can't normalize sensibly I suggest transforming to json and storing as jsonb. | |
| Dec 4, 2015 at 23:14 | comment | added | s.m. | @JoishiBodio ah sure, I'm already reading the docs for it. The XML doesn't carry an XSD now that I think about it (don't ask, we're lucky we can parse it at all, it's generated from cobol by hand), so it's all strings anyway :/ | |
| Dec 4, 2015 at 23:07 | comment | added | Joishi Bodio | hmm... Well I'd suggest at least looking through the postgres documentation about XML and see what functionality it offers. If you don't think that'd be good enough, then by all means use HSTORE for it (it really is a pretty useful key -> value tool). Just a warning, though - if memory serves, every value you get from HSTORE will be given to you as a string and you'll have to cast if it's not supposed to be a string. | |
| Dec 4, 2015 at 23:02 | comment | added | s.m. | @JoishiBodio because just thinking about the kind of XML I'm dealing with makes me want to cry. I wish it was like the one I made up for my example. It's chock-full of weird non-printable characters that need to be stripped away. Plus, I seem to recall that XML can't be indexed as efficiently as hstore. I'll give it some more thought, though. | |
| Dec 4, 2015 at 22:56 | comment | added | Joishi Bodio | hstore is great, for sure - I have used it (in 9.1 which didn't support JSON - which is what I was working with). But Postgres 9.4 supports XML (and JSON).. Why not just process the XML as XML? | |
| Dec 4, 2015 at 22:49 | history | asked | s.m. | CC BY-SA 3.0 |