Cílem tohoto článku je demonstrovat tři různé způsoby automatické správy partitions jedné tabulky, shrnout rozdíly mezi nimi, jejich výhody a nevýhody, a situace pro které se hodí.

Řekněme že chceme rozdělit tabulku s touto (velmi jednoduchou) strukturou

CREATE TABLE table_base (
    id      SERIAL PRIMARY KEY,
    created TIMESTAMP DEFAULT now() NOT NULL,
    data    TEXT
);

do které jsou v průběhu času zapisována další a další data. Typickým příkladem jsou tabulky pro záznamy o návštěvnosti webové aplikace, tabulky pro logování, apod. Cílem je rozdělit tabulku tak aby byla data zapisována vždy jen do nejnovější partition, což je výhodné s ohledem na zálohování, obnovu a odstraňování (což se na úrovni celých partitions dělá jednodušeji a efektivněji).

Takovou tabulku lze dělit buď dle umělého primárního klíče (tj. neustále rostoucí hodnoty ve sloupci "id" načítané ze sekvence), nebo dle časové hodnoty ve sloupci "created." V prvním případě je možné určit velikost jednotlivých partitions co se počtu řádek týká (např. mohou být "stejně velké" tj. obsahovat stejný počet řádek), ve druhém je možné vytvářet partitions pokrývající určité časové období (např. den, týden nebo měsíc). Je zřejmé že volba sloupce dle kterého je partitioning prováděn hraje při partitioningu i dále v procesu automatizace důležitou roli.

Důležitou roli hraje rovněž způsob partitioningu - zda je "přesměrování" dat ze základní tabulky do jednotlivých partitions prováděno pomocí triggerů (ať již s dynamickými inserty nebo bez nich), nebo pomocí rules - podrobnější popis těchto tří způsobů s příklady najdete například v článku srovnávajícím výkon různých způsobů partitioningu, nicméně uveďme zde alespoň stručnou definici:

• rules - záznamy jsou do partitions přesměrovávány pomocí rules vytvořených na základní tabulce (pro každou partition jedno samostatné pravidlo)
• triggery bez dynamických SQL dotazů - záznamy jsou do partitions přesměrovávány pomocí triggeru, v jehož těle je pro každou partition uveden samostatný INSERT (v IF podmínce)
• triggery s dynamickými SQL dotazy - záznamy jsou do do partitions přesměrovávány pomocí triggeru, v jehož těle je INSERT realizován pomocí dynamicky složeného dotazu, následně spuštěného přes EXECUTE (tj. příslušná partition je odvozena z dat řádku)

Tento článek se však nezabýbá partitioningem jako takovým, ale automatizací procesu vytváření / rušení partitions. Podívejme se co vše vlastně vytvoření nové (a případně zrušení staré) partition pro jednotlivé způsoby partitioningu obnáší, a otázky které jsou s tím spojeny:

• vytvoření nové partition (tabulky)
• vytvoření rule pro novou partition resp. úprava triggeru tak aby do nové partition vkládal
• odstranění rule pro starou partition resp. úprava triggeru tak aby do staré partition nevkládal
• odstranění staré partition (tabulky)

Vytvoření nové partition

Zaprvé je třeba správné načasování kdy má být nová partition vytvořena - možnosti jsou v zásadě dvě

• až když je skutečně potřeba (je do ní vložen první záznam)
• s předstihem (např. zbývá posledních 1000 ID v poslední partition, případně časový interval platnosti poslední partition skončí za 10 minut, apod.)

S tím samozřejmě souvisí i načasování rušení starých partition - viz. dále.

Vytváření rule resp. úprava triggeru

V případě vytvoření nové partition je třeba odpovídajícím způsobem modifikovat trigger provádějící partitioning resp. vytvořit pro novou partition rule, tak aby data vkládala i do nově vytvořené partition. Tento krok se pochopitelně liší dle způsobu partitioningu, konkrétně:

• rules - nutno vytvořit nové pravidlo
• trigger bez dynamických SQL dotazů - nutno doplnit INSERT do nové partition
• trigger s dynamickými SQL dotazy - není nutno upravovat

Odstraňování partitions

První otázkou je stejně jako v případě vytváření partitions načasování - není nic jednoduššího než partitions odstraňovat v okamžiku kdy je vytvářena nová.

Cílem je totiž držet v systému jen ta data která jsou skutečně potřeba, což např. v případě hrubých dat o návštěvnosti nebude více než několik málo měsíců (po zpracování a vygenerování reportů už hrubá data většinou nejsou potřeba). To znamená že je třeba rozhodnout kolik partitions chcete v systému držet, a při vytvoření každé nové partition zrušit i jednu starou (tj. nejstarší). V ukázkové implementaci jsou vždy drženy pouze dvě partitions, ale počet je pochopitelně na vás.

Jak bylo uvedeno v článku o výkonu různých způsobů partitioningu, výkon partitioningu je nepřímo úměrný počtu aktivních partitions tj. počtu partitions pro která jsou definována pravidla resp. partitions uvedených v triggeru. Je tedy žádoucí nevyužívaná pravidla odstraňovat. ať již z rules nebo z triggerů:

• rules - nutno odstranit staré (neaktivní) pravidlo
• trigger bez dynamických SQL dotazů - nutno odstranit INSERT do staré partition
• trigger s dynamickými SQL dotazy - není nutno upravovat

Způsoby automatizace

Otázkou zůstává jak správu partitions automaticky spouštět. Existují dva základní způsoby:

1. pomocí triggeru, který před vložením každého řádku zkontroluje zda už nastala "správná chvíle" pro vytvoření nové resp. odstranění staré partition, a pokud ano tak ji vytvoří resp. zruší
2. pomocí cronu, tj. příkazu pravidelně spouštěného cronem (resp. jakkoliv jinak automaticky pravidelně spouštěným skriptem).

Mohlo by se zdát že oba způsoby automatizace jsou ekvivalentní, ale není to pravda - na rozdíl od cronového skriptu má totiž trigger informaci o ID každého vkládaného řádku. Podívejme se na výhody a nevýhody které z toho plynou.

Cron skript

Řešení využívající cronu je založeno na pravidelném spouštění (shellového) skriptu, který kontroluje zda je třeba vytvořit novou partition, a pokud ano tak ji (stejně jako trigger popsaný v následujícím odstavci) vytvoří, a případně odstraní starou, upraví příslušné rules a triggery, atd.

Z toho plynou následující výhody a nevýhody cronového řešení:

• (+) ideální pro partitioning dle času
• (+) není s ním spojen overhead jako s triggerem (při vkládání každého nového řádku)
• (-) nehodí se pro partitioning dle ID - pokud tabulka roste rychleji než se čekalo, může se poslední partition zaplnit dříve než je vytvořena ta následující

Trigger

Řešení přes trigger je založeno na vytvoření row-level "before insert" triggeru který před vložením každého nového řádku provede víceméně to samé cronový skript, tj. zkontroluje zda je potřeba vytvořit novou partition, provede všechny potřebné úpravy triggerů a rules, atd.

Podívejme se na výhody a nevýhody tohoto řešení:

• (+) čistě databázové řešení
• (+) ideální pro partitioning dle ID, tj. chceme-li získat partitions o stejném počtu řádek
• (-) nehodí se pro partitioning dle času, tj. dělíme-li tabulku na části pokrývající stejně velké časové úseky (den, týden, měsíc, ...) - pro tento účel je vhodnější řešení využívající cron (řešení přes trigger spouštěný pro každý nový řádek představuje zbytečný overhead)

Automatizace přes cron

Nejdříve se podívejme na automatizaci pomocí cronu, protože je technicky poněkud jednodušší než automatizace pomocí triggeru. Jako první vyřešíme automatizaci partitioningu dle času, pro kterou je cron koncepčně vhodnější, a poté se podíváme na partitioning dle ID a problémy s ním spojené.

I když se jedná o řešení založené na cronu, vlastní logika samozřejmě může být implementována v PL/pgSQL proceduře, spouštěné pomocí cronu. Ta je ve všech čtyřech případech (partitioning dle času a dle ID, realizovaný pomocí triggeru i rules) pojmenována "manage_partitions" a v cronu tedy stačí provést například něco jako

0 0 10 * * echo "SELECT manage_partitions();" | psql my_database

což každého desátého dne měsíce spustí proceduru "manage_partitions."

Partitioning dle času

Předpokládejme že tabulku uvedenou v úvodu textu chceme dělit dle času, například po měsících. V tom případě je třeba někdy v průběhu měsíce (např. v polovině každého měsíce) vytvořit partition pro následující měsíc a případně dropnout příliš staré partitions (například z předchozího měsíce).

Procedura tedy v případě partitioningu bude sestávat z těchto kroků:

• vytvoří základní tabulku "table_base" která bude sloužit jako základ pro jednotlivé partitions (a nebudou do ní vkládána žádná data)
• vytvoří funkci "manage_partitions" vykonávající následující činnosti
  
  1. zruší partiton za předchozí měsíc (pokud existuje)
  2. vytvoří partition pro tento měsíc (pokud neexistuje) - uplatní se v podstatě použe při inicializaci během prvního spuštění, při dalších spuštěních už by partition pro daný měsíc vždy měla existovat
  3. vytvoří partition pro následující měsíc (pokud neexistuje)
  4. upraví trigger / rules tak aby počítaly se všemi existujícími partitions (pro aktuální a následující měsíc)

• je spuštěna funkce "manage_partitions" čímž je partitioning inicializován (vytvoří výchozí partitions pro aktuální a následující měsíc, a odpovídající trigger nebo rules)

Ukázkovou implementaci pro rules si můžete stáhnout zde a pro triggery zde.

Partitioning dle ID

V případě partitioningu dle ID je situace poněkud komplikovanější - na rozdíl od partitioningu dle času totiž není jasné kdy vytvořené partitions přestanou "stačit." Tj. zatímco u partitioningu dle času je zřejmé že partition platí např. do konce měsíce, u partitioningu dle ID víme že partition platí pro ID mezi 1 a 100000, ale nevíme kdy přesně se vloží záznam s ID 100000 a bude potřeba nová partition.

Lze samozřejmě odhadovat kdy bude další partition potřeba, ale žádné spolehlivé řešení v případě cronu neexistuje. Z tohoto důvodu je dobré partitions vytvářet s dostatečným předstihem - proto je do procedury zabudována kontrola kolik volných ID ještě v poslední partition "zbývá," a pokud jich je méně než zvolený limit tak dojde k vytvoření nové partition.

Pokud si opět shrneme hlavní kroky procedury "manage_partitions" resp. celého skriptu:

• vytvoří základní tabulku "table_base" která bude sloužit jako základ pro jednotlivé partitions (a nebudou do ní vkládána žádná data)
• vytvoří funkci "manage_partitions" vykonávající činnosti
  
  1. zjistí zda se jedná o první spuštění (žádná data ještě nebyla vložena), pokud ano tak vykoná inicializaci (vytvoří první partition apod.)
  2. zjistí kolik "volných" ID ještě v aktuální partition zbývá, a pokud jich je více než daný počet skočí až na krok 5, jinak se pokračuje dál krokem 3
  3. vytvoří novou partition (pokud neexistuje)
  4. zruší příliš starou partition (pokud existuje)
  5. trigger / rules jsou upraveny tak aby se data vkládala do existujících partitions

• spustí funkci "manage_partitions" čímž vykoná inicializaci (vytvoří výchozí partition a odpovídající trigger nebo rule)

Ukázkovou implementaci pro rules si můžete stáhnout zde a pro triggery zde.

Automatizace přes trigger

Ve srovnání s cronem má řešení založené na triggeru několik velmi zajímavých vlastností, zejména díky tomu že trigger má informaci o každém novém vkládaném řádku (což cron pochopitelně nemá). Nic samozřejmě není bez problémů, takže s triggerem jsou spojeny i některé problémy (viz. dále).

Automatizace přes trigger je založena na triggeru který za daných podmínek vytvoří novou partition a provede další potřebné operace (úprava triggeru, rules, apod.). Příkladem podmínek může být například:

• aktuální datum je blíže než týden od konce platnosti poslední vytvořené partition
• aktuální datum již překročilo konec poslední vytvořené partition (tj. do žádné partition nepasuje)
• ID vkládaného řádku je blíže než 1000 od konce poslední vytvořené partition
• ID vkládaného řádku překročilo konec poslední vytvořené partition (tj. do žádné partition nepasuje)

Díky tomu že triggerem projde každý nový řádek nehrozí v případě partitioningu dle ID obdobné problémy jako v případě cronového řešení (vyčerpání místa v partition), a rozdíl mezi partitioningem dle ID a dle času se tak v případě řešení přes trigger do jisté míry smývá.

Na druhou stranu však využití triggeru přináší rozdíly mezi partitioningem realizovaným pomocí rules a partitioningem realizovaným pomocí triggeru.

Podívejme se jaké kroky musí trigger provádějící správu partitions provádět:

• kontrola zda je splněna podmínka pro vytvoření nové partition (příklady viz. výše)
• pokud je podmínka splněna, jsou provedeny následující kroky (víceméně shodné jako v případě cronového řešení):
  1. vytvoří novou partition (pokud neexistuje)
  2. zruší příliš starou partition (pokud existuje)
  3. trigger / rules jsou upraveny tak aby vkládaly data do existujících partitions
• inicializace (vytvoření první partition a odpovídajícího triggeru / rule)

V podstatě se dá říci že jediné co je v triggeru navíc oproti proceduře spouštěné z cronu, je právě úvodní kontrola podmínky zda má být vytvořena nová partition (i když v případě partitioningu dle ID je podmínka kontrolována i v cronu).

Využití triggeru s sebou ale přináší také několik (na první pohled méně zřejmých) problémů, které u cronu z principu nenastávají. Podívejme se na tři hlavní.

Race conditions

První potíž souvisí s tím že zatímco cron je vždy spuštěn pouze jednou (tj. vyloučíme-li různé divoké scénáře, nebude procedura spuštěna dvakrát současně), trigger může být spuštěn dvakrát současně (ve dvou různých sessions). To ale pochopitelně může vést k race conditions - stačí si představit následující scénář:

krok	session A	session B
1.	kontrola zda je splněna podmínka pro vytvoření nové partition (ano)
2.		kontrola zda je splněna podmínka pro vytvoření nové partition (ano)
3.	kontrola zda nová partition ještě neexistuje (neexistuje)
4.		kontrola zda nová partition ještě neexistuje (neexistuje)
5.	vytvoření nové partiton (úspěšně)
6.		vytvoření nové partiton (chyba, partition již existuje - byla vytvořena v kroku 5)

Je tedy třeba použít na vhodných místech mutex (zámek), ale takovým způsobem aby pokud možno nebyl negativně ovlivněn výkon. Proto je zamykání prováděno až po kontrole úvodní podmínky - ovlivní tedy pouze inserty spuštěné v době mezi spuštěním prvního insertu splňujícího danou podmínku, a jeho dokončením (tj. řekněme řádově během vteřiny).

Partitioning pomocí rules

Druhý problém nastává v případě použití partitioningu realizovaného pomocí rules. Podívejme se totiž na pořadí ve kterém rules a triggery během vyhodnocování dotazu přichází ke slovu:

1. dotaz je naparsován a je vygenerován "query tree" (což není plán vyhodnocení ale syntaktický strom reprezentující naparsovaný dotaz, viz. například zde a podrobněji zde)
2. na "query tree" získaný v předchozím kroku jsou aplikována rules (vlastně přepisovací pravidla), jejichž výsledkem je několik nových stromů (v podstatě pro každé pravidlo jeden)
3. tyto jednotlivé stromy jsou vyhodnoceny (je pro ně připraven execution plan a je spuštěn, přičemž součástí stromu je i WHERE podmínka u rule - a pokud je splněna je provedena vlastní operace uvedená v rule za klíčovým slovem "DO" - v případě partitioningu se jedná o INSERT do jiné tabulky)
4. během provádění operace v každém stromě jsou spuštěny případné odpovídající triggery (definované na dané partition)

To ale znamená že z triggeru není možno vytvořením nových rules ovlivnit již běžící dotaz, protože triggery se spouští až v kroku (4), zatímco rules se aplikují již v kroku (2). Pokud je tedy partitioning realizován pomocí rules, není možné trigger "pověsit" na základní tabulku (table_base), protože ten by se spustil až v okamžiku kdy by se do ní skutečně vkládal nový záznam, tj. v případě že pro záznam neexistuje odpovídající partition. Nově vytvořené pravidlo (rule) by se na něj ale již neaplikovalo, a řádek by se tak skutečně vložil do základní tabulky, což je pochopitelně nežádoucí.

Jediným řešením je nedefinovat trigger přímo na základní tabulce, ale vždy na poslední vytvořené partition (což sebou ale nese problém v případě partitioningu dle času, pokud se do poslední partition nevloží žádný záznam vyhovující podmínce pro vytvoření nové partition).

Příklad automatizace partitioningu pomocí triggerů dle id a dle času.

Partitioning pomocí triggerů

Třetí problém nastává v případě že je partitioning realizován pomocí triggeru. V tomto případě sice nenastává problém s různými fázemi zpracování dotazu (aplikace rules vs. spuštění triggeru), popsaný v předchozím odstavci. Je ale třeba si dávat pozor na pořadí těchto triggerů (triggeru pro management partitions a triggeru provádějícího partitioning). Jako první je vždy třeba spouštět trigger provádějící management partition, a až po něm trigger provádějící partitioning jako takový (přesměrovávající záznamy do jednotlivých partitions).

Pokud by totiž triggery byly spuštěny v opačném pořadí, došlo by víceméně k podobnému problému jako v případě rules (trigger by bylo nutno "pověsit" na poslední partition a definice nové partition by se projevila až pro následující inserty).

Uvažujme že je vkládán záznam který by měl spustit vytvoření nové partition, ale že se triggery spustí v opačném pořadí, tj.

1. partitioning trigger - přesměruje záznam do příslušné partition
2. management trigger - vytvoří novou partition, aktualizuje partitioning trigger kvůli nové partition

Problém je že partitioning trigger v okamžiku spuštění ještě neví o nové partition do které nový záznam patří (protože daná partition ještě neexistuje). Vložení záznamu pro první záznam patřící do nové, ještě neexistující, partition buď rovnou skončí chybou (pokud partitioning trigger usoudí že  do základní tabulky se data vkládat nesmí), nebo se spustí ale změny které provede (definice nové partition, úprava partitioning triggeru) se už nebudou moci projevit a záznam se tak vloží do základní tabulky.

Řešením je zajistit spuštění triggerů v opačném pořadí, tj.

1. management trigger - vytvoří novou partition, aktualizuje partitioning trigger, ...
2. partitioning trigger - provede přesměrování záznamu do nové partition (již o ní ví, protože se spustil až po management triggeru)

To ale není obtížné zajistit, protože PostgreSQL spouští triggery na dané tabulce v pořadí dle jejich jména. Triggery tedy stačí vhodně pojmenovat (v ukázkových skriptech jsou použity názvy "manage_partitions" a "partition_rows").

Za zmínku také stojí že právě možnost měnit v rámci prvního triggeru (management trigger) definici druhého (partitioning trigger) umožňuje vynechání "bezpečnostního limitu" před koncem intervalu platnosti dané partition, nutného v případě použití rules. Nová partition tak může být vytvořena skutečně až v okamžiku kdy je potřeba (je vložen první záznam který do ní patří).

Příklad automatizace partitioningu pomocí triggerů dle id a dle času.

Poznámky na konec

A na závěr několik drobných poznámek ...

Automatické rušení partitions

V příkladech je kromě vytváření nových partitions prováděno i odstraňování "starých" partitions, což v produkčním prostředí nemusí být úplně dobré protože to může být příčinou ztráty dat - zejména v případě partitioningu dle ID. Může se totiž stát že mezi zálohami dojde k vytvoření tolika nových partitions že automaticky "dropnete" i ještě nezazálohované partitions.

Proto si vždy dobře rozmyslete kolik partitions chcete zálohovat, a raději v systému ponechte více partitions (byť je třeba odstraníte z IF podmínky v triggeru, nebo zrušíte příslušné rule).

Dostatečně časté vkládání záznamů v chronologickém pořadí

Všechny příklady víceméně předpokládají vkládání záznamů v chronologickém pořadí a v dostatečně "malé vzdálenosti," tj. předpokládá se že hodnoty ve sloupcích "created" a "id" budou růst, a že mezi jednotlivými řádky nebudou velké rozdíly.

Například pokud budete provádět partitioning po měsících, a mezi vložením řádků uplyne více než měsíc, je klidně možné že "přestřelíte" konec poslední vytvořené partition, což pochopitelně skončí chybou.

Odkazy

• PostgreSQL Partitioning / Slicing and dicing your schema for quicker queries and dishing data (PGCon 2007)
• partitioning question -- how to guarantee uniqueness across partitions (pgsql-general mailing list post)