Slon nezapomíná (co nás čeká v PostgreSQL 8.3)

byla uvolněna beta PostgreSQL 8.3. V tuto chvíli to znamená, že se do kódu dostanou již pouze opravy a je tedy docela jisté, jak bude nová verze PostgreSQL vypadat. Od vydání bety do vydání ostré verze se v případě PostgreSQL čas měří na týdny, takže se můžeme začít těšit na ostrou verzi.

Na psaní tohoto článku jsem se těšil, a na jeho dokončení si pár měsíců počkal. Oproti původním plánům se vydání bety zdrželo o několik měsíců. Mám dojem, že se uzavírá jedna kapitola v dějinách open source databází. Konečně mizí malicherné důvody proč nepoužívat nebo používat určitou databázi. Tak jak se databáze přibližují k standardu SQL2003, tak mizí povrchní rozdíly. Diskuze typu "MySQL není databáze, jelikož nepodporuje transakce" atd konečně ztratily jakýkoliv smysl, jelikož MySQL podporuje jak transakce, referenční integritu tak i např. uložené procedury. Všechny databáze z velké trojky (Firebird, MySQL a PostgreSQL) používají multigenerační architekturu, cenově orientované hledání optimálního prováděcího plánu, write ahead log atd. MySQL se profiluje jako SQL databáze schopná používat specializované databázové backendy schopné maximální efektivity v určitých prostředích (za cenu netriviální a nejednotné konfigurace a správy db. engines). PostgreSQL je široce použitelná databáze, těžící z vynikající stability, s perfektní rozšiřitelností, s komfortním prostředím a snadnou konfigurací. Konečně, Firebird je vynikající embeded databáze, která sbírá body v takřka bojových podmínkách v tisíci instalací u koncových uživatelů.

Víceméně skončila éra nezávislých o.s. vývojářů. Velká část vývojářů se PostgreSQL věnuje na plný úvazek. Vývoj ale není soustředěný v jedné firmě, jako je tomu u MySQL nebo Firebirdu. A i když nezávislých vývojářů je minimum, tak jsou stále vidět (např. podpora scrollable kurzorů, tabulkových funkcí, SQL/XML). Konečně má PostgreSQL komerční 24x7 podporu. Několik pokusů o komerční podporu tu už bylo, bohužel zkrachovaly, poněvadž nedokázaly nabídnout přidanou hodnotu vůči nekomerční podpoře, která je skoro dokonalá. Na mailing listu je odezva do 24 hodin standardem, vetšinou do několika hodin (vlivem časového posunu). Na irc je, díky globalizaci, v průměru přes 300 lidí nepřetržitě a pokud člověk nepřijde fakt s nějakou perličkou, tak je pomoc okamžitá. Není příliš firem schopných zajistit takovou podporu, a nevyplatí se platit za něco, co je zdarma v takové kvalitě. EnterpriseDB nabízí modifikovanou PostgreSQL a k ní odpovídající podporu zhruba v cenových relacích komerční podpory MySQL. S EnterpriseDB moc lidí zkušenosti nemá, jedná se o close source, a proto na irc pomoc nenajdete a musíte si podporu zaplatit. Obě databáze jsou si velice podobné, takže podpora PostgreSQL je pro EnterpriseDB jen další byznys s minimálními náklady. Obě databáze mají shodné jádro a EnterpriseDB poměrně intenzivně sponzoruje vývoj nových fíčur pro PostgreSQL (resp. platí své zaměstnance, kteří na tom pracují). Příkladem mohou být bitmapové indexy, rekurzivní dotazy, a desítky dalších menších nebo větších změn vedoucích k vyšší efektivitě jádra. EnterpriseDB je v podstatě PostgreSQL upravený tak, aby byl kompatibilní se staršími verzemi Oracle. Cílem je oslovit zákazníky používající Oracle, kterým končí podpora, a kterým se nevyplatí přecházet na nové verze Oraclu. Samozřejmě, že cenová politika EnterpriseDB je jiná než Oraclu. Logicky. Oracle se pohybuje někde jinde. Na druhou stranu, nepochybuji, že 90% existujících instalací Oracle možností Oracle zdaleka nevyužívá a může být bezproblémově nahrazeno právě třeba EnterpriseDB. Stejné zákazníky se snaží ulovit i podobný projekt Firacle, který vychází z Firebirdu. Soužití s EnterprseDB není jednoduché. Poslední roky se PostgreSQL snaží jít svou vlastní cestou, nekopírovat Oracle, což je v rozporu s filozofií EDB, která se snaží být co nejvěrnější kopií Oracle, včetně všech knihoven a balíků. Kvůli grafickému správci databáze a integrovanému debuggeru bych si EDB neinstaloval, zato jejich implementaci PL/SQL musím ocenit a tiše závidět. Z EDB by se však do PostgreSQL mělo časem dostat několik zajímavých vlastností, např. linkované servery nebo autonomní transakce (a aktuálně dlouho očekávaný interaktivní debugger PL/pgSQL). EDB opisuje skutečně poctivě. Instalace obsahuje aplikační server zajišťující vzdálenou správu. Díky tomu (a také integraci runtime javy) zabere instalace 300M. Kromě toho, stejně jako Oracle, zakazuje zveřejnění benchmarků, což zapříčinilo určité neshody mezi core vývojáři a marketingem EDB. V těchto sporech vždy vedení EDB vývojářům ustoupilo.

