Skip to main content
Database Administrators

Timeline for Best index for jsonb in Postgres

Current License: CC BY-SA 4.0

8 events
when toggle format what by license comment
S Oct 1, 2019 at 16:08 history edited András Váczi CC BY-SA 4.0
I believe adding follower_count on the actual table is normalizing the data, not denormalizing it.
S Oct 1, 2019 at 16:08 history suggested Bwvolleyball CC BY-SA 4.0
I believe adding follower_count on the actual table is normalizing the data, not denormalizing it.
Oct 1, 2019 at 14:57 review Suggested edits
S Oct 1, 2019 at 16:08
May 17, 2017 at 16:03 comment added Evan Carroll No, it's far better than text. At least you can run a comparison operator on it. It's just far worse than int. Generally, my rule of thumb for jsonb is to use it only on things you do not query on, that need to be extended and would otherwise result in an EAV table. You're querying on jsonb and trying to impose a schema. I don't believe that's what it's good at. Ideally, no constraints, and no b-trees on jsonb. If they're there in my work it's because of an oversight in design, not by intent.
May 17, 2017 at 15:59 comment added borjagvo If I understand correctly, then you just would recommend store text or boolean inside a jsonb field? What are the disadvantages of storing it as numeric? How much does that impact on the performance? I guess is a function of the dataset size...
May 17, 2017 at 15:53 comment added Evan Carroll jsonb stores internally as an inflated numeric type. even if you index it as an int, afaik it's getting stored as a super-fat numeric type on the row. And, pulling down that requires you to CAST(AS int) which you would not otherwise need to do.
May 17, 2017 at 15:47 comment added borjagvo Thanks Evan. However, do you mean taking out the field and putting it at the same level of 'ig'? What would it be the difference between that and having it indexed inside a jsonb? (in terms of performance).
May 17, 2017 at 15:38 history answered Evan Carroll CC BY-SA 3.0