Создание индекса с использованием слова «CONCURRENTLY» ровно в два раза замедляет процесс. Почему? Потому что на создание такого индекса нужно будет затратить два последовательных сканирования всей таблицы.
Если у вас есть возможность заблокировать таблицу на какое-то время, то можете смело создавать индекс без слова «CONCURRENTLY«. Имейте ввиду, что запросы (добавление, изменение или удаление записей в таблице) в заблокированную таблицу будут отброшены. Создание индекса займёт только одно последовательное сканирование таблицы при её блокировке. Если это критично для ваших пользователей и приложений — тогда не делайте так.
Чем больше размер таблицы, тем дольше будет строиться индекс. Это пропорциональная зависимость. С этим нужно просто смириться.
Например, неблокирующее создание индекса для 250 миллионов записей по столбцу с типом данных bigint занимает около 40 секунд. Блокирующее — 20 секунд.
Не хочу быть банальным, но возможно не все данные в этой таблице вам нужны? Может стоит что-то удалить и забыть об этом?
Если будет возможность, то можно попытаться из одной таблицы сделать несколько отдельных. Это называют секционированием, но по мне на 2023 год — это очень рукотворная сырая реализация, которая мало чем отличается от простого создания отдельных таблиц. Нужно будет самому придумывать проверки ограничений, проверки операций CRUD.
Чем медленнее доступ к данным на диске, тем дольше будет построение индекса.
Можно перенести некоторые таблицы на более быстрые диски — с HDD на SSD. Или с SSD на NVME. Или лучше всего использовать NAS с RAID-контроллерами в качестве хранилища и 40 гигабит сеть.
Убедитесь, что ваша база данных качественно обслуживается процессами авто очистки. Я говорю о процессах:
Очень может быть так, что ваша база данных за десятилетие накопила кучу бесполезных страниц, которые уже не обслуживаются, но хранятся и отвлекают от нормальной работы. Их нужно вычищать!
Если вы пришлю в чужой проект, то кто-то мог отключить авто очистку в настройках конфигурационных файлов. Обязательно проверьте его!
Файл конфига PostgreSQL находится в директории «/etc/postgresql/15/main» (15 — это версия СУБД, у вас может быть другая), если у нас операционная система Debian (Linux).
В нём есть параметр «maintenance_work_mem«. Он задаёт максимальный объём памяти для операций обслуживания БД:
Посмотреть текущий размер памяти можно командой:
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
При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…
-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…
Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…
Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…
У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…
Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…