Операции над индексами в PostgreSQL
В этой статье рассмотрим, как создать индекс, как его удалить и переименовать, а также как произвести переиндексацию в PostgreSQL. В качестве примера возьмём опыт инженеров из подразделения PayPal — Braintree Payments.
Создаём индекс
Если вы хотите создать индекс и запустите для этого CREATE INDEX ..., вы получите блокировку на всей индексируемой таблице уровня ACCESS EXCLUSIVE. А вот если вы выполните команду CREATE INDEX CONCURRENTLY ..., то уровень блокировки станет всего лишь SHARE UPDATE EXCLUSIVE. При этом во втором случае будут разрешены и запись, и чтение Правда, разница в том, что вместо одного сканирования таблицы придётся выполнить 2..
На что тут стоить обратить внимание: • если несколько созданий индексов выполняются параллельно на одной таблице, они не завершат выполнение ни одного из CREATE INDEX CONCURRENTLY ..., и это утверждение верно до тех пор, пока самый медленный из них всё ещё работает; • CREATE INDEX CONCURRENTLY ... не сможет выполниться внутри транзакции. Вместо этого транзакциями управляет PostgreSQL, делая это неявно. В результате никакие auto-vacuum’ы не могут очистить ненужные кортежи, появившиеся после начала построения индекса и вплоть до завершения данного процесса. Если же у таблицы имеется большой объём изменений (это в особенности плохо, когда непосредственно сама таблица мала), всё это может закончиться низкой скоростью исполнения запроса; • CREATE INDEX CONCURRENTLY ... завершит выполнение лишь тогда, когда завершатся все транзакции, которые используют таблицу.
Удаляем индекс
Стандартное выражение DROP INDEX ... получит ACCESS EXCLUSIVE на всей PostgreSQL-таблице и на всё время удаления индекса. Если речь идёт о небольших индексах, проблем нет, и операция не занимает много времени. Но если мы говорим об огромных индексах, то работа с файловой системой займёт гораздо больше времени. Тут поможет выражение DROP INDEX CONCURRENTLY ..., которое потребует блокировку уровня SHARE UPDATE EXCLUSIVE. При этом и запись, и чтение будут продолжаться до тех пор, пока осуществляется наша операция удаления индекса.
У использования DROP INDEX CONCURRENTLY ... есть ряд подводных камней: • запрос нельзя использовать для удаления индекса, который поддерживал какое-нибудь ограничение (к примеру, PRIMARY KEY либо UNIQUE); • запрос нельзя использовать в качестве части транзакции, т. к. ими управляет PostgreSQL, что называется, «под капотом». В результате никакие auto-vacuum’ы не могут очистить ненужные кортежи, появившиеся после начала построения индекса и вплоть до завершения данного процесса. Опять же, если у таблицы имеется большой объём изменений (это в особенности плохо, когда непосредственно сама таблица мала), всё это может закончиться низкой скоростью исполнения запроса; • запрос завершит выполнение лишь тогда, когда завершатся все транзакции, которые используют таблицу.
Учтите, что DROP INDEX CONCURRENTLY ... добавили лишь в Postgres 9.2. Если вы работаете с более ранними версиями, у вас есть возможность достичь приблизительного того же результата, если вы отметите индекс как некорректный (invalid) и к записи не готовый; потом останется сбросить буфер посредством расширения pgfincore, а уже после этого вы сможете просто удалить индекс.
Переименовываем индекс
ALTER INDEX ... RENAME TO ... требует блокировку на переименовываемом индексе уровня ACCESS EXCLUSIVE , то есть блокируется чтение и запись в соответствующую таблицу. Правда, существует коммит, понижающий данное требование до SHARE UPDATE EXCLUSIVE.
Производим переиндексацию
REINDEX INDEX ... тоже требует ACCESS EXCLUSIVE на индексе. Дабы это исключить, рекомендуем определённый алгоритм: • создаём новый индекс, (описано выше) повторяющий существующий; • удаляем старый индекс, делая это наименее затратным способом (тоже описано выше); • переименовываем новый индекс таким образом, чтобы он повторил имя старого индекса.
Примечание: если индекс, который вы хотите перестроить, содержал ограничения, обязательно добавьте эти ограничения и в новый индекс.
По материалам «PostgreSQL at Scale: Database Schema Changes Without Downtime».
