What is the difference between a B-tree index and a hash index in PostgreSQL? What other indexes do you know?

In PostgreSQL, B-tree and hash indexes differ in structure and application:

• B-tree index:

  • This is the default index in PostgreSQL.
  • Based on a balanced tree (B-tree), supports range queries, sorting, and equality search.
  • Suitable for most operations, including =, <, >, BETWEEN, ORDER BY.

• Hash index:

  • Uses a hash table for fast equality searches (=).
  • Does not support range queries or sorting.
  • In older versions of PostgreSQL, it was less reliable, but it has been improved in newer versions.

Other index types in PostgreSQL:

• GIN (Generalized Inverted Index): for indexing arrays, JSONB, full-text search.
• GiST (Generalized Search Tree): for geometric data, full-text search, and other extensible types.
• SP-GiST: for specific data structures, such as spatial indexes.
• BRIN (Block Range Index): for very large tables with ordered data, saves space.

The choice of index depends on the data type and queries. B-tree is versatile, hash is useful for fast equality, GIN and GiST for complex structures and full-text search.