PostgreSQL wprowadził deklaratywne partycjonowanie w wersji 10, aby zastąpić uciążliwy model partycjonowania oparty na dziedziczeniu. Planner zapytań przeprowadza statyczne przycinanie partycji podczas fazy planowania, porównując predykaty zapytania z granicami partycji, ale wymaga, aby wyrażenia były ocenialne do stałych w czasie planowania, aby określić, które partycje można wyeliminować.
Podstawowym ograniczeniem architektonicznym jest to, że funkcje STABLE, takie jak now() lub current_timestamp, nie są oceniane podczas planowania, ponieważ ich wyniki mogą się różnić między planowaniem a wykonaniem, a nawet w trakcie wykonywania zapytania. W związku z tym planner traktuje predykaty zawierające te funkcje jako czarne skrzynki, nie mogąc udowodnić, że niektóre partycje nie mogą zawierać pasujących wierszy, co zmusza do skanowania wszystkich partycji.
Rozwiązanie polega na przepisaniu predykatu, aby używał funkcji IMMUTABLE lub stałych literowych, lub poleganiu na przycinaniu partycji w czasie wykonania dostępnym w PostgreSQL 11 i później. Ustawiając enable_partition_pruning na on, wykonawca ocenia wyniki funkcji STABLE w odniesieniu do granic partycji w czasie wykonania, dynamicznie pomijając nieistotne partycje po początkowej fazie planowania.
Firma analityki finansowej podzieliła tabelę transakcji według TIMESTAMPTZ na kolumnie execution_time, używając dziennych zakresów do zarządzania terabajtami danych tickowych. Analitycy często zapytywali o niedawną aktywność z użyciem WHERE execution_time >= now() - interval '1 hour', ale zauważyli, że te zapytania cierpiały na katastrofalne spowolnienie wydajności, skanując wszystkie 365 dziennych partycji zamiast tylko najnowszej.
Pierwszym rozważanym podejściem było obliczenie granicy znacznika czasu przez warstwę aplikacji i wstrzyknięcie jej jako stałą literową. Umożliwiło to natychmiastowe statyczne przycinanie i zmniejszyło czas zapytania z 45 sekund do 80 milisekund. Jednakże złamało to istniejące zapytania SQL osadzone w narzędziach BI innych firm, których nie można było łatwo zmodyfikować.
Drugie podejście polegało na stworzeniu niestandardowej funkcji niemutowalnej, która zwracała stały znacznik czasu na podstawie bieżącej daty. To podejście zostało odrzucone, ponieważ mogłoby prowadzić do niepoprawnych wyników, jeśli transakcja bazy danych pozostała otwarta w czasie przejścia przez granicę północy, naruszając umowę STABLE, którą zapewnia now() w ramach transakcji. Takie naruszenie mogłoby prowadzić do cichych niepoprawnych wyników zapytań, jeśli planner przechowywał nieaktualny zakres partycji.
Wybrane rozwiązanie wykorzystało zdolności przycinania partycji w czasie wykonania dostępne w PostgreSQL 12. Administratorzy bazy danych upewnili się, że enable_partition_pruning było włączone i przekształcili aplikację, aby używała przygotowanych instrukcji, z granicą czasową przekazywaną jako parametr, zamiast konkatenowanej do ciągu SQL. Umożliwiło to wykonawcy dynamiczne przycinanie partycji za pomocą rzeczywistej wartości parametru w czasie wykonania, osiągając niemal optymalną wydajność bez konieczności wprowadzania zmian w generowaniu tekstu SQL w narzędziach raportowych używających starszych technologii.
Jak mechanizm wykluczenia ograniczeń PostgreSQL dla dziedziczonych tabel różni się od natywnego przycinania partycji i dlaczego ten pierwszy wymaga jawnej konfiguracji GUC, a drugi nie?
Wykluczenie ograniczeń było metodą partycjonowania stosowaną przed deklaratywnym partycjonowaniem, opierającą się na ograniczeniach CHECK w tabelach podrzędnych, aby udowodnić, że tabele nie mogą zawierać odpowiednich wierszy. Ponieważ ocena tych ograniczeń w odniesieniu do każdej dziedziczonej tabeli podczas planowania jest kosztowna, kiedy istnieje setki tabel, jest ona kontrolowana przez parametr constraint_exclusion, który domyślnie wynosi partition (sprawdzanie tylko podczas zapytania w oparciu o dziedziczenie). Natomiast natywne przycinanie partycji korzysta ze specjalnych struktur danych w plannerze, które bezpośrednio rozumieją hierarchię partycji, co czyni je szybszymi i zawsze włączonymi, nie wymagającymi regulacji GUC dla poprawnej funkcjonalności.
Dlaczego, aktualizując wiersz, aby przenieść go między partycjami przez modyfikację klucza partycji, PostgreSQL wewnętrznie wykonuje DELETE i INSERT, a nie aktualizację w miejscu, i jakie implikacje to ma dla wyzwalaczy?
Ponieważ każda partycja jest odrębną relacją kopca z oddzielnym magazynem fizycznym, zmiana klucza partycji wymaga przeniesienia krotki z jednego pliku do drugiego. PostgreSQL realizuje tę przejrzystość poprzez usunięcie wiersza z partycji źródłowej i wstawienie go do partycji docelowej. Oznacza to, że wyzwalacze poziomu wiersza BEFORE DELETE, AFTER DELETE, BEFORE INSERT i AFTER INSERT są uruchamiane podczas tego, co wydaje się być pojedynczą operacją UPDATE. Dodatkowo, replikacja logiczna przesyła to jako dwa oddzielne wpisy WAL (usunięcie i wstawienie), co może powodować konflikty u subskrybentów, jeśli identyfikator repliki nie jest poprawnie skonfigurowany.
Jakie konkretne przeciążenie blokowania i walidacji występuje przy dołączaniu nowej partycji do tabeli, która ma partycję DEFAULT, i jak można uniknąć pełnego skanowania partycji domyślnej?
Przy dołączaniu nowej partycji do tabeli podzielonej na zakres lub listę, zawierającej partycję DEFAULT, PostgreSQL musi przeskanować całą partycję DEFAULT, aby upewnić się, że nie istnieją wiersze, które powinny należeć do nowej, bardziej specyficznej partycji. Ta walidacja wymaga uzyskania blokady ACCESS EXCLUSIVE na partycjonowanej tabeli i może trwać godziny dla dużych partycji domyślnych. Aby tego uniknąć, należy odłączyć partycję DEFAULT przed dołączeniem nowej partycji, a następnie ponownie dołączyć partycję DEFAULT tylko po upewnieniu się, że nie istnieją konflikty, lub alternatywnie, użyć CREATE TABLE ... PARTITION OF, aby utworzyć nową pustą partycję i przenieść dane za pomocą INSERT ... SELECT z klauzulą WHERE, która filtruje dla konkretnego zakresu partycji, omijając skanowanie walidacyjne zawartości partycji domyślnej.