Zmiany indeksów bez przestojów w PostgreSQL: bezpieczny playbook

Dlaczego zmiany indeksów powodują przestoje (i jak ich uniknąć)

Operacje na indeksach brzmią nieszkodliwie. „Po prostu” dodajesz strukturę pomocniczą. W PostgreSQL jednak tworzenie, usuwanie lub zamiana indeksu może wymagać blokad, które zatrzymują inne sesje. Jeśli tabela jest mocno używana, oczekiwania narastają i aplikacja zaczyna sprawiać wrażenie uszkodzonej.

Przestoje rzadko wyglądają jak ładny baner wyłączenia. Częściej objawiają się zawieszającymi się stronami, zalegającymi zadaniami backgroundowymi i rosnącą kolejką żądań oczekujących na bazę. Ktoś klika „Szukaj” i dostaje timeout, a narzędzia wsparcia i ekrany administracyjne nagle działają wolno, bo proste zapytania nie mogą dostać potrzebnej blokady.

„Po prostu uruchom to w nocy” zawodzi z dwóch powodów. Wiele systemów nigdy nie jest naprawdę cichych (użytkownicy globalni, zadania wsadowe, ETL, backupy). Poza tym operacje na indeksach mogą trwać dłużej, niż się spodziewasz, bo muszą przetworzyć dużo danych i rywalizować o CPU oraz dysk. Jeśli okno konserwacji zamyka się w trakcie budowy, musisz wybierać między czekaniem lub przerywaniem pracy.

Zmiany indeksów bez przestojów to nie magia. Chodzi o wybór operacji najmniej blokującej, ustawienie ograniczeń (timeouty i kontrole dysku) oraz obserwowanie bazy podczas pracy.

Ten playbook skupia się na praktycznych nawykach produkcyjnych:

• Preferuj budowę indeksów w trybie concurrent, gdy czytania i zapisy muszą działać dalej.
• Monitoruj blokady i postęp budowy, aby reagować wcześnie.
• Miej gotowy plan wycofania, jeśli zmiana powoduje regresje lub trwa za długo.

Czego tu nie omawiamy: głębokiej teorii projektowania indeksów, szerokiego strojenia zapytań ani refaktoringów schematu przepisujących dużo danych.

Prosty model blokad przy pracy z indeksami

PostgreSQL używa blokad, by zachować poprawność danych, gdy wiele sesji dotyka tej samej tabeli. Blokada to reguła mówiąca, kto teraz może czytać lub zapisywać obiekt, a kto musi czekać.

Większość czasu nawet nie zauważasz blokad, bo PostgreSQL stosuje lekkie tryby, które pozwalają normalnym zapytaniom działać. DDL jest inny. Przy tworzeniu lub usuwaniu indeksu PostgreSQL potrzebuje wystarczającej kontroli nad tabelą, by zachować spójność katalogu i danych. Im więcej kontroli, tym więcej innych sesji może zostać zmuszonych do oczekiwania.

Budowa indeksu vs korzystanie z indeksu

Korzystanie z indeksu zwykle nie jest kosztowne pod kątem blokad. SELECT, UPDATE i DELETE mogą czytać i utrzymywać indeksy jednocześnie z innymi sesjami.

Budowa indeksu jest inna. PostgreSQL musi przeskanować tabelę, posortować lub zahashować klucze i zapisać nową strukturę na dysku. Ta praca zajmuje czas, a czas zamienia „małe blokady” w „duże problemy” w produkcji.

Co zmienia CONCURRENTLY (a czego nie zmienia)

Zwykły CREATE INDEX bierze mocną blokadę, która blokuje zapisy na czas budowy. CREATE INDEX CONCURRENTLY jest zaprojektowany tak, by pozwolić na normalne odczyty i zapisy podczas tworzenia indeksu.

Ale „concurrent” nie znaczy „bez blokad”. Nadal występują krótkie okna blokad na początku i na końcu, a budowa może się nie powieść lub utknąć, jeśli coś innego trzyma niekompatybilne blokady.

Wyniki, które mają znaczenie:

• Nie-konkurencyjne budowy mogą blokować INSERTy, UPDATEy i DELETE na tabeli.
• Budowy concurrent zwykle pozwalają na odczyty i zapisy, ale mogą być spowolnione albo zatrzymane przez długotrwałe transakcje.
• Końcowe kroki nadal potrzebują krótkich blokad, więc bardzo zajęte systemy mogą doświadczyć krótkich oczekiwań.

