How To Create Partition on JSONB column in PostgreSQL

Yusuf KAHVECİ

5 years ago

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?

--Create table that has JSONB column.
CREATE TABLE jsonBDemo (data jsonb) partition by range ((data->'id'));
 
--Specify Partitions.
CREATE TABLE jsonBDemo_01 PARTITION OF jsonBDemo
 FOR VALUES FROM ('1') TO ('10');
 
CREATE TABLE jsonBDemo_02 PARTITION OF jsonBDemo
 FOR VALUES FROM ('11') TO ('20');
 
CREATE TABLE jsonBDemo_03 PARTITION OF jsonBDemo
 FOR VALUES FROM ('21') TO ('30');
 
CREATE TABLE jsonBDemo_04 PARTITION OF jsonBDemo
 FOR VALUES FROM ('31') TO ('40');
 
--Create indexes on partitions.
CREATE INDEX ON jsonBDemo_01 USING BTREE ((data->'id'));
CREATE INDEX ON jsonBDemo_02 USING BTREE ((data->'id'));
CREATE INDEX ON jsonBDemo_03 USING BTREE ((data->'id'));
CREATE INDEX ON jsonBDemo_04 USING BTREE ((data->'id'));
 
--Insert Demo Data.
INSERT INTO jsonBDemo (data) VALUES ('{"id":11,"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"], "published": false}');  
INSERT INTO jsonBDemo VALUES ('{"id":34,"title": "Influence", "genres": ["Marketing &amp; Sales", "Self-Help ", "Psychology"], "published": true}');  
INSERT INTO jsonBDemo VALUES ('{"id":41,"title": "The Dictator''s Handbook", "genres": ["Law", "Politics"], "authors": ["Bruce Bueno de Mesquita", "Alastair Smith"], "published": true}');  
INSERT INTO jsonBDemo VALUES ('{"id":16,"title": "Deep Work", "genres": ["Productivity", "Reference"], "published": true}');  
INSERT INTO jsonBDemo VALUES ('{"id":22,"title": "Siddhartha", "genres": ["Fiction", "Spirituality"], "published": true}');

--Create table that has JSONB column.

CREATE TABLE jsonBDemo (data jsonb) partition by range ((data->'id'));

--Specify Partitions.

CREATE TABLE jsonBDemo_01 PARTITION OF jsonBDemo

FOR VALUES FROM ('1') TO ('10');

CREATE TABLE jsonBDemo_02 PARTITION OF jsonBDemo

FOR VALUES FROM ('11') TO ('20');

CREATE TABLE jsonBDemo_03 PARTITION OF jsonBDemo

FOR VALUES FROM ('21') TO ('30');

CREATE TABLE jsonBDemo_04 PARTITION OF jsonBDemo

FOR VALUES FROM ('31') TO ('40');

--Create indexes on partitions.

CREATE INDEX ON jsonBDemo_01 USING BTREE ((data->'id'));

CREATE INDEX ON jsonBDemo_02 USING BTREE ((data->'id'));

CREATE INDEX ON jsonBDemo_03 USING BTREE ((data->'id'));

CREATE INDEX ON jsonBDemo_04 USING BTREE ((data->'id'));

--Insert Demo Data.

INSERT INTO jsonBDemo (data) VALUES ('{"id":11,"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"], "published": false}');

INSERT INTO jsonBDemo VALUES ('{"id":34,"title": "Influence", "genres": ["Marketing & Sales", "Self-Help ", "Psychology"], "published": true}');

INSERT INTO jsonBDemo VALUES ('{"id":41,"title": "The Dictator''s Handbook", "genres": ["Law", "Politics"], "authors": ["Bruce Bueno de Mesquita", "Alastair Smith"], "published": true}');

INSERT INTO jsonBDemo VALUES ('{"id":16,"title": "Deep Work", "genres": ["Productivity", "Reference"], "published": true}');

INSERT INTO jsonBDemo VALUES ('{"id":22,"title": "Siddhartha", "genres": ["Fiction", "Spirituality"], "published": true}');

Then we select demo data we inserted.

demo=# select * from jsonbdemo;

1	demo=# select * from jsonbdemo;

demo=# explain analyze select * from jsonbdemo where data->'id'='22';

1	demo=# explain analyze select * from jsonbdemo where data->'id'='22';

Analyze result;

Append  (cost=3.21..12.05 rows=7 width=32) (actual time=0.013..0.013 rows=1 loops=1)
   ->  Bitmap Heap Scan on jsonbdemo_03  (cost=3.21..12.02 rows=7 width=32) (actual time=0.012..0.012 rows=1
 loops=1)
         Recheck Cond: ((data -> 'id'::text) = '22'::jsonb)
         Heap Blocks: exact=1
         ->  Bitmap Index Scan on jsonbdemo_03_expr_idx  (cost=0.00..3.21 rows=7 width=0) (actual time=0.008
..0.008 rows=1 loops=1)
               Index Cond: ((data -> 'id'::text) = '22'::jsonb)
 Planning Time: 0.348 ms
 Execution Time: 0.145 ms
(8 rows)

Append (cost=3.21..12.05 rows=7 width=32) (actual time=0.013..0.013 rows=1 loops=1)

-> Bitmap Heap Scan on jsonbdemo_03 (cost=3.21..12.02 rows=7 width=32) (actual time=0.012..0.012 rows=1

loops=1)

Recheck Cond: ((data -> 'id'::text) = '22'::jsonb)

Heap Blocks: exact=1

-> Bitmap Index Scan on jsonbdemo_03_expr_idx (cost=0.00..3.21 rows=7 width=0) (actual time=0.008

..0.008 rows=1 loops=1)

Index Cond: ((data -> 'id'::text) = '22'::jsonb)

Planning Time: 0.348 ms

Execution Time: 0.145 ms

(8 rows)

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.