public inbox for [email protected]help / color / mirror / Atom feed
hstore or jsonb ? 9+ messages / 3 participants [nested] [flat]
* hstore or jsonb ? @ 2022-11-14 14:50 Bzzzz <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Bzzzz @ 2022-11-14 14:50 UTC (permalink / raw) To: [email protected] Hi folks, I'd like to store specs, as they vary a lot from one item to another, hstore or jsonb look like the best solution. Specs can have 2 levels at most. ie: "color": ["white", "black", "blue"]). The indexation is mandatory for fulltext accurate researches in (almost) natural language. So, as the processing is almost the same (maps I/O in Erlang), which one would be the best for my needs, hstore or jsonb ? Also, from what I read, the both of them can be easily indexed, but which index is the best, GIN or RUM ? And furthermore, which index option (XXX_ops), if any, would be the best for the chosen one ? Jean-Yves ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: hstore or jsonb ? @ 2022-11-14 21:03 Laurenz Albe <[email protected]> parent: Bzzzz <[email protected]> 0 siblings, 3 replies; 9+ messages in thread From: Laurenz Albe @ 2022-11-14 21:03 UTC (permalink / raw) To: Bzzzz <[email protected]>; [email protected] On Mon, 2022-11-14 at 15:50 +0100, Bzzzz wrote: > I'd like to store specs, as they vary a lot from one item to another, > hstore or jsonb look like the best solution. > > Specs can have 2 levels at most. > ie: "color": ["white", "black", "blue"]). > > The indexation is mandatory for fulltext accurate researches in > (almost) natural language. > > So, as the processing is almost the same (maps I/O in Erlang), which one > would be the best for my needs, hstore or jsonb ? "hstore" is obsolete; don't use it. > Also, from what I read, the both of them can be easily indexed, but > which index is the best, GIN or RUM ? > > And furthermore, which index option (XXX_ops), if any, would be the best > for the chosen one ? That depends on the query you need to support. I have no idea what you mean by "fulltext accurate search" in a hstore or JSON. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: hstore or jsonb ? @ 2022-11-14 21:21 Bzzzz <[email protected]> parent: Laurenz Albe <[email protected]> 2 siblings, 1 reply; 9+ messages in thread From: Bzzzz @ 2022-11-14 21:21 UTC (permalink / raw) To: [email protected] On Mon, 14 Nov 2022 22:03:02 +0100 Laurenz Albe <[email protected]> wrote: > On Mon, 2022-11-14 at 15:50 +0100, Bzzzz wrote: > > I'd like to store specs, as they vary a lot from one item to > > another, hstore or jsonb look like the best solution. > > > > Specs can have 2 levels at most. > > ie: "color": ["white", "black", "blue"]). > > > > The indexation is mandatory for fulltext accurate researches in > > (almost) natural language. > > > > So, as the processing is almost the same (maps I/O in Erlang), > > which one would be the best for my needs, hstore or jsonb ? > > "hstore" is obsolete; don't use it. Oh, I missed that, so jsonb it'll be. > > Also, from what I read, the both of them can be easily indexed, but > > which index is the best, GIN or RUM ? > > > > And furthermore, which index option (XXX_ops), if any, would be the > > best for the chosen one ? > > That depends on the query you need to support. > > I have no idea what you mean by "fulltext accurate search" in a hstore > or JSON. One kind, that will be something like : SELECT websearch_to_tsquery('english', 'switching power supply output tension 5 Vcc 5 A') ; This will be for external, internal queries will specify more to be more accurate/restrictive. I can't put specs into columns because they are very different from one to another family of products, hence the use of json to keep them as malleable as possible. Jean-Yves ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: hstore or jsonb ? @ 2022-11-14 21:40 Bzzzz <[email protected]> parent: Laurenz Albe <[email protected]> 2 siblings, 0 replies; 9+ messages in thread From: Bzzzz @ 2022-11-14 21:40 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; [email protected] On Mon, 14 Nov 2022 22:03:02 +0100 Laurenz Albe <[email protected]> wrote: Oops, I forgot to say that a jsonb entry will automatically feed it's tsvector counterpart. Jean-Yves ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: hstore or jsonb ? @ 2022-11-15 07:05 Laurenz Albe <[email protected]> parent: Bzzzz <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Laurenz Albe @ 2022-11-15 07:05 UTC (permalink / raw) To: Bzzzz <[email protected]>; [email protected] On Mon, 2022-11-14 at 22:21 +0100, Bzzzz wrote: > That depends on the query you need to support. > > > > I have no idea what you mean by "fulltext accurate search" in a hstore > > or JSON. > > One kind, that will be something like : > SELECT websearch_to_tsquery('english', 'switching power supply > output tension 5 Vcc 5 A') ; Indexing full text search is simple. If the query is ... WHERE <someexpression> @@ <somequery> the index is to be CREATE INDEX ON tab USING gin ((<someexpression>)); Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: hstore or jsonb ? @ 2022-11-15 08:39 hubert depesz lubaczewski <[email protected]> parent: Laurenz Albe <[email protected]> 2 siblings, 1 reply; 9+ messages in thread From: hubert depesz lubaczewski @ 2022-11-15 08:39 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Bzzzz <[email protected]>; [email protected] On Mon, Nov 14, 2022 at 10:03:02PM +0100, Laurenz Albe wrote: > On Mon, 2022-11-14 at 15:50 +0100, Bzzzz wrote: > > I'd like to store specs, as they vary a lot from one item to another, > > hstore or jsonb look like the best solution. > > > > Specs can have 2 levels at most. > > ie: "color": ["white", "black", "blue"]). > > > > The indexation is mandatory for fulltext accurate researches in > > (almost) natural language. > > > > So, as the processing is almost the same (maps I/O in Erlang), which one > > would be the best for my needs, hstore or jsonb ? > > "hstore" is obsolete; don't use it. Do you have any support for this claim? Best regards, depesz ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: hstore or jsonb ? @ 2022-11-15 17:02 Laurenz Albe <[email protected]> parent: hubert depesz lubaczewski <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Laurenz Albe @ 2022-11-15 17:02 UTC (permalink / raw) To: [email protected]; +Cc: Bzzzz <[email protected]>; [email protected] On Tue, 2022-11-15 at 09:39 +0100, hubert depesz lubaczewski wrote: > > "hstore" is obsolete; don't use it. > > Do you have any support for this claim? No. But is there anything that hstore can that JSON cannot? Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: hstore or jsonb ? @ 2022-11-15 17:32 hubert depesz lubaczewski <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: hubert depesz lubaczewski @ 2022-11-15 17:32 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Bzzzz <[email protected]>; [email protected] On Tue, Nov 15, 2022 at 06:02:51PM +0100, Laurenz Albe wrote: > On Tue, 2022-11-15 at 09:39 +0100, hubert depesz lubaczewski wrote: > > > "hstore" is obsolete; don't use it. > > > > Do you have any support for this claim? > > No. > > But is there anything that hstore can that JSON cannot? It's faster for some things: https://www.depesz.com/2021/04/21/getting-value-from-dynamic-column-in-pl-pgsql-triggers/ And it can be trivially used to check for differences in update triggers: hstore(NEW) - hstore(OLD) It's "abusability" is lower thanks to lack of nesting. depesz ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: hstore or jsonb ? @ 2022-11-15 18:51 Bzzzz <[email protected]> parent: hubert depesz lubaczewski <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bzzzz @ 2022-11-15 18:51 UTC (permalink / raw) To: hubert depesz lubaczewski <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] On Tue, 15 Nov 2022 18:32:24 +0100 hubert depesz lubaczewski <[email protected]> wrote: > > But is there anything that hstore can that JSON cannot? > > It's faster for some things: > https://www.depesz.com/2021/04/21/getting-value-from-dynamic-column-in-pl-pgsql-triggers/ Thanks for the URL, the difference is indeed quite large. > And it can be trivially used to check for differences in update > triggers: > hstore(NEW) - hstore(OLD) I didn't knew that (and it's not part of the official doc :/) > It's "abusability" is lower thanks to lack of nesting. Yeah, it is a problem as some specs can contain a list, I discovered that last night - so jsonb it'll be. Jean-Yves ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2022-11-15 18:51 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-11-14 14:50 hstore or jsonb ? Bzzzz <[email protected]> 2022-11-14 21:03 ` Laurenz Albe <[email protected]> 2022-11-14 21:21 ` Bzzzz <[email protected]> 2022-11-15 07:05 ` Laurenz Albe <[email protected]> 2022-11-14 21:40 ` Bzzzz <[email protected]> 2022-11-15 08:39 ` hubert depesz lubaczewski <[email protected]> 2022-11-15 17:02 ` Laurenz Albe <[email protected]> 2022-11-15 17:32 ` hubert depesz lubaczewski <[email protected]> 2022-11-15 18:51 ` Bzzzz <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox