Partitioning v PostgreSQL

11. července 2007 v 15:01 | Honza |  Odborné
Tento článek je první z rubriky Odborné. V této rubrice bychom rádi publikovali postřehy, návody či připomínky, které nás - programátory, webové vývojáře a linuxové administrátory - při práci v Jyxu napadnou. Doufáme, že naše zápisku si najdou své čtenáře z řad odborníků či studentů. A teď již k partitioningu.
Partitioning je způsob, jak rozdělit data z jedné tabulky do více tabulek, a přitom navenek zachovat zdání jediné tabulky. PostgreSQL partitioning podporuje, nicméně poměrně krkolomným způsobem. Tento článek shrne důvody, které nás v Jyxu k nasazení partitioningu vedly, stručně seznámí se způsobem řešení partitioningu v Postgresu a poreferuje o problémemech, na které jsme narazili. Není v něm nic příliš objevného, co by v anglických zdrojích již nebylo popsáno, ani se nesnaží být kompletním návodem, ale pro běžného českého uživatele PostgreSQL, který uvažuje nad nasazením partitioningu, by to mohlo být zajímavé čtení.

Naše důvody pro nasazení partitioningu

Jednoduše řečeno, bylo příliš mnoho dat v jedné tabulce. V Skliku si pro každou kombinaci klíčového slova a inzerátu udržujeme počet zobrazení a kliků pro každý den. Čili velice zjednodušeně nějak takto:
     CREATE TABLE statistika (
id serial NOT NULL PRIMARY KEY,
datum date NOT NULL DEFAULT NOW(),
slovo integer NOT NULL REFERENCES slovo(id),
inzerat integer NOT NULL REFERENCES inzerat(id),
kliky integer NOT NULL DEFAULT 0,
zobrazeni integer NOT NULL DEFAULT 0
);
V současné době přibývá sedmiciferný počet záznamů denně, Sklik již nějaký ten pátek běží, a blížil se stav, kdy by se tabulka stala nezvladatelnou. Ačkoli samotné dotazy byly díky indexům poměrně rychlé, velice dlouho na ní běželo VACUUM a vytváření indexů bylo utrpení.
Přitom je to téměř totožný případ jako příklad z dokumentace. Pokud by tabulky byly fyzicky rozděleny například po měsících, udržovaly by se na konstantní velikosti, navíc by bylo snadné odstraňovat stará data jednoduchým a rychlým dropnutím posledních tabulek.

Dědičnost už i v databázi

Partitioning se realizuje pomocí dědičnosti. Strašné, že? Jako by nestačila dědičnost v programovacím jazyce aplikační vrstvy. Dědičnost (v PostgreSQL) funguje tak, že k libovolné tabulce (označme ji "základní") lze vytvořit tabulky odvozené, které budou obsahovat stejné sloupce a omezení, jako základní tabulka, a navíc mohou obsahovat další sloupce či omezení.
Pokud využíváme dědičnosti k realizaci partitioningu, tak typicky platí, že základní tabulka zůstává prázdná, a data jsou nějakým způsobem roztříděna do odvozených tabulek. Každá odvozená tabulka má nadefinována omezení, která zaručují, že záznamy lze do odvozených tabulek jednoznačně roztřídit. Pokud je zapnutá konfigurační volba constraint_exclusion, planner navíc dokáže tato omezení správně zanalyzovat a při dotazech bude prohledávat pouze relevantní tabulky.
Na základní tabulce je potom zadefinováno pravidlo nebo trigger, které zajistí, že data se vkládají, upravují či mažou v příslušné podřízené tabulce, místo toho, aby se tak dělo v tabulce základní.
To je stručný princip partitioningu v PostgreSQL. Nutno podotknout, že tento způsob má mnoho úskalí a zákoutí a je dobré si předem přečíst příslušnou sekci dokumentace (včetně malých poznámek na konci), a pokud možno i další články, které o tomto tématu jsou. Já jsem četl kupříkladu zápisek na Mozilla.com a prezentaci z letošního PgConu.

"Náš" partitioning

Nebudu zde vypisovat celou posloupnost SQL příkazů, kterými byla stávající data převedena na nový systém, je to totiž hodně podobné dokumentaci. Uvedu jenom jeden obrat a pár poznámek.
Hlavní tabulku lze vytvořit takto:
     CREATE TABLE statistika_2 (LIKE statistika INCLUDING DEFAULTS);
Tento způsob má velkou výhodu - není třeba vyjmenovávat sloupce, pokud SQL příkazy píšete stejně jako já ručně, a jednu malou nevýhodu. Pokud tabulka obsahuje sloupec typu serial, je zkopírována i jeho výchozí hodnota, v našem případě "nextval('statistika_id_seq')". To ale typicky není žádoucí, protože sekvence "statistika_id_seq" byla automaticky vytvořena spolu s tabulkou, a v okamžiku, kdy se původní tabulka bude mazat, PostgreSql bude chtít sekvenci smazat také. Je nutné sekvenci pro novou tabulku vytvořit manuálně (s odpovídající počáteční hodnotou) a sloupci "id" správně nastavit výchozí hodnotu.
Partitioning jsme nasadili bez servisní odstávky. Vedle původní tabulky byla vytvořena tabulka nová, s partitioningem, a data se postupně duplikovala. V okamžiku, kdy byla data zduplikována (a aplikační logika zajistila, že v daný okamžik nebudou přibývat nové záznamy), v rámci transakce byla původní tabulka přejmenováním uklizena z cesty a nová tabulka dostala původní jméno. Až poté, co bylo ověřeno, že to funguje jak má, byla velká stará tabulka smázána. Nutná podmínka byla, aby se na disk vlezlo dvojnásobné množství dat.
Při rozhodování, co použít pro přesměrování vkládání ze základní tabulky do příslušné podřízené tabulky, jsme nakonec dali přednost pravidlu. Pravidlo je obecně rychlejší než trigger. Nevýhodou je, že pro každou podřízenou tabulku, do které se přesměrovává, musí existovat samostatné pravidlo, což v případě většího množství podřízených tabulek představuje výkonnostní problém. V našem případě se vkládá jen do posledního páru tabulek, takže jsou nutná jen dvě pravidla.
Připomínám, že je důležité zkontrolovat prováděcí plán kritických dotazů přesně v tom tvaru, ve kterém půjdou do databáze.

Závěr a výhled do budoucna

Nasazení partitioningu naplnilo naše očekávání. Dotazy na aktuální sadu dat jsou rychlejší, některé dotazy pro generování přehledů za delší období se zpomalily, což ale není příliš velký problém. A jsme připraveni bezbolestně umazat nejstarší záznamy, až jich bude opravdu příliš moc.
PostgreSQL 8.2 podporuje možnost "osamostatnění" odvozené tabulky na základní tabulce, a "adopci" existující tabulky. To by umožňovalo například dávkové vkládání - jedna odvozená tabulka by se zduplikovala, na duplikátu by se provedlo množství úprav či vkládání (s odstraněnými indexy pro rychlejší zpracování) a poté by se v rámci jediné rychlé transakce původní tabulka z partitioningu vyjmula a nahradila upraveným duplikátem.
Postgres a velký objem dat zkrátka zajišťuje množství kreativní, přiměřeně adrenalinové zábavy.
 

2 lidé ohodnotili tento článek.

Komentáře

1 Jan Tichý Jan Tichý | E-mail | Web | 13. července 2007 v 9:27

Uff :). Super článek, opravdu zajímavé, díky  moc.

2 krteQ krteQ | 13. července 2007 v 12:38

Nechcete nekdy napsat clanek na tema: PostgreSQL vs MySQL5 na webu? Nevim, jestli v Jyxu jede vsechno od zacatku na PostgreSQL, mozna jste MySQL take zvazovali.... Hlavne by me zajimaly vyhody v oblasti webovych aplikaci s vetsimi objemy dat a vysokou zatezi.

Diky

3 Honza Honza | E-mail | Web | 15. července 2007 v 21:10

[2]: V době, kdy se zvažovalo zda postgre či mysql, tak ještě nebylo MySQL5, takže argumenty pro postgre typu referenční itegrita jasně převážily. Ale používáme i mysql - pro případy s velkým množstvím updatů. Ale "pětkové" funkce z MySQL pokud vím nemáme nikde, takže je ani nemůžeme zhodnotit :)

4 krteQ krteQ | 27. července 2007 v 14:14

OK, diky :) Ted jsme zacali pouzivat na jednom vetsim projektu MySQL 5 vcetne SP, triggeru, UDF a FK. Zatim jsem spokojeny, ale jeste jsme to nevystavili vetsi zatezi, uvidime. Co mysql nema poresene, je pohodli vyvoje triggeru a procedur. Hledat chybu je mirne receno nepohodlne...

5 m m | 8. srpna 2007 v 19:33

prave pri zatizeni s vice dat bych se mysql trochu obaval. Mame otestovano, ze pri pouziti vice views vypne mysql optimalizator a ty query jsou o hodne pomalejsi nez v PSQL (vyzkouseno na mysql5).

6 Jan Tichý Jan Tichý | E-mail | Web | 9. srpna 2007 v 13:43

[2][6] Musíme si ale vyjasnit, o čem se bavíme.

Pokud se bavíme o  multigeneračních databázích, tak se bavíme o PostgreSQL versus MySQL InnoDB a tady poznámka [6] platí. Neboli pokud bych měl úlohu, která je vhodná pro multigenerační databáze, tak se rozhoduji mezi zmíněnýma dvěma databázema a osobně bych z podobných důvodů taktéž zvolil Postgres.

Nicméně pak existuje jiný typ úloh, kde místo všech možných ACID výhod databázového systému potřebuji hlavně jen a pouze rychlé uložiště hromady dat. V takovém případě ale už otázka, zda PostgreSQL nebo MySQL, nedává smysl. Protože na tento typ úlohy se multigenerační databáze vůbec nehodí, nemá smysl tedy vůbec uvažovat ani PostgreSQL ani MySQL InnoDB. Z toho, co zbývá, je pak relativně nejpoužitelnější právě MySQL MyISAM.

Komentáře jsou uzavřeny.