When to Avoid JSONB in a PostgreSQL Schema
blog.heapanalytics.com
When to Avoid JSONB in a PostgreSQL Schema
1–10 of 115 posts
Re: When to Avoid JSONB in a PostgreSQL Schema
#2Author here. Curious what experiences y'all have had with JSONB.
We're in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far.
Another win has been that the better stats make it possible to reliably get bitmap joins from the planner. Our configuration uses ~12 RAIDed ebs drives, so the i/o concurrency is really high and prefetching for a bitmap scan works particularly well.
Re: When to Avoid JSONB in a PostgreSQL Schema
#3Using jsonb also brings the headache of having to worry about the version of Postgres you're using. Simple functionality like updating an object property in place might be missing in your version. And the documentation and stack-overflow-ability of json/jsonb is not very good yet.
But as an alternative to things like serialized objects, I think it's definitely a huge win. You can do things like join a jsonb object property to its parent table, which wouldn't be possible with serialized objects.
Re: When to Avoid JSONB in a PostgreSQL Schema
#4Using jsonb also brings the headache of having to worry about the version of Postgres you're using. Simple functionality like updating an object property in place might be missing in your version. And the documentation and stack-overflow-ability of json/jsonb is not very good yet. But as an alternative to things like serialized objects, I think it's definitely a huge win. You can do things like join a jsonb object pr…
We have a bag of utils internally to paper over the missing JSONB functions. This was definitely a headache at first.
This is mostly fixed in 9.5: http://blog.2ndquadrant.com/jsonb-and-postgresql-9-5-with-ev...
Re: When to Avoid JSONB in a PostgreSQL Schema
#5Author here. Curious what experiences y'all have had with JSONB. We're in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far. Another win has been that the better stats make it possible to reliably get bitmap joins from the planner. Our configuration uses ~12 RAIDed ebs drives, so the i/o concurrency is really high and prefetching for a bitmap scan…
Was the 30% disk saving over petabyte+ data set on a single-node Postgres or on your Citus cluster?
Re: When to Avoid JSONB in a PostgreSQL Schema
#6Author here. Curious what experiences y'all have had with JSONB. We're in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far. Another win has been that the better stats make it possible to reliably get bitmap joins from the planner. Our configuration uses ~12 RAIDed ebs drives, so the i/o concurrency is really high and prefetching for a bitmap scan…
Was the 30% disk saving over petabyte+ data set on a single-node Postgres or on your Citus cluster?
This isn't live yet, but we expect it to be across our citus cluster. The ~30% figure comes from the profiling we did on individual postgres nodes.
Re: When to Avoid JSONB in a PostgreSQL Schema
#7Author here. Curious what experiences y'all have had with JSONB. We're in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far. Another win has been that the better stats make it possible to reliably get bitmap joins from the planner. Our configuration uses ~12 RAIDed ebs drives, so the i/o concurrency is really high and prefetching for a bitmap scan…
I'm using JSONB and the downsides on performance are not noticeable for most cases. For those where there are real problems then crafting a custom index usually fixes the issue.
Using ->> (or ->) in a WHERE statement is generally a bad idea, and certainly a terrible idea without an explicit index. Use @> instead.
Re: When to Avoid JSONB in a PostgreSQL Schema
#8Using jsonb also brings the headache of having to worry about the version of Postgres you're using. Simple functionality like updating an object property in place might be missing in your version. And the documentation and stack-overflow-ability of json/jsonb is not very good yet. But as an alternative to things like serialized objects, I think it's definitely a huge win. You can do things like join a jsonb object pr…
And why is this a headache? Every time a new feature is introduced, you have to worry about the PostgreSQL version.
If you're developing an application in-house, this is not a big deal - you can make sure you have the right PostgreSQL version. If you're hosting the application on a shared database server, well, you're exactly in the same situation as with other software products.
Re: When to Avoid JSONB in a PostgreSQL Schema
#9Author here. Curious what experiences y'all have had with JSONB. We're in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far. Another win has been that the better stats make it possible to reliably get bitmap joins from the planner. Our configuration uses ~12 RAIDed ebs drives, so the i/o concurrency is really high and prefetching for a bitmap scan…
I'm using JSONB and the downsides on performance are not noticeable for most cases. For those where there are real problems then crafting a custom index usually fixes the issue. Using ->> (or ->) in a WHERE statement is generally a bad idea, and certainly a terrible idea without an explicit index. Use @> instead.
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.
Re: When to Avoid JSONB in a PostgreSQL Schema
#10Earlier quoted context omitted.
I'm using JSONB and the downsides on performance are not noticeable for most cases. For those where there are real problems then crafting a custom index usually fixes the issue. Using ->> (or ->) in a WHERE statement is generally a bad idea, and certainly a terrible idea without an explicit index. Use @> instead.
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.