GIN (Generalized Inverted Index) indexes on JSONB columns for queries

[hamidahoderinwale]

GIN (Generalized Inverted Index) indexes can efficiently handle both JSONB data types and full-text search. Key features are as follows:

• Supports LIKE searches and operates using key-value pair queries
• Works with JSONB to enable flexible, updateable schemas - tables can change structure while maintaining queryability
• Can be combined with B-Tree indexes for hybrid querying capabilities

We will use one multi-column GIN index rather than multiple separate indexes. One reason why is that when updating data (e.g, adding columns and rows), PostgreSQL only needs to update one index.

Relevant reference: https://anyblockers.com/posts/postgres-as-a-search-engine