Podczas dodawania NOT NULLkolumn z DEFAULTwartością - czy PostgreSQL optymalizuje tę operację?

W przypadku, gdy tabela ma n wierszy, niezoptymalizowana kolumna alter-table-add-kolumna dałaby n zapisów wartości domyślnej - co oczywiście może być bardzo bolesne. Dzięki optymalizacji DB utworzy natychmiast nową kolumnę, zapisze tylko jedną kopię wartości domyślnej, która zostanie zwrócona, gdy w odpowiedniej strukturze danych indeksu nie zostanie znaleziona wartość inna niż domyślna dla tej kolumny.

Na przykład Oracle 11g ma taką optymalizację .

W PostgreSQL nie ma takiego mechanizmu.

Nadal jednak można uniknąć nadmiernych skutków takiej zmiany tabeli.

Następująca instrukcja nabywa blokadę dostępu na wyłączność dla tabeli na czas trwania wyciągu / transakcji:

ALTER TABLE your_table
    ADD COLUMN new_column integer NOT NULL DEFAULT 0;

Ta instrukcja zmienia katalog, a następnie przepisuje całą tabelę, tak aby nowa kolumna zawierała wartość domyślną we wszystkich wierszach. Jeśli tabela ma wiele wierszy i jest dostępna często, może to spowodować przejściowe problemy.

Aby tego uniknąć, spróbuj przytrzymać wyłączną blokadę tak krótko, jak to możliwe:

ALTER TABLE your_table
    ADD COLUMN new_column integer;
ALTER TABLE your_table
    ALTER COLUMN new_column SET DEFAULT 0;

Ponieważ jest to w zasadzie tylko (właściwie dwie) zmiana katalogu (nie następuje zmiana danych), zostanie ona wykonana dość szybko. Następnie, w zależności od potrzeb i użycia tabeli, możesz zaktualizować nową kolumnę do domyślnej w jednym kroku lub partiach, a po zakończeniu ustaw kolumnę na NOT NULL.

Aktualizacja o spełnieniu się życzenia: PostgreSQL 11 będzie miał tę funkcję. Zobacz https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/, aby uzyskać więcej.

Tak, z PostgreSQL 11

Ta funkcja jest nowa i została wprowadzona w wersji 11.

ALTER TABLE your_table
    ADD COLUMN new_column integer NOT NULL DEFAULT 0;

Powyższe jest jednym z takich poleceń, na które ta optymalizacja będzie miała wpływ; ale należy powiedzieć, że nieNOT NULL jest to wymagane. Każda nowa kolumna dodana z wartością inną niż null jest teraz zoptymalizowana. Możesz znaleźć wpis w tym konkursie. Powinieneś także sprawdzić ten wspaniały artykuł na jego temat: „Brakujący link w Postgres 11: Szybkie tworzenie kolumn z ustawieniami domyślnymi” .

Obejście przed PostgreSQL 11

Jeśli próbujesz uniknąć wyłącznej blokady stołu na stole, skorzystaj z porady Craiga Ringera,

• Dodaj kolumnę bez DEFAULT
• ALTERaby dodać DEFAULTpóźniej, stosuje się do nowo wstawionych wierszy
• Następnie wypełnić nową kolumnę na istniejących wierszy przez postępowych partii UPDATEs
• Gdy wszystkie wiersze mają wartość, dodajesz NOT NULLograniczenie