Partycjonowanie tabel

Jeśli tabela jest duża, to koszt jej skanowania również nie należy do niskich. Wyobraźmy sobie taką sytuację, że mamy tabelę z olbrzymią ilością faktur. Faktury dotyczą poszczególnych okresów – miesięcy bądź kwartałów. Są gromadzone od 2 dekad. Zazwyczaj na potrzeby analityczne będziemy przetwarzali dokumenty z aktualnego lub poprzedniego okresu, no dajmy na to najdalej roku. Jeśli będzie to zwykła niepartycjonowana tabela to przeszukiwana będzie cała długość tabeli bądź indeksu jeśli taki na niej utworzymy. To marnotrawstwo czasu i zasobów, bo przeszukiwanie wcześniejszych 19 lat jest całkiem bezcelowe. Co możemy zrobić? Możemy wydzielić w ramach tabeli partycje. Każda partycja będzie zawierała dane tylko z jednego okresu. Tabelę będzie można przeszukiwać jako całość, ale jeśli z warunków zapytania będzie wynikać że faktury których poszukujemy mogą znaleźć się tylko w określonych partycjach i nigdzie indziej, PostgreSQL przeszuka tylko te partycje. Taka konfiguracja jest zalecana przez dokumentację jeśli wielkość tabeli będzie większa od dostępnej pamięci lub wielkość tabeli przekroczy 100 milionów (!) wierszy. Jak to zrobić?


Podział na partycje


Zaczynamy od stworzenia tabeli :


create table some_stuff(

x integer primary key,

y integer

);


Jako partycję będą służyły osobne tabele które będą dziedziczyły konstrukcję po naszej właśnie stworzonej tabeli. W poniższym kodzie zwróć uwagę na parametr inherits i check. Inherits wskazuje po której tabeli dziedziczy tworzona tabela, a więc na której ma wzorować konstrukcję – dotyczy to ilości, nazw i typów kolumn. Klucze główne, obce ani indeksy nie są kopiowane. Parametr check określa warunek wskazujący warunki dla wierszy które się w tej partycji mają znaleźć. To na podstawie tego właśnie warunku PostgreSQL będzie określał które partycje należy przeszukiwać. Warto zadbać o to, by stworzyć partycje obejmujące cały zakres możliwych wartości.

Weź pod uwagę, że jeśli w tabeli bazowej (w tym przypadku some_stuff) już wcześniej znajdowały się jakieś dane, nie zostaną one rozdzielone na podległe tabele!


Na potrzeby niniejszego przykładu przyjąłem, że wstawiane wiersze mogą mieć wartość w kolumnie X w zakresie 1-900. Tworzę trzy podległe tabele (partycje jak kto woli) dzielące zakres na 3 części.


create table x300 (

check (x<=300 and x>0)

)

inherits(some_stuff);


create table x600 (

check (x<=600 and x>300)

)

inherits(some_stuff);


create table x900 (

check (x<=900 and x>600)

)

inherits(some_stuff);


Jeśli tabela bazowa miała klucz główny lub klucze obce, a chcemy te własności zachować i dla partycji, musimy niestety ręcznie to skonfigurować dla każdej podległej tabeli:


alter table x300 add constraint x300pk primary key(x);

alter table x600 add constraint x600pk primary key(x);

alter table x900 add constraint x900pk primary key(x);


Automatyczne rozdzielanie wstawianych wierszy


PostgreSQL domyślnie nie będzie rozdzielał wstawianych wierszy na partycje i sami musimy zadbać o odpowiedni mechanizm. Najwydajniejszym rozwiązaniem będzie zastosowanie reguł. Reguły służą do zastępowania czynności na obiektach innymi czynnościami. Można tu by było również użyć triggerów (wyzwalaczy), ale takie rozwiązanie charakteryzuje się mniejszą wydajnością. Kod z poniższego przykładu spowoduje że w zależności od zakresu wartości w kolumnie X wstawianego wiersza, wiersze będą lądowały w różnych partycjach. Niestety musimy stworzyć taką regułę dla każdej partycji:


create rule ss_insert_300 as

on insert to some_stuff where (x>0 and x<=300)

do instead

insert into x300 values (new.*);


create rule ss_insert_600 as

on insert to some_stuff where (x>300 and x<=600)

do instead

insert into x600 values (new.*);


create rule ss_insert_900 as

on insert to some_stuff where (x>600 and x<=900)

do instead

insert into x900 values (new.*);


Teraz w ramach doświadczenia wstawmy trzy wiersze o takim zakresie wartości w X, by każdy wiersz wylądował we właściwej „podtabeli”. Zwróć uwagę że inserty są wykonywane na naszej „głównej” tabeli a nie partycji. O odpowiednie rozdzielenie wierszy zadbają stworzone przed momentem reguły:


insert into some_Stuff values (10,null);

insert into some_Stuff values (310,null);

insert into some_Stuff values (610,null);


Zajrzyjmy teraz do naszej „głównej” tabeli:


select * from some_Stuff;




Widzimy wszystkie wstawiane dane pomimo że znajdują się one tak naprawdę w osobnych tabelach.


Zajrzyjmy teraz do którejś partycji osobno:


select * from x900;



Czyli dane zostały rozdzielone na właściwe „podtabele”.


Automatyczne przeszukiwanie tylko właściwych partycji


Zobaczmy teraz co się stanie jeśli odpytam naszą tabelę nadrzędną, a z warunków WHERE będzie wynikać że dane których szukam mogą być wyłącznie w jednej partycji:


explain analyze select * from some_stuff where x=10;



Jak widzimy nastąpił skan z użyciem indeksu założonego w związku z utworzeniem klucza głównego na jednej tylko partycji. Czyli PostgreSQL zajrzał tak naprawdę tylko do jednej partycji.


Uwagi do partycjonowania


Parametr constraint_exclusion


W PostgreSQL jest parametr constraint_exclusion od którego włączenia zależy czy mechanizm przeszukiwania wybranych partycji działa. Od wersji 8.4 jest on włączony domyślnie. Jeśli masz wcześniejszą wersję PostgreSQL i nie jesteś pewien czy u Ciebie jest to włączone, sprawdź to z użyciem komendy:


show constraint_exclusion;


Powinieneś dostać taki rezultat:



Automatyczne tworzenie nowych partycji


Niestety na ten moment (wersja 9.4) nie istnieje metoda na automatyczne tworzenie kolejnych partycji przez PostgreSQL. Robi się to po prostu przez automatyczne uruchamianie skryptów przez CRONa, jeśli np. mamy nowy okres rozliczeniowy a nie została jeszcze utworzona dla niego partycja.


 

Ten artykuł jest elementem poniższych kursów: