Rivalry2

HTML Diff

0 added 0 removed

Original 2026-01-01

Modified 2026-03-10

1 В этой статье рассмотрим, как создать индекс, как его удалить и переименовать, а также как произвести переиндексацию в PostgreSQL. В качестве примера возьмём опыт инженеров из подразделения PayPal - Braintree Payments.

2 <h2>Создаём индекс</h2>

3 Если вы хотите создать индекс и запустите для этогоCREATE INDEX ..., вы получите блокировку на всей индексируемой таблице уровняACCESS EXCLUSIVE. А вот если вы выполните командуCREATE INDEX CONCURRENTLY ..., то уровень блокировки станет всего лишьSHARE UPDATE EXCLUSIVE. При этом во втором случае будут разрешены и запись, и чтение Правда, разница в том, что вместо одного сканирования таблицы придётся выполнить 2..

4 На что тут стоить обратить внимание: • если несколько созданий индексов выполняются параллельно на одной таблице, они не завершат выполнение ни одного изCREATE INDEX CONCURRENTLY ..., и это утверждение верно до тех пор, пока самый медленный из них всё ещё работает; •CREATE INDEX CONCURRENTLY ...не сможет выполниться внутри транзакции. Вместо этого транзакциями управляет PostgreSQL, делая это неявно. В результате никакие auto-vacuum’ы не могут очистить ненужные кортежи, появившиеся после начала построения индекса и вплоть до завершения данного процесса. Если же у таблицы имеется большой объём изменений (это в особенности плохо, когда непосредственно сама таблица мала), всё это может закончиться низкой скоростью исполнения запроса; •CREATE INDEX CONCURRENTLY ...завершит выполнение лишь тогда, когда завершатся все транзакции, которые используют таблицу.

5 <h2>Удаляем индекс</h2>

6 Стандартное выражениеDROP INDEX ...получитACCESS EXCLUSIVEна всей PostgreSQL-таблице и на всё время удаления индекса. Если речь идёт о небольших индексах, проблем нет, и операция не занимает много времени. Но если мы говорим об огромных индексах, то работа с файловой системой займёт гораздо больше времени. Тут поможет выражениеDROP INDEX CONCURRENTLY ..., которое потребует блокировку уровняSHARE UPDATE EXCLUSIVE. При этом и запись, и чтение будут продолжаться до тех пор, пока осуществляется наша операция удаления индекса.

7 У использованияDROP INDEX CONCURRENTLY ...есть ряд подводных камней: • запрос нельзя использовать для удаления индекса, который поддерживал какое-нибудь ограничение (к примеру, PRIMARY KEY либо UNIQUE); • запрос нельзя использовать в качестве части транзакции, т. к. ими управляет PostgreSQL, что называется, "под капотом". В результате никакие auto-vacuum’ы не могут очистить ненужные кортежи, появившиеся после начала построения индекса и вплоть до завершения данного процесса. Опять же, если у таблицы имеется большой объём изменений (это в особенности плохо, когда непосредственно сама таблица мала), всё это может закончиться низкой скоростью исполнения запроса; • запрос завершит выполнение лишь тогда, когда завершатся все транзакции, которые используют таблицу.

8 Учтите, чтоDROP INDEX CONCURRENTLY ...добавили лишь в Postgres 9.2. Если вы работаете с более ранними версиями, у вас есть возможность достичь приблизительного того же результата, если вы отметите индекс как некорректный (invalid) и к записи не готовый; потом останется сбросить буфер посредством расширения pgfincore, а уже после этого вы сможете просто удалить индекс.

9 <h2>Переименовываем индекс</h2>

10 ALTER INDEX ... RENAME TO ...требует блокировку на переименовываемом индексе уровня ACCESS EXCLUSIVE , то есть блокируется чтение и запись в соответствующую таблицу. Правда, существует<a>коммит</a>, понижающий данное требование доSHARE UPDATE EXCLUSIVE.

11 <h2>Производим переиндексацию</h2>

12 REINDEX INDEX ...тоже требуетACCESS EXCLUSIVEна индексе. Дабы это исключить, рекомендуем определённый алгоритм: • создаём новый индекс, (описано выше) повторяющий существующий; • удаляем старый индекс, делая это наименее затратным способом (тоже описано выше); • переименовываем новый индекс таким образом, чтобы он повторил имя старого индекса.

13 Примечание: если индекс, который вы хотите перестроить, содержал ограничения, обязательно добавьте эти ограничения и в новый индекс.

14 По материалам "<a>PostgreSQL at Scale: Database Schema Changes Without Downtime</a>".