When should you use the JSONB type in Postgres?

Thomas Dickson

2 minute read

Caveat: this note is heavily context dependent. The solution of a given problem is informed by the particular version of a database, the difficulty of implementing a solution and ease of maintaining the solution when in production. Don’t be afraid to come up with multiple plans and iterating over the design cycle a few times.

I recently had to design a database table schema for use to store new metrics being produced as part of a new data analytics process. As a softare engineer who is fresh to database schema design I investigated different options for storing data. One of these options was to use the JSONB type to store multiple values. This post is a summary of the different knowledge I gleaned from various blog posts.

JSONB can appear to be an attractive choice as it means that there is no requirement to manage the schema of the database. The use of JSONB is apparently a common trap is it can result in multiple undocumented variations of a given schema which are stored in an opaque manner in the database. There are numerous downsides to using JSON for anything other than unstructured blobs of data in a manner similar to NoSQL or JSONs.

Pros for using JSONB

Cons of using JSONB

Annotated bibliography

Heap: When To Avoid JSONB In A PostgreSQL Schema

When to Avoid JSONB in a PostgreSQL Schema

Faster Operations with the JSONB Data Type in PostgreSQL

Explanation of JSONB introduced by PostgreSQL