Wybierz właściwe podejście: concurrent czy normalnie

Masz dwie główne opcje przy zmianie indeksów: zbudować indeks normalnie (szybko, ale blokująco) albo zbudować go z CONCURRENTLY (zwykle nieblokująco dla ruchu aplikacji, ale wolniej i bardziej podatnie na długie transakcje).

Kiedy CONCURRENTLY jest właściwym wyborem

Użyj CREATE INDEX CONCURRENTLY, gdy tabela obsługuje rzeczywisty ruch i nie możesz wstrzymać zapisów. Zwykle jest to bezpieczniejszy wybór, gdy:

• Tabela jest na tyle duża, że normalna budowa może trwać minuty lub godziny.
• Tabela ma stałe zapisy, a nie tylko odczyty.
• Nie możesz zaplanować prawdziwego okna konserwacji.
• Musisz najpierw zbudować, zweryfikować, a dopiero potem usunąć stary indeks.

Kiedy zwykła budowa indeksu jest dopuszczalna

Normalny CREATE INDEX może być w porządku, gdy tabela jest mała, ruch jest niski lub masz kontrolowane okno konserwacji. Często kończy się szybciej i jest prostszy do uruchomienia.

Rozważ zwykłe podejście, jeśli budowa konsekwentnie jest szybka w środowisku staging i możesz tymczasowo zatrzymać zapisy (nawet na krótko).

Jeśli potrzebujesz unikalności, zdecyduj wcześniej. CREATE UNIQUE INDEX CONCURRENTLY działa, ale zakończy się błędem, jeśli istnieją duplikaty. W wielu systemach produkcyjnych znalezienie i naprawienie duplikatów to faktyczny zakres prac.

Kontrole przed dotknięciem produkcji

Większość problemów zaczyna się zanim polecenie się uruchomi. Kilka kontroli pomaga uniknąć dwóch dużych niespodzianek: nieoczekiwanego blokowania i budowy indeksu, która trwa dużo dłużej (lub zajmuje więcej miejsca), niż planowałeś.

1. Upewnij się, że nie jesteś w transakcji. CREATE INDEX CONCURRENTLY zakończy się błędem, jeśli uruchomisz go po BEGIN, a niektóre narzędzia GUI cicho otaczają polecenia transakcją. Jeśli nie jesteś pewien, otwórz świeżą sesję i uruchom tam tylko polecenie indeksu.

2. Ustal oczekiwania co do czasu i miejsca na dysku. Budowy concurrent zwykle trwają dłużej niż normalne i potrzebują dodatkowej przestrzeni roboczej podczas działania. Zaplanuj miejsce na nowy indeks plus narzut tymczasowy i potwierdź, że masz wygodny zapas wolnego miejsca na dysku.

3. Ustaw timeouty odpowiadające Twojemu celowi. Chcesz, aby budowa szybko zakończyła się błędem, jeśli nie może dostać blokady, ale nie chcesz, żeby sesja ginęła w trakcie budowy z powodu zbyt agresywnego statement_timeout.

4. Zrób snapshot bazowy. Chcesz dowód, że zmiana pomogła i szybki sposób wykrycia regresji. Zapisz przed: czasy wolnych zapytań, reprezentatywne EXPLAIN (ANALYZE, BUFFERS) oraz szybki widok CPU, IO, połączeń i wolnego miejsca na dysku.

Bezpieczne ustawienia sesji, których wiele zespołów używa jako punktu wyjścia (dostosuj do swoich reguł):

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

Krok po kroku: tworzenie indeksu z CONCURRENTLY

Użyj CREATE INDEX CONCURRENTLY, gdy potrzebujesz, aby ruch aplikacji nadal działał i możesz poświęcić dłuższy czas budowy.

Najpierw zdecyduj dokładnie, co tworzysz:

• Bądź konkretny co do kolejności kolumn (ma to znaczenie).
• Zastanów się, czy wystarczy indeks częściowy. Jeśli większość zapytań filtruje po wierszach "active", indeks częściowy może być mniejszy, szybszy i tańszy w utrzymaniu.

Bezpieczny przebieg wygląda tak: zapisz cel i nazwę indeksu, uruchom budowę poza blokiem transakcyjnym, obserwuj do zakończenia, a potem zweryfikuj, że planner potrafi go użyć, zanim cokolwiek usuniesz.

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