Na domácí půdě je pokrok mírný, v mezích zákona. Na cestě je publikace Marka Olšavského, kterého můžete znát z http://www.linuxsoft.cz. Stránky PostgreSQL snad konečně zakotvily na http://www.pgsql.cz. K dispozici jsou komerční školení linuxoftu.cz nebo www.pgsql.cz. Proběhly dvě relativně úspěšná školení pod patronací Roota. Běží wiki, která soustřeďuje česky psanou dokumentaci. Jinak ticho po pěšině. Začátkem května proběhla PPBP v Praze (pod patronací Jixo:)). Jako určitý ukazatel popularity jednotlivých databází by se mohl brát počet příspěvků na http://forum.dbsvet.cz/ . Nejpopulárnější DB by v ČR byl Microsoft SQL server s 72 dotazy (začátek června), pak Oracle (40 dotazů), potom MS Access (33 dotazů). O čtvrtou pomyslnou příčku se dělí Firebird, MySQL (26 dotazů). PostgreSQL je na páté příčce s 22 příspěvky. Ostatní databáze se zde patrně nepoužívají (méně než 5 dotazů). Pořadí je nepochybně ovlivněno tím, že toto fórum vyhledávají hlavně začátečníci. Pokročilí uživatelé komunikují většinou přímo s podporou nebo na tematicky orientovaných portálech. I tak to o něčem vypovídá - jak často s kterými databázemi přijdou do styku začátečnicí.

Původně se měla 8.3 objevit před prázdninami - mělo jít o verzi obsahující patche dokončené pro 8.2, ale v té době nedostatečně otestované. Nakonec se ukázalo, že ty nejdůležitější patche je třeba dopracovat. Jednalo se o tak atraktivní vlastnosti, že se rozhodlo s vydáním nové verze ve více-méně obvyklém předvánočním termínu. 8.3 obsahuje integrovaný fulltext, podporu opožděného potvrzování (asynchronní commit), synchronizované sekvenční čtení datových souborů, úspornější ukládání dynamických datových typů (kratších 256byte), HOT updates. Z patchů připravených pro 8.2 se v 8.3 neobjeví podpora bitmapových indexů a podpora aktualizovatelných pohledů. Původní řešení založené na pravidlech (rules) bylo příliš komplikované. 8.3 obsahuje aktualizovatelné kurzory, takže v 8.4 se možná dočkáme aktualizovatelných pohledů postavených právě nad touto třídou kurzorů.

Vývoj pokračuje implementací dalších modulů SQL. Ve verzi 8.3 je to konkrétně SQL/XML (rozšíření ANSI SQL), která umožňuje operace s XML dokumenty přímo v databázi a zjednodušuje generování XML dokumentů. Zásadní (interní) změnou je zkrácení hlavičky řádku z 28 bajtů na 24 bajtů. Další změnou, která by měla vést k minimalizaci velikosti uložených dat je diverzifikace typu varlena. Tento typ se v PostgreSQL používá pro serializaci hodnot všech typů s variabilní délkou. Datový typ varlena je obdobou typu string v Borland Pascalu (pro pamětníky). První byty nesou informaci o délce, další nesou obsah. Starší verze PostgreSQL používaly pouze typ varlena s 4byte informací o délce. 8.3 podporuje také úspornější typ varlena s 1byte záhlavím. Úspora by se měla projevit hlavně u typu NUMERIC a krátkých řetězců. PostgreSQL lze, počínaje touto verzí, přeložit překladači gcc a MINGW a nově Microsoft Visual C++ (pouze na platformě Microsoft Windows).

Integrace TSearch2

Integrace TSearch2 do jádra PostgreSQL je výsledkem dlouholetého úsilí Olega Bartunova a Teodora Sigaeva. Integrací se zjednodušila konfigurace fulltextu a pro určité jazyky (pro které existuje podpora v projektu Snowball) lze fulltext používat ihned po instalaci databáze. Čeština bohužel mezi tyto jazyky nepatří - je potřeba provést několik dalších kroků. Předně převést Open Office slovníky do kódování UTF8 a zkopírovat je do příslušného podadresáře PostgreSQL. Dalším krokem je registrace slovníků a tzv. konfigurací. Jinak jsou rozdíly mezi integrovaným fulltextem a TSearch2 spíše kosmetické.

CREATE TEXT SEARCH DICTIONARY cspell(
   template=ispell, 
   dictfile = czech, afffile=czech, stopwords=czech);

CREATE TEXT SEARCH CONFIGURATION cs (copy=english);

ALTER TEXT SEARCH CONFIGURATION cs 
   ALTER MAPPING FOR word, lword  WITH cspell, simple;

postgres=# select * from ts_debug('cs','Příliš žluťoučký kůň se napil žluté vody');
 Alias |  Description  |   Token   |  Dictionaries   |    Lexized token    
-------+---------------+-----------+-----------------+---------------------
 word  | Word          | Příliš    | {cspell,simple} | cspell: {příliš}
 blank | Space symbols |           | {}              | 
 word  | Word          | žluťoučký | {cspell,simple} | cspell: {žluťoučký}
 blank | Space symbols |           | {}              | 
 word  | Word          | kůň       | {cspell,simple} | cspell: {kůň}
 blank | Space symbols |           | {}              | 
 lword | Latin word    | se        | {cspell,simple} | cspell: {}
 blank | Space symbols |           | {}              | 
 lword | Latin word    | napil     | {cspell,simple} | cspell: {napít}
 blank | Space symbols |           | {}              | 
 word  | Word          | žluté     | {cspell,simple} | cspell: {žlutý}
 blank | Space symbols |           | {}              | 
 lword | Latin word    | vody      | {cspell,simple} | cspell: {voda}
(13 rows)

Podporu fulltextu nad konkrátním sloupcem můžeme aktivovat také vytvořením funkcionálního GIN indexu:

CREATE INDEX data_poznamka_ftx ON data 
   USING gin(to_tsvector('cs', poznamka))

Vyhledáváme použitím operátoru @@ (fulltextové vyhledávání):

SELECT * FROM data
  WHERE to_tsvector('cs',poznamka) @@ to_tsquery('žlutá & voda')

Podpora SQL/XML

Zásadně se změnila podpora XML. To co v předchozích verzích se neohrabaně řešilo skrz doplňky se nyní dostalo přímo do jádra. Jednak jsou k dispozici funkce generující XML (xmlelement, xmlforest, ..) jednak jsou tu funkce mapující obsah tabulky do XML. Výsledkem může být xml schéma (použitelné pro validaci nebo pro přenos definice tabulky), XML dokument s integrovaným schématem, nebo samotný xml dokument. Jelikož je výstupní formát standardizován v SQL/XML neměl by být přenos těchto tabulek problémem (mezi těmi databázemi, které SQL/XML podporují).

pavel=# create table a(a date, b varchar(10));
CREATE TABLE
pavel=# insert into a values(current_date, 'něco'),(current_date+1, NULL);
INSERT 0 2
pavel=# select table_to_xml_and_xmlschema('a', true, false, '');
                                   table_to_xml_and_xmlschema                                   
------------------------------------------------------------------------------------------------
 <a xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="#">
 
 <xsd:schema
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 
 <xsd:simpleType name="DATE">
   <xsd:restriction base="xsd:date">
     <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/>
   </xsd:restriction>
 </xsd:simpleType>
 
 <xsd:simpleType name="VARCHAR">
 </xsd:simpleType>
 
 <xsd:complexType name="RowType.root.public.a">
   <xsd:sequence>
     <xsd:element name="a" type="DATE" nillable="true"></xsd:element>
     <xsd:element name="b" type="VARCHAR" nillable="true"></xsd:element>
   </xsd:sequence>
 </xsd:complexType>
 
 <xsd:complexType name="TableType.root.public.a">
   <xsd:sequence>
     <xsd:element name="row" type="RowType.root.public.a" minOccurs="0" maxOccurs="unbounded"/>
   </xsd:sequence>
 </xsd:complexType>
 
 <xsd:element name="a" type="TableType.root.public.a"/>
 
 </xsd:schema>
 
 <row>
   <a>2007-02-19</a>
   <b>něco</b>
 </row>
 
 <row>
   <a>2007-02-20</a>
   <b xsi:nil='true'/>
 </row>
 
 </a>

Stejného výsledku dosáhneme pomocí funkcí generujících XML. Jejich použití je univerzálnější, a o trochu komplikovanější:

pavel=# SELECT xmlelement(name a,
			xmlagg(
				xmlelement(name row, 
					xmlforest(a,b)))) 
		FROM a;
                                 xmlelement                                 
----------------------------------------------------------------------------
 <a><row><a>2007-02-19</a><b>něco</b></row><row><a>2007-02-20</a></row></a>
(1 řádka)

Nové datové typy a rozšíření možností stávajících datových typů

Verze 8.2 PostgreSQL podporuje několik nových datových typů: XML zajišťující validitu obsahu, UUID (universal unique identifier) dle RFC 4122. Vlastní generátor je v contribu uuid-ossp (je nutné doinstalovat package uuid a uuid-devel (na fedoře)). K dispozici je deset generátorů jednoznačných univerzálních identifikátorů. Dále je tu možnost používat vlastní výčtové typy (zjevně inspirováno MySQL). Na rozdíl od MySQL je v PostgreSQL nutné před použitím vytvořit pro určitý seznam hodnot vlastní typ. Jeho použití je potom širší než v MySQL.

