Názory k článku PostgreSQL 9.1 aneb stále vpřed

Osobně daleko nejlépe hodnotím možnost použít INSERT, UPDATE a DELETE v CTE. Díky tomu lze řešit spoustu věcí, na které byly třeba stored procedures.

Namátkou např. autoincrement v updatable pohledu sestávajícím se z více tabulek, nebo smazat data z jedné tabulky a zároveň ji vložit do druhé.

Druhá možnost je IMHO obzvlášť užitečná, protože když to chtěl člověk dřív dělat dvěma kroky a neměl repeatable read, tak musel řešit, aby toho nesmazal víc, než vložil.

Co mě v poslední době v postgresql chybělo.. teda spíš přebejvalo :-)... jsou různý omezení na rekurzivní CTE (např. zákaz limit, omezený možnosti joinování apod.)

Nazdar,
nechci flamovat, ale jak se tvarite na MySQL vs. PostgreSQL ? Na netu je porovnani dost, chtel bych jeste znat vas realnej nazor pri durazu na rychlost dotazu male databaze (v radu max. jednotek GB, ani to spise ne...)
Diky moc.
J.K.

Pokud bych extrémně zjednodušil situaci na pouze read-only přístup, tak lze orientačně počítat s tím, že u jednoduchých SQL dotazů bude MySQL cca o 1/3 rychlejší (v průměru TPC-B test), případně několika násobně pomalejší (raw SELECT) u nudloidních tabulek. S vyšší složitostí dotazů klesá rychlost MySQL a roste rychlost dotazů u PostgreSQL. U složitějších dotazů může být PostgreSQL několikanásobně rychlejší - při použití pohledů, poddotazů. PostgreSQL má oproti MySQL složitější "formát" pro ukládání dat na disk + nějaká režie, a komplexnější planner - v PostgreSQL je větší nabídka vestavěných algoritmů pro JOIN, pro poddotazy - hlavně je tam hasjoin a hashagg, umí lépe používat víc indexů v jednom dotazu. MySQL s InnoDB používá clustrovaný index, s MyISAM zase extrémně jednoduchý formát na disku. MySQL je dlouhodobě optimalizováno na jednoduché dotazy, PostgreSQL je zase dlouhodobě optimalizovano na složitější dotazy - takže záleží, kde se nacházíte. Při testování, kterého jsem se účastnil a kde se porovnával výkon 5.5 a 9.1 v nejhorších případech (pro pg) byl pg 2x pomalejší, v jiných byl naopak 5x 10x rychlejší - databáze na kterých se testovalo byly něco mezi 5-11G. Z testů se ukazuje, že je pro dlouhodobé uživatele MySQL minimálně zajímavé důkladněji otestovat chování a výkon aplikací v PostgreSQL - minimálně ten zákazník, který platí testování, tak do PostgreSQL jde.

Pokud se neomezíme jen na read-only přístup, tak je situace zase složitější. PostgreSQL se nehodí na ukládání krátkodobých netransakčních dat - tj. data, která třeba po půl hodině smažeme - na druhou stranu se PostgreSQL docela dobře vyrovná s komplexnějším multiuživatelským přístupem - většinou lépe než MySQL - takže opět záleží co děláte. V PostgreSQL už je několik let group commit, který se do MySQL teprve přidává - tím se docela dost snižuje zátěž systému a volná kapacita může přispět k vyšší rychlosti dotazů. Prostě ohledně výkonu není univerzální vítěz - každá databáze je lepší v té oblasti pro kterou je dlouhodobě optimalizovaná.

Pokud vím, tak v MySQL je mnohem širší paleta replikačních modelů. Teď si nejsem jistý, jestli to už má 5.5 - MySQL by mělo obsahovat thread-pooling - což by mělo zajistit lepší chování v ms-win při velkém počtu souběžně připojených uživatelů (v PostgreSQL se musí řešit externí aplikací - pgpool nebo bucardo). Jinak ve všem ostatním (si myslím,- je to můj názor, který tu jen prezentuji, ale nikomu nevnucuji) je PostgreSQL dál než MySQL - komfort pro vývojáře, administrátory, nabídce datových typů, rozšiřitelnosti, podpoře geodat, v uložených procedurách.

