Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | --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}'); |
1 | demo=# select * from jsonbdemo; |
1 | demo=# explain analyze select * from jsonbdemo where data->'id'='22'; |
1 2 3 4 5 6 7 8 9 10 11 | 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) |
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.
