public inbox for [email protected]
help / color / mirror / Atom feedhstore or jsonb ?
9+ messages / 3 participants
[nested] [flat]
* hstore or jsonb ?
@ 2022-11-14 14:50 Bzzzz <[email protected]>
2022-11-14 21:03 ` Re: hstore or jsonb ? Laurenz Albe <[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 14:50 hstore or jsonb ? Bzzzz <[email protected]>
@ 2022-11-14 21:03 ` Laurenz Albe <[email protected]>
2022-11-14 21:21 ` Re: hstore or jsonb ? Bzzzz <[email protected]>
2022-11-14 21:40 ` Re: hstore or jsonb ? Bzzzz <[email protected]>
2022-11-15 08:39 ` Re: hstore or jsonb ? hubert depesz lubaczewski <[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 14:50 hstore or jsonb ? Bzzzz <[email protected]>
2022-11-14 21:03 ` Re: hstore or jsonb ? Laurenz Albe <[email protected]>
@ 2022-11-14 21:21 ` Bzzzz <[email protected]>
2022-11-15 07:05 ` Re: hstore or jsonb ? 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 14:50 hstore or jsonb ? Bzzzz <[email protected]>
2022-11-14 21:03 ` Re: hstore or jsonb ? Laurenz Albe <[email protected]>
2022-11-14 21:21 ` Re: hstore or jsonb ? Bzzzz <[email protected]>
@ 2022-11-15 07:05 ` Laurenz Albe <[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-14 14:50 hstore or jsonb ? Bzzzz <[email protected]>
2022-11-14 21:03 ` Re: hstore or jsonb ? Laurenz Albe <[email protected]>
@ 2022-11-14 21:40 ` Bzzzz <[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-14 14:50 hstore or jsonb ? Bzzzz <[email protected]>
2022-11-14 21:03 ` Re: hstore or jsonb ? Laurenz Albe <[email protected]>
@ 2022-11-15 08:39 ` hubert depesz lubaczewski <[email protected]>
2022-11-15 17:02 ` Re: hstore or jsonb ? 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-14 14:50 hstore or jsonb ? Bzzzz <[email protected]>
2022-11-14 21:03 ` Re: hstore or jsonb ? Laurenz Albe <[email protected]>
2022-11-15 08:39 ` Re: hstore or jsonb ? hubert depesz lubaczewski <[email protected]>
@ 2022-11-15 17:02 ` Laurenz Albe <[email protected]>
2022-11-15 17:32 ` Re: hstore or jsonb ? 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-14 14:50 hstore or jsonb ? Bzzzz <[email protected]>
2022-11-14 21:03 ` Re: hstore or jsonb ? Laurenz Albe <[email protected]>
2022-11-15 08:39 ` Re: hstore or jsonb ? hubert depesz lubaczewski <[email protected]>
2022-11-15 17:02 ` Re: hstore or jsonb ? Laurenz Albe <[email protected]>
@ 2022-11-15 17:32 ` hubert depesz lubaczewski <[email protected]>
2022-11-15 18:51 ` Re: hstore or jsonb ? Bzzzz <[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-14 14:50 hstore or jsonb ? Bzzzz <[email protected]>
2022-11-14 21:03 ` Re: hstore or jsonb ? Laurenz Albe <[email protected]>
2022-11-15 08:39 ` Re: hstore or jsonb ? hubert depesz lubaczewski <[email protected]>
2022-11-15 17:02 ` Re: hstore or jsonb ? Laurenz Albe <[email protected]>
2022-11-15 17:32 ` Re: hstore or jsonb ? hubert depesz lubaczewski <[email protected]>
@ 2022-11-15 18:51 ` Bzzzz <[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