JSONbeyond: Exploring PostgreSQL’s JSONb DataType

Working as Backend Engineer we’re bound to be exposed to datatypes, choosing a suitable datatype for each task is highly crucial in designing a System. Throughout the years, technology has evolved and many datatypes has been made to describe each data for a certain scenario and purpose.

This story is about my experience on my last feature development where I explored JSONb and its features, adapting to new methods and features to solve my problem.

Problem

The problem I faced is when storing an array of JSON structure. Implementing read and write operations by treating JSON as a TEXT datatype is a very inefficient task, since as a Backend the steps to present the data is divided into many steps which I found unnecessary. After some research, I was able to find out about JSON datatypes in Postgres and what other developers opinion about it.

What is JSONb ?

There are actually two datatypes for JSON in Postgres, each with their pros and cons, JSON and JSONb. What does the “b” stands for you might ask ? it’s stands for binary, but some might say it stands for “better”. JSONb has a unique way of storing its data in a decomposed binary form, which grants faster access time since it doesn’t need to be parsed before used.

Upon solving my problem, I needed a way to store a simple JSON array of objects for every row then access them again when needed. The first approach was to store it simply as text then retrieve it and parse it and search for the wanted object in the JSON (which includes a lot of steps and resources to do for the Backend system). Luckily JSONb is created with some features that helps a lot, it comes with built in functions to help query them easily and it also supports indexing (which will save a lot of time when searching).

Performance

On the side of things, these two has a difference in performance. Each with their own pros and cons

JSONb pros :

• Fast read time since it’s stored in a binary form and holds its JSON structure you can easily query them with fast response.
• Accessibility, it’s easy to access and query certain fields in a JSON structure since it is supported with its built-in function from Postgres.
• Indexing, fully supports indexing to boost reading performance.

JSONb cons :

• Slower write time, meaning inserting an object may be slower than JSON datatype.
• Can’t be used if JSON that is stored is format sensitive, since JSONb will change it’s format (ex : trimming whitespaces, not keeping order of keys, etc).

Conclusion

As a developer we’re bound to be exposed to new features, technology is something that will keep evolving. To be able to adapt is what makes us a developer, solving new problems with newer solutions, taking advantage of what is available. I was lucky enough to try and implement Postgres JSONb datatype to solve our tech problem and it worked very efficiently. I hope this experience I’m sharing can be helpful and also encourage readers to be open to new solutions in our day to day problem solving scenarios.