What types of indexes can be created in PostgreSQL database?

In PostgreSQL, you can create multiple types of indexes, each optimized for different tasks:

• B-tree — the most common type, suitable for most comparison operations (=, <, >, <=, >=, BETWEEN).
• Hash — used for fast equality searches, but less versatile and less frequently used.
• GIN (Generalized Inverted Index) — effective for indexing arrays, JSONB, full-text search.
• GiST (Generalized Search Tree) — a versatile index supporting complex data structures, such as geometric objects.
• SP-GiST (Space-Partitioned GiST) — for specific data structures, such as points in space.
• BRIN (Block Range Index) — a compact index for very large tables, works well with data ordered by physical location.

Example of creating a B-tree index:

CREATE INDEX idx_users_name ON users USING btree (name);