0Pricing
SQL Academy · Lekcja

Migracje online: dlaczego ALTER TABLE blokuje

Rozumieć, które formy ALTER TABLE uzyskują blokadę ACCESS EXCLUSIVE i przepisują tabelę, a które dotyczą wyłącznie metadanych

Migracje online: dlaczego ALTER TABLE blokuje to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 1 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.

Problem migracji w środowisku produkcyjnym

Na małej bazie danych polecenie ALTER TABLE wykonuje się natychmiast. Na działającej tabeli o rozmiarze 500 GB to samo polecenie może zablokować zapisy na 20 minut. Znajomość bezpiecznych i ryzykownych operacji ALTER jest niezbędna.

Poziomy blokad

Blokady PostgreSQL mają następujące poziomy:

  • ACCESS SHARE — odczyty
  • ROW EXCLUSIVE — zapisy
  • SHARE / SHARE ROW EXCLUSIVE — DDL współistniejący z odczytami
  • EXCLUSIVE — blokuje odczyty
  • ACCESS EXCLUSIVE — blokuje WSZYSTKO

Jakie blokady uzyskuje ALTER TABLE

Większość wariantów ALTER uzyskuje blokadę ACCESS EXCLUSIVE — blokują one odczyty i zapisy aż do zakończenia operacji.

Szybkie operacje ALTER (tylko metadane)

Niektóre operacje ALTER zmieniają tylko katalog i kończą się w ciągu milisekund nawet na ogromnych tabelach:

ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ALTER COLUMN a SET DEFAULT ...;
ALTER TABLE t ADD COLUMN x INT;             -- nullable, no default: metadata only (PG 11+)
ALTER TABLE t ADD COLUMN x INT NOT NULL DEFAULT 0;  -- metadata only PG 11+ if default is constant

Wolne operacje ALTER (przepisujące tabelę)

Te operacje przepisują całą tabelę:

ALTER TABLE t ALTER COLUMN x TYPE BIGINT;     -- when types not binary-compatible
ALTER TABLE t SET LOGGED;
CLUSTER t USING idx;                          -- physically reorders rows
VACUUM FULL t;                                -- rewrites whole table

Bezpieczne dodawanie NOT NULL

W przypadku dużej tabeli:

-- BAD: full table scan + ACCESS EXCLUSIVE lock
ALTER TABLE t ALTER COLUMN x SET NOT NULL;

-- BETTER:
ALTER TABLE t ADD CONSTRAINT x_not_null CHECK (x IS NOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT x_not_null;     -- scans without exclusive lock
-- then drop the CHECK and add NOT NULL (still cheap because already validated):
ALTER TABLE t ALTER COLUMN x SET NOT NULL;
ALTER TABLE t DROP CONSTRAINT x_not_null;

Dodawanie kluczy obcych bez przestoju

Ta sama sztuczka z NOT VALID:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

Problemy z oczekiwaniem na blokadę

Operacja ALTER oczekująca na blokadę ACCESS EXCLUSIVE ustawi się w kolejce za każdą długo trwającą transakcją. Nowsze transakcje również ustawią się za operacją ALTER — powstanie łańcuch zablokowanych zapytań.

lock_timeout

Nie należy pozwalać, aby migracje oczekiwały bez końca:

SET lock_timeout = '5s';
ALTER TABLE t ...;
-- ERROR if it can't get the lock in 5s — retry.

Pętle ponawiania prób

Migracje powinny ponawiać próbę po wystąpieniu lock_timeout:

for (let i = 0; i < 20; i++) {
  try {
    await client.query('SET lock_timeout = 5000');
    await client.query('ALTER TABLE ...');
    break;
  } catch (e) {
    if (e.code === '55P03') continue;     // lock_not_available
    throw e;
  }
}

statement_timeout dla bezpieczeństwa

Należy ograniczyć czas wykonywania każdego pojedynczego polecenia w ramach migracji:

SET statement_timeout = '30s';

Przydatne narzędzia

  • strong_migrations (Rails)
  • django-migrate-zero-downtime
  • pg-osc (Postgres Online Schema Change)
  • pgRoll

Podsumowanie

Migracje bez przestoju wymagają znajomości:

  • tego, które operacje ALTER zmieniają tylko metadane, a które przepisują tabelę
  • stosowania NOT VALID + VALIDATE w przypadku ograniczeń
  • ustawiania lock_timeout i ponawiania prób
  • unikania długo trwających transakcji blokujących migracje

Szybki test

Dodają Państwo ograniczenie NOT NULL do tabeli o rozmiarze 500 GB za pomocą jednego ALTER TABLE — co stanie się z zapisami?

Często zadawane pytania

Czy lekcja „Migracje online: dlaczego ALTER TABLE blokuje” jest bezpłatna?

Tak — pełny tekst „Migracje online: dlaczego ALTER TABLE blokuje” 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 „Migracje online: dlaczego ALTER TABLE blokuje”?

Rozumieć, które formy ALTER TABLE uzyskują blokadę ACCESS EXCLUSIVE i przepisują tabelę, a które dotyczą wyłącznie metadanych Ć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 1 z 4.

Ile czasu zajmuje lekcja „Migracje online: dlaczego ALTER TABLE blokuje”?

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

  1. Migracje online: dlaczego ALTER TABLE blokuje
  2. Indeksy współbieżne (CREATE INDEX CONCURRENTLY)
  3. Zmiana nazw kolumn bez przestoju
  4. Narzędzia: Flyway, Liquibase, Sqitch
← Powrót do SQL Academy