Friday , July 19 2024

How To Create Partition on JSONB column in PostgreSQL

I will share an example that creates partition on JSONB column in PostgreSQL in this article. But first we need to know What JSON and JSONB are.

What is JSON and JSONB?

JSON and JSONB data types are available in PostgreSQL to store JSON data. Json stores data as a string and parses each query.

JSONB parses json and saves it in binary format. In such columns, the writing speed is slow, but the reading speed is quite fast.

JSON is an indispensable language for those who develop web applications, JavaScript, and REST-based mobile application. We said it is a language, but JSON is actually a data interchange format.

In PostgreSQL 9.4, the JSONB data type support(the binary version of JSON) was introduced.

Up to this point, we have tried to understand the JSON and JSONB data types.

So how do we create partition on JSONB columns?

Then we select demo data we inserted.
Analyze result;
As seen above, the query uses the index on the 3rd partition.

In this way, we created a partition on the column with jsonb data type.

If you want to learn more about PostgreSQL partitioning, you should read my other article about partitioning “Table Partitioning in PostgreSQL 11.2

Good luck with.


About Yusuf KAHVECİ

Leave a Reply

Your email address will not be published. Required fields are marked *