Dla notatek o postępie (przydatnych do audytu) zanotuj czas rozpoczęcia, czas zakończenia i wszelkie oczekiwania, które zaobserwowałeś. W trakcie działania możesz z innej sesji zapytać pg_stat_progress_create_index.

Weryfikacja to nie tylko „indeks istnieje”. Potwierdź, że planner może go wybrać, a potem obserwuj rzeczywiste czasy zapytań po wdrożeniu. Jeśli nowy indeks nie jest używany, nie spiesz się z usuwaniem starego. Najpierw popraw zapytanie lub definicję indeksu.

Krok po kroku: zamiana lub usuwanie indeksów bez blokowania

Najbezpieczniejszy wzorzec to najpierw dodać nowy indeks, pozwolić ruchowi skorzystać z niego, a dopiero potem usunąć stary. W ten sposób zachowujesz działający plan awaryjny.

Zamiana starego indeksu na nowy (bezpieczna kolejność)

1. Stwórz nowy indeks z CREATE INDEX CONCURRENTLY.

2. Zweryfikuj, że jest używany. Sprawdź EXPLAIN dla wolnych zapytań, które Cię interesują, i obserwuj użycie indeksów w czasie.

3. Dopiero wtedy usuń stary indeks concurrent. Jeśli ryzyko jest wysokie, trzymaj oba indeksy przez pełny cykl biznesowy przed usunięciem czegokolwiek.

Usuwanie indeksów: kiedy CONCURRENTLY działa (a kiedy nie)

Dla zwykłego indeksu, który sam stworzyłeś, DROP INDEX CONCURRENTLY zwykle jest właściwym wyborem. Dwa zastrzeżenia: nie może działać wewnątrz bloku transakcyjnego i wciąż potrzebuje krótkich blokad na początku i końcu, więc może być opóźniony przez długotrwałe transakcje.

Jeśli indeks istnieje z powodu PRIMARY KEY lub UNIQUE constraint, zwykle nie możesz go po prostu usunąć. Musisz zmienić constraint przez ALTER TABLE, co może wymagać silniejszych blokad. Traktuj to jako osobną planowaną operację konserwacyjną.

Zmiana nazwy indeksu dla jasności

Zmiana nazwy (ALTER INDEX ... RENAME TO ...) zwykle przebiega szybko, ale unikaj jej, jeśli narzędzia lub migracje odnoszą się do nazw indeksów. Bezpieczniejszym nawykiem jest wybranie jasnej nazwy na początku.

Jeśli stary indeks nadal jest potrzebny

Czasami dwa wzorce zapytań potrzebują dwóch różnych indeksów. Jeśli istotne zapytania dalej polegają na starym, zostaw go. Rozważ dopracowanie nowego indeksu (kolejność kolumn, warunek częściowy), zamiast wymuszać usunięcie.

Monitoruj blokady i postęp podczas budowy indeksu

Nawet przy CREATE INDEX CONCURRENTLY powinieneś obserwować, co się dzieje w czasie rzeczywistym. Większość niespodzianych incydentów wynika z jednego z dwóch powodów: sesji blokującej, której nie zauważyłeś, albo długotrwałej transakcji, która trzyma budowę w miejscu.

Wykrywanie sesji blokujących (kto kogo blokuje)

Zacznij od znalezienia sesji oczekujących na blokady:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

Jeśli potrzebujesz dokładnego blokującego, podążaj od blocked_pid do blocking_pid:

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Obserwuj postęp budowy i sygnały „zablokowania"

PostgreSQL udostępnia postęp budowy indeksu. Jeśli przez długi czas nie widzisz ruchu, sprawdź długą transakcję (często bezczynna sesja trzymająca stary snapshot).

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

Zwracaj też uwagę na obciążenie systemu: IO dysku, opóźnienia replikacji i rosnące czasy zapytań. Budowy concurrent są przyjaźniejsze dla dostępności, ale wciąż czytają dużo danych.

Proste zasady, które dobrze działają w produkcji:

• Czekaj, jeśli postęp się porusza, a wpływ na użytkownika jest niski.
• Anuluj i przełóż, jeśli budowa utknęła za długą transakcją, której nie możesz bezpiecznie zakończyć.
• Wstrzymaj się w czasie szczytu, jeśli IO szkodzi zapytaniom skierowanym do użytkownika.
• Przerywaj tylko jako ostateczność i dopiero po potwierdzeniu, co robi dana sesja.

Dla komunikacji w zespole trzymaj krótkie aktualizacje: czas rozpoczęcia, bieżąca faza, co jest blokowane (jeśli cokolwiek) i kiedy sprawdzisz ponownie.

Plan wycofania: jak bezpiecznie się cofnąć

Zmiany indeksów pozostają niskiego ryzyka tylko wtedy, gdy zaplanujesz wyjście przed startem. Najbezpieczniejsze wycofanie często nie jest dramatycznym cofnięciem. To po prostu zatrzymanie nowej pracy i pozostawienie starego indeksu na miejscu.

Typowe sposoby, w jakie praca nad indeksem się nie udaje

Większość awarii produkcyjnych jest przewidywalna: budowa trafia na timeout, ktoś ją anuluje podczas incydentu, serwer ma mało miejsca na dysku albo budowa konkuruje z normalnym ruchem na tyle, że opóźnienia dla użytkowników rosną.

Przy CREATE INDEX CONCURRENTLY anulowanie jest zwykle bezpieczne dla aplikacji, bo zapytania nadal działają. Kosztem jest sprzątanie: anulowana lub nieudana budowa może zostawić nieprawidłowy indeks.

Zasady bezpiecznego anulowania i sprzątania

Anulowanie budowy concurrent nie cofa się jak normalna transakcja. PostgreSQL może zostawić indeks, który istnieje, ale nie jest ważny dla planera.

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(\u003cpid\u003e);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

Zanim usuniesz, potwierdź, co widzisz:

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'your_index_name';

Jeśli indisvalid = false, nie jest używany i można go bezpiecznie usunąć.

Praktyczna checklista wycofania przy zastępowaniu istniejącego indeksu:

• Trzymaj stary indeks, dopóki nowy nie będzie w pełni zbudowany i ważny.
• Jeśli nowa budowa zawiedzie lub zostanie anulowana, usuń nieprawidłowy nowy indeks konkurencyjnie.
• Jeśli już usunąłeś stary indeks, odtwórz go przez CREATE INDEX CONCURRENTLY, aby przywrócić poprzedni stan.
• Jeśli przyczyną była presja na dysk, zwolnij miejsce, a potem spróbuj ponownie.
• Jeśli przyczyną były timeouty, zaplanuj spokojniejsze okno zamiast wymuszać próbę.

Przykład: zaczynasz nowy indeks dla wyszukiwania w panelu admina, działa 20 minut, potem alarmy dysku. Anulujesz budowę, usuwasz nieprawidłowy indeks konkuretnie i zostawiasz stary indeks obsługujący ruch. Możesz spróbować ponownie po zwolnieniu miejsca, bez widocznego dla użytkownika przestoju.

Typowe błędy, które tworzą niespodziewane przestoje

Większość przestojów związanych z indeksami nie wynika z „powolnego” PostgreSQL. Dzieje się tak, gdy jedna mała kwestia zamienia bezpieczną zmianę w blokującą.

1) Umieszczanie concurrent build wewnątrz transakcji

CREATE INDEX CONCURRENTLY nie może działać wewnątrz bloku transakcyjnego. Wiele narzędzi migracyjnych owija zmiany w jedną transakcję domyślnie. Efekt to albo twardy błąd (najlepszy przypadek), albo kłopotliwe wdrożenie z próbami ponowienia.

Przed uruchomieniem migracji potwierdź, że twoje narzędzie może wykonać polecenie bez zewnętrznej transakcji lub rozdziel migrację na specjalny krok nie-transakcyjny.

2) Uruchomienie go w czasie szczytu

Concurrent builds zmniejszają blokowanie, ale wciąż zwiększają obciążenie: dodatkowe odczyty, dodatkowe zapisy i większe obciążenie autovacuum. Rozpoczęcie budowy w oknie wdrożeniowym, gdy ruch rośnie, to częsty sposób na spowolnienie, które wygląda jak przestój.

Wybierz spokojny okres i traktuj to jak każdą inną konserwację produkcyjną.

3) Ignorowanie długotrwałych transakcji

Pojedyncza długa transakcja może powstrzymać fazę sprzątania concurrent build. Indeks może wyglądać, że się rozwija, a potem utknąć blisko końca, czekając na zniknięcie starych snapshotów.