-- klasické řešení výčtu
CREATE TABLE foo(varianta char(2) CHECK (varianta IN ('a1','a2','a3')));

-- pouziti typu enum
CREATE TYPE vycet_variant AS ENUM('a1','a2','a3','a4','a5');
CREATE TABLE foo(varianta vycet_variant);
-- enum lze pouzit i v poli
SELECT '{a1,a3}'::vycet_variant[] as pripustne_varianty;

Rozsah hodnot získáme voláním funkce enum_range. Pokud funkci předáme parametr NULL, získáme úplný výčet hodnot.

postgres=# SELECT enum_range('a2'::va, 'a4'::vycet_variant);
 enum_range 
------------
 {a2,a3,a4}
(1 row)

postgres=# SELECT enum_range(null::vycet_variant);
    enum_range    
------------------
 {a1,a2,a3,a4,a5}
(1 row)

Kromě opravy několika chyb v PL/pgSQL (chyběla kontrola NOT NULL domén), došlo k, viz níže popsanému, rozšíření příkazu RETURN o tabulkový výraz. Konečně lze i v PL/pgSQL používat scrollable kurzory i nově přidané updatable kurzory. U SRF funkcí můžeme upřesnit jejich náročnost a předpokládaný počet vrácených řádků (atributy COST a ROWS). V předchozích verzích se při hledání optimálního prováděcího plánu předpokládalo, že SRF funkce vrátí vždy 1000 řádků, což nebyla pokaždé pravda (výsledkem byl neoptimální prováděcí plán)

Vedlejším efektem implementace subsystému pro kešování prováděcích plánů bylo odstranění problémů s neplatnými prováděcími plány v PL/pgSQL. Tyto problémy se projevovaly hlavně při použití dočasných tabulek, které se nesměly explicitně rušit. Jinak při použití SQL příkazu vázaného na zrušenou a opětovně vytvořenou tabulku došlo k chybě. Nyní při rušení databázového objektu dojde k inicializaci cache prováděcích plánů, které obsahují reference na rušený objekt. Samozřejmě, že funkce volané v cyklu budou prováděné efektivně jen tehdy, pokud nebude docházet k opakovanému regenerování prováděcích plánů.

V 8.3 můžeme vytvářet pole i ze složených typů - v podstatě tabulku můžeme uložit jako jednu hodnotu). Stále však chybí podpora domén (a vkládaný záznam je nutné explicitně typovat):

postgres=# CREATE TYPE at AS (a integer, b integer);
CREATE TYPE
postgres=# CREATE TABLE foo(a at[]);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(ARRAY[(10,20)::at]);
INSERT 0 1
postgres=# INSERT INTO foo VALUES(ARRAY[(10,20)::at, (20,30)::at]);
INSERT 0 1
postgres=# SELECT * FROM foo;
           a           
-----------------------
 {"(10,20)"}
 {"(10,20)","(20,30)"}
(2 rows)

postgres=# SELECT a[1] FROM foo;
    a    
---------
 (10,20)
 (10,20)
(2 rows)

postgres=# SELECT a[1].a FROM foo;
 a  
----
 10
 10
(2 rows)

Původně zamýšlená podpora SQL/PSM se do 8.3 nedostala. Zatím jazyk SQL/PSM není příliš rozšířen a není tu (celosvětově) velká potřeba na něj přecházet a to v době, kdy víceméně panuje s PL/pgSQL absolutní spokojenost. Nicméně PL/pgSQL ztrácí kontakt s PL/SQL, přičemž PL/pgPSM představuje standard respektující o jednu generaci modernější jazyk. Na tomto místě musím poděkovat těm několika dobrovolníkům, kteří se postarali o překlad dokumentace.

Optimalizace

Ve verzi 8.3 došlo k celé řadě změn a úprav, které by měly vést k rychlejšímu zpracování SQL příkazů. Mělo by se urychlit načítání dat příkazem COPY. U tohoto příkazu není žádný důvod pro zápis do write ahead logu (ten je základem procesu obnovy po pádu PostgreSQL) a tato verze dokáže zápis do WAL obejít (COPY musí být v transakci). Nová řadící metoda top-n urychlí dotazy typu ORDER BY c LIMIT n, pokud nad sloupcem c chybí index. Výběr prvních sta řádků z tabulky o jednom sloupci a miliónu řádků se zrychlil cca 10x (z 4200ms na 433ms). Příkaz EXPLAIN ANALYZE nyní poskytuje další informace o řazení (typ, spotřeba paměti):

