What types of indexes do you know in PostgreSQL? Tell about B-tree indexes.

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

• B-tree — the most frequently used type, suitable for most comparison operations (=, <, <=, >, >=, BETWEEN).
• Hash — for fast equality searches, but less versatile.
• GIN (Generalized Inverted Index) — for indexing arrays, JSONB, full-text search.
• GiST (Generalized Search Tree) — for geometric data, full-text search, and other specialized tasks.
• SP-GiST — for specific data structures, such as prefix trees.
• BRIN (Block Range Index) — for very large tables with ordered data.

B-tree indexes

B-tree (Balanced Tree) is a balanced tree where all leaf nodes are at the same level. It provides logarithmic time for search, insert, and delete operations.

Features of B-tree in PostgreSQL:

• Supports comparison and sorting operations.
• Used by default when creating an index without specifying a type.
• Efficient for range searches and exact matches.
• Automatically maintains uniqueness if the index is created with the UNIQUE option.

Example of creating a B-tree index:

CREATE INDEX idx_users_email ON users(email);

This index will speed up searching users by email.

B-tree is a universal and the most commonly used index in PostgreSQL.