Postgres hstore vs JSON - comparing different unstructured data types in PostgreSQL

A brief comparison of hstore and JSON data types in PostgreSQL. We learn about the pros and cons of both the data types and when to use them.

Posted by Jitendra Nirnejak on 2022-11-25
Postgres hstore vs JSON - comparing different unstructured data types in PostgreSQL

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.

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 can not 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 in 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 the exact copy of the input text, that way inserting the data in 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 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

Let’s get you started

This website uses cookies to ensure you get the best experience.