Re: Indexes in JSONB

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(at)hagander(dot)net> wrote:

>
>
> On Tue, Mar 29, 2022 at 7:06 AM Saurav Sarkar <saurav(dot)sarkar1(at)gmail(dot)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/>
>