t=# explain analyze SELECT * FROM foo ORDER BY a LIMIT 12;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3685.48..3685.51 rows=12 width=4) (actual time=290.549..290.588 rows=12 loops=1)
   ->  Sort  (cost=3685.48..3935.48 rows=100000 width=4) (actual time=290.544..290.557 rows=12 loops=1)
         Sort Key: a
         Sort Method:  top-N heapsort  Memory: 17kB
         ->  Seq Scan on foo  (cost=0.00..1393.00 rows=100000 width=4) (actual time=0.036..153.526 rows=100000 loops=1)
 Total runtime: 290.658 ms
(6 rows)

t=# explain analyze SELECT * from foo order by a;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Sort  (cost=10894.82..11144.82 rows=100000 width=4) (actual time=520.528..683.190 rows=100000 loops=1)
   Sort Key: a
   Sort Method:  external merge  Disk: 1552kB
   ->  Seq Scan on foo  (cost=0.00..1393.00 rows=100000 width=4) (actual time=0.022..159.028 rows=100000 loops=1)
 Total runtime: 800.065 ms
(5 rows)

Počínaje verzí 8.3 je podporována metoda HASHJOIN sloupců typu NUMERIC. MERGEJOIN by měl být dvojnásobně rychlejší.

Zrychlit by měla i operace LIKE, zvlášť pokud se používá více bajtové kódování - použil se jiný algoritmus na porovnání řetězců. Nyní se již neporovnávají znaky, ale bajty, což ušetří jednu konverzi z UTF8 do UTF16. Na zkušební tabulce o sta tisících žlutých koních se sekvenční čtení tabulky zrychlilo ze 169ms na 105ms. Po opravě lze použít ILIKE i pro kódování UTF8. Bohužel je nyní několikanásobně pomalejší než LIKE, a proto bych doporučil používat ILIKE co nejméně (náhradou může být např. fulltext).

Pokud je detekováno paralelní sekvenční čtení tabulky více procesy, dojde k pokusu o synchronizaci těchto procesů. Sekvenční čtení je přibližně stejně rychlé u všech procesů, a tak je šance, že všechny sesynchronizované procesy budou přistupovat v jeden okamžik ke stejné datové stránce. Zvyšuje se tak efektivita vyrovnávající paměti a naopak snižuje počet požadavků na fyzické čtení datové stránky ze souboru. Tato úvaha má smysl při větším počtu souběžně pracujících uživatelů, kdy je vyšší šance, že dojde k synchronizaci, a také kdy je větší tlak na vyrovnávací paměť.

V PostgreSQL stále chybí COLLATE. Podařilo se alespoň rozšířit klauzuli ORDER a to v pozicování řádků s hodnotou NULL (ORDER BY .. NULLS FIRST/LAST). Adekvátně tomu se rozšířili parametry u btree indexů. Refaktorizací kódu se docílila podpora NULL v indexech. Starší verze nedokázali indexovat hodnotu NULL. Berlička v podobě podmíněného indexu je nyní konečně zbytečná.

postgres=# explain SELECT count(*) FROM fx WHERE a IS NULL;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Aggregate  (cost=8.28..8.29 rows=1 width=0)
   ->  Index Scan using bb on fx  (cost=0.00..8.28 rows=1 width=0)
         Index Cond: (a IS NULL)
(3 rows)

Trochu z jiného soudku je logování čekání na uvolnění zámků. V předchozích verzích jsem musel, pokud jsem měl podezření, že mi zámky brzdí provádění SQL příkazů, monitorovat databázi a sledovat tabulku zámků a tabulku aktivních dotazů. V 8.3, s nastaveným parametrem log_lock_waits, dojde při detekci zámku k zápisu do logu. Podobnou diagnostiku obsahuje i MySQL 5.1.

Podpora asynchronního commitu je méně nebezpečnou obdobou vřele nedoporučované konfigurace fsync=off. Při asynchronním commitu je zaručena konzistence databáze, nicméně při havárii hrozí ztráta několika posledních transakcí. Parametr synchronous_commit je vázán na session, takže se každý vývojář může (relativně volně) rozhodnout pro méně bezpečný, zato rychlejší způsob řešení transakcí. Z mých několika málo testů se ukazuje, že v případě málo zatížené databáze, kdy nedochází ke sdílení zápisu do transakčního logu, má tento parametr smysl. Typickým příkladem je administrace databáze, kdy s databází pracuje pouze dba a ostatní uživatelé nemají přístup k databázi (pgbench ukazuje o třetinu vyšší hodnoty).

8.3 obsahuje sofistikovanější metodu pro vytváření nových verzí označovanou jako HOT (Heap Only Tuples). Starší verze při jakékoliv operaci UPDATE modifikovaly všechny indexy spojené s aktualizovanou tabulkou, a to i přesto, že nedošlo k modifikaci oindexovaných sloupců. Tzv. horký UPDATE je podmíněný dostatkem volného prostoru na datové stránce a změnou pouze neoindexovaných sloupců. Pokud tyto podmínky nejsou splněny, provede se klasický "studený" UPDATE. HOT UPDATE je vůči klasické implementaci operace mnohem úspornější a tudíž i rychlejší. Navíc tato nová metoda dokáže využít prostor na datové stránce obsazený nedostupnými verzemi (které byly vytvořeny také touto metodou) bez potřeby spuštění operace VACUUM.

