Neizmantotie indeksi PostgreSQL ir viens no lētākajiem veiktspējas uzlabojumiem nobriedušā datubāzē. Neviens vairs neatceras, kāpēc pastāv orders_status_created_idx, bet katrs ieraksts joprojām raksta tajā, katrs vacuum to skenē, un tas aizņem vietu shared buffers un katrā rezerves kopijā. Tabulās ar desmit vai piecpadsmit indeksiem lieko indeksu noņemšana bieži ievērojami paātrina rakstīšanu un atbrīvo desmitiem vai simtiem gigabaitu.
Āķis ir tas, ka "neizmantots" ir apgalvojums par statistiku, un statistika var maldināt. Tālāk: vaicājums kandidātu atrašanai, indeksi, kurus nedrīkst dzēst pat ar nulli skenējumu, dublikāti un nederīgi indeksi, un dzēšanas procedūra, kas ir droša lielā produkcijas sistēmā.
Kāpēc neizmantots indekss nav bez maksas
- Rakstīšana. Katrs
INSERTpievieno ierakstu katrā tabulas indeksā.UPDATEarī, ja vien tas nevar būt HOT atjauninājums. Saskaņā ar dokumentāciju par HOT tas iespējams tikai tad, ja atjauninājums nemaina indeksētas kolonnas (izņemot BRIN indeksus kopš PostgreSQL 16) un jaunā rindas versija ietilpst tajā pašā lapā. Katrs papildu indekss uz bieži atjauninātas kolonnas atņem šiem atjauninājumiem HOT. - Vacuum. Vacuum jāiztīra mirušie ieraksti no katra indeksa, tāpēc vairāk indeksu nozīmē ilgākus vacuum uz lielākajām tabulām.
- Atmiņa un disks. Indeksu lapas konkurē ar noderīgiem datiem par kešatmiņu, un tās tiek iekopētas katrā bāzes rezerves kopijā un atjaunotas kopā ar to.
Cik daudz HOT jūs iegūstat šodien, var redzēt ar šo vaicājumu. Zems procents uz aktīvas tabulas ar daudziem indeksiem ir spēcīgs signāls, ka indeksu pārskatīšana atmaksāsies:
SELECT relname, n_tup_upd, n_tup_hot_upd,
round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 20;
Kandidātu atrašana ar pg_stat_user_indexes
PostgreSQL uzskaita indeksu lietojumu skatā pg_stat_user_indexes. idx_scan ir indeksā uzsākto skenējumu skaits, un kopš PostgreSQL 16 last_idx_scan rāda, kad indekss pēdējo reizi izmantots. Vaicājums zemāk parāda indeksus ar nulli skenējumu, lielākos vispirms, un izslēdz indeksus, kas kaut ko nodrošina:
SELECT s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
s.idx_scan,
s.last_idx_scan, -- PostgreSQL 16+
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
AND NOT i.indisunique -- includes primary keys
AND NOT i.indisexclusion
AND NOT i.indisreplident
AND NOT EXISTS (SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;
PostgreSQL 15 un vecākās versijās izņemiet rindu ar last_idx_scan. Filtri balstās uz pg_index katalogu: indisunique aptver arī primārās atslēgas, indisexclusion apzīmē izslēgšanas ierobežojumus, un indisreplident apzīmē indeksu, kas izvēlēts kā replica identity loģiskajai replikācijai. Pārbaude pret pg_constraint notver visu pārējo, no kā atkarīgs kāds ierobežojums.
idx_scan = 0 un tomēr būt būtiski. Nekad nedzēsiet tos, balstoties uz lietojuma skaitļiem.Pārbaudiet, cik ilgi skaitītāji ir skaitījuši
Nulle ir jēgpilna tikai tad, ja skaitītāji aptver pietiekami ilgu laiku. Statistikas dokumentācijā teikts, ka skaitītāji saglabājas pēc korektas apturēšanas, bet tiek atiestatīti pēc tūlītējas apturēšanas (immediate shutdown), avārijas, startēšanas no bāzes rezerves kopijas un atjaunošanas uz laika punktu (PITR). Ikviens ar atbilstošām tiesībām var tos atiestatīt arī ar pg_stat_reset() vai pg_stat_reset_single_table_counters().
SELECT stats_reset, pg_postmaster_start_time()
FROM pg_stat_database
WHERE datname = current_database();
stats_reset rāda pēdējo tiešo atiestatīšanu. Paskatieties arī servera starta laiku un žurnālus: ja pēdējais starts bija pēc avārijas vai pārslēgšanās uz tikko atjaunotu mezglu, skaitītāji ir jaunāki, nekā liek domāt stats_reset. Ja tie aptver divas dienas, jūs neesat redzējuši nedēļas atskaiti, mēneša slēgšanu vai ceturkšņa eksportu. Mēs dodam priekšroku vismaz vienam pilnam biznesa ciklam, parasti mēnesim, un ilgākam laikam sistēmām ar ceturkšņa vai gada darbiem.
Replikām ir sava statistika
Šis ir slazds, kas izraisa lielāko daļu kļūdainu dzēšanu. Statistika tiek vākta lokāli katrā serverī, un fiziskā replika glabā savu kopiju. Lasīšanas vaicājumi, kas darbojas uz standby, izmanto primārā servera indeksus, jo fiziskā replika ir to pašu failu kopija, bet šie skenējumi tiek uzskaitīti standby serverī, nevis primārajā.
Tāpēc indekss, kas primārajā rāda nulli skenējumu, var būt tieši tas indekss, kas uztur atskaišu repliku ātru. Pirms kaut ko dzēšat, palaidiet to pašu vaicājumu katrā replikā un uzskatiet indeksu par neizmantotu tikai tad, ja tas nav izmantots nekur. Tas pats attiecas uz loģiskās replikācijas abonentiem, lai gan tiem ir savi indeksi un savi lēmumi.
Dublēti un nederīgi indeksi
Dublikāti ir vienkāršāki un drošāki par neizmantotiem indeksiem, jo otrā kopija neko nepievieno. Šis vaicājums grupē indeksus pēc tabulas, kolonnām, operatoru klasēm, kolācijām, kārtošanas opcijām, izteiksmēm un daļējiem predikātiem:
SELECT indrelid::regclass AS table_name,
array_agg(indexrelid::regclass) AS same_definition
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, indcollation::text,
indoption::text, coalesce(indexprs::text, ''), coalesce(indpred::text, '')
HAVING count(*) > 1;
Ja divi indeksi sakrīt, paturiet to, kas nodrošina ierobežojumu, vai to, kuru jūsu migrācijas zina pēc nosaukuma, un dzēsiet otru. Pārklājošiem indeksiem vajag vairāk pārdomu: indeksu uz (customer_id) parasti aizstāj indekss uz (customer_id, created_at), bet ne tad, ja īsākais ir unikāls, daļējs vai daudz mazāks un to izmanto aktīvs vaicājums, kam izmēra starpība ir svarīga. Pārskatiet tos pa vienam.
Nederīgi indeksi bieži paliek pāri, kad CREATE INDEX CONCURRENTLY neizdodas, kā apraksta CREATE INDEX dokumentācija. Saskaņā ar pg_index nederīgu indeksu nevar izmantot vaicājumiem, bet ieraksti un atjauninājumi to joprojām uztur, tāpēc tas ir tīras izmaksas. Indekss, ko pašlaik būvē strādājošs CREATE INDEX CONCURRENTLY, arī tiek rādīts kā nederīgs, tāpēc pirms dzēšanas pārbaudiet pg_stat_activity:
SELECT indexrelid::regclass AS index_name,
indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;
Dzēsiet to un izveidojiet no jauna, ja tas patiešām bija vajadzīgs. Pārbūvi aprakstām mūsu rokasgrāmatā par REINDEX CONCURRENTLY.
Droša neizmantoto indeksu dzēšana
- Saglabājiet definīciju. Ielieciet
pg_get_indexdefizvadi migrāciju repozitorijā, lai indeksa atjaunošana būtu kopēšana un ielīmēšana, nevis arheoloģija. - Pārbaudiet lietotni. Meklējiet indeksa nosaukumu kodā un migrācijās. Daži ORM un vaicājumu norādes atsaucas uz indeksiem pēc nosaukuma, un daži upsert vaicājumi tieši nosauc unikālu indeksu vai ierobežojumu.
- Dzēsiet konkurenti. Parasts
DROP INDEXņemACCESS EXCLUSIVEbloķējumu uz tabulas.DROP INDEX CONCURRENTLYnebloķē lasīšanu un rakstīšanu, bet tā vietā gaida, līdz konfliktējošās transakcijas beidzas. - Vērojiet nedēļu. Salīdziniet vaicājumu latentumu,
pg_stat_statementsun lēno vaicājumu žurnālu ar bāzes līniju. Ja kāds vaicājums kļuva lēnāks, izveidojiet indeksu no jauna arCREATE INDEX CONCURRENTLY.
-- 1. Keep the definition so you can recreate it
SELECT pg_get_indexdef('orders_status_created_idx'::regclass);
-- 2. Drop without blocking reads and writes (not inside BEGIN ... COMMIT)
DROP INDEX CONCURRENTLY orders_status_created_idx;
Dokumentācija uzskaita CONCURRENTLY ierobežojumus: viens indekss vienā komandā, bez CASCADE, ne transakcijas blokā un ne indeksiem uz sadalītām tabulām. Dzēsiet vienu indeksu vienā migrācijas solī un izslēdziet transakcijas ietvaru, ko jūsu migrāciju rīks pievieno pēc noklusējuma.
Kas mainās lielās datubāzēs
- Kļūdīties ir dārgi. 200 GB indeksa dzēšana aizņem sekundes. Tā pārbūvei ar
CREATE INDEX CONCURRENTLYvajag divus tabulas skenējumus, un vairāku terabaitu tabulā tas var ilgt stundas, kamēr vaicājums, kam tas bija vajadzīgs, strādā ar secīgiem skenējumiem. Lielākos indeksus dzēsiet pēdējos, kad mazākās dzēšanas ir pierādījušas jūsu metodi. - Garas transakcijas bloķē dzēšanu.
DROP INDEX CONCURRENTLYgaida konfliktējošās transakcijas. Noslogotā sistēmā gara atskaite vai idle-in-transaction sesija var likt tai gaidīt bezgalīgi. Pirms sākat, pārbaudietpg_stat_activityun palaidiet dzēšanu klusā laikā. - Sadalītām tabulām vajag plānu. Tā kā
CONCURRENTLYnedarbojas indeksiem uz sadalītām tabulām, dzēšana ņem īsu ekskluzīvu bloķējumu. Izmantojiet zemulock_timeoutun atkārtošanas ciklu, lai dzēšana padotos, nevis nostādītu rindā aiz sevis katru vaicājumu. - Skaitiet katru repliku, katru reizi. Lielās sistēmās bieži ir atskaišu replika, analītikas replika un katastrofu atjaunošanas standby ar atšķirīgām slodzēm. Vāciet indeksu lietojumu no visām un atkārtojiet to pēc pārslēgšanās, jo paaugstināta mezgla skaitītāji var būt jauni.
- Neizmantots un uzpūsts ir dažādas problēmas. Indekss ar daudziem skenējumiem tomēr var būt vairākas reizes lielāks, nekā vajadzētu. Tam vajag pārbūvi, nevis dzēšanu; mūsu rokasgrāmata par autovacuum un uzpūšanos skaidro, kā uzpūšanās rodas.
- Padariet to par rutīnu. Indeksi tiek pievienoti incidentu laikā un reti noņemti. Ceturkšņa pārskats ar augstāk minētajiem vaicājumiem neļauj rakstīšanas izmaksām un rezerves kopiju izmēram gadu no gada rāpot uz augšu.
Galvenās atziņas
- Izmantojiet
pg_stat_user_indexes, lai atrastu indeksus aridx_scan = 0, unlast_idx_scanPostgreSQL 16 un jaunākās versijās. - Nekad nedzēsiet unikālus, primārās atslēgas, izslēgšanas, replica identity vai ierobežojumu indeksus, balstoties uz lietojuma skaitļiem.
- Pārliecinieties, ka skaitītāji aptver pilnu biznesa ciklu: avārijas, atjaunošana, PITR un manuāla atiestatīšana tos notīra.
- Pārbaudiet katru repliku. Skenējumi uz standby tiek uzskaitīti tikai šajā standby.
- Dublēti un nederīgi indeksi ir drošākie pirmie kandidāti noņemšanai.
- Saglabājiet definīciju, izmantojiet
DROP INDEX CONCURRENTLYpa vienam indeksam un pēc tam vērojiet latentumu.