Linux E X P R E S, PostgreSQL Conference Europe 2012: Středa

Autory článku jsou Lukáš Jelínek a Miroslav Hrončok. Není-li uvedeno jinak, je autorem fotografií Tomáš Vondra, jehož fotografie z akce jsou dostupné pod licencí CC BY-SA. Více jich najdete v galeriích z pondělí a úterý, středy, čtvrtku i pátku.

PostgreSQL Conference Europe neboli pgconf.eu je největší událost věnovaná PostgreSQL v Evropě. Jedná se o několikadenní konferenci, která se koná již od roku 2008 (dříve pod názvem European PGDay). Zatím se pořádala ve městech Prato (Itálie), Paříž (Francie), Stuttgart (Německo), Amsterdam (Nizozemsko) a letos právě u nás v hlavním městě.

Joe Celko s maskotem PostgreSQL

U příležitosti konání konference u nás jsme vydali sadu rozhovorů o PostgreSQL:

Letošní ročník začal už v úterý 23. října – první den probíhala ale samostatně placená školení, na kterých jsme nebyli. „Normální“ konference začala až ve středu 24. – otevřel ji Joe Celko, který se jako člen výboru ANSI X3H2 významně podílel na standardech SQL89 a SQL92 a je jedním z nejprodávanějších autorů knih o SQL.

Miroslav Hrončok

Místo konání konference, foto Lukáš Jelínek

Keynote

Joe Celko

Za keynote byl na této konferenci odpovědný Joe Celko a představil jakýsi „průlet“ historií ukládání dat. Věděli jste například, že děrné štítky vznikly již okolo roku 1725, pomohly výrazně urychlit sčítání lidu v USA v roce 1890 a hojně se využívaly ještě v 70. letech 20. století?

Po děrných štítcích, resp. současně s nimi, přišly děrné pásky, které byly relativně levné a oproti štítkům dokázaly pojmout poměrně velké množství dat, stroje pro práci s nimi však zrovna levné nebyly. Děrné pásky se používaly od první poloviny 19. století zhruba do 90. let století dvacátého (někdo z českých účastníků si jistě vzpomněl na hromady použitých pásek povalujících se před někdejším Podnikem výpočetní techniky).

Joe Celko samozřejmě nezapomněl ani na magnetické bubny (které na výsluní nebyly příliš dlouho) a hlavně magnetické pásky, jejichž historie sahá do 19. století a pro zálohování dat se aktivně používají i dnes. Podmnožinou této éry pak bylo, resp. stále je, využití magnetopáskových kazet. Dříve se běžně používaly kazety určené pro záznam zvuku, v dnešní době jsou to ale již výhradně kazety vyráběné přímo pro mechaniky na datové zálohování.

Joe Celko

Po magnetických páskách přišly na scénu disky, a to jak magnetické, tak optické. Joe neopomněl připomenout různé přístupy k rychlosti otáčení disků (u magnetických disků se využívala hlavně konstantní úhlová rychlost, u optických konstantní lineární rychlost; existovaly i další způsoby nakládání s rychlostmi). Hitem poslední doby jsou pak „disky v pevné fázi“ (SSD), které zažívají rozmach. Je otázkou, kdy převezmou vládu i v oblasti velkých objemů dat, kde mají klasické pevné magnetické disky zatím neotřesitelné postavení.

290 návštěvníků konference

Přestože fyzická úložná média toho nemají přímo moc společného s databázemi, je třeba si uvědomit, že nebýt jich, databáze by jednoduše nemohly existovat. Proto byla cesta historií ukládání dat zajímavým zpestřením myšlenek pohybujících se o jednu až dvě úrovně výše. Joe Celko se zamýšlel i nad tím, že relační databáze odprošťují data od fyzického média a my se od něj musíme odprostit také, abychom neuvažovali zpátečnicky.

Lukáš Jelínek

Writing a foreign data wrapper

Bernd Helmle

Z databází je občas potřeba přistupovat k externím zdrojům dat, ať už se jedná o jiné databáze, obyčejné soubory, nebo i úplně specifické datové zdroje, místní, či vzdálené. Na to existuje v jazyce SQL standard SQL/MED z roku 2003. V databázovém systému PostgreSQL se podpora objevila ve verzi 9.1.

Lze využívat již existující wrappery (Foreign Data Wrappers, FDW) nebo si vytvořit vlastní. A právě do této oblasti se nás pokusil zasvětit Bernd Helmle. Řešil totiž požadavek na vytvoření FDW pro databázi Informix a narážel při tom na různá úskalí.

Bernd Helmle

Při tvorbě nového FDW je třeba v první řadě implementovat náležité rozhraní (callbacky v rámci FDW API). Aby to nebylo tak jednoduché, verze 9.2 přidává do rozhraní nové funkce – zatímco v 9.1 jsou v rozhraní především operace pro vlastní přístup k datům, 9.2 obsahuje i analytické funkce. Nicméně není problém napsat implementaci tak, aby byla použitelná se všemi verzemi (počínaje 9.1).

Další oblastí je mapování datových typů a případná konverze dat. Pokud se jedná o vzdálený zdroj dat, musí se vytvořit také „cizí server“, do něhož se zabalí záležitosti související s připojením na vzdálený zdroj, a případně vytvořit i mapování uživatelů na vzdálený zdroj. Bernd se zabýval i některými „nízkoúrovňovými“ záležitostmi, například specifiky správy paměti v PostgreSQL.

Lukáš Jelínek

Provoz PostgreSQL na AWS

Tomáš Vondra

Středa byla jediným dnem konference, ve kterém probíhaly i české přednášky. První z nich měl Tomáš Vondra, který mluvil o zkušenostech s nasazením PostgreSQL na cloudu od Amazonu (AWS, Amazon Web Services). Jednalo se o ryze praktické zkušenosti, jelikož firma GoodData, pro kterou Tomáš pracuje a která byla jedním ze sponzorů konference, právě tento cloud využívá. Tomáš hned na začátku upozornil, že ne všechny věci, které řekne, platí obecně, ale že jde spíše o zkušenosti s konkrétním nasazením.

U AWS (a u cloudu obecně) je třeba počítat s několika pojmy: instance jsou virtuální stroje, které máte k dispozici, tyto mají přiřazenou nějakou storage, což je místo na ukládání dat, která mohou různé instance sdílet. Instance i storage jsou pak umístěné v různých datacentrech, která jsou rozdělená na tzv. availability zóny, což by měla být cca čtvrtina datacentra s oddělenou infrastrukturou (konektivita, napájení apod.).

Jednoznačnou výhodou použití cloudového řešení je škálovatelnost – když čekáte před Vánoci nápor, přikoupíte další instance a po Vánocích je opět zrušíte. Obrovská míra virtualizace ale znemožňuje sledovat konkrétní hardwarové problémy a často se tak stane, že něco jede jako po másle a z ničeho nic to přestane fungovat, ačkoli nevíte proč.

Tomáš zdůraznil, že samotná databáze jakožto aplikace funguje bez problémů, největší problém je s ukládáním dat, tedy se storage. Amazon nabízí dva základní typy storage: ephemeral a EBS (Elastic Block Store). Ephemeral znamená prchlivý nebo pomíjivý a přesně taková storage to je, rozhodně se nehodí na ukládání dat, které budete ještě potřebovat, spíše se hodí na uchovávání dočasných souborů. Databázi tedy spíše uložíte na EBS, zde je ale problém, fyzický hardware je sdílený mezi spoustu zákazníků, a tak se může stát a stává se, že EBS nestíhá, můžete si ale zaplatit Provisioned IOPS, který vám garantuje určitou dostupnost.

Jak má Tomáš ve zvyku, všechna tvrzení podložil grafy. Nakonec ještě poukázal na některá řešení, která fungují (RAID0, tablespaces), a na některá, která nefungují (RAID10, pre-zeroing). Ideální je však všechno nacpat do RAM, která může mít u instance až 70 GB.

Miroslav Hrončok

Multimaster replication

Andres Freund, Simon Riggs

Kdo se těšil na to, že se dozví o realizaci multimaster replikace se současnou verzí PostgreSQL, byl asi zklamán. Účast na této přednášce byla velmi hojná (tak hojná, že kapacita sálu nestačila a řada lidí se účastnila vestoje). Náplní tedy ale nebylo to, co a jak lze nyní udělat, nýbrž co obnáší replikace s více stroji v režimu „master“ a jak budou jednotlivé součásti postupně přidávány do PostgreSQL.

Multimaster replikace (též master–master či obousměrná replikace) znamená, že existují dva nebo více rovnocenných uzlů a změna v datech provedená na kterémkoli z nich se projeví i na těch ostatních (na rozdíl od replikace typu master–slave, kde se změny provádějí jen na jediném uzlu a odtud se replikují na ty ostatní).

PostgreSQL v současné době multimaster replikaci neumí. Kdo ji potřebuje, musí využít některé z doplňkových řešení, například PgCluster, Bucardo, Postgres-R nebo rubyrep. Ovšem postupně by se mohla master–master replikace dostat i jako nativní implementace přímo do PostgreSQL.

Simon Riggs

Replikační řešení není triviální a hlavně ani neexistuje jediný způsob řešení. Například lze replikovat synchronně či asynchronně (synchronní zajišťuje konzistenci dat, ale na úkor výkonu), s různou granularitou, s různými způsoby řešení konfliktů.

Cílem pro nativní replikační řešení do PostgreSQL je, aby bylo robustní, nenáročné na výkon i úložný prostor, v první řadě asynchronní a volitelně i synchronní. Již se na něm pracuje – jednotlivé součásti se budou v PostgreSQL objevovat postupně od verze 9.3, ve verzi 9.5 bychom se již mohli dočkat multimaster replikace, přinejmenším té asynchronní.

Lukáš Jelínek

PL/pgSQL Internals

Pavel Stěhule

Druhou českou přednášku měl Pavel Stěhule, který účastníky seznámil s tím, jak funguje PL/pgSQL. Nejdříve vysvětlil, že jakýkoliv myslitelný programovací jazyk jde napojit na PostgreSQL. Od základu je k dispozici C, PL/Python, PL/SQL a PL/pgSQL. Není problém přidat třeba PL/Java – vše je v tabulce pg_proc, kde je uložen název a několik ukazatelů na způsob, jak se funkce mají volat.

PL/pgSQL je, řečeno Pavlovými slovy, lepidlo na SQL příkazy. Není to plnohodnotný jazyk, vůbec neřeší například výrazy – to vše nechává na samotném PostgreSQL. Díky tomu je absolutně kompatibilní s aktuálně používanou verzí PostgreSQL a navíc běží ve stejném procesu jako aktuální PostgreSQL session, takže nemusí docházet k meziprocesové komunikaci, jako je tomu například u Oraclu.

Pavel Stěhule

Pokud programujete vlastní úložné procedury v PL/pgSQL, měli byste si uvědomit, že na rozdíl například od jazyka C nedochází k žádným optimalizacím a téměř všechno se mění na SELECTy – čím méně jich budete mít, tím rychlejší celá procedura nebo funkce bude. Pavel ukázal, jak výsledné SELECTy zobrazit (#option dump), a také několik příkladů, jak určité věci (ne)dělat. Kromě toho také zdůraznil, že pokud chcete provádět nějaké složité výpočty, je určitě lepší použít například C, které může být až 30krát rychlejší; pokud ale chcete jen rozumě lepit SQL příkazy, je PL/pgSQL jasnou volbou.

Miroslav Hrončok

Migrace z MySQL na PostgreSQL

Tomáš Vondra

Přednáška Tomáše Vondry měla sice poměrně obecný název, šlo však v podstatě o případovou studii migrace z MySQL na PostgreSQL ve firmě GoodData, která poskytuje platformu (PaaS) pro business intelligence. Platformu využívají firmy jak pro své vlastní účely, tak hlavně pro poskytování BI služeb svým zákazníkům.

Původně se data ukládala do MySQL 5.1, postupně se však stále více projevovaly nevýhody tohoto databázového systému. Při ukládání do tabulek MyISAM nelze zajistit konzistenci dat (bez kompletního zamčení tabulky), proto se přidávání nových dat muselo řešit zkopírováním, aktualizací a prohozením tabulek (stínové tabulky). Problémy byly i s výkonem při spojování tabulek (MySQL nemá full outer join a používá nested loop), ty byly řešeny pomocí MEMORY enginu (vytváření dočasných tabulek v paměti), což ale nebylo vždy úspěšné.

Pavel Stěhule a Tomáš Vondra, foto Lukáš Jelínek

Úložiště InnoDB řeší některé problémy, ale ukládání nových dat je výrazně pomalejší. Nakonec bylo rozhodnuto o migraci na PostgreSQL, kde je většina požadované funkcionality chybějící v MySQL k dispozici. PostgreSQL je na této konkrétní aplikaci cca pětkrát rychlejší (2x oproti MySQL s využití Lumem), byly přidány i vlastní patche, z nichž některé byly odeslány do upstreamu (a jsou tedy k dispozici všem).

Jedním z přetrvávajících problémů je chybějící MERGE. Zatím se to řeší pro malé objemy dat procedurami a výjimkami, pro velké objemy nástrojem pg_bulkload. Dále jsou problémem i mezivýsledky ukládané do tabulek – pomalost při autovacuum a rušení tabulek. Každopádně byla migrace úspěšná (přestože nebyly ještě přesunuty všechny projekty) a většina problémů byla odstraněna.

Lukáš Jelínek

Range Types in PostgreSQL 9.2

Jonathan S. Katz

Jonathan předvedl velmi zajímavou vlastnost PostgreSQL 9.2 – intervalové datové typy. Interval je v databázi potřeba definovat velmi často, doteď jste asi používali dva sloupce, značící začátek a konec intervalu (například začátek a konec směny). Práce s nimi a jejich porovnávání je však velmi krkolomné. V PostgreSQL 9.2 tak naštěstí vznikly datové typy, které nejenže umožňují uchovávat otevřené, uzavřené i polouzavřené intervaly v jednom sloupci, ale také poskytují operátory pro jejich porovnávání a pro zjištění, jestli daná hodnota patří do tohoto intervalu. Například pro zjištění, jestli je daná hodnota v intervalu, vám tak stačí použít hodnota <@ interval. Určitě oceníte i možnost integritního omezení, které zamezí překrývání intervalů v jednom sloupci.

Velikou výhodou je, že kromě předdefinovaných intervalových typů (celá čísla, reálná čísla, data, časy apod.) můžete kdykoli vytvořit další typ pomocí klauzule AS RANGE (SUBTYPE XXX).

Jonathan S. Katz

Osobně považuji tuto funkci PostgreSQL za velmi užitečnou a použitelnou – sám Jonathan nám ukázal, jak je používá u svého projektu VenueBook.

Miroslav Hrončok

Indexy jsou grunt

Pavel Stěhule

Pravděpodobně každý, kdo pracuje s nějakou databází, přinejmenším slyšel o indexech. Indexy výrazně zrychlují přístup k datům – ovšem jen při správném použití. Pavel Stěhule ve své přednášce vysvětlil, jak indexy fungují, kdy je používat a kdy naopak nepoužívat (protože naopak práci zpomalují). Věnoval se podrobněji konkrétním druhům indexů v PostgreSQL (a ve stručnosti též porovnání se situací u jiných databázových systémů) a tomu, kdy se které používají.

Indexy jsou výhodné především v případech, kdy je potřeba získat z tabulky menší množství dat. Při větším množství už může být výhodnější procházet celou tabulku, zejména vzhledem k sekvenčnímu charakteru I/O operací (oproti náhodnému přístupu k datům v případě indexů). Je třeba mít na paměti, že indexy je třeba aktualizovat při každé změně v datech. Proto se hodí vytvářet indexy jen nad těmi sloupci, které se často využívají pro výběr dat.

PostgreSQL má v základní instalaci v zásadě tři druhy indexů: B-strom, GIN a GiST (ještě je zde hash, ale jeho budoucí osud je nejistý). Základním typem je B-strom, který se používá pro ty datové typy, u kterých existuje relace uspořádání (číselné typy, datum a čas...). Indexy typu GIN jsou tzv. invertované indexy a umožňují práci s daty přes více klíčů, například pole. Lze u nich definovat mnoho různých způsobů fungování. Podobně je to i u indexů GiST (zobecněné vyhledávací stromy), které jsou principiálně „ztrátové“ (hledání může kromě správného vrátit i nesprávný výsledek) a pracují pomaleji, ale zabírají méně místa a jejich budování a aktualizace jsou rychlejší.

Indexy typu GIN a GiST v PostgreSQL lze rozšiřovat (přidávat algoritmy a operátory). Lze je také použít pro hledání na částečnou shodu, například jako u operátoru typu LIKE. S běžným B-stromovým indexem je LIKE použitelný jen v případě, že má hledaný řetězec konstantní začátek (% je jen na pravé straně). Vhodnější je proto využít některý z jiných indexů a vyhledávat operátorem @@ za pomoci typů tsvector a tsquery.

Výhodou zmíněných obecnějších indexů je například i možnost hledání na základě vzdálenosti – a to nejen v geometrickém smyslu, ale i třeba z hlediska podobnosti textů. Slouží k tomu operátor <-> v kombinaci s limitem vzdálenosti.

Lukáš Jelínek

Load dat do PostgreSQL

Jan Holčapek

Občas je potřeba „nalít“ do databáze větší množství dat. Jak to udělat co nejlépe a nejpohodlněji, se nám pokusil ukázat Jan Holčapek. Je několik možností. Nejzákladnější jsou SQL příkazy INSERT a UPDATE, ty však nelze použít přímo, tedy bez externí aplikace, která udělá řadu věcí, především ošetří vstupní data a pak řídí vstup do databáze.

Další možností je příkaz COPY. Ten umožňuje kopírovat data z formátu CSV a potřebné pomocné operace udělá sám. Dokáže zpracovávat data jak z klientu, tak ze serveru (za předpokladu práv přístupu k souboru se vstupními daty). Nepříjemné je, že vkládání dat pomocí COPY je jednak pomalé (hlavně pokud jsou v tabulce indexy), ale především neřeší situaci, kdy už v tabulce nějaká data jsou (nefunguje jako příkaz MERGE, který v PostgreSQL chybí). Vypnutí indexů po dobu práce COPY umožňuje práci zrychlit, ale to se týká jen počátečního plnění – při aktualizaci je to naopak pomalejší.

V japonské společnosti NTT proto kdysi vytvořili nástroj pg_bulkupload, který je schopen fungovat efektivněji, ale má i své neduhy. pg_bulkupload má dva základní režimy – jeden (buffered) funguje klasickým způsobem a je o něco rychlejší než COPY. Druhý režim direct nezapisuje transakční logy a umožňuje dosáhnout výrazně vyšší rychlosti.

Ovšem zrovna v tomto případě platí „něco za něco“. Během vkládání dat nelze k datům přistupovat, protože pg_bulkupload je musí zamknout, což může být zásadní problém. Pg_bulkupload umí také filtraci dat, ale ta výrazně zpomaluje práci. Naopak lze přenos dat zrychlit použitím doplňku pg_timestamp, pokud jsou časová razítka v akceptovaném formátu.

Nejlepší na konec: Zlí jazykové tvrdí, že existence pg_bulkupload je příčinou, že PostgreSQL dosud nemá skutečně kvalitní zabudovaný nástroj pro load velkých objemů dat. Asi na tom něco bude...

Lukáš Jelínek

Pokračování reportáže vyjde co nevidět...