When to Avoid JSONB in a PostgreSQL Schema

Author here. Curious what experiences y&#x27;all have had with JSONB.<p>We&#x27;re in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far.<p>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&#x2F;o concurrency is really high and prefetching for a bitmap scan works particularly well.

The fact that you can pass attributes for a record into the JSONB field without defining the table structure in advance is really the decisive feature because then you never have to bother with changing your data model. For example, if you have contacts streaming into your table from Android devices you don&#x27;t need to say whether or not there should be a column for &quot;work email&quot; and &quot;home email2&quot; etc etc... you just send everything into a column with key&#x2F;value pairs and you can put whatever key&#x2F;value pairs you want in that column. And then query over the keys without inserting a gazillion nulls into your database for rows that don&#x27;t have a value for a particular key. You can also do updates on the json column AND you get all of the benefits of relational databases with other columns in the same table. I can&#x27;t really see how anyone would not love this data type now that I have been exposed to it in production.

&quot;For datasets with many optional values, it is often impractical or impossible to include each one as a table column.&quot;<p>Honest question - what settings would many optional values be impractical or impossible? Is it purely space&#x2F;performance constraints? If so, it doesn&#x27;t sound like JSONB gives you wins in either of those cases.

Keep in mind that Heap collects a huge amount of data and has a huge dataset. I like this[1] talk, it gives you an overview of their architecture. Great post though, thank you for sharing.<p>[1] <a href="https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=NVl9_6J1G60" rel="nofollow">https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=NVl9_6J1G60</a>

One thing I would add to the list: Don&#x27;t use it for data that you need to change. There are (at least at present) no built-in functions to modify values within JSON(B) objects, which makes it very tedious modify data once it has been stored.<p>It is much better for data that is stored once and then queried.

<i>&gt; It has no way of knowing, for example, that record -&gt;&gt; &#x27;value_2&#x27; = 0 will be true about 50% of the time</i><p>Can&#x27;t this be solved by introducing an expression index[1] for &quot;record -&gt;&gt; &#x27;value_2&#x27;&quot;?<p>This would add a specialized index that will be used of all queries that have a filter like &quot;WHERE record -&gt;&gt; &#x27;value_2&#x27; = 0 AND ...&quot;.<p>[1] <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;indexes-expressional.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;indexes-expre...</a>

Gee go figure that using native columns is faster and better to query over.

In fact, JSONB is probably not a good idea when it comes to analytics.<p>The storage is almost x2, accessing attributes are expensive than tabular data even though JSONB is indexed, the data can be dirty (the client can send extra attributes or invalid values for existing attributes, since JSONB doesn&#x27;t have any schema, Postgresql doesn&#x27;t validate and cast the values) and as the author mentioned, Postgresql statistics and indexes don&#x27;t play nicely with JSONB.

Most of our tables have a uuid, a jsonb entity along with relationships stored as uuid columns with a _fk suffix. We then index the FK&#x27;s.

Does adding a Gin index to the JSONB help this?

Why dont document databases automatically save common keys in some kind of lookup table?<p>Seems like a basic feature to improve space savings and processing speed.

Hey SQL newbie question: why use JSONB when you could split out tables into `user` and `user_meta`? isn&#x27;t that how Wordpress works?

Using jsonb also brings the headache of having to worry about the version of Postgres you&#x27;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&#x2F;jsonb is not very good yet.<p>But as an alternative to things like serialized objects, I think it&#x27;s definitely a huge win. You can do things like join a jsonb object property to its parent table, which wouldn&#x27;t be possible with serialized objects.

I used JSONB to keep the Tags on an object. Used to be EAV. Works way better here

I usually use JSONB but keep a non jsonb column as the PK.

Definitely have been bitten with the query statistics issue before. I worked with a colleague once who was adamant that we build our backend on MongoDB, but I was able to convince him to build on Postgres because of it&#x27;s JSONB support. I don&#x27;t get why, since schema updates are generally very cheap with databases like Postgres (adding columns without a default or deleting columns is basically just a metadata change), but some developers believe its worth the headache of going schema-less to avoid migrations. In a sense, that suggestion kind of bit me in the ass when we started having some painfully slow report generation queries that should have been using indexes, but were doing table scans because of the lack of table statistics. In a much larger sense, I&#x27;m still thankful we never used MongoDB.<p>Protip: Use the planner config settings[1] (one of which is mentioned in this article) with SET LOCAL in a transaction if you&#x27;re really sure the query planner is giving you guff. On more structured data that Postgres can calculate statistics on, let it do its magic.<p>[1]: <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;runtime-config-query.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;runtime-confi...</a>