Wytwórz nawyk: sprawdzaj długotrwałe transakcje przed startem i ponownie, jeśli postęp utknie.

4) Usunięcie niewłaściwej rzeczy (albo złamanie constraintu)

Zespoły czasem usuwają indeks z pamięci albo kasują indeks, który wspiera regułę unikalności. Jeśli usuniesz niewłaściwy obiekt, możesz stracić wymuszanie (unique constraints) lub natychmiast cofnąć wydajność zapytań.

Szybka lista bezpieczeństwa: zweryfikuj nazwę indeksu w katalogu, potwierdź, czy wspiera constraint, sprawdź schemat i tabelę, i trzymaj „utwórz nowy” oddzielnie od „usuń stary”. Miej komendę rollback gotową przed startem.

Realistyczny przykład: przyspieszenie wyszukiwania w panelu admina

Częstym problemem jest wyszukiwanie w panelu admina, które w stagingu jest natychmiastowe, a w produkcji zamula. Załóżmy, że masz dużą tabelę tickets (dziesiątki milionów wierszy) za panelem wewnętrznym, a agenci często szukają „otwartych ticketów dla jednego klienta, najnowsze pierwsze.”

Zapytanie wygląda tak:

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Pełny indeks na (customer_id, status, created_at) pomaga, ale dodaje narzut zapisu przy każdej aktualizacji ticketu, także zamkniętych. Jeśli większość wierszy nie jest open, indeks częściowy często daje prostszy zysk:

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

Bezpieczny harmonogram w produkcji:

• Preflight: potwierdź, że kształt zapytania jest stabilny i tabela ma wystarczająco wolnego miejsca na dysku na nowy indeks.
• Budowa: uruchom CREATE INDEX CONCURRENTLY w oddzielnej sesji z jasnymi ustawieniami timeout.
• Weryfikacja: uruchom ANALYZE tickets; i potwierdź, że planner używa nowego indeksu.
• Sprzątanie: gdy będziesz pewien, usuń zbędny starszy indeks przez DROP INDEX CONCURRENTLY.

Sukces wygląda tak:

• Wyszukiwanie admina spada z sekund do kilkudziesięciu milisekund dla typowych klientów.
• Normalne odczyty i zapisy działają podczas budowy.
• CPU i IO dysku rosną podczas budowy, ale pozostają w bezpiecznych granicach.
• Masz wyraźne liczby przed/po: czas zapytań, przeskanowane wiersze i historię blokad.

Szybka checklista i następne kroki

Prace nad indeksami są najbezpieczniejsze, gdy traktujesz je jak małe wydanie produkcyjne: przygotuj się, obserwuj podczas działania, a potem zweryfikuj wynik zanim posprzątasz.

Zanim zaczniesz:

• Ustaw timeouty, aby niespodziewana blokada nie wisiała wiecznie.
• Potwierdź wystarczającą ilość wolnego miejsca na dysku na budowę nowego indeksu.
• Szukaj długotrwałych transakcji, które mogłyby spowolnić budowę.
• Wybierz okres niskiego ruchu i zdefiniuj, co znaczy „skończone”.
• Zapisz teraz plan wycofania.

W czasie działania:

• Obserwuj blokady i łańcuchy oczekiwań.
• Śledź postęp budowy przez pg_stat_progress_create_index.
• Monitoruj symptomy aplikacji: wskaźnik błędów, timeouts i wolne endpointy powiązane z tabelą.
• Bądź gotów do anulowania, jeśli oczekiwania na blokady rosną lub timeouts użytkowników skaczą.
• Zaloguj, co się stało: czas startu, czas zakończenia i wszystkie alerty.

Po zakończeniu, potwierdź, że indeks jest ważny, uruchom jedno-dwa kluczowe zapytania, by sprawdzić plan i poprawę czasu, i dopiero wtedy usuń stare indeksy w sposób nieblokujący.

Jeśli robisz to częściej, zamień to w powtarzalny krok dostawczy: mały runbook, próba na stagingu z danymi podobnymi do produkcyjnych i jasny właściciel obserwujący budowę.

Jeśli budujesz narzędzia wewnętrzne lub panele admina z AppMaster (appmaster.io), warto traktować zmiany bazy danych jak budowy indeksów — jako część tej samej checklisty wydania co aktualizacje backendu: mierzone, monitorowane i z planem wycofania do szybkiego wykonania.