PostgreSQL REINDEX CONCURRENTLY: uzpūstu indeksu pārbūve bez dīkstāves

REINDEX CONCURRENTLY pārbūvē PostgreSQL indeksu, nebloķējot tabulā ievietošanu, atjaunināšanu vai dzēšanu. Tas pastāv kopš PostgreSQL 12, un noslogotā produkcijas datubāzē tas parasti ir vienīgais pieņemamais veids, kā samazināt uzpūstu indeksu vai aizstāt bojātu. Tas arī ir lēnāks par parastu pārbūvi, nedarbojas visur un, ja neizdodas, atstāj aiz sevis nederīgu indeksu. Šis ceļvedis aplūko, kā saprast, vai indeksam vajadzīga pārbūve, kā to droši izpildīt un ko darīt, ja kaut kas noiet greizi.

Kad indeksu patiešām vajag pārbūvēt

REINDEX dokumentācijā minēti galvenie iemesli:

• Uzpūšanās (bloat). Indekss satur daudz tukšu vai gandrīz tukšu lapu, tāpēc tas ir daudz lielāks, nekā vajadzētu.
• Bojājums. Indekss vairs nesatur derīgus datus, piemēram, pēc aparatūras vai programmatūras problēmas.
• Mainīts glabāšanas parametrs, piemēram, fillfactor, kam jāstājas spēkā pilnībā.
• Nederīgs indekss, kas palicis pēc neizdevušās CREATE INDEX CONCURRENTLY.

Ir vēl viens gadījums, ko viegli palaist garām. PostgreSQL 13 laidiena piezīmēs teikts, ka B-tree deduplikācija, kas dublikātu atslēgas glabā tikai vienreiz, esošiem indeksiem, kas atjaunināti ar pg_upgrade, sāk darboties tikai pēc REINDEX. Ja šādi atjauninājāt no 12. vai vecākas versijas, indeksu pārbūve kolonnām ar maz atšķirīgu vērtību var tos manāmi samazināt.

Indeksu uzpūšanās parasti rodas no tā paša avota kā tabulu uzpūšanās: mirušās rindu versijas uzkrājas ātrāk, nekā vacuum tās izņem. Ja indeksi pēc pārbūves atkal uzpūšas, īstais risinājums ir autovacuum iestatījumos un ilgstošās transakcijās, kas aplūkotas mūsu autovacuum regulēšanas ceļvedī.

1. solis: atrodiet kandidātus

Sāciet ar lielākajiem indeksiem un to, cik bieži tie tiek izmantoti:

SELECT s.schemaname,
       s.relname      AS table_name,
       s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan
FROM pg_stat_user_indexes s
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;

Lieli indeksi ar idx_scan nulli vai tuvu tai ir vērts pārskatīt, pirms kaut ko pārbūvēt: indekss, ko neviens neizmanto, varbūt jādzēš, nevis jāpārbūvē. Divas piezīmes: dokumentācijā norādīts, ka visi statistikas skaitītāji tiek atiestatīti pēc nekorektas izslēgšanas, avārijas vai atjaunošanas uz laika punktu, tāpēc pārbaudiet, cik ilgi skaitītāji ir uzkrājušies; un, ja vaicājumus apkalpo arī replikas, pirms secināt, ka indekss netiek izmantots, pārbaudiet indeksu statistiku arī tur.

B-tree indeksam, kuram ir aizdomas par uzpūšanos, paplašinājums pgstattuple dod reālus skaitļus:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT pg_size_pretty(index_size) AS size,
       leaf_pages, empty_pages, deleted_pages,
       avg_leaf_density, leaf_fragmentation
FROM pgstatindex('public.orders_customer_id_idx');

• avg_leaf_density ir lapu (leaf) vidējā aizpildītība. B-tree noklusējuma fillfactor ir 90, un dokumentācijā teikts, ka sākotnējās izveides laikā lapas tiek aizpildītas līdz šim procentam. Daudz zemāka vērtība nozīmē, ka liela daļa indeksa ir tukša vieta.
• empty_pages un deleted_pages skaita lapas, kurās nav dzīvu ierakstu.
• leaf_fragmentation parāda, cik tālu lapu fiziskā secība novirzījusies no loģiskās.

Pēc noklusējuma šīs funkcijas var izpildīt tikai superlietotāji un pg_stat_scan_tables dalībnieki. Dokumentācijā norādīts, ka rezultāti tiek apkopoti lapu pa lapai, nevis kā vienlaicīgs momentuzņēmums; ļoti lieliem indeksiem pārbaudi veiciet ārpus maksimālās slodzes laika.

Kāpēc parasts REINDEX produkcijā ir problēma

Dokumentācija par slēdzenēm runā tieši. Parasts REINDEX bloķē ierakstus, bet ne lasīšanu, vecāktabulā. Tas arī ņem ACCESS EXCLUSIVE slēdzeni pārbūvējamajam indeksam, un, tā kā vaicājumu plānotājs mēģina ņemt slēdzeni katram tabulas indeksam neatkarīgi no vaicājuma, parasts REINDEX "bloķē praktiski jebkurus vaicājumus" šai tabulai, izņemot dažus sagatavotus vaicājumus ar kešotiem plāniem, kas šo indeksu neizmanto. Lielai tabulai tas var nozīmēt minūtēm ilgi bloķētu trafiku.

REINDEX CONCURRENTLY izveido jaunu indeksa kopiju blakus vecajai un apmaina tās, neņemot slēdzenes, kas liegtu ievietošanu, atjaunināšanu vai dzēšanu:

-- viens indekss
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;

-- visi vienas tabulas indeksi
REINDEX TABLE CONCURRENTLY public.orders;

Saskaņā ar dokumentāciju tas pievieno pagaidu indeksa definīciju, izveido to pirmajā gājienā, otrajā gājienā pievieno pa to laiku ierakstītās rindas, pārslēdz ierobežojumus un nosaukumus uz jauno indeksu, atzīmē veco kā nederīgu un beigās to dzēš. Katrs solis notiek savā transakcijā.

Izmaksas un ierobežojumi

• Tas ir lēnāks. PostgreSQL katram indeksam skenē tabulu divreiz un gaida visas esošās transakcijas, kas varētu izmantot indeksu. Dokumentācijā teikts, ka tas "aizņem ievērojami ilgāku laiku" nekā parasta pārbūve.
• Garas transakcijas to aptur. Tā kā tas gaida vecākas transakcijas, aizmirsta sesija var pārbūvi aizturēt stundām. Vispirms pārbaudiet tās; mūsu rakstā par "idle in transaction" sesijām parādīts, kā.
• Ne transakcijas blokā. Parastu REINDEX TABLE var izpildīt BEGIN ... COMMIT iekšpusē; REINDEX CONCURRENTLY – nevar. Daži migrāciju rīki katru soli ietin transakcijā, tāpēc pārbaudiet, kā to dara jūsējais.
• Pa vienam katrai tabulai. Vienai tabulai vienlaikus var notikt tikai viena konkurējoša indeksa izveide, un tikmēr nav atļautas citas shēmas izmaiņas.
• Ne sistēmas katalogiem, tāpēc REINDEX SYSTEM nav konkurējošas formas.
• Ne izslēgšanas ierobežojumiem (exclusion constraints). Šāda indeksa tieša norādīšana rada kļūdu; konkurējoši pārbūvējot visu tabulu vai datubāzi, šie indeksi tiek izlaisti, un tiem vajadzīgs parasts REINDEX.
• Tam vajag vietu. Vecā un jaunā kopija pastāv blakus līdz apmaiņai, tāpēc pārliecinieties, ka diskā ir vieta vēl vienai indeksa kopijai.

Privilēģijas aktuālajās versijās (PostgreSQL 17 un jaunākās): viena indeksa vai tabulas pārbūvei vajadzīga MAINTAIN privilēģija tabulai; shēmai vai datubāzei – īpašumtiesības vai dalība pg_maintain.

Progresa uzraudzība

Kopš PostgreSQL 12 pg_stat_progress_create_index atspoguļo gan CREATE INDEX, gan REINDEX:

SELECT pid, command, phase,
       blocks_done, blocks_total,
       tuples_done, tuples_total
FROM pg_stat_progress_create_index;

Kolonna command rāda REINDEX CONCURRENTLY, bet phase – kur tas atrodas. Ja tas ilgi stāv tādā fāzē kā waiting for old snapshots vai waiting for readers before marking dead, tas nav lēns – tas gaida, kamēr cita sesija beigs darbu. Tas ir brīdis meklēt vecas transakcijas pg_stat_activity.

Ja tas neizdodas: _ccnew un _ccold

Ja konkurējoša pārbūve neizdodas, piemēram, unikalitātes pārkāpuma dēļ unikālā indeksā, komanda beidzas ar kļūdu, bet atstāj nederīgu indeksu. Vaicājumi šādu indeksu ignorē, bet, kā norāda dokumentācija, tas joprojām palielina katra ieraksta izmaksas. Atlikumus atrodiet šādi:

SELECT indexrelid::regclass AS index_name,
       indrelid::regclass   AS table_name
FROM pg_index
WHERE NOT indisvalid;

Sufikss pasaka, ko darīt:

• _ccnew ir pusizveidotais jaunais indekss. Dzēsiet to ar DROP INDEX un, kad cēlonis novērsts, vēlreiz izpildiet REINDEX CONCURRENTLY.
• _ccold ir sākotnējais indekss, ko beigās neizdevās dzēst. Pati pārbūve izdevās, tāpēc vienkārši dzēsiet veco.

Lai nosaukumi būtu unikāli, var tikt pievienots skaitlis, piemēram, _ccnew1. Nederīgu indeksu konkurējoši var pārbūvēt tikai REINDEX INDEX.

Daudzu indeksu pārbūve

Utilīta reindexdb izpilda to pašu komandu un var palaist vairākas paralēli:

reindexdb --concurrently --jobs=2 --table=public.orders --table=public.order_items mydb

--jobs atver tikpat savienojumu un, kā brīdina dokumentācija, palielina servera slodzi, tāpēc produkcijā turiet to mērenu. To nevar apvienot ar --system. Viena liela indeksa izveidē var izmantot paralēlos procesus: B-tree, GIN un BRIN atbalsta paralēlu izveidi, ko ierobežo maintenance_work_mem un max_parallel_maintenance_workers.

Vai pārbūve jāveic regulāri?

Parasti nē. Mūsdienu PostgreSQL daudz indeksu atkritumu izņem pats. Deduplikācija B-tree indeksiem ir ieslēgta pēc noklusējuma un saskaņā ar B-tree dokumentāciju ievērojami samazina indeksu izmēru atkārtotām vērtībām. Kopš PostgreSQL 14 bottom-up indeksu dzēšana izņem novecojušus ierakstus, lai novērstu lapu dalīšanu, ko laidiena piezīmes raksturo kā īpaši noderīgu indeksiem tabulās ar bieži atjauninātām kolonnām. Iknedēļas visu indeksu pārbūve galvenokārt pievieno I/O. Mēriet, pārbūvējiet acīmredzami uzpūstos indeksus un salabojiet vacuum problēmu, kas tos radīja.