11–20 of 115 posts
Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
Earlier quoted context omitted.
Using @> instead of ->> only causes the selectivity estimate of the predicate to be a different hard coded estimate. It doesn't fix the underlying problem of Postgres not keeping statistics on JSONB.
True it doesn't solve the problem of not having statistics on the values, but it does bring the query response time down to the same order of magnitude as the non-JSON table.
In the specific example given it might, but you will still wind up with a handful of queries that are planned wrong and are orders of magnitude slower.
Earlier quoted context omitted.
True it doesn't solve the problem of not having statistics on the values, but it does bring the query response time down to the same order of magnitude as the non-JSON table.
> but it does bring the query response time down to the same order of magnitude as the non-JSON table. In the specific example given it might, but you will still wind up with a handful of queries that are planned wrong and are orders of magnitude slower.
"For datasets with many optional values, it is often impractical or impossible to include each one as a table column." Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
Earlier quoted context omitted.
> but it does bring the query response time down to the same order of magnitude as the non-JSON table. In the specific example given it might, but you will still wind up with a handful of queries that are planned wrong and are orders of magnitude slower.
There are two separate issues. The lack of statistics is one thing, but the use of ->> instead of @> is another. Look at https://explain.depesz.com/s/zJiT Vs https://explain.depesz.com/s/ihwk for the difference.
"For datasets with many optional values, it is often impractical or impossible to include each one as a table column." Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
This may make sense if you have custom fields, i.e. the set of keys is user-defined and ever increasing.
Apart from that, there is no reason to do this. If it is all about avoiding having "too many" columns and/or "too may" null values, then I'd say: Don't worry. Just use as many columns as you need.
I would even go one step further and say: It is a common anti-pattern to introduce generic key-value stores (through a separate table, JSON structures or XML structures) without a compelling reason.
"For datasets with many optional values, it is often impractical or impossible to include each one as a table column." Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
I was also wondering about this one. This may make sense if you have custom fields, i.e. the set of keys is user-defined and ever increasing. Apart from that, there is no reason to do this. If it is all about avoiding having "too many" columns and/or "too may" null values, then I'd say: Don't worry. Just use as many columns as you need. I would even go one step further and say: It is a common anti-pattern to introduc…
This is an anti-pattern because you can't model "1:1" that way. Instead, it will be "1:0..1", and now you have some nasty corner cases when the first table has an entry whose counterpart in the second table is missing. Also, when using a column now you always have to think about which table had it - the first or the second table?
I had to work with such a design in a real-world project and it was really annoying.
"For datasets with many optional values, it is often impractical or impossible to include each one as a table column." Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
"For datasets with many optional values, it is often impractical or impossible to include each one as a table column." Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
actually we store only 6 things inside the table and the rest inside the jsonb.
however we sill miss like 8 values which we are using inside a list, which are slow, but materialized view to the rescue. however we may pull them out at some point, still need to figuring out since the jsonb data set is also the value of a hash. that checks the validity of the data inside their which we use for change detection against other stuff