Categories: 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-оболочки 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

 

admin

Share
Published by
admin

Recent Posts

PostgreSQL | Как получить первый элемент из ячейки типа record?

При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…

1 год ago

PostgreSQL | Как отфильтровать JSONB-массив объектов по логическому значению ключа?

-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…

1 год ago

PostgreSQL | Как отбросить нули в типе numeric?

Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…

1 год ago

JavaScript | Как получить следующий день?

Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…

1 год ago

PostgreSQL | Агрегация JSONB-объектов в один объект

У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…

1 год ago

PostgreSQL | Группировка по 10 записей

Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…

1 год ago