Re: Indexes in JSONB

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/
 Work: https://www.redpill-linpro.com/