PostgreSQL posiada narzędzia do pracy z dużymi zbiorami danych i zarządzania nimi. Możliwe jest dzielenie dużych tabel na części z bardziej zarządzalnymi rozmiarami zwanymi partycjami. Każda taka partycja zawiera określony podzbiór danych.
Dane w konkretnej partycji bazują na kluczu partycjonowania tabeli, który jest kolumną określającą logiczną partycję.
Klucz partycjonowania określony jest poprzez wybraną metodę dzielenia jako:
- zakresy wartości – range
- listę wartości – list
- haszowanie indywidualnych wartości kluczy i przypisania wynikowych wartości do pakietów (buckets) – hash
Głównym celem takiego podziału jest zwiększenie wydajności zapytań, ale to nie jedyne zalety.
Najbardziej użyteczną zaletą jest możliwość dodawania i usuwania całych podzbiorów danych – z użyciem poleceń DDL (attach, detach) zamiast kosztownych operacji DML (delete).
Czynności związane z zarządzaniem danymi operują na pojedynczych partycjach zamiast na całych tabelach. Dzięki temu skala operacji do wykonania jest mniejsza i działają szybciej.
Lepsze jest również zarządzanie buforami pamięciowymi podczas wykonywania zapytań z rodzaju OLTP, z uwagi na selekcję tylko tych partycji, które są potrzebne dla zapytań i tylko te są ładowane do buforów. Wydajne jest również procesowanie złożonych analitycznych zapytań poprzez używanie przez złączenia i funkcje agregujące pojedynczych partycji.
Użycie
Od wersji PostgreSQL v8.1 do implementacji partycjonowania i zarządzania możliwe jest użycie rozszerzenia zwanego constraint exclusion. Rozszerzenie jest używane przez optymalizator. W tej wersji używa się dziedziczenia tabeli do ustawienia hierarchii tabel. Ograniczenie CHECK na tabeli podrzędnej opisuje jej zakres partycjonowania, a trigger na tabeli głównej usprawnia dystrybucję danych w ramach partycji.
Przykład definicji tabeli partycjonowanej z ograniczeniami i triggerem:
CREATE TABLE traffic (
bts_id int not null,
check_date date not null,
headsets int
);
CREATE TABLE traffic_2020_01 (
CHECK (
check_date >= ‘2020-01-01'
AND check_date < ‘2020-02-01’
)
)
INHERITS (traffic);
CREATE TABLE traffic_2020_12 (
CHECK (
check_date >= ‘2020-12-01'
AND check_date < ‘2021-01-01’
)
)
INHERITS (traffic);
CREATE TRIGGER insert_traffic_trig
BEFORE INSERT ON traffic
FOR EACH ROW EXECUTE PROCEDURE traffic_insert_trig();
CREATE OR REPLACE FUNCTION traffic_insert_trig()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.check_date >= DATE ‘2020-01-01’
AND NEW.check_date < DATE ‘2020-02-01’
) THEN INSERT INTO traffic_2020_01 VALUES (NEW.*);
...
ELSIF ( NEW.check_date >= DATE ‘2020-12-01’
AND NEW.check_date < DATE ‘2021-01-01’
) THEN INSERT INTO traffic_2020_12 VALUES (NEW.*);
ELSE
RAISE EXCEPTION ‘check_date out of range’;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;W wersji PostgreSQL 10 została wprowadzona ulepszona składnia. Wprowadzone zostało również partycjonowanie z użyciem RANGE i LIST. W kolejnej wersji (PostgreSQL 11) dołożone zostało partycjonowanie HASH, z wielopoziomowym partycjonowaniem i partycją domyślną – DEFAULT
CREATE TABLE traffic (
bts_id int not null,
techn_type text not null,
check_date date not null,
headsets int
) PARTITION BY RANGE (check_date);
CREATE TABLE traffic_2020_01
PARTITION OF traffic
FOR VALUES FROM (‘2020-01-01’) TO (‘2020-02-01’);
…
CREATE TABLE traffic_2020_12
PARTITION OF traffic
FOR VALUES FROM (‘2020-12-01’) TO (‘2021-01-01’)
PARTITION BY LIST ( techn_type);
CREATE TABLE traffic_2020_12_2G
PARTITION OF traffic_2020_12
FOR VALUES IN (‘GSM’);
CREATE TABLE traffic_2020_12_3G
PARTITION OF traffic_2020_12
FOR VALUES IN (‘UMTS’);
CREATE TABLE traffic_2020_12_4G
PARTITION OF traffic_2020_12
FOR VALUES IN (‘LTE’);
CREATE TABLE traffic_2020_12_default
PARTITION OF traffic_2020_12
DEFAULT;
CREATE TABLE traffic (
bts_id int not null,
techn_type text not null,
check_date date not null,
headsets int
) PARTITION BY HASH (techn_type);
CREATE TABLE traffic_0
PARTITION OF traffic
FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE traffic_1
PARTITION OF traffic
FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE traffic_2
PARTITION OF traffic
FOR VALUES WITH (MODULUS 3, REMAINDER 2);
CREATE TABLE traffic (
bts_id int not null,
techn_type text not null,
check_date date not null,
headsets int
) PARTITION BY RANGE (bts_id, check_date);
CREATE TABLE traffic_1000_2020_01
PARTITION OF traffic
FOR VALUES FROM (1,‘2020-01-01’) TO (1000,‘2020-02-01’);
CREATE TABLE traffic_2000_2020_01
PARTITION OF traffic
FOR VALUES FROM (1000,‘2020-01-01’) TO (2000,‘2020-02-01’);
…
CREATE TABLE traffic_2020_12
PARTITION OF traffic
FOR VALUES FROM (‘2020-12-01’) TO (‘2021-01-01’)
PARTITION BY LIST ( techn_type);
Przykłady dołączania i odłączania partycji do tabeli - roll into i rollout:
ALTER TABLE traffic
ATTACH PARTITION traffic_2021_01
FOR VALUES FROM (‘2021-01-01’) TO (‘2020-02-01’);
ALTER TABLE traffic
DETACH PARTITION traffic_2020_01;Użycie indeksów
Indeksy na tabelach partycjonowanych są również możliwe do użycia. Dzięki użyciu indeksów można wymusić ograniczenia takie jak na przykład unikalność, jak również poprawić wydajność zapytań. Indeksy na tabelach partycjonowanych mogą być definiowane zarówno na całej tabeli, jak i na pojedynczej partycji. W przypadku użycia unikalnego indeksu na całej tabeli, w składzie indeksu musi się zawrzeć kolumna będąca kluczem partycjonowania.
Użycie ograniczeń – check
Ograniczenia mogą być też definiowane na poziomie całej tabeli, jak i pojedynczej partycji. Nie wszystkie typy ograniczeń mogą być zastosowane – EXCLUSION nie jest dostępne, a ograniczenie na obce klucze (foreign keys) jest dostępne dopiero od wersji 12 PostgreSQL.
Użycie triggerów
Triggery mogą być definiowane na poszczególnych partycjach.
Dla tego typu tabel są dostępne triggery:
- Statement-level – od wersji 10,
- Row-level after – od wersji 11
- Row-level before – od wersji 13
Replikowanie
Replikacja tabel partycjonowanych jest możliwa dopiero w wersji 13 – domyślnie replikowane są pojedyncze zmiany na pojedynczych partycjach. Jest możliwe również replikowanie poprzez tabelę nadrzędną poprzez ustawienie parametru publish_via_partition_root.
Zapytania na tabelach partycjonowanych
Optymalizacja zapytań wynika głównie z tzw. partition pruning czy omijania podczas skanowania całych partycji nie zawierających danych będących w zainteresowaniu zapytania. Ten algorytm uległ optymalizacji wraz z wersją 11 i umożliwia również działanie, gdy planer nie ma dość informacji, aby taką operację wykonać, np. w złączeniach z zagnieżdżoną pętlą, czy inteligentnym łączeniu między partycjami tabel.
Szybkość wykonywania zapytań również może być zwiększona dzięki wykonywaniu zapytania w równoległych procesach operujących na partycjach tabeli.
Agregacje mogą być wykonywane częściowo na pojedynczych partycjach, a następnie łączone, aby uzyskać ostateczny wynik zapytania – aby z tego skorzystać, należy włączyć parametr enable_partitionwise_aggregate.
Jeśli grupowanie jest zgodne z kluczem partycjonowania, to grupowanie jest realizowane na poziomie partycji. Od wersji 13 podczas zapytania z order by na tabeli partycjonowanej z użyciem zakresu (range) nie jest już wykonywany krok sortowania.
Najlepsze praktyki, jeśli chodzi o użycie partycjonowania
Przy wyborze klucza partycjonowania należy kierować się następującymi założeniami:
- wykorzystanie wzorca do kasowania danych – poprzez usuwanie partycji zamiast operacji delete
- wydajności zapytań – wybór najbardziej występującego klucza w klauzuli where zapytania
Wybór właściwej metody partycjonowania (range, list, hash) jest zależny od typu danych w kluczu.
Dla serii dat czy innego typu gdzie występuje pewnego rodzaju naturalny porządek danych, najlepszą metodą będzie range. Wskazuje na to używanie w warunkach takich operatorów jak <,<=,>,>=
Jeśli klucz zawiera kilka powtarzających się wartości, najlepszym wyborem będzie użycie metody list. Najpopularniejszym operatorem w klauzuli where zapytania dla tego typu metody jest =.
Ostatecznie, kiedy powyższe metody nie mają uzasadnienia, należy użyć metody hash. Umożliwi ona sensowne rozłożenie danych w ramach partycji.
Liczba partycji powinna być starannie dobrana. Nie powinna być zbyt mała – kilka, ale zbyt dużych partycji nie spełni swojej roli. Zbyt duża liczba partycji – w przypadku zapytania bez możliwości użycia eliminacji partycji czas wykonania znacząco wzrośnie. Maksymalna liczba partycji powinna oscylować w granicach kilku tysięcy lepiej jednak aby nie przekraczać ilości kilkuset.
Materiał został przygotowany na podstawie wykładu “Table Partitioning in Postgres – How far we’ve come” prezentowanego podczas PostgreSQL Vision 2020.
