Lehký úvod do PostgreSQL



Silné stránky PostgreSQL:
spolehlivost (kriteria ACID)
zajištění referenční, doménové a entitní integrity
bohatá podpora ANSI SQL (plná podpora příkazů SELECT, pohledy, úložné procedury, triggery)
vysoká propustnost v silně konkurenčním prostředí

Základní rysy:
MVCC
referenční integrita (FOREIGN, UNIQUE, CHECK)
široká škála datových typů
vlastní objekty
úložné procedury v mnoha jazycích
indexy
dědičnost
partitioning
triggery
pravidla
sekvence

Slabé stránky PostgreSQL:
menší výkon u velkého množství modifikovaných řádků
složitější replikace a pouze master -> slave
slabá podpora u webhostingů
ACID:
Atomičnost - v rámci transakce se provedou všechny změny nebo žádná.
Konzistence - transakce zajišťují převedení dat z jednoho konzistentního stavu do druhého. Tato podmínka nemusí platit uvnitř transakce.
Izolace - transakce není ovlivněna souběžnými transakcemi.
Trvanlivost - pokud je transakce potvrzená, pak jsou změny dat trvalé a to i pokud nastane havárie systému. 
MVCC:
Nevýhodou řešení založeném na zamykání (tabulek, stránek, řádků) jsou možné výkonnostní problémy. Řešením je MVCC architektura, kdy namísto zamknutí modifikovaného řádku (ať operací Update nebo Delete) se provede kopie řádku - vytvoří se nová verze.
Nevýhody:
Nutnost mazat neplatné záznamy (VACUUM).
Několikanásobně menší rychlost ukládání a čtení z databáze.
Výhody:
Vysoká prostupnost v silně konkurenčním prostředí.
Velice levný COMMIT a ROLLBACK.
Indexy:
jednoduchý index - jeden sloupec
složený index - více sloupců
částečný index - pouze nad částí tabulky
funkční index - nad výsledkem funkce

Formáty indexů:
B-tree
Hash
GiST a GiN

Pavel Stěhule radí:
Indexy navrhujte pouze pro sloupce s vysokou selektivitou.
Kandidátem na index by měl být každý sloupec s cizím klíčem. PostgreSQL je sám nedělá.
Neoupžívané indexy odstrante.
Preferujte jednoduché indexy.
Indexy navrhujte pro tabulky nad cca 1000 řádků.
Aktualizujte statistiky po velké změně dat.
Pravidelně reindexujte databázi.

Limity
maximální velikost tabulky: 32 TB
maximální velikost řádku: 400 GB
maximální velikost položky: 1 GB
maximální počet sloupců v tabulce: 250-1600 (v závislosti na verzi a typu)

Indexy se drží v cache, takže první dotaz trvá déle než druhý. U jiných databází (MySQL) rozdíl nemusí být tak znatelný.
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]

Parametry:
UNIQUE
CONCURRENTLY
Index Storage Parameters:
FILLFACTOR
Václav Novotný