Čistě orientace pgbench (v 8.2 zapnuto autovacuum) s parametry -c n -t 1000 na jednoprocesorovém notebooku P1.6:

Hodnoty pgbench
Počet klientů	1	10	20
8.2 (t/s)	541	390	335
8.3 (t/s)	843	813	650

Spuštění operace VACUUM ve více procesech

V 8.3 automatické vacuum podporuje více procesů, tj. pokud trvá vacuum jedné databáze příliš dlouho, vytvoří se nový pracovní proces (worker), který zajišťuje vacuování dalších databází (smyslem není urychlit operaci VACUUM paralelizací, ale zajistit, že v daném časovém okně se provede spravedlivě VACUUM všech databází). Úkolem pracovního procesu je zpracování provozních statistik všech tabulek v databázi a následný výběr tabulek určených k vacuování. Pracovní proces je na úrovni databáze sekvenční, a paralelní na úrovni clusteru. Nově také vacuum zajišťuje automatické volání VACUUM FREEZE (ochrana před přetečením rozsahu identifikátorů verzí řádků).

Rozšíření PL/pgSQL - RETURN QUERY a lokální systémové proměnné

Předchozí verze neumožňovaly vrátit množinu záznamů jako výsledek SRF funkce. Jediným řešením bylo volání příkazu RETURN NEXT pro každý řádek výsledku dotazu. V podstatě totéž (ale na nižší úrovni, tudíž efektivněji) provádí příkaz RETURN QUERY. Jeho parametrem je SQL dotaz. Výsledek tohoto dotazu (množina) se připojí k výstupu. Podobně jako RETURN NEXT neukončuje provádění funkce.

CREATE OR REPLACE FUNCTION dirty_series(m integer, n integer) 
RETURNS SETOF integer AS $$
BEGIN
  RETURN QUERY SELECT * FROM generate_series(1,m) g(i)
                 WHERE i % n = 0;
  RETURN;
END; $$ LANGUAGE plpgsq;

Další novou vlastností je lokální přenastavení (pouze pro konkrétní funkci) systémových proměnných. Podobně se chová T-SQL nebo MySQL, kde se implicitně ukládá aktuální nastavení systémových proměnných v čase registrace funkce. V PostgreSQL dosud podobný mechanismus nebyl. Lokálním přenastavením systémové proměnné search_path (resetem) lze konečně zabezpečit SECURITY DEFINER funkce. Zápis je zřejmý z následujícího příkladu:

CREATE FUNCTION report_guc(text) RETURNS TEXT AS
  $$ SELECT current_setting($1) $$ LANGUAGE sql
  SET regex_flavor = basic;

ALTER FUNCTION report_guc(text) 
  RESET search_path 
  SET regex_flavor = extended;

Podpora režimu Warm Standby - pokus o řešení replikace databáze

Počínaje verzí 8.2 lze replikovat databázový cluster exportem transakčního logu. Docela pochybuji, že se to používá. Nejde o multi-master replikaci jako v případě MySQL. Druhý systém je až do signálu nedostupný, tudíž tato metoda se dá použít jedině pro fail-over replikaci. 8.3 obsahuje příkaz pg_standby (ve stejnojmenném contrib adresáři), který zajistí udržení druhé instance PostgreSQL v režimu Warm Standby.

Master (postgresql.conf):
archive_command = 'cp %p ../archive/%f'
archive_timeout = 20        

Warm Standby (recovery.conf)
restore_command = 'pg_standby -l -d -k 255 -r 2 -s 2 -w 0 -t /tmp/stop /usr/local/pgsql/archive %f %p 2>> standby.log'

Po modifikaci konfiguračních souborů stačí spustit oba servery. Záložní server zůstane v recovery režimu, kdy pg_standby postupně podvrhuje segmenty transakčního logu (kopíruje exportované segmenty) a nedovolí dokončit obnovu záložní databáze. Teprve po signalizaci (existencí předem určeného souboru (v příkladu /tmp/stop)), pg_standby dovolí dokončení obnovy databáze a tím i přepnutí do stavu, kdy je záložní server schopen přijímat požadavky. Signální soubor musí vygenerovat uživatel postgres, tak aby jej pg_standby mohlo odstranit.

4916 ?        S      0:00 /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql2/data
4918 ?        Ss     0:00 postgres: startup process                                
5226 ?        S      0:00 sh -c pg_standby -l -d -t /tmp/aaa /usr/local/pgsql/archive 000000010000000000000018 pg_xlog/RECOVERYXLOG 2>> standby.log
5227 ?        S      0:00 pg_standby -l -d -t /tmp/aaa /usr/local/pgsql/archive 000000010000000000000018 pg_xlog/RECOVERYXLOG

Mírnou zradou je, že záložní cluster musí být klonem zálohovaného klusteru. Musí být vytvořen zkopírováním adresáře databázového clusteru. Je to docela logické, i když neintuitivní (přeci jen každý DBA má zafixovaný příkaz initdb). Zatím si nedovedu představit praktické použití replikace pomocí pg_standby. pg_standby nedokáže zachytit výjimku a tudíž ji nedokáže korektně obsloužit - jakákoliv chyba vede k přerušení procesu obnovy, tj. replikace (co hůř, ztrátě veškerých replikovaných dat na záložním serveru).

Regulární výrazy

Podpora reg. výrazů není v PostgreSQL novinkou. V 8.3 se objevily nové funkce regexp_matches a dvojice regexp_split_to_array a regexp_split_to_table. Je to malá, ale vítaná změna. Pro řadu úloh nyní nemusím používat plperl. Uvedu příklad. Při revizi jedné databáze jsem řešil pomalé dotazy (cca 5-10 sec) nad relativně malou (90 tis. záznamů) tabulkou. Jediná indexovatelná hodnota v dotazu byl seznam identifikačních čísel. Tato čísla se nacházela někde v XML dokumentu uloženém v záznamu (porušení 1.NF). Pro vyhledávání se používala následující podmínka:

objednava_v_xml LIKE '%<id>hledane_id</id>%'

První řešení, které mne napadlo, bylo použít fulltext. Fulltext je ale příliš univerzální. Existuje jednodušší řešení - pole identifikátorů aktualizované triggerem (také porušující 1NF, nicméně indexovatelné):

  NEW.objednavka_id_produktu := ARRAY(SELECT i[1] FROM regexp_matches(NEW.objednavka_v_xml,'<id>(\\d+)</id>','g' r(i));

Predikát v dotazech se upravil do tvaru:

objednavka_id_produktu @> ARRAY[hledane_id]

S GiN indexem nad sloupcem objednavka_id_produktu se provádění těchto dotazů zrychlilo na cca 1-20ms (v závislosti na dohledání stránek indexu v cache). Tato úprava je poměrně triviální, přičemž efekt je více než nezanedbatelný. Další, zatím netriviální řešení, je použití funkcionálního indexu nad funkcí XPath (nepotřebuji ovšem trigger):

CREATE OR REPLACE FUNCTION xml_list_to_int(xml[])
RETURNS int[] AS $$
SELECT ARRAY(SELECT to_number(($1[i])::text,'999999.99')::int 
                FROM generate_series(1, array_upper($1,1)) g(i)) 
$$ LANGUAGE SQL IMMUTABLE;
CREATE CAST (xml[] AS int[]) WITH FUNCTION xml_list_to_int(xml[]);
CREATE INDEX fx ON foo USING GIN((xpath('//id/text()',objednavka_v_xml)::int[]));

Ostatní změny

Nezanedbatelného rozšíření se dočkalo prostředí ecpg (PostgreSQL Embedded SQL/C Precompiler). Vylepšuje podporu prepared statements, nabízí auto prepare mód, pozicované proměnné. Jedná o zásadní změny - došlo ke změně verze z 4.4 na 6.0. Jedním z prvních backportů z EnterpriseDB je debugger a profiler PL/pgSQL. Nová verze pgAdminIII obsahuje grafické rozhraní pro tento debugger, které je zpřístupněno, pokud je v PostgreSQL nainstalován plugin s debuggerem (ke stažení na pgfoundry). Ve srovnání s moderními debuggery nabízí tento debugger PL/pgSQL pouze základní funkce - nicméně je to ohromný pokrok.

Soubor:Plpgsql-debugger.png

pavel=# LOAD '$libdir/plugins/plugin_profiler';
LOAD
pavel=# SET plpgsql.profiler_tablename = 'profilerstats';
SET
pavel=# SELECT line_number, sourcecode, time_total, exec_count, func_oid::regproc  
           FROM profilerstats 
           ORDER BY 1;
 line_number |      sourcecode       | time_total | exec_count | func_oid 
-------------+-----------------------+------------+------------+----------
           0 |                       |          0 |          0 | x
           1 | begin                 |          0 |          0 | x
           2 |   for i in 1..4 loop  |   0.000315 |          1 | x
           3 |     return next i;    |    9.8e-05 |          4 | x
           4 |   end loop;           |          0 |          0 | x
           5 |   return;             |      3e-06 |          1 | x
(6 rows)

Index advisor

Blbůstkou, která se do 8.3 dostala, je API pro tvorbu pluginů umožňujících monitorování plánovače dotazů. Zatím neexistuje žádný použitelný plugin, kromě Tomova prototypu. Nejedná se o žádný zázrak (Tom jej napsal během jednoho dne). Po jeho aktivici vidíme, kromě skutečného prováděcího plánu, i hypotetické plány zahrnující dosud neexistující indexy:

regression=# load '/home/tgl/pgsql/advisor';
LOAD
regression=# explain select * from fooey order by unique2,unique1;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Sort  (cost=809.39..834.39 rows=10000 width=8)
   Sort Key: unique2, unique1
   ->  Seq Scan on fooey  (cost=0.00..145.00 rows=10000 width=8)

 Plan with hypothetical indexes:
 Index Scan using <hypothetical index> on fooey  (cost=0.00..376.00 rows=10000 width=8)
(6 rows)

regression=# explain select * from fooey where unique2 in (1,2,3);
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on fooey  (cost=0.00..182.50 rows=3 width=8)
   Filter: (unique2 = ANY ('{1,2,3}'::integer[]))

 Plan with hypothetical indexes:
 Bitmap Heap Scan on fooey  (cost=12.78..22.49 rows=3 width=8)
   Recheck Cond: (unique2 = ANY ('{1,2,3}'::integer[]))
   ->  Bitmap Index Scan on <hypothetical index>  (cost=0.00..12.77 rows=3 width=0)
         Index Cond: (unique2 = ANY ('{1,2,3}'::integer[]))
(8 rows)

Ne všechno v projektu PostgreSQL je dokonalé. Mám pocit, že se PostgreSQL pomalu dostává do stavu, ve kterém se poslední dva roky nacházel Debian. Perfekcionalismus brzdí vývoj (daň za vynikající kvalitu kódu), což bere chuť experimentovat. V podstatě ubylo lidí, kteří si dovolí provést commit v depozitáři, takže se protahuje seznam čekajících záplat. V důsledku toho se přelévají záplaty z jedné verze do druhé. Začínají se ukazovat nevýhody triviální evidence a správy záplat založené na emailové konferenci a Bruceho evidenci. Svůj podíl na zpomalování vývoje má i fakt, že jednodušší problémy jsou už poměrně dobře vyřešené, tudíž aktuální patche nejsou ani malé, ani jednoduché. K tomu EnterpriseDB odsála možná třetinu vývojářů. A ti teď chybí. Hledají se další možnosti řízení projektu, ale jak to dopadne je ve hvězdách. Chce to rok, dva počkat, až se začne vracet kód z EnterpriseDB (ostatně z toho aktuálně dost těží Firebird (proj. Firacle a Vulcan)). V každém případě je 8.3 ukázkou dobře odvedené práce a patří k tomu nejlepšímu, co svět open source nabízí.

Co nás čeká

Docela by mne zajímalo, kam to spěje. Zatím se core hackers drží unixové strategie a odmítají přidávat do PostgreSQL vlastnosti, které primárně nesouvisí s ukládáním a správou samotných dat. Podpora XML byla trochu partyzánština - v podstatě jsem ani nečekal, že by se v jádře objevila. Není náhodou, že všichni zainteresovaní na podpoře XML jsou evropané. Plná implementace ANSI SQL 2003 je zatím nedostižná meta. Některým jejím částem (např. SQL/MM nebo SQL/OLAP) Tom Lane (nejvyšší guru všech PostgreSQL vývojářů) nemůže přijít na jméno, a přes něj nejede vlak. Vývoj hlavního stromu bude proto nejspíš mírně konzervativní (zaměřený na výkon a spolehlivost). To by nemělo tolik vadit. Vzhledem k licenci, tradicím a kvalitě kódu je PostgreSQL primární platformou pro experimentální RDBMS na univerzitách. Přestože se jedná o zajímavé projekty, do core se nic z nich nedostane. Málo kdy jsou kompatibilní s aktuálním kódem a migrovat hromady kódu se nikomu nechce. To je jeden z důvodů pro opuštění cvs, které komplikuje vytváření a údržbu experimentálních verzí. Těch pár, kterých zatím vzniklo, nemělo dlouhého trvání (vyjma komerčních), jelikož rychle přestaly být kompatibilní s kódem v hlavním stromu. Podporu rekurzivních a analytických dotazů slibují vývojáři docela dlouho, tak snad v 8.4 se dočkám. Výhledově perspektivní mi přijde proudové zpracování dat (např. projekt TelegraphCQ). Na rozdíl od objektových databází nebo XML databází, které vlastně nic zvláštního nepřinesly, přináší tento směr opravdu zcela novou a jednoduchou ideu do světa relačních sql databází. Proudovým databázím se poslední roky akademicky i komerčně věnuje prof. Stonebraker (pod jeho vývojem v Berkeley vznikl prapředek dnešního PostgreSQL) a jehož perfektní úsudek si asi těžko kdo dovolí zpochybnit. Kromě prof. Stonebrakera se tomuto tématu věnuje i Neil Conway, jeden z dalších aktivních core vývojářů. Takže bych se vůbec nedivil, kdyby hlavním tématem za dva tři roky bylo právě proudové zpracování dat.

Související články: