Postgres tsvector_update_trigger иногда занимает минуты

Question

Я настроил поиск свободного текста по таблице в моей базе данных postgres. Довольно простые вещи, с именем, фамилией и адресом электронной почты. Это работает хорошо и быстро.

Однако иногда я испытываю длительные задержки при вставке новой записи в таблицу, когда вставка продолжается в течение нескольких минут, а также генерирует огромные файлы WAL. (Мы используем файлы WAL для репликации).

Есть ли что-то, что мне нужно знать о моем свободном текстовом индексе? Как и Postgres, может быть, случайным образом реструктурировать его по причинам производительности? Мой индекс в настоящее время составляет около 400 МБ.

Заранее спасибо!

Кристиан

Answer 1

Учитывая размер WAL-файлов, я подозреваю, что вы правы, что проблема связана с обновлением / перебалансировкой индекса. Однако я должен задаться вопросом, что еще происходит.

Я бы рекомендовал не хранить цвета в отдельных столбцах. Лучший способ - запустить индекс для вывода to_tsvector(). Вы можете иметь несколько индексов для нескольких языков, если вам нужно. Поэтому вместо триггера, который принимает, скажем, поле с именем description и сохраняет tsvector в desc_tsvector, я бы порекомендовал просто сделать:

 CREATE INDEX mytable_description_tsvector_idx ON mytable(to_tsvector(description));

Теперь, если вам нужен согласованный интерфейс поиска по всей таблице, есть более элегантные способы сделать это с помощью "табличных методов".

В целом, у подхода с функциональным индексом меньше проблем, чем с чем-либо еще.

Теперь второе, о чем вы должны знать, это частичные индексы. Если вам нужно, вы можете индексировать только интересующие вас записи. Например, если большинство моих запросов проверяют только в прошлом году, я могу:

 CREATE INDEX mytable_description_tsvector_idx ON mytable(to_tsvector(description))
  WHERE created_at > now() - '1 year'::interval;