Thread: hstore or jsonb ?
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
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
On Mon, 14 Nov 2022 22:03:02 +0100
Laurenz Albe <laurenz.albe@cybertec.at> 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
On Mon, 14 Nov 2022 22:03:02 +0100 Laurenz Albe <laurenz.albe@cybertec.at> wrote: Oops, I forgot to say that a jsonb entry will automatically feed it's tsvector counterpart. Jean-Yves
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
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
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
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
On Tue, 15 Nov 2022 18:32:24 +0100 hubert depesz lubaczewski <depesz@depesz.com> 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