What is an index and what types of indexes are available in PostgreSQL?

An index in PostgreSQL is a data structure that speeds up the search for rows in a table based on certain columns, reducing the number of read operations.

Main types of indexes in PostgreSQL:

• B-tree (default): Suitable for most comparison operations (=, <, <=, >, >=, BETWEEN).
• Hash: Optimized for equality operations (=), but less versatile.
• GIN (Generalized Inverted Index): Used for full-text search, arrays, JSONB, where elements inside collections need to be searched.
• GiST (Generalized Search Tree): Supports complex structures, such as geometric data, full-text search.
• SP-GiST: A specialized index for certain data types, such as prefix trees.
• BRIN (Block Range Index): A lightweight index for very large tables with ordered data.

Example of creating a B-tree index:

CREATE INDEX idx_users_email ON users(email);