HStore and JSON, or JSONB, are two of the most popular unstructured data types supported in PostgreSQL databases, with 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.
HStore
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 cannot store nested key-value pairs; you are only limited to one level.
JSON
JSON stands for Javascript Object Notation. It’s an open format for storing key-value pairs. JSON support was added to Postgres in version 9.2. With that, you can store full JSON objects in your database tables. The key-value pair data can have different data types like string, number, and boolean. Not only that, you can also store nested key-value pairs with JSON data type support in Postgres. In Postgres, JSON data is stored as an exact copy of the input text; that way, inserting the data into your tables is fast, but reading is slow.
JSONB
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 the 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 the 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 are 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. I hope this article helped you understand the basic differences between the 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
