What indexes do you use in relational databases (PostgreSQL)? How does B-Tree differ from Hash index?

The most commonly used index types in PostgreSQL are:

• B-Tree — the standard index, suitable for most comparison operations (>, <, =, BETWEEN, LIKE with prefix).
• Hash — an index for fast exact match searches (operator =).
• GIN and GiST — for full-text search, working with arrays, geospatial data.
• BRIN — for very large tables with ordered data.

Differences between B-Tree and Hash indexes:

• B-Tree index stores data in a balanced tree, allowing efficient range queries and sorting. It supports comparison operations, making it suitable for a wide range of queries.

• Hash index is optimized only for exact match searches using the = operator. It is faster than B-Tree for such queries but does not support range operations or sorting.

• Historically, Hash indexes in PostgreSQL were less reliable (could get corrupted), but this has been fixed in newer versions.

• B-Tree indexes are more versatile and are more commonly used in production.

Example of creating B-Tree and Hash indexes:

CREATE INDEX idx_btree ON table_name USING btree(column_name);
CREATE INDEX idx_hash ON table_name USING hash(column_name);