PostgreSQL | Как ускорить создание индекса?

PostgreSQL | Как ускорить создание индекса?

Замедление из-за предложения CONCURRENTLY

Создание индекса с использованием слова «CONCURRENTLY» ровно в два раза замедляет процесс. Почему? Потому что на создание такого индекса нужно будет затратить два последовательных сканирования всей таблицы.

Если у вас есть возможность заблокировать таблицу на какое-то время, то можете смело создавать индекс без слова «CONCURRENTLY«. Имейте ввиду, что запросы (добавление, изменение или удаление записей в таблице) в заблокированную таблицу будут отброшены. Создание индекса займёт только одно последовательное сканирование таблицы при её блокировке. Если это критично для ваших пользователей и приложений — тогда не делайте так.

 

Замедление из-за увеличения размера таблицы

Чем больше размер таблицы, тем дольше будет строиться индекс. Это пропорциональная зависимость. С этим нужно просто смириться.

Например, неблокирующее создание индекса для 250 миллионов записей по столбцу с типом данных bigint занимает около 40 секунд. Блокирующее — 20 секунд.

Не хочу быть банальным, но возможно не все данные в этой таблице вам нужны? Может стоит что-то удалить и забыть об этом?

Если будет возможность, то можно попытаться из одной таблицы сделать несколько отдельных. Это называют секционированием, но по мне на 2023 год — это очень рукотворная сырая реализация, которая мало чем отличается от простого создания отдельных таблиц. Нужно будет самому придумывать проверки ограничений, проверки операций CRUD.

 

Замедление из-за медленных дисковых устройств

Чем медленнее доступ к данным на диске, тем дольше будет построение индекса.

Можно перенести некоторые таблицы на более быстрые диски — с HDD на SSD. Или с SSD на NVME. Или лучше всего использовать NAS с RAID-контроллерами в качестве хранилища и 40 гигабит сеть.

 

Замедление из-за отсутствия очистки

Убедитесь, что ваша база данных качественно обслуживается процессами авто очистки. Я говорю о процессах:

  1. VACUUM
  2. VACUUM FULL

Очень может быть так, что ваша база данных за десятилетие накопила кучу бесполезных страниц, которые уже не обслуживаются, но хранятся и отвлекают от нормальной работы. Их нужно вычищать!

Если вы пришлю в чужой проект, то кто-то мог отключить авто очистку в настройках конфигурационных файлов. Обязательно проверьте его!

Файл конфига PostgreSQL находится в директории «/etc/postgresql/15/main» (15 — это версия СУБД, у вас может быть другая), если у нас операционная система Debian (Linux).

В нём есть параметр «maintenance_work_mem«. Он задаёт максимальный объём памяти для операций обслуживания БД:

  1. VACUUM
  2. CREATE INDEX
  3. ALTER TABLE ADD FOREIGN KEY

Посмотреть текущий размер памяти можно командой:

SHOW maintenance_work_mem;

 

Информационные ссылки

Официальный сайт WEB-оболочки pgAdminhttps://www.pgadmin.org

Официальный сайт СУБД PostgreSQLhttps://www.postgresql.org

Создание индекса — Команда CREATE INDEXhttps://postgrespro.ru/docs/postgresql/15/sql-createindex

Удаление индекса — Команда DROP INDEXhttps://postgrespro.ru/docs/postgresql/15/sql-dropindex

Представление pg_indexeshttps://postgrespro.ru/docs/postgresql/15/view-pg-indexes

Раздел «Неблокирующее построение индексов» — https://postgrespro.ru/docs/postgresql/15/sql-createindex#SQL-CREATEINDEX-CONCURRENTLY