Какой индекс использовать для поиска по точному совпадению строки в колонке с большими текстами в PostgreSQL

Что проверяют

Знаете ли вы, что обычный B-tree на длинных строках не просто неэффективен, а может сломать вставку. Вопрос с подвохом: ответ «конечно, B-tree» здесь неполный. Следом спрашивают, как искать по такому индексу из кода и что делать с коллизиями хэша.

Как отвечать

Сначала ограничение. Запись в B-tree индексе не может занимать больше трети страницы, для страницы 8 КБ это 2704 байта даже после сжатия. Более длинное значение даст ошибку index row size ... exceeds btree version 4 maximum 2704, и INSERT не пройдёт. Для коротких строк B-tree нормальный выбор, для больших текстов нужен другой ход:

• хэш-индекс: CREATE INDEX ON docs USING hash (body). Хранит только 4-байтовый хэш, поэтому длина текста не важна, работает ровно на =. С PostgreSQL 10 пишется в WAL и переживает сбои. Ограничения: не бывает уникальным и составным, не помогает сортировке и диапазонам;
• B-tree по выражению: CREATE INDEX ON docs (md5(body)) и запрос WHERE md5(body) = md5($1) AND body = $1. Второе условие защищает от коллизии, сам индекс компактный. Через CREATE UNIQUE INDEX на то же выражение так делают и уникальность, если риск коллизии md5 вас устраивает;
• отдельная колонка с хэшем: GENERATED ALWAYS AS (md5(body)) STORED плюс обычный индекс на неё. Удобно, когда хэш нужен и приложению.

В Java запрос должен повторять выражение индекса. В Spring Data JPA это нативный @Query с md5(body) = md5(:value), иначе планировщик не узнает индекс и уйдёт в последовательное чтение таблицы. Проверяйте через EXPLAIN ANALYZE.

Где спотыкаются

Советуют GIN или pg_trgm. Они для полнотекстового поиска и LIKE '%...%', на точном совпадении это лишний размер и медленная запись.

Не знают про лимит 2704 байта и узнают о нём на проде, когда первый длинный документ не вставляется.

Строят индекс по md5(body), а запрос пишут WHERE body = ?. Индекс по выражению используется, только когда в условии стоит то же выражение.