MySQL hodně ztratila bitvou s Oraclem (poté co Oracle koupil InnoDB). Zase dneska existuje několik týmů, které se snaží optimalizovat výkon MySQL - v Googlu, ve Facebooku, v Perconě, v Oraclu (dost často jsou to lidé z původní MySQL a.b) - těm jde ale primárně o surový výkon elemntárních dotazů a monitoring a o nic jiného. A pár malých firmiček se snaží uspět se specializovanými engines - do low level optimalizace MySQL se asi dost investuje - hodně firem je na MySQL životně závislá a aktuálně není situace ohledně MySQL nijak zvlášť přehledná. PostgreSQL jde trochu jinou cestou - spíš než na web se orientuje na vnitropodnikové IS, telco, finance nebo embeded databáze u náročnějšího hw - rengeny, ústředny, ..

sorry - napsal jsem to špatně "případně několika násobně pomalejší (raw SELECT) u nudloidních tabulek".

Správně - MySQL může být několikanásobně rychlejší - 2x - 10x (raw SELECT) u nudloidních tabulek - (úzké velké tabulky o dvou třech int sloupcích)

Přecijenom jsem si na jednu hodně otravnou vlastnost vzpomněl. Pokud je trigger procedure, tak při insertu není definován OLD tuple. Takže nejde udělat unvierzální trigger pro insert/update (respektive se tam v každý podmínce musí zjištovat zdali jde o insert nebo update, což je opruz).

Nešlo by s tím něco dělat? Úprava je to triviální a přispěla by k čitelnějšímu kódu. Nebo to jde nějak řešit, popř mi něco ušlo?

Tohle je zrovna věc, kterou nelze udělat, tak aby to bylo sémanticky správně - v PL/pgSQL a v SQL obecně se nerozlišuje mezi NULL hodnotou a složenou hodnotou, kde všechny položky jsou NULL - tudíž nelze použít NULL ve významu 'nedefinováno' nebo neplatné - tudíž fakticky, pokud píšete univerzální trigger, tak stejně musíte kód rozdělit do větví, podle události. Souhlasím s Vámi, že je to opruz, ale myslím si, že je to správně, protože to vede ke správnému kódu. Já osobně jsem během té doby, co dělám s Postgresem přešel od univerzálních triggerů a procedur k jednoúčelovým adresným triggerům a funkcím. Pokud zrovna nepíšete univerzální framework, tak s generickými triggery (čest výjimkám) narazíte - a jde to i proti smyslu uložených procedur (tak jak to chápu já). Každá uložená procedura včetně triggeru by měla řešit specificky jeden specifický problém a přistupovat stále ke stejným tabulkám - hlavně, když používáte PL/pgSQL. Tím si dost zjednodušíte život. V C lze psát genericky, ale v PL/pgSQL to moc nejde, resp. lze, ale musíte nadužívat EXECUTE - a výsledek může být dost pomalý a nečitelný.

Omlouvám se, tady Vám život neusnadním - pokud chcete psát univerzální triggery, tak je nepište v PL/pgSQL, ale v PL/Perlu nebo PL/Pythonu nebo v C. Dost věcí totiž v PL/pgSQL principiálně (z důvodu kešování prováděcích plánů) nelze napsat elegantně nebo efektivně.

Díky za odpověď. Mě jde o triviální a přitom hrozně častý případ, kdy trigger reaguje na změnu nějaké hodnoty. Např. proto, aby jí zkontroloval, nebo aby vykonal nějakou akci. Nebo naopak o "úklid" při změně či rušení objektu. V drtivém počtu případů je přitom třeba něco dělat, pokud se hodnota změnila, přičemž stav NULL je ekvivalentní stavu před založním objektu. Nejde tedy o nějaké generické triggery, ale právě o malé jednoúčelové funkce.

