Thread

1. hstore or jsonb ?

   Bzzzz <lazyvirus@gmx.com> — 2022-11-14T14:50:57Z

   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
   
   
   
   

2. Re: hstore or jsonb ?

   Laurenz Albe <laurenz.albe@cybertec.at> — 2022-11-14T21:03:02Z

   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
   
   
   
   

3. Re: hstore or jsonb ?

   Bzzzz <lazyvirus@gmx.com> — 2022-11-14T21:21:13Z

   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
   
   
   
   

4. Re: hstore or jsonb ?

   Bzzzz <lazyvirus@gmx.com> — 2022-11-14T21:40:51Z

   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
   
   
   
   

5. Re: hstore or jsonb ?

   Laurenz Albe <laurenz.albe@cybertec.at> — 2022-11-15T07:05:31Z

   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
   
   
   
   

6. Re: hstore or jsonb ?

   hubert depesz lubaczewski <depesz@depesz.com> — 2022-11-15T08:39:46Z

   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
   
   
   
   
   

7. Re: hstore or jsonb ?

   Laurenz Albe <laurenz.albe@cybertec.at> — 2022-11-15T17:02:51Z

   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
   
   
   
   

8. Re: hstore or jsonb ?

   hubert depesz lubaczewski <depesz@depesz.com> — 2022-11-15T17:32:24Z

   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
   
   
   
   

9. Re: hstore or jsonb ?

   Bzzzz <lazyvirus@gmx.com> — 2022-11-15T18:51:55Z

   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