Замедление из-за предложения CONCURRENTLY
Создание индекса с использованием слова «CONCURRENTLY» ровно в два раза замедляет процесс. Почему? Потому что на создание такого индекса нужно будет затратить два последовательных сканирования всей таблицы.
Если у вас есть возможность заблокировать таблицу на какое-то время, то можете смело создавать индекс без слова «CONCURRENTLY«. Имейте ввиду, что запросы (добавление, изменение или удаление записей в таблице) в заблокированную таблицу будут отброшены. Создание индекса займёт только одно последовательное сканирование таблицы при её блокировке. Если это критично для ваших пользователей и приложений — тогда не делайте так.
Замедление из-за увеличения размера таблицы
Чем больше размер таблицы, тем дольше будет строиться индекс. Это пропорциональная зависимость. С этим нужно просто смириться.
Например, неблокирующее создание индекса для 250 миллионов записей по столбцу с типом данных bigint занимает около 40 секунд. Блокирующее — 20 секунд.
Не хочу быть банальным, но возможно не все данные в этой таблице вам нужны? Может стоит что-то удалить и забыть об этом?
Если будет возможность, то можно попытаться из одной таблицы сделать несколько отдельных. Это называют секционированием, но по мне на 2023 год — это очень рукотворная сырая реализация, которая мало чем отличается от простого создания отдельных таблиц. Нужно будет самому придумывать проверки ограничений, проверки операций CRUD.
Замедление из-за медленных дисковых устройств
Чем медленнее доступ к данным на диске, тем дольше будет построение индекса.
Можно перенести некоторые таблицы на более быстрые диски — с HDD на SSD. Или с SSD на NVME. Или лучше всего использовать NAS с RAID-контроллерами в качестве хранилища и 40 гигабит сеть.
Замедление из-за отсутствия очистки
Убедитесь, что ваша база данных качественно обслуживается процессами авто очистки. Я говорю о процессах:
- VACUUM
- VACUUM FULL
Очень может быть так, что ваша база данных за десятилетие накопила кучу бесполезных страниц, которые уже не обслуживаются, но хранятся и отвлекают от нормальной работы. Их нужно вычищать!
Если вы пришлю в чужой проект, то кто-то мог отключить авто очистку в настройках конфигурационных файлов. Обязательно проверьте его!
Файл конфига PostgreSQL находится в директории «/etc/postgresql/15/main» (15 — это версия СУБД, у вас может быть другая), если у нас операционная система Debian (Linux).
В нём есть параметр «maintenance_work_mem«. Он задаёт максимальный объём памяти для операций обслуживания БД:
- VACUUM
- CREATE INDEX
- ALTER TABLE ADD FOREIGN KEY
Посмотреть текущий размер памяти можно командой:
SHOW maintenance_work_mem;
Информационные ссылки
Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org
Официальный сайт СУБД PostgreSQL — https://www.postgresql.org
Создание индекса — Команда CREATE INDEX — https://postgrespro.ru/docs/postgresql/15/sql-createindex
Удаление индекса — Команда DROP INDEX — https://postgrespro.ru/docs/postgresql/15/sql-dropindex
Представление pg_indexes — https://postgrespro.ru/docs/postgresql/15/view-pg-indexes
Раздел «Неблокирующее построение индексов» — https://postgrespro.ru/docs/postgresql/15/sql-createindex#SQL-CREATEINDEX-CONCURRENTLY
