What types of indexes do you know in PostgreSQL? Tell about B-tree index, how it is arranged. What is a GIN index?

In PostgreSQL, there are different types of indexes, the most common ones are:

• B-tree — the default index, suitable for most comparison operations (=, <, >, BETWEEN, LIKE with a prefix).
• GIN (Generalized Inverted Index) — an index for fast searching within complex data types, such as arrays, JSONB, full-text search.
• GiST — a generalized index supporting various structures, for example, for geospatial data.
• Hash — for equality, but used rarely due to limitations.

B-tree index

This is a balanced tree where data is stored in sorted order. Search, insert, and delete operations work in logarithmic time. The index stores keys and references to table rows. When searching by index, PostgreSQL quickly finds the required range of values.

GIN index

Designed for indexing collections of values, such as arrays or JSONB documents. Instead of storing keys and references to rows, it stores an inverted index — for each value, a list of rows where it appears. This speeds up searches within complex structures.

Example of creating a GIN index for JSONB:

CREATE INDEX idx_jsonb_gin ON table_name USING gin (jsonb_column);