How does a GIN index work?

GIN (Generalized Inverted Index) is a type of index in PostgreSQL optimized for fast searches on complex data types such as arrays, JSON, full-text search, and other structures where a single key can correspond to multiple values.

The working principle of GIN is based on an inverted index: for each value (key), a list of references to the table rows where this value appears is stored. This allows efficient execution of queries with operators that check for the presence of elements in an array or keys in JSON.

Features of GIN:

• Supports fast search for multiple values within a single field.
• The index occupies more space and requires more time to update compared to B-tree.
• Well-suited for full-text search and working with JSONB.

Example of using GIN for JSONB:

CREATE INDEX idx_data_gin ON my_table USING GIN (data jsonb_path_ops);

SELECT * FROM my_table WHERE data @> '{"key": "value"}';

Here, the index allows quick finding of rows where the data field contains the specified key and value.