PostgreSQL indexes: what are they, what types do you know, and why is B-tree better than Hash?

In PostgreSQL, an index is a data structure that speeds up the search for rows in a table based on certain columns.

Main types of indexes:

• B-tree — the most common type, suitable for equality and range queries.
• Hash — an index for fast equality searches.
• GIN and GiST — for full-text search and complex data types.

Why B-tree is better than Hash:

• B-tree supports range searches (e.g., BETWEEN, >, <), Hash only supports exact matches.
• B-tree indexes can be used for sorting, Hash cannot.
• B-tree is more versatile and reliable, Hash indexes in PostgreSQL are less common and have limitations.

Example of creating a B-tree index:

CREATE INDEX idx_users_name ON users USING btree(name);

Hash index:

CREATE INDEX idx_users_email_hash ON users USING hash(email);