V současné době musím udělat pro každou operaci insert/update/de­lete jeden trigger, což vede k nečitelnému a hůř udržitelnému kódu (jedna kontrola je na třech místech), nebo tam musí bejt podmínka ve stylu
(TG_OP = 'UPDATE' AND .... ) OR (TG_OP = 'INSERT' AND ...) OR
což je nečitelné. Přitom by stačilo:
(NEW.value IS DISTINCT FROM OLD.value)

jen kdyby šlo nějak rozumně pracovat s OLD/NEW, když je nedefinované. Napadá mě spousta variant, jak to udělat, např.

- Nedefinované OLD/NEW by bylo NULL, tak, jak to už od verze 8.4 tvrdí dokumentace :-)
>> This variable is NULL in statement-level triggers and for INSERT operations.
Chápu, proč se Vám to systémově nelíbí, ale vzhledem k tomu, že v současném stavu nelze OLD tam, kde je nedefinované používat, tak by tato úprava nic nerozbila, kód, který by mohl být po úpravě špatně by před úpravou neběžel vůbec. Pro rozlišení, zdali má OLD = NULL význam nedefinováno nebo definováno, ale NULL, by šlo užít TG_OP stejně jako dnes.

- Kdyby to i přesto vadilo, tak doplněním nějakého přepínače do definice funkce, kterým by si člověk mohl nové chování explicitně zapnout. Např. zápis v následujícím tvaru by byl hezký a myslím i vcelku konzistentní (defaultní hodnota parametru).
function f(OLD = NULL, NEW = NULL) returns trigger

- Vytvořením funkce ekvivalentní COALESCE, která by z nedefinované hodnoty udělala definovanou. Podmínka typu
(NEW.value IS DISTINCT FROM defined(OLD,NUL­L).value)
nebo ještě lépe
(NEW.value IS DISTINCT FROM defornull(OLD)­.value)
je furt daleko čitelnější, než to, co se musí psát dnes.

Kdyby byl typ record podobnější refernci v Javě nebo ukazateli v C, tak by o tom šlo uvažovat. Ale protože není, tak si myslím, že by to nadělalo větší paseku než užitek. PL/pgSQL vychází s ADY a je snaha, aby potenciálně chybný kód šel napsat hůře i za cenu větší ukecanosti správného kódu.

Pokud Vám vadí nutnost testování TG_OP, tak piště triggery čistě pro jednotlivé události - nemá to žádný vliv na výkon. Musíte napsat víc omáčky :( ale zase kód může být o něco jednodušší a o fous rychlejší. Navíc pokud chcete porovnávat NEW a OLD tak je mnohem lepší tyto hodnoty porovnat mimo trigger - skrze podmíněný trigger. Tím můžete snížit počet volání triggeru (následně počet inicializací plpgsql) a urychlit provádění příkazu.

http://developer.postgresql.org/pgdocs/postgres/sql-createtrigger.html

CREATE TRIGGER log_update
    AFTER UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE log_account_update();

Podmíněný trigger to někdy částečně řeší, jenže...
V definici podmíněného triggeru taktéž můžu použít OLD jen v UPDATE triggeru. Takže sice napíšu jednu proceduru, ale zas místo jednoho triggeru tři. Navíc budu mít podmínku a z ní vyplývající akci na jiném místě, takže se čitelnost zhorší. Kdyby alespoň v postgresql existovali anonymní procedury (něco ve stylu):

CREATE TRIGGER ..... EXECUTE BLOCK $$ $$;

Další problém je v tom, že např. někdy tyto triggery udržují např. nějakou předpočítanou sumu apod. A v tom případě potřebuji vypočíst něco ve stylu
COALESCE(NEW.value) - COALESCE(old.va­lue): takže tady opět i s podmíněným triggerem skončím u tří procedur. Nebo mohu to old.value a new.value předat jako parametr, ale tím se kód stane opět nepřehlednějším.

To, že se PLPGSQL snaží být maximálně bezpečný beru - u DB jazyka je to jen dobře. Ale nezdá se mi, že by explicitní vyžádání toho, aby bylo NEW definované na NULL, když jde o DELETE trigger nějak narušilo bezpečnost jazyka. Nemá to žádné sideefekty, jde pouze o definici jedné proměnné a její inicializaci na NULL. (A také by se sladil skutečný stav s dokumentací, byť uznávám, že opravit dokumentaci je asi snažší... jen to, že se to tam objevilo mi asi zavdalo plané naděje....).

PS: Ale přes ty všechny výhrady na postgresql po přechodu z mysql slintám blahem, to zas jo :-) Abych zas nevypadal jak nějakej nespokojenec.

A není nejjednodušší si nadefinovat vlastní proměnnou s rowtype dané tabulky, na začátku si to nastavit jak potřebuji (na NULL v případě insertu, na OLD jinak), a pak porovnávat už tu proměnnou. Něco jako toto

create table tx (i int, j int);

create or replace function tx_trigger() returns trigger as $$
declare
v_old tx%rowtype;
begin

IF (TG_OP = 'INSERT') THEN
v_old := NULL;
ELSE
v_old := OLD;
END IF;

IF (v_old.i = NEW.i) THEN
raise notice 'equals';
END IF;

RETURN NEW;

END;
$$ LANGUAGE plpgsql;

create trigger tx_trig before insert or update on tx for each row execute procedure tx_trigger();

To samozřejmě jde, ale dělat to na začátku každého jednoúčelové triggeru je hrozná opičárna :-).Kdyby se všechno dělalo takhle tak to by bylo jednodušší dělat všechnu logiku na straně klienta.

I když musím říct, že čím dál tím víc přemýšlím nad vytvořením nějakého šablonovacího jazyka pro postgresql. Prostě sadu maker, který by překládali jednoduchý konstruklce na ukecanost PLPGSQL :-).

Vlastní šablonovací jazyk není úplně běžná záležitost, ale není to zas úplně neobvyklé. Např. zdrojáky PostGISu se prohánějí sedem před tím než se pošlou do pg. Vím o několika projektech, kde mají generátor tabulek - který jim vegeneruje CREATE TABLE a všechny potřebné triggery a funkce. No a já jsem dělal na jednom projektu, kde byl v PL/pgSQL napsán jazyk, který podporoval objekty, metody a workflow, a který se překládal do PL/pgSQL.

SERIALIZABLE úroveň je už ze své podstaty pomalejší, navíc musíte aplikaci připravit na to že transakce mohou běžně padat protože nejsou serializovatelné (tj. aplikace by je měla být schopna zopakovat).

Primárně si musíte rozmyslet jestli anomálie které se standardním READ COMMITED módu mohou objevit (non-repeatable reads a phantom reads) jsou pro vaši aplikaci problém nebo ne (a pokud to jde tak to rovnou psát tak aby nebyly). Pokud vám stačí READ COMMITED, není důvod nastavovat SERIALIZABLE.

Jiná otázka je optimalita implementace, nicméně neočekávám že by tam byly nějaké zásadní boty které by to řádově zpomalovaly.

A nezapomínejme že se to dá nastavovat pro jednotlivé transakce, takže aplikace může běžet jako READ COMMITED ale pár vybraných procesů může běžet jako SERIALIZABLE.

Těžko říct - PostgreSQL je první databází, kde se produkčně nasazuje SSI řešení - takže zkušenosti z ostatních db nejsou relevantní. V každém případě bude SERIALIZABLE o hodně náročnější než býval v předchozích verzích - přičemž ale v předchozích verzích měla tato úroveň téměř nulovou režii v porovnání s ostatními db. SSI ještě nikdo nezkoušel - zveřejněna byla teprve v roce 2009.