Обычный CREATE INDEX берёт ShareLock и останавливает запись на всё время построения. CREATE INDEX CONCURRENTLY (CIC) обходится слабым замком, но платит двумя проходами по таблице, тремя ожиданиями чужих транзакций и риском оставить недостроенный индекс. Что с ним делать и как пересобрать раздутый индекс, видно по каталогу и по представлению прогресса.
Что делает CONCURRENTLY
Порядок шагов записан в комментарии к validate_index (index.c, строки 3337–3343, тег REL_18_6):
* Next, we mark the index "indisready" (but still not "indisvalid") and
* commit the second transaction and start a third. Again we wait for all
* transactions that could have been modifying the table to terminate. Now
* we know that any subsequently-started transactions will see the index and
* insert their new tuples into it. We then take a new reference snapshot
* which is passed to validate_index(). Any tuples that are valid according
* to this snap, but are not in the index, must be added to the index.Сначала индекс появляется в каталоге с indisready = false и indisvalid = false, затем транзакции, которые могли писать в таблицу, ждут. Первый проход строит индекс по снимку, второй (validate_index) досыпает строки, которые появились за это время. Последнее ожидание стоит в DefineIndex перед тем, как индекс станет читаемым (indexcmds.c, строки 1806–1813):
* The index is now valid in the sense that it contains all currently
* interesting tuples. But since it might not contain tuples deleted just
* before the reference snap was taken, we have to wait out any
* transactions that might have older snapshots.
*/
pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
PROGRESS_CREATEIDX_PHASE_WAIT_3);
WaitForOlderSnapshots(limitXmin, true);CREATE INDEX CONCURRENTLY: пять фаз и состояние индекса в каталоге
ready = f), либо пишется, но не читается (ready = t, valid = f). Прерванный на этом отрезке CIC оставляет INVALID: он дорожает на каждой записи и не ускоряет ни одного запроса.Опыт 1: фазы под нагрузкой
Таблица на 4 млн строк (417 МБ), 4 клиента pgbench, каждый в транзакции делает UPDATE одной строки и INSERT новой. Параллельно psql с \watch 0.05 снимает pg_stat_progress_create_index:
docker run -d --name dba-index-maintenance-concurrently -e POSTGRES_PASSWORD=pg postgres:18
docker exec -i dba-index-maintenance-concurrently psql -U postgres <<'EOF'
CREATE EXTENSION amcheck;
CREATE TABLE orders (id bigserial PRIMARY KEY, user_id int NOT NULL, status int NOT NULL, pad text);
INSERT INTO orders (user_id, status, pad) SELECT (random()*100000)::int, 0, repeat('x',60) FROM generate_series(1,4000000);
ANALYZE orders;
EOF
docker exec -i dba-index-maintenance-concurrently sh -c 'cat > /tmp/load.sql' <<'EOF'
\set id random(1,1000000)
BEGIN;
UPDATE orders SET status = status + 1 WHERE id = :id;
INSERT INTO orders (user_id, status, pad) VALUES (:id % 100000, 0, 'n');
END;
EOF
docker exec -i dba-index-maintenance-concurrently sh -c 'cat > /tmp/sample.sql' <<'EOF'
\pset tuples_only on
\pset format unaligned
SELECT to_char(clock_timestamp(),'SS.MS') AS t, phase, lockers_total||'/'||lockers_done AS lockers,
blocks_done||'/'||blocks_total AS blocks, tuples_done||'/'||tuples_total AS tup
FROM pg_stat_progress_create_index \watch 0.05
EOF
docker exec -d dba-index-maintenance-concurrently sh -c 'pgbench -U postgres -n -f /tmp/load.sql -c 4 -j 4 -T 25 postgres > /tmp/pgb.out'
sleep 3
docker exec -d dba-index-maintenance-concurrently sh -c 'timeout 15 psql -U postgres -f /tmp/sample.sql > /tmp/samples.txt'
docker exec -i dba-index-maintenance-concurrently psql -U postgres -c '\timing on' -c 'CREATE INDEX CONCURRENTLY orders_user_idx ON orders (user_id)'
sleep 22
docker exec dba-index-maintenance-concurrently sh -c 'grep "|" /tmp/samples.txt' | awk -F'|' '{if($2!=p){print; p=$2}}'Сводка из awk оставляет первую пробу каждой фазы (колонки: секунды, фаза, ожидаемые транзакции всего/дождались, блоки, кортежи):
34.006|initializing|0/0|0/0|0/0
34.056|building index: scanning table|0/0|6482/53350|0/0
34.806|building index: loading tuples in tree|0/0|0/0|692526/4000720
35.356|waiting for writers before validation|1/0|0/0|4000720/4000720
35.406|index validation: scanning index|0/0|1992/3551|0/0
35.906|index validation: scanning table|0/0|13140/53358|0/0CREATE INDEX
Time: 2075.387 ms (00:02.075)
number of transactions actually processed: 5302
number of failed transactions: 0 (0.000%)Первый проход (сканирование, сортировка, загрузка в дерево) занял с 34.056 по 35.356, то есть 1,3 с; ожидание писателей перед проверкой уложилось в одну пробу; второй проход начался в 35.406 и закончился до конца запроса за ≈ 0,7 с. Ожидания перед построением и старых снимков в пробы не попали: на коротких транзакциях pgbench они короче шага 0,05 с. Писатели не упали ни разу (0 ошибочных транзакций из 5 302). На этой же таблице позже без нагрузки обычный CREATE INDEX занял 1 126 мс, CIC — 1 910 мс: цена двух проходов и ожиданий.
Опыт 2: кого ждёт CIC
Те же фазы растягиваются, если кто-то держит нужный замок или снимок. Для этого опыта сэмплер показывает, кого ждёт CIC (current_locker_pid), с шагом 0,2 с:
docker exec -i dba-index-maintenance-concurrently sh -c 'cat > /tmp/sample2.sql' <<'EOF'
\pset tuples_only on
\pset format unaligned
SELECT to_char(clock_timestamp(),'SS.MS') AS t, phase, lockers_total||'/'||lockers_done AS lockers, current_locker_pid
FROM pg_stat_progress_create_index \watch 0.2
EOFСначала писатель, открытая транзакция с UPDATE на 4 секунды, запущенная за полсекунды до CIC:
docker exec -d dba-index-maintenance-concurrently psql -U postgres \
-c "BEGIN; UPDATE orders SET status = status WHERE id = 1; SELECT pg_sleep(4); COMMIT;"
sleep 0.5
docker exec -d dba-index-maintenance-concurrently sh -c 'timeout 9 psql -U postgres -f /tmp/sample2.sql > /tmp/s2a.txt'
docker exec -i dba-index-maintenance-concurrently psql -U postgres -c '\timing on' \
-c 'CREATE INDEX CONCURRENTLY orders_user_idx ON orders (user_id)'22.008|waiting for writers before build|1/0|237
25.408|building index: scanning table|0/0|0
Time: 5109.412 ms (00:05.109)CIC стоял на первой фазе 3,4 с, ожидая процесс 237 (current_locker_pid) — писателя из соседней сессии. Всего 5,1 с против 2,1 с без ожидания. Второй случай тише: транзакция REPEATABLE READ, которая вообще не обращалась к orders, и нагрузка pgbench на фоне:
docker exec -d dba-index-maintenance-concurrently psql -U postgres \
-c "BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1; SELECT pg_sleep(6); COMMIT;"41.465|waiting for old snapshots|1/0|291
Time: 5434.873 ms (00:05.435)Процесс 291 ни разу не обращался к orders, но его снимок старше опорного снимка validate_index, поэтому пятая фаза ждёт его. Практический вывод: CIC блокирует не тот, кто пишет в таблицу, а любой долгий снимок в базе, включая idle in transaction из другого приложения: CIC ждёт, пока снимок не закроют.
Опыт 3: INVALID, который пишется, но не читается
Прервём CIC на пятой фазе: под нагрузкой pgbench (10 с) снимок из опыта 2 держится 9 с, а у CIC statement_timeout 4 с:
docker exec -d dba-index-maintenance-concurrently sh -c 'pgbench -U postgres -n -f /tmp/load.sql -c 4 -j 4 -T 10 postgres'
sleep 1
docker exec -d dba-index-maintenance-concurrently psql -U postgres \
-c "BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1; SELECT pg_sleep(9); COMMIT;"CIC запускается сразу, пока снимок открыт; запросы после него — когда pgbench закончит. После отмены в каталоге остаётся недостроенный индекс:
SET statement_timeout = '4s';
CREATE INDEX CONCURRENTLY orders_user_idx ON orders (user_id);
SELECT indexrelid::regclass AS idx, indisvalid, indisready, indislive FROM pg_index WHERE indrelid = 'orders'::regclass;
SELECT pg_relation_size('orders_user_idx') AS idx_bytes;
INSERT INTO orders (user_id, status, pad) SELECT (random()*100000)::int, 0, 'n' FROM generate_series(1,300000);
SELECT pg_relation_size('orders_user_idx') AS idx_bytes;
EXPLAIN SELECT * FROM orders WHERE user_id = 42;ERROR: canceling statement due to statement timeout
idx | indisvalid | indisready | indislive
-----------------+------------+------------+-----------
orders_pkey | t | t | t
orders_user_idx | f | t | t
idx_bytes
-----------
29138944
idx_bytes
-----------
30941184
Gather (cost=1000.00..77474.27 rows=42 width=77)
Workers Planned: 2
-> Parallel Seq Scan on orders (cost=0.00..76470.07 rows=18 width=77)
Filter: (user_id = 42)\d orders помечает такой индекс словом INVALID. Это тот самый промежуточный статус со схемы: indisready = t, indisvalid = f. Индекс не участвует в плане (Parallel Seq Scan), но каждая из 300 000 вставок добавила в него запись: 29 138 944 → 30 941 184 байт. Такой индекс только замедляет запись. По документации такой остаток оставляют и взаимоблокировка, и нарушение уникальности при CREATE UNIQUE INDEX CONCURRENTLY. Лечится пересборкой или удалением: DROP INDEX CONCURRENTLY.
Опыт 4: REINDEX CONCURRENTLY строит рядом и меняет местами
На том же INVALID-индексе:
SELECT 'orders_user_idx'::regclass::oid AS oid_before;
\timing on
REINDEX INDEX CONCURRENTLY orders_user_idx;
SELECT 'orders_user_idx'::regclass::oid AS oid_after;
SELECT indexrelid::regclass AS idx, indisvalid, indisready FROM pg_index WHERE indrelid = 'orders'::regclass; oid_before
------------
16414
REINDEX
Time: 3200.958 ms (00:03.201)
oid_after
-----------
16415
idx | indisvalid | indisready
-----------------+------------+------------
orders_pkey | t | t
orders_user_idx | t | tИмя осталось, OID сменился: построен второй индекс, затем index_concurrently_swap одним обновлением каталога помечает новый валидным, а старый невалидным (index.c, строки 1658–1663). Старый индекс после этого сначала помечается мёртвым, потом удаляется, и перед каждым из двух шагов ReindexRelationConcurrently ждёт тех, кто мог бы использовать индекс (WaitForLockersMultiple с AccessExclusiveLock, фазы WAIT_4 и WAIT_5). Если прервать REINDEX CONCURRENTLY на тех же условиях (открытый снимок, statement_timeout 4 с), недостроенная копия остаётся рядом с рабочим индексом:
ERROR: canceling statement due to statement timeout
idx | indisvalid | indisready
-----------------------+------------+-----------
orders_pkey | t | t
orders_user_idx | t | t
orders_user_idx_ccnew | f | tРабочий orders_user_idx цел, а orders_user_idx_ccnew в статусе, что INVALID из опыта 3 (indisready = t, indisvalid = f). Он удаляется DROP INDEX CONCURRENTLY orders_user_idx_ccnew.
Опыт 5: дубли, неиспользуемые индексы и amcheck
Добавим дубль и перекрывающий индекс, сбросим счётчики и выполним один запрос:
DROP INDEX CONCURRENTLY orders_user_idx_ccnew;
CREATE INDEX CONCURRENTLY orders_user_idx2 ON orders (user_id);
CREATE INDEX CONCURRENTLY orders_user_status_idx ON orders (user_id, status);
SELECT pg_stat_reset_single_table_counters('orders'::regclass);
SELECT count(*) FROM orders WHERE user_id = 42;
SELECT pg_stat_force_next_flush();
SELECT indexrelid::regclass AS idx, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan
FROM pg_index JOIN pg_stat_user_indexes s USING (indexrelid)
WHERE indrelid = 'orders'::regclass AND NOT indisprimary ORDER BY 1;
SELECT array_agg(indexrelid::regclass ORDER BY indexrelid) AS same_definition
FROM pg_index WHERE indrelid = 'orders'::regclass
GROUP BY indrelid, indkey, indclass, indoption, indexprs, indpred, indisunique HAVING count(*) > 1; idx | size | idx_scan
------------------------+-------+----------
orders_user_idx | 30 MB | 0
orders_user_idx2 | 30 MB | 0
orders_user_status_idx | 30 MB | 1
same_definition
------------------------------------
{orders_user_idx,orders_user_idx2}Группировка по indkey, indclass, indoption, indexprs, indpred и indisunique находит индексы с одинаковым определением. Нулевой idx_scan у обоих членов пары не значит, что лишние оба: запрос ушёл в третий индекс, и из пары убирают один. orders_user_idx и orders_user_idx2 — полные дубли, orders_user_status_idx перекрывает их префиксом user_id. Счётчик живёт с последнего сброса (опыт начался со pg_stat_reset_single_table_counters), поэтому перед DROP смотри stats_reset; уникальные индексы держат ограничение, и их из списка исключают.
Структуру проверяет amcheck. Пока другая сессия держит открытый UPDATE на таблице, при lock_timeout = 1s:
docker exec -d dba-index-maintenance-concurrently psql -U postgres \
-c "BEGIN; UPDATE orders SET status = status WHERE id = 1; SELECT pg_sleep(6); COMMIT;"SET lock_timeout = '1s';
SELECT bt_index_check('orders_user_idx', heapallindexed => true);
SELECT bt_index_parent_check('orders_user_idx', heapallindexed => true); bt_index_check
----------------
ERROR: canceling statement due to lock timeoutbt_index_check прошёл параллельно с записью, bt_index_parent_check упёрся в замок и отменился. По документации amcheck первая функция берёт AccessShareLock на индекс и таблицу, вторая — ShareLock, а он конфликтует с RowExclusiveLock открытого UPDATE. Когда писатель закончил, проверка с heapallindexed => true (каждая строка кучи найдена в индексе) заняла 1 778 мс на 30 МБ индекса, а bt_index_check('orders_user_idx') без этого флага — 33 мс. Пустой результат — норма: при повреждении будет ERROR.
Что из этого следует
- Прерванный CIC всегда проверяй. Он оставляет
INVALID, который платит за каждую запись (+1 802 240 Б за 300 000 вставок) и не читается планами. Мониторингindisvalid = falseобязателен. - Мешают долгие снимки, а не только долгие писатели. Транзакция без обращения к таблице держала CIC в пятой фазе: 5,4 с вместо 2,1 с. Если в базе висит
idle in transaction, ожидание длится, пока он не закончится. - Дубли и неиспользуемые индексы ищи по определению, а не по
idx_scan = 0. Один нулевой счётчик ничего не доказывает: обе половины дубля показывают 0.
Чек-лист
- Перед
CREATE INDEX CONCURRENTLYпроверьpg_stat_activityна старые снимки (backend_xmin) иidle in transaction, затем следи заpg_stat_progress_create_index. - После любой ошибки CIC или REINDEX CONCURRENTLY ищи
indisvalid = falseвpg_indexи имена_ccnew; удаляй найденное черезDROP INDEX CONCURRENTLY. - Перед удалением «неиспользуемого» индекса сверь
idx_scan,stats_resetи уникальность; дубли ищи по определению.bt_index_checkгодится на живой таблице,bt_index_parent_checkостанавливает запись. - Убери контейнер вместе с данными:
docker rm -f -v dba-index-maintenance-concurrently.