Indexes in JSONB
Hi All,
We use JSONB /NoSQL functionality of PostgreSQL.
One of the column "doc" in our table "Table1" is of type JSONB.
Now the rows in this column "doc" can have different values with different
schemas.
For e.g values of doc per row will be something like below
ROW1 = {"id":"1", "name":"abc" }
ROW2 = {"id:"2" , "address": "address1"}
ROW3= {"id":"3" , "name":"def", "country":"country1" }
So you can see the JSON is changing and keys/schema can be different for
each rows.
During the course of time indexes will be created for the json keys of the
JSON values in this column.
For e.g. on "name", "address" , "country" now. Later I can have another key
and index like on "continent".
As per postgresql limitations there are limits on the indexes. So I
understand we can create many indexes.
But obviously I understand creating many indexes will impact write
performance and space will be utilized.
Would like to know if creating indexes in this manner can create any other
issues or inputs on the whole topic of indexes in JSONB types.
Best Regards,
Saurav
On Tue, Mar 29, 2022 at 7:06 AM Saurav Sarkar <saurav.sarkar1@gmail.com>
wrote:
Hi All,
We use JSONB /NoSQL functionality of PostgreSQL.
One of the column "doc" in our table "Table1" is of type JSONB.
Now the rows in this column "doc" can have different values with different
schemas.For e.g values of doc per row will be something like below
ROW1 = {"id":"1", "name":"abc" }
ROW2 = {"id:"2" , "address": "address1"}
ROW3= {"id":"3" , "name":"def", "country":"country1" }So you can see the JSON is changing and keys/schema can be different for
each rows.During the course of time indexes will be created for the json keys of the
JSON values in this column.
For e.g. on "name", "address" , "country" now. Later I can have another
key and index like on "continent".As per postgresql limitations there are limits on the indexes. So I
understand we can create many indexes.But obviously I understand creating many indexes will impact write
performance and space will be utilized.Would like to know if creating indexes in this manner can create any other
issues or inputs on the whole topic of indexes in JSONB types.
You should not be creating indexes for every individual field if you have
those needs. You should create one index, using GIN and probably
jsonb_path_ops.
Take a look at
https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
No matter what you do, you will not typically get the same level of
performance as normalized tables as you do not get statistics on the values
stored in your jsonb column. No knowledge of null fraction, number of
distinct values, or most common values and their frequencies.
Thanks a lot Magnus for the reply.
I understand that jsonb_path_ops creates index on the whole path or on all
the attributes.
Does it takes more storage or reduces write performance in comparison to
normal gin index ?
On Tue, Mar 29, 2022 at 2:19 PM Magnus Hagander <magnus@hagander.net> wrote:
Show quoted text
On Tue, Mar 29, 2022 at 7:06 AM Saurav Sarkar <saurav.sarkar1@gmail.com>
wrote:Hi All,
We use JSONB /NoSQL functionality of PostgreSQL.
One of the column "doc" in our table "Table1" is of type JSONB.
Now the rows in this column "doc" can have different values with
different schemas.For e.g values of doc per row will be something like below
ROW1 = {"id":"1", "name":"abc" }
ROW2 = {"id:"2" , "address": "address1"}
ROW3= {"id":"3" , "name":"def", "country":"country1" }So you can see the JSON is changing and keys/schema can be different for
each rows.During the course of time indexes will be created for the json keys of
the JSON values in this column.
For e.g. on "name", "address" , "country" now. Later I can have another
key and index like on "continent".As per postgresql limitations there are limits on the indexes. So I
understand we can create many indexes.But obviously I understand creating many indexes will impact write
performance and space will be utilized.Would like to know if creating indexes in this manner can create any
other issues or inputs on the whole topic of indexes in JSONB types.You should not be creating indexes for every individual field if you have
those needs. You should create one index, using GIN and probably
jsonb_path_ops.Take a look at
https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>