Re: Indexes in JSONB

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/>