
Porównanie możliwości PostgreSQL: indeksować czy partycjonować?

Przetwarzanie dużej ilości danych zawsze stanowi wielopoziomowe wyzwanie. Począwszy od doboru sprzętu i oprogramowania, przez projekt struktury przechowywanych danych aż po finalną optymalizację zapytań. Poniższy artykuł porównuje wydajność dwóch funkcjonalności silnika PostgreSQL 10 – partycjonowania tabel i indeksu typu BRIN, służących między innymi do optymalizacji wyszukiwania na obszernych zbiorach danych.
Przetwarzanie dużej ilości danych zawsze stanowi wielopoziomowe wyzwanie. Począwszy od doboru sprzętu i oprogramowania, przez projekt struktury przechowywanych danych aż po finalną optymalizację zapytań. Poniższy artykuł porównuje wydajność dwóch funkcjonalności silnika PostgreSQL 10 – partycjonowania tabel i indeksu typu BRIN, służących między innymi do optymalizacji wyszukiwania na obszernych zbiorach danych.
Indeksy, partycje – co to jest?
Partycjonowanie tabel polega na podzieleniu logicznej tabeli na mniejsze, fizycznie uniezależnione od siebie elementy składowe – tabela master jest pusta, dane są przechowywane w tabelach potomnych.
Do wydania 10 PostgreSQL implementacja tego rozwiązania wymagała manualnego stworzenia triggerów BEFORE INSERT
i BEFORE UPDATE
. Najnowsza wersja wprowadza mocno ułatwiającą implementację składnię:
- na poziomie tworzenia tabeli master:
PARTITION BY {RANGE | LIST}
, - na poziomie tworzenia tabel potomnych:
PARTITION OF table_name FOR VALUES FROM (x) TO (y)
.
Indeks typu BRIN (Block Range Index) PostgreSQL wprowadza wraz z wersją 9.5. Został on zaprojektowany z myślą o bardzo dużych tabelach, w których dane w pewnych kolumnach posiadają naturalną korelację względem ich lokalizacji na dysku.
Podczas tworzenia indeksu możemy przekazać parametr pages_per_range
, który określa, dla ilu stron tabel ma być tworzony blok. Dla każdego bloku indeks przechowuje największą i najmniejszą wartość. Zatem podczas wykonywania zapytania odnoszącego się do indeksowanej kolumny, silnik zawęża zakres przeszukiwanych wartości, optymalizując czas wykonywania.
Metryka testów
No potrzeby testów powstały dwie tabele, identyczne pod względem kolumn:
CREATE TABLE t_brin ( id serial , string text , created timestamp ); -- stworzenie indeksu typu brin na kolumnie id CREATE INDEX idx_b_t_brin ON t_brin USING BRIN (id) WITH (pages_per_range = 10 );
CREATE TABLE t_partition ( id serial , string text , created timestamp ) PARTITION BY RANGE (id); -- stworzenie w bloku kodu 100 tabel potomych -- w każdej tabeli potomnej znajdzie się 100 tysięcy rekordów DO $$ DECLARE table_name text; step int := 100000; stop_value int; i int; BEGIN FOR i IN SELECT generate_series(1, 10000000, step) LOOP table_name := 't_partition_' || i::text; stop_value := i + step; EXECUTE format('CREATE TABLE %s PARTITION OF t_partition FOR VALUES FROM (%s) TO (%s)', table_name, i, stop_value); END LOOP; END; $$
Tabele zostały zasilone danymi testowymi – 10 milionami rekordów (nieposiadających wartości null), powstałymi za pomocą generatora danych.
Przygotowano pięć zapytań:
SELECT count(1) FROM [TABLE] WHERE id > 80000;
SELECT max(id) FROM [TABLE];
SELECT * FROM [TABLE] ORDER BY id DESC LIMIT 10;
SELECT * FROM [TABLE] WHERE id = 800;
SELECT * FROM [TABLE] WHERE id IN (10, 15, 20);
.
Dla każdej tabeli przeprowadzono 10 cykli, podczas których wykonano wszystkie powyższe zapytania. Po każdym cyklu następował restart serwera bazy danych. Dla testów została przygotowana maszyna wirtualna o minimalnych parametrach (2 GB RAM, HDD, 1 CPU).
Wyniki
Scenariusz | Funkcjonalność | Czas max [s] | Czas min [s] | Średnia [s] |
---|---|---|---|---|
COUNT WHERE id > 80000 | BRAIN PARTYCJONOWANIE |
1.533 1.874 |
1.313 1.668 |
1.421 1.781 |
SELECT max(id) | BRAIN PARTYCJONOWANIE |
1.303 1.457 |
1.213 1.352 |
1.247 1.418 |
SELECT ORDER BY id DESC | BRAIN PARTYCJONOWANIE |
1.867 2.262 |
1.665 1.898 |
1.747 1.995 |
SELECT WHERE id = 800 | BRAIN PARTYCJONOWANIE |
1.446 0.021 |
1.274 0.010 |
1.354 0.012 |
SELECT WHERE id IN 10,15,20 | BRAIN PARTYCJONOWANIE |
1.569 0.441 |
1.292 0.010 |
1.406 0.033 |
Wyniki trzech pierwszych testów przemawiają na rzecz indeksowania – BRIN działał szybciej o średnio 20.22%, 12.03% i 12.44%. Różnice są dosyć zbliżone zarówno przy najdłuższych, jak i najkrótszych czasach. Sytuacja zmienia się i to diametralnie przy zapytaniach pobierających pojedyncze rekordy – różnice wynoszą średnio 1.34 (99.14%) i 1.37 (97.64%) sekundy na rzecz partycjonowania, które działało w tym wypadku 116 i 42 razy szybciej. Co ciekawe, łączna średnia długość operacji przemawia za partycjonowaniem: 1.056686 kontra 1.476645 sekundy (zatem statystycznie operacje na partycjonowanej tabeli wykonują się 0 28.44% szybciej).
Podsumowanie
Naturalnie jedno, uniwersalne, dobrze działające w każdych warunkach rozwiązanie nie istnieje.
Indeksy można stworzyć każdym etapie życia bazy, tymczasem decyzję o tym, że tabela będzie partycjonowana, powinniśmy podjąć w zasadzie na etapie projektowania struktury (zapytania odnoszące się do kolumny, która nie jest objęta partycjonowaniem, powoduje sekwencyjny skan na wszystkich tabelach potomnych).
Partycjonowanie posiada jednak przewagę względem indeksu BRIN – ułatwia nie tylko przeszukiwanie dużych zbiorów danych, ale także ich archiwizację. W bardzo prosty sposób, niestanowiący większego obciążenia dla serwera, możemy usunąć sporą ilość danych z produkcyjnej bazy (nie narażając się na ryzyko „spuchnięcia” tabeli) – po prostu usuwając konkretną tabelkę potomną. Stąd też w opinii autora odpowiedź na pytanie postawione w tytule brzmi: „jednak partycjonować”.
Warto również zaznaczyć, iż EuroLinux specjalizuje się w dostarczaniu i utrzymywaniu środowisk bazodanowych opartych o bazę PostgreSQL. Optymalizujemy, naprawiamy i modernizujemy bazy danych dla naszych klientów. Jeśli potrzebujesz tego rodzaju usług, pozwól nam o tym wiedzieć.
Źródła:
https://www.postgresql.org/docs/10/static/ddl-partitioning.html
https://www.postgresql.org/docs/9.6/static/brin-intro.html