SQL Academy · Lekcja

Konserwacja indeksów i bloat

Diagnozować bloat indeksów, przebudowywać je za pomocą REINDEX CONCURRENTLY i bezpiecznie usuwać nieużywane indeksy

Konserwacja indeksów i bloat to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Dlaczego indeksy się rozrastają

PostgreSQL używa MVCC: operacja UPDATE zapisuje nową wersję wiersza, pozostawiając starą widoczną dla istniejących transakcji. Wpisy indeksu istnieją dla obu wersji. Z czasem:

Tabele często aktualizowane gromadzą nieaktualne wpisy indeksów
Indeksy stają się większe, niż jest to konieczne
Wyszukiwanie zwalnia, ponieważ drzewo B-tree staje się głębsze

Diagnozowanie rozrostu indeksów

Zapytania służące do sprawdzania rozrostu indeksów nie są trywialne. Typowe narzędzia:

Rozszerzenie pgstattuple
Raporty pg_repack
Zapytania wykrywające rozrost indeksów z check_postgres / narzędzi monitorujących

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

REINDEX

Przebudowuje indeks. Klasyczna forma przejmuje blokadę ACCESS EXCLUSIVE — co jest niekorzystne w środowisku produkcyjnym:

REINDEX INDEX orders_user_id_idx;       -- blocks writes!

REINDEX CONCURRENTLY (PG 12+)

Wariant nieblokujący — odczyty i zapisy mogą być kontynuowane podczas przebudowy:

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

Nieużywane indeksy

Nieużywany indeks spowalnia każdy zapis, ale nie przyspiesza żadnego odczytu. Można je znaleźć za pomocą:

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Usuwanie nieużywanych indeksów

Należy je usunąć, ale wcześniej trzeba sprawdzić wszystkie środowiska i przedziały czasowe. Indeksy używane do sporadycznych raportów przez większość czasu wyglądają na nieużywane.

DROP INDEX CONCURRENTLY old_unused_idx;

Zduplikowane indeksy

Czasami ten sam indeks jest tworzony zarówno przez ograniczenie, jak i ręczne polecenie CREATE INDEX. Należy sprawdzić w pg_indexes, czy występują duplikaty, i usunąć zbędny indeks:

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Wzmocnienie obciążenia zapisu przez indeksy

Każda operacja INSERT/UPDATE/DELETE aktualizuje wszystkie odpowiednie indeksy. Trzy indeksy na często modyfikowanej tabeli = 3× większy koszt zapisu. Należy dodawać tylko indeksy, które przynoszą korzyści.

Lista oczekująca GIN

Indeksy GIN grupują aktualizacje na liście oczekującej. Listę można opróżniać ręcznie lub polegać na autovacuum:

SELECT gin_clean_pending_list('events_data_gin');

VACUUM czyści wpisy indeksów

VACUUM (omówione w lekcji o MVCC) usuwa nieaktualne wpisy indeksów ze stron sterty. Bez autovacuum indeksy rosną bez ograniczeń.

Monitorowanie rozmiaru indeksów

Rozmiar indeksów należy śledzić w czasie:

SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

pg_repack: przepisywanie tabel online

W przypadku znacznego rozrostu pg_repack przepisuje tabelę i indeksy online — bez blokowania całej tabeli. Należy zainstalować go jako pakiet systemu operacyjnego oraz rozszerzenie PostgreSQL.

Podsumowanie

Indeksy wymagają dbałości.

Rozrost spowodowany przez MVCC jest normalny — należy zarządzać nim za pomocą VACUUM i REINDEX CONCURRENTLY
Należy usuwać nieużywane indeksy
Należy unikać duplikatów
Każdy nowy indeks spowalnia zapisy — należy dodawać go świadomie

Szybkie sprawdzenie

Które polecenie PostgreSQL przebudowuje indeks BEZ blokowania zapisów?

Często zadawane pytania

Czy lekcja „Konserwacja indeksów i bloat” jest bezpłatna?

Tak — pełny tekst „Konserwacja indeksów i bloat” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Konserwacja indeksów i bloat”?

Diagnozować bloat indeksów, przebudowywać je za pomocą REINDEX CONCURRENTLY i bezpiecznie usuwać nieużywane indeksy Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.

Ile czasu zajmuje lekcja „Konserwacja indeksów i bloat”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?

Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

← Powrót do SQL Academy