Каковы ключевые различия между индексами B-Tree и Hash в PostgreSQL

Основное различие заключается в их внутренней структуре и, как следствие, в сценариях использования. B-Tree является универсальным и используется по умолчанию, в то время как Hash-индекс оптимизирован для узкой задачи.

B-Tree (B-дерево)

Сбалансированная древовидная структура, которая хранит данные отсортированными. Это делает её эффективной для широкого спектра операций.

• Лучше всего подходит для:
  • Запросов на точное совпадение (=).
  • Диапазонных запросов (>, <, BETWEEN).
  • Поиска по префиксу (LIKE 'prefix%').
  • Сортировки (ORDER BY).
• Преимущества: Универсальность, поддержка всех типов данных, поддержка ограничений UNIQUE.

Hash (Хеш-индекс)

Хранит хеш-коды от индексируемых значений. Поиск происходит путем вычисления хеша и прямого доступа к соответствующей "корзине" данных.

• Лучше всего подходит для:
  • Только запросов на точное совпадение (=).
• Ограничения: Не поддерживает диапазонные запросы, сортировку или поиск по префиксу. До версии PostgreSQL 10 хеш-индексы не были транзакционно-безопасными и требовали ручной перестройки.

Пример создания:

-- B-Tree индекс (используется по умолчанию)
CREATE INDEX idx_users_email_btree ON users (email);

-- Hash индекс (нужно явно указать USING HASH)
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

Вывод: На практике B-Tree используется в подавляющем большинстве случаев из-за своей гибкости. Hash может дать небольшое преимущество в производительности и размере для очень больших таблиц, где запросы выполняются исключительно на точное равенство.