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

Partycjonowanie

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ą jednego z komponentów EuroDB do generowania danycheurodb-datagenerator.

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

ScenariuszFunkcjonalnośćCzas max [s]Czas min [s]Średnia [s]
COUNT WHERE id > 80000BRAIN
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 DESCBRAIN
PARTYCJONOWANIE
1.867
2.262
1.665
1.898
1.747
1.995
SELECT WHERE id = 800BRAIN
PARTYCJONOWANIE
1.446
0.021
1.274
0.010
1.354
0.012
SELECT WHERE id IN 10,15,20BRAIN
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. W celu zminimalizowania czasu przestojów i ułatwienia pracy administratorom firma z pomocą doświadczonych developerów stworzyła rozwiązanie EuroDB oparte o silnik PostgreSQL. Ponadto EuroDB zawiera szereg komponentów, które znacznie poszerzają jego możliwości, a dzięki swoim gigantycznym możliwościom i zachowaniu relatywnie niskiej ceny deklasuje wszystkie konkurencyjne rozwiązania.

Źródła:
https://www.postgresql.org/docs/10/static/ddl-partitioning.html
https://www.postgresql.org/docs/9.6/static/brin-intro.html

2 komentarzy dla “Porównanie możliwości PostgreSQL: indeksować czy partycjonować?

  1. Michał Lipka Odpowiedz

    Wypadałoby jeszcze opublikować explainy dla tych zapytań w obu przypadkach.
    Obstawiam, że bardzo dobre czasy dla „indeksowania” są wynikiem index only scan (jako że operujemy tylko na kolumnie na której jest index).
    może select count(1) from [table] where timestamp > …. ? Pewnie dałoby zupełnie inne wyniki

    • EuroLinux Autor wpisuOdpowiedz

      Dziękujemy za komentarz. Ma Pan rację, w testach użyto kolumny, na której był założony indeks typu BRIN — było to naturalne, ponieważ użycie innej kolumny nie pozwalałoby obiektywnie porównać wydajności obu mechanizmów.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *