HStore and JSON or JSONB are two of the most popular unstructured data types supported in PostgreSQL databases, HStore being one of the oldest ones introduced in Postgres 8.3, whereas support for JSON data types was added later in PostgreSQL 9.2. Over the years Postgres has evolved to support wider use cases and has remained one of the best(if not the best) databases of choice to build apps. In this article, we’ll dive into various unstructured data types supported in Postgres and what’s common, and what’s the difference between them.
Being the first unstructured data type supported in Postgres, HStore came with some limitations. First, it can only store data in string format. You cannot store numbers, booleans, or other types of values. Everything is stored as a string. Another limitation it has is that hstore in Postgres can not store nested key-value pairs, you are only limited to one level.
JSONB is an improvement over the traditional JSON data type, with the difference being the way JSON data is stored in JSONB. JSONB in Postgres stores the binary representation of the data. That helps it overcome the performance issues of JSON data type. Since the data is stored in a decomposed binary format, that makes it significantly faster to read and process since no parsing is required, and also slower to input due to added step of data conversion. Another advantage it has over the JSON type is that it also supports Indexing.
There’s more: JSONPath
With the increase in usage and popularity of the JSON and JSONB fields, the 2016 SQL standard added a new standard/path language for navigating and traversing JSON data in Postgres. It’s a new approach to searching JSON data. It’s very similar to XPath for XML data, and it was added in PostgreSQL 12.
So, these we all the ways to store unstructured data in a Postgres database. With the increase in complexity of the apps, the traditional SQL databases evolved to support these new data types and have become more versatile and an even better database of choice. Hope this article helped you understand the basic differences between various unstructured data types supported in the PostgreSQL database. I will recommend checking out the links below to learn more about them.
Postgres Data Types: https://www.postgresql.org/docs/15/datatype.html
Postgres HStore Docs: https://www.postgresql.org/docs/15/hstore.html
Postgres JSON Functions Docs: https://www.postgresql.org/docs/15/functions-json.html