Thread: 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 belowROW1 = {"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.
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:
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 belowROW1 = {"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.--