Zobrazení zdroje stránky Desatero

Autor: [[Pavel Stěhule|Pavel Stěhule]], 1.11.2007 (aktualizováno 14.1.2020)

Dobře nakonfigurovaná a dobře provozovaná databáze se Vám odvděčí perfektním, stabilním a spolehlivým výkonem. To ostatně platí pro každou aplikaci. Následující desatero obsahuje doporučení a odkazy, co a jak dělat (nastavit) v PostgreSQL, aby databáze mohla běžet rychle a se stabilním výkonem.

== <b>Vacuujte</b> ==
S mírnou nadsázkou by se dalo konstatovat, že příkaz [https://www.postgresql.org/docs/current/sql-vacuum.html VACUUM] je v PostgreSQL nejdůležitějším příkazem. Ve starších verzích Postgresu bylo nutné tento příkaz pouštět ručně, nyní většinu práce zastane pomocný proces Postgresu pojmenovaný [https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM autovacuum]. Ze zkušenosti ovšem vím, že není na škodu příkaz <i>VACUUM ANALYZE</i> případně příkaz <i>VACUUM FREEZE</i> pustit ručně (i za slabšího provozu). <i>Po upgrade major verzí Postgresu je nutné ručně pustit ANALYZE</i>. <i>VACUUM FULL</i> znovu vytváří tabulky (a indexy) v ideálním stavu (bohužel na větších datech vyžaduje odstávku aplikace). Tento příkaz je obdobou jakési defragmentace, kterou známe ze souborových systémů. 

Pozor - u dočasných (temporary) tabulek je nutné vždy pouštět <i>VACUUM ANALYZE</i> ručně.

Příkaz VACUUM FULL můžete urychlit odstraněním indexů. Po dokončení příkazu indexy znovu vytvoříte.

* http://www.pgsql.cz/skoleni/skoleni_postgres_web.pdf
* http://www.postgresql.org/docs/9.4/static/monitoring-stats.html
* http://www.postgresql.org/docs/9.4/static/pgstattuple.html

Komerční odkaz: [[Jednodenn%C3%AD_%C5%A1kolen%C3%AD_PostgreSQL#PostgreSQL_efektivn.C4.9B.2C_v.C5.A1eobecn.C3.A9_z.C3.A1klady|Všeobecné školení PostgreSQL]]

== Analyzujte ==
Pro každý SQL příkaz existuje tzv. prováděcí plán. Je to posloupnost nízkoúrovňových příkazů, jejichž provedení zajistí korektní naplnění výsledné množiny. Ke každému netriviálnímu SQL příkazu existují desítky až tisíce různě efektivních prováděcích plánů. Výběr jednoho konkrétního, který se bude provádět, je úkol tzv. optimalizátoru. Optimalizátor, použitý v PostgreSQL, vychází ze statistik. Ke každému sloupci se udržuje histogram hodnot a orientační počet řádek. Pak se relativně snadno určí efekt podmínky. Pokud ale statistiky neodpovídají reálným datům, pak optimalizátor 100% nevybere optimální prováděcí plán. Tento problém se identifikuje snadno. V detailu prováděcího plánu EXPLAIN ANALYZE se rozchází odhady počtu zpracovávaných řádků a skutečného počtu zpracovávaných řádků.

O aktualizaci statistik se postará příkaz [http://www.postgresql.org/docs/current/interactive/sql-analyze.html ANALYZE]. Ten by se měl spouštět při změně 20&nbsp;% řádků (samozřejmě, že vždy po každém importu). Stejně jako VACUUM i příkaz ANALYZE sekvenčně prochází tabulku. Celkem logicky se spojily do jednoho příkazu VACUUM ANALYZE. Přesnost odhadu závisí na počtu tříd v histogramu. Výchozí hodnota 100 tříd (default_statistics_target) nemusí být pro některá data dostatečná a doporučuje se zvýšit na 100-200 tříd (maximum je 10000). Naopak se nedoporučuje tuto hodnotu zvyšovat více než je nutné, dochází ke zpomalení optimalizace (optimalizátor musí zpracovat větší objem vstupních dat).

Proces autovacuum zajišťuje automatické spuštění příkazu ANALYZE v případě dostatečného počtu modifikovaných řádků v tabulce. Na dočasných tabulkách je nutné tento příkaz pouštět ručně. Z praxe není na škodu cca 1x týdně (záleží na provozu) příkaz ANALYZE pustit ručně (plánovaně z cronu) při slabším provozu. U malých tabulek - do 50 řádků se VACUUM ANALYZE nevolá automaticky. Po importu číselníků je vhodné zavolat VACUUM ANALYZE (případně alepoň ANALYZE) explicitně (ručně).

Pozor - až do verze 12 (včetně) se autovacuum rozhoduje jen na základě poměru počtu změněných řádků vůči celkovému počtu. U tabulek, kde dochází jen k vložení nových řádků, nedojde k automatickému spuštění příkazů VACUUM a ANALYZE.

Pozor - v případě havárie Postgresu dojde k resetu provozních statistik, kterými se řídí autovacuum. Může tak dojít k situaci, že by se autovacuum mělo spustit, ale nespustí se. Není proto na škodu po resetu provozních statistik nebo po havárii pustit ručně příkaz ANALYZE. K resetu statistik také dochází při povýšení (upgrade) Postgresu.

Pozor - partišnované tabulky také mají statistiky, ačkoliv nemají žádná data (data jsou uložená v partišnách). Díky tomu, že jsou prázdné, tak jsou mimo zájem procesu autovacuum, a není na škodu, jednou za čas, pustit na rodičovských partišnovaných tabulkách příkaz ANALYZE ručně.

== Optimalizujte ==
<i>Správně navržená aplikace nesmí generovat pomalé dotazy!</i> Každý pomalý dotaz alokuje zdroje serveru a zpomaluje provádění ostatních dotazů. Za pomalý dotaz se považuje dotaz, jehož zpracování trvá déle než 200&nbsp;ms (pro interaktivní aplikace z důvodu uživatelského komfortu). Věřte, že to není až tak nereálné kritérium. Samozřejmě, že existují pomalejší dotazy, ale ty by se neměly vyskytovat v interaktivních aplikacích a v pracovní době. Jelikož také tady platí pravidlo 80/20, které v tomto případě znamená, že 20&nbsp;% příkazů generuje 80&nbsp;% zátěže, stačí se zaměřit na 20&nbsp;% dotazů, které generuje aplikace. S jejich identifikací nám pomůže [https://github.com/milo/pgFouine pgfouine] nebo novější [http://dalibo.github.io/pgbadger/ pgBadger]. <i>V případě typických www aplikací je zásadní použití cache</i>. Optimalizace dotazu spočívá ze 70&nbsp;% přidáním indexů, z 20&nbsp;% přepsáním dotazu (aby se aplikoval index) a zbylých 10&nbsp;% představuje změnu přístupu (např. ochranu proti vygenerování pomalých dotazů vynucením zadání dostatečného počtu kritérií, omezením výpisů na určité měsíce, LIKE nahradit fulltextem, atd). <i>Využívejte podmíněné a funkcionální indexy</i>.

Vývojovou databázi nadimenzujte na předpokládaný objem po 5-10 letech provozu systému. Indexy přidávejte uvážlivě. Každý index může zásadně urychlit provádění dotazu, ovšem za cenu mírného zpomalení ostatních operací (aktualizace indexu má také režii). Index nad primárním klíčem je automaticky, indexem nad cizím klíčem zpravidla nic nezkazíme (pokud má tabulka alespoň 1000 řádků). Z výpisu detailu prováděcího plánu zjistíme, které indexy se použily, které tabulky se čtou sekvenčně, atd. Výpis získáme příkazem EXPLAIN ANALYZE. Před detailní analýzou nezapomeňte spustit [http://www.postgresql.org/docs/current/interactive/sql-explain.html|VACUUM ANALYZE]. S optimalizací souvisí normalizace. Nenavrhujte široké tabulky - je to jedna se začátečnických chyb.

Hodnota ''efective_cache_size'' je ve výchozí konfiguraci zbytečně nízká. Na základě této hodnoty se optimalizátor rozhoduje mezi použitím sekvenčního čtení a čtením s pomocí indexu. Použití indexů je náročnější, a proto systém upřednostní sekvenční čtení, pokud je jen malá naděje (nízká hodnota ''efective_cache_size''), že se index udrží v cache. ''efective_cache_size'' by měla odpovídat velikosti paměti použité v systému jako disková cache. Pro dedikované servery se hrubě doporučuje 2/3 RAM.

* http://www.root.cz/clanky/zakys-jmenem-flattening/
* [[%C3%9Avod_do_PostgreSQL_SQL#Optimalizace|Optimalizace v PostgreSQL]]
* [[Čtení prováděcích plánů v PostgreSQL]]
* [[Používání indexů v PostgreSQL - krátce a pro začátečníky]]
* http://www.gtsm.com/oscon2003/toc.html
* http://neilc.treehou.se/optimizer.pdf
* http://momjian.us/main/writings/pgsql/performance.pdf
* http://pooteeweet.org/files/phpworks06/explaining_explain.pdf
* http://explain.depesz.com/ - www aplikace umožňující snadné sdílení prováděcích plánů a jejich základní analýzu

Bez dobré znalosti SQL není možné psát rychlé dotazy, tudíž i rychlé aplikace - tedy naučte se dobře SQL:
* [[LEFT_INNER_JOIN|Spojování tabulek v SQL]]
* [[Korelované vnořené dotazy]]
* [[Moderní SQL]]
* http://www.pgsql.cz/skoleni/skoleni_sql_web.pdf

Zapisujte podmínky jako tzv čisté predikáty - pro ně optimalizátor může použít index, tj
<pre>
--špatně
a + 1 = 10
EXTRACT(YEAR FROM d) = 2018 AND EXTRACT(MONTH FROM d) = 11

--dobře
a = 9
d >= '2018-11-01' AND d < '2018-12-01'
</pre>

Vyvarujte se ORM. Pokud jej již používáte, ověřte si, jakou skutečnou zátěž generuje:
* http://php.vrana.cz/snizeni-poctu-dotazu-v-orm.php

<b>Pro primární data nepoužívejte EAV (Entity-Attribute-Value) tabulky nebo také key/value model</b> - pokud potřebujete pracovat s neznámým počtem atributů použijte modul [http://www.postgresql.org/docs/9.0/static/hstore.html hstore].

Snažte se, aby Vaše databáze byla normalizovaná - nepoužívejte příliš široké tabulky.

Komerční odkaz: '''[[Služby|Optimalizace a ladění databáze]], [[Jednodenn%C3%AD_%C5%A1kolen%C3%AD_PostgreSQL#PostgreSQL_efektivn.C4.9B.2C_z.C3.A1klady_SQL|Školení PostgreSQL efektivně - Základy SQL]]'''

==Používejte cache==
SQL příkaz, který, díky cache, ušetříte je nejrychlejší a nejméně zatěžuje databázi. Do cache ukládejte krátkodobá netransakční data - např - http sessions, do cache ukládejte agregovaná data, v případě zatižených aplikací do cache  ukládejte vše, co lze uložit do cache. Snažší (ohledně správy cache) je plnění cache ještě před vlastním požadavkem na obsah - změní li se data, změním obsah cache. Jiný přístup - invalidace cache je většinou náročnější na implementaci - aplikace musí podporovat oba přístupy - po restartu je cache prázdná. Aplikace jako je např. wikipedie používá 160GB memcached. 

* http://memcached.org/
* http://pgfoundry.org/projects/pgmemcache/
* http://zdrojak.root.cz/clanky/zrychlete-sve-webove-aplikace-s-memcached/

== Přidělte PostgreSQL dostatek paměti ==
Paměti není nikdy dost. To platí i pro PostgreSQL. Pravidlo č. 1. <i>Server, na kterém běží PostgreSQL, nesmí nikdy swapovat</i>. Pravidlo č. 2. Čím je více paměti, tím větší může být vyrovnávající paměť používaná na načtená data z disků. Použití vyrovnávající paměti má zásadní význam při víceuživatelském režimu - je docela pravděpodobné, že uživatelé budou chtít stejná data, a čtení z vyrovnávající paměti je řádově rychlejší než čtení z disku. Jelikož operační paměť je dost omezená, nelze udržovat v paměti veškerá přečtená data. Existují sofistikované algoritmy, které určují, které stránky vyrovnávající paměti se uvolní a použijí pro nově načtená data. Na rozdíl od operačního systému, který také používá vyrovnávající paměť, tyto algoritmy v PostgreSQL jsou optimalizovány pro databázové operace. Doporučuje se zhruba 1/3 operační paměti vyhrazené pro PostgreSQL použít jako vyrovnávající paměť (shared buffers). Výchozí velikost shared_buffers 28 MB je bezpečná pro start PostgreSQL (PostgreSQL nastartuje, operační systém má téměř vždy vyšší limity, takže tuto paměť přidělí), pro provoz je zbytečně nízká. Počínaje 9.3 je výchozí velikost 128MB, což je mnohem lepší, přesto ale pro dedikované servery neoptimální (příliš nízká hodnota).

Zbývající paměť (work_mem) vyhrazená pro PostgreSQL se používá pro zpracování db operací (třídění, slučování). Opět platí, že čím větší je přidělená operační paměť, tím je pravděpodobnější, že se úloha bude moci zpracovat výlučně v operační paměti, čímž se několikanásobně zrychlí. Hledáme rozumný kompromis mezi maximálním počtem klientů a maximální velikostí operační paměti vyhrazené jednomu klientu. <i>Tyto parametry nesmí překračovat fyzické limity</i>. Je to pojistka proti swapování operačního systému, a pokud neodpovídají realitě, tak se systém nezhroutí, ale začne swapovat. Zákonitě se zvýší několikanásobně odezva. Na zatíženém systému se začnou hromadit nezpracované požadavky a posléze může systém přestat komunikovat. 

Příklad: Dedikovaný server s 8G operační paměti. Počáteční konfigurace:
<pre>
operační systém .... 0.5G
vyrovnávací paměť .. 4.0G
maximální počet klientů .... 200
pracovní paměť pro klienta . 17.5M 
</pre>

Ukázka chybné konfigurace - v důsledku použití chybného návrhu aplikace došlo k neuvolňování spojení. Na tuto situaci administrátor reagoval zvednutím maximálního počtu klientů:
<pre>
operační systém .... 0.5G
vyrovnávací paměť .. 4.0G
maximální počet klientů .... 400
pracovní paměť pro klienta . 17.5M 
</pre>
V důsledku tohoto zásahu se situace jenom razantně zhoršila. PostgreSQL vytvořil dalších 200 spojení, pro které už nezbývala fyzická paměť (systém swapoval 3.5G). Posléze došlo k zahlcení serveru, následoval vynucený restart.

Korekce - a) oprava návrhu aplikace, b) realitě odpovídající konfigurace (zvýšení počtu klientů musí odpovídat snížení pracovní paměti):
<pre>
operační systém .... 0.5G
vyrovnávací paměť .. 4.0G
maximální počet klientů .... 400
pracovní paměť pro klienta . 8.75M 
</pre>

Rozdělení paměti lze popsat rovnicí:
<pre>
    maximalni pocet klientu * 2 * pracovni pamet + vyrovnavaci pamet = vyhrazena pamet pro PostgreSQL
</pre>

Optimální hodnoty musí vycházet z konkrétní aplikace, hw vybavení, objemu dat.

<i>Doporučení, která naleznete na internetu, si ověřte testováním.</i> Ne všechno, co je na internetu k dispozici, je pravdivé, správné a aktuální. Při vývoji dochází k odlišnostem v chování, citlivosti na parametrech, limitech různých verzích PostgreSQL. To, co platilo pro PostgreSQL 7.4, neplatí v některých bodech pro verzi 8.2. Navíc některá doporučení jsou spojena s určitou aplikací, určitým charakterem zpracování dat, a mohou být zavádějící (nesmyslná) ve Vaší aplikaci nebo na Vašem hw.
* http://www.pgsql.cz/skoleni/skoleni_administrace_web.pdf
* http://www.varlena.com/GeneralBits/Tidbits/perf.html
* http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html
* http://edoceo.com/liber/db-postgresql-performance.php
* http://www.powerpostgresql.com/download/TFCKUpload/5.x-pdf
* http://joeconway.com/terabytes_osc2005.pdf
* http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Výkon PostgreSQL závisí na výkonu celého systému. Nelze předpokládat, že pokud "přidusíte" o.s. (např. jej přinutíte swapovat), že PostgreSQL poběží rychle. Všechny parametry systému musí být v rovnováze. Paměť není jediná hw komponenta ovlivňující rychlost. Významnou roli hrají rychlosti disků a vlastnosti diskového řadiče. Mám empiricky ověřeno, že čím je hw sofistikovanější (obvykle i dražší), tím důležitější je jeho správná konfigurace. Kromě vlastních testů můžete použít program pgbench.

<b>Pro o.s. Windows neplatí doporučení maximalizovat shared_buffers!</b> Naopak, doporučuje se nastavit shared_buffers na co možná nejnižší hodnotu (s 4GB RAM cca 64-128MB).

A aby to nebylo tak jednoduché, existuje další výjimka. Pokud provozujete databázi, kde dochází k intenzivním změnám (opravdu intenzivním, např. od víc než stovky připojených uživatelů atd), a používáte PostgtreSQL 8.2 a starší, tak zvýšení <i>shared_buffers</i> může způsobit problémy se zvýšenou zátěží způsobenou checkpointy. Tento problém se již nevyskytuje u verze 8.3. O tomto více a o správné konfiguraci v tomto případě se dočtete ve článku http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm.

S novými servery s operační pamětí o velikosti stovek GB se můžou nežádoucí problémy objevit znovu. Obyčejně je vhodné omezit share buffers na cca 20-50GB - v závislosti na typu zátěže (při intenzivních zápisech spíše méně), a snížit limity pro aktivaci čištění cache souborového systému (tak aby nedocházelo k zahlcení IO z důvodu zápisu špinavých bloků cache). Servery s opravdu velkým množstvím paměti by měl konfigurovat expert na správu paměti a souborových systémů pro daný operační systém (problémy se projevují latencemi na IO systémech, případně nárůstem spinlocků a tím razantním poklesem výkonu serveru). Obecně při velké zátěži (desítky tisíc transakcí za sec) není vhodné pracovat s často modifikovanými, případně dočasnými, netransakčními daty v PostgreSQL. 

<b>pokud máte větší hodnotu shared_buffers (nad jednotky GB) zkontrolujte si, že PostgreSQL pro ně používá [https://franckpachot.medium.com/did-you-forget-to-allocate-huge-pages-on-your-postgresql-server-7a97e7727b03 huge pages].</b>

Komerční odkaz: '''[[Jednodenn%C3%AD_%C5%A1kolen%C3%AD_PostgreSQL#PostgreSQL_efektivn.C4.9B.2C_administrace|Školení administrace PostgreSQL]]'''

==Monitorujte==
Rychlost databáze je úměrná rychlosti IO a v případě přetížení IO výkon databáze rychle klesá. Monitoring IO vám umožní rychle tuto situaci rozpoznat.
* http://serverfault.com/questions/699583/munin-disk-utilization-calculation
* https://www.root.cz/clanky/monitoring-rdbms-postgresql/

==Časté checkpointy zvyšují zátěž IO==
Výchozí nastavení PostgreSQL je hodně konzervativní (šetrné na zdroje). Při větší zátěži ovšem může způsobovat neefektivní práci s IO. Proto se doporučuje zvýšit proměnnou checkpoint_segments (pro začátek) na hodnotu 32 - což znamená, že po naplnění 32 16MB segmentů transakčního logu dojde k tzv. CHECKPOINTu, kdy dojde k zápisu všech špinavých stránek cache. CHECKPOINT by se měl aktivovat zhruba po 5 minutách. S IO souvisí další konfigurační proměnná, která je ve výchozí konfiguraci zbytečně nízká - wal_buffers, kterou na počítačích s RAM > 4G můžeme nastavit alespoň na 2MB (v novějších verzích PostgreSQL se výchozí hodnota wal_buffers nastavuje dynamicky (hodnota -1) a lépe podle velikosti shared_buffers).

Pozn. V novějších verzích by měla být konfigurace checkpointů pro běžný provoz ve výchozím nastavení dostatečná.

== Reindexujte ==
Změny v databázi vedou jak ke fragmentaci datových souborů tak ke fragmentaci indexů. Odstraněním a opětovným vytvořením indexu dojde k defragmentaci a optimalizaci indexu. Toto je přesně smyslem příkazu [http://www.postgresql.org/docs/8.2/interactive/sql-reindex.html REINDEX]. Stejně jako u příkazu VACUUM FULL doporučuje se jej spouštět 1x měsíčně. U novějších verzích PostgreSQL příkaz VACUUM FULL zároveň vytvoří i nové indexy, tudíž pokud používáte VACUUM FULL, pak není nutné reindexovat. Pokud nemůžete na nezbytně nutnou dobu zamknout tabulky (REINDEX i VACUUM FULL vyžadují exkluzivní zámky), můžete reindexovat vytvořením nových indexů příkazem CREATE INDEX CONCURRENTLY a zrušením starých indexů příkazem DROP INDEX CONCURRENTLY. 

Diagnostiku defragmentace indexu [[SQL_Triky#Vyu.C5.BEit.C3.AD_funkc.C3.AD_pgstattuple_as_pgstatindex|poskytne]] funkce getstatindex z modulu pgstattuple. Spouštění reindexace má smysl, pokud hodnota u avg_leaf_density je menší než 50%, a pokud je index větší než 10 datových stránek (80KB).

== Aktualizujte PostgreSQL (upgrade) ==
Na databázovém jádru PostgreSQL se stále pracuje. Každá verze přináší novou funkcionalitu a zatím i vyšší výkon. Tým vývojářů uvolňuje každý rok novou verzi. Pro reálné nasazení se doporučuje používat verzi půl roku až rok starou, a migrovat každé dva roky. Při častější migraci se zmenšují rozdíly mezi verzí, kterou používáte a cílovou verzí. 

Při častější migraci je větší naděje, že ve vaší organizaci stále pracuje autor aplikace nebo někdo, kdo má zkušenosti z předchozí migrace. Naopak, čím déle je provozovaný systém zakonzervovaný, tím pravděpodobnější jsou problémy s migrací. Pokud stáří systému přesáhne kritickou hranici, pak náklady na migraci odpovídají aktualizaci celého systému, v horším případě se systém odstaví. Po 3-4 letech jsou to ty systémy, kolem kterých se chodí po špičkách a do kterých se kdokoliv bojí sáhnout.
* http://www.root.cz/clanky/postgresql-8-3-nejen-razantni-navyseni-vykonu/
* http://www.root.cz/clanky/postgresql-9-1-aneb-stale-vpred/

== Hlídejte si počet spojení do databáze ==
Relativně častým problémem je zbytečně velký počet připojení do databáze. Maximálního výkonu se dosahuje zhruba u počtu spojení, které odpovídá cca desetinásobku počtu CPU. Při stonásobku může dojít k výraznému snížení výkonu z důvodu pomalosti operací nad sdílenou pamětí. Dobře napsané (a i velmi zatížené) aplikace si mohou vystačit s několika desítkami spojení do databáze. To, že nízký počet spojení nedostačuje může být signálem chyb v aplikaci. Jednou z nejhorších chyb je neukončování transakce (PostgreSQL sám nemá timeout na otevřenou transakci). Stále otevřená transakce zabraňuje znovupoužití připojení a hlavně efektivně blokuje vacuum (což vede k dalšímu zpomalování databáze). '''Takové chování je nutné opravit'''. Pokud software neobsahuje chyby a vyžaduje větší množství spojení, pak spíš než  zvyšení ''max_connection''  nainstalujte a používejte poolovací sw jako je [https://wiki.postgresql.org/wiki/PgBouncer pgbouncer] nebo [http://www.pgpool.net/mediawiki/index.php/Main_Page pgpool].
<pre>
-- identifikace otevřené transakce, která je déle než 1min ve stavu IDLE
postgres=# select datname, usename, current_timestamp - xact_start duration 
              from pg_stat_activity
             where state = 'idle in transaction' 
               and current_timestamp - xact_start > '1min';
-[ RECORD 1 ]-------------
datname  | postgres
usename  | pavel
duration | 00:05:14.272201
</pre>

== Používejte uložené procedury a předpřipravené dotazy ==
Stále se vedou diskuze, zda-li používat nebo nepoužívat uložené procedury. Osobně si nedovedu představit složitější aplikaci, kde by použití uložených procedur bylo na škodu. Pokud používáte ORM (technika, jak se v SQL databázi obejít bez SQL), pak pravděpodobně procedury nepoužijete. Bez diskuzí je jejich přínos k bezpečnosti aplikace. <i>Předpřipravené dotazy jsou jedinou 100% ochranou proti SQL injektáži</i>. A nezanedbatelné je i zrychlení určitých operací. Pokud dokážete udržet zpracování úlohy na db. serveru odpadá režie na udržení komunikace mezi klientem a serverem, na nezbytné konverze, atd. Díky rychlejšímu zpracování úlohy, server drží kratší dobu alokované zdroje na úlohu, dříve je uvolní a dříve je bude moci použít pro ostatní úlohy. 

* [[SQL_Triky#Pomal.C3.A9_dotazy_v_ulo.C5.BEen.C3.BDch_procedur.C3.A1ch|Pomalé dotazy v uložených procedurách]]
* [[Kešování výsledku funkcí v PL/Perl]]
* http://www.pgsql.cz/index.php/PL/pgSQL
* http://www.pgsql.cz/skoleni/skoleni_plpgsql_web.pdf
* [[PL/pgSQL]]
* [[PL/pgSQL efektivně]]
* [[Jak nepoužívat PL/pgSQL, případně PL/SQL, a další fatální chyby]]

Komerční odkaz: [http://www.pgsql.cz/index.php/Jednodenn%C3%AD_%C5%A1kolen%C3%AD_PostgreSQL#PostgreSQL_efektivn.C4.9B.2C_v.C3.BDvoj_.28ulo.C5.BEen.C3.A9_procedury.29 Školení pro pokročilé - návrh a vývoj uložených procedur v jazyce PL/pgSQL]

== Udržte pořádek v databázi ==
Co nejdříve odstraňujte všechny nepoužívané databázové objekty (samozřejmě až po té, co se skutečně ujistíte, že jsou nepoužívané). <i>Rušené objekty zálohujte</i>. Pokud úloha, která se spouští jednou měsíčně vyžaduje index, který se jindy nepoužije, přemýšlejte o tom, zda-li by nebylo možné vytvořit index před provedením úlohy a poté jej opět odstranit. 

Nepoužívané tabulky a indexy nejsnáze identifikujeme analýzou provozních statistik z tabulek pg_stat_user_tables(seq_scan, idx_scan) a pg_stat_user_indexes(idx_scan). Až do verze 8.3 bylo nutné aktualizaci provozních statistik explicitně povolit [http://www.postgresql.org/docs/8.2/interactive/runtime-config-statistics.html stats_start_collector a stats_row_level]). 

Udržujte aktuální dokumentaci db schématu. Změny v databázovém schématu neprovádějte přímo, ale pomocí skriptů. Změny konfiguračních souborů archivujte pomocí systémů správy verzí. Používejte wiki, blog .. systémy, které vám umožní dokumentovat změny, aniž by obtěžovaly.

* [[SQL_Triky_III.#Správa indexů| Správa indexů]]

== Použijte partitioning ==
Pro opravdu velké tabulky (nad jeden milión záznamů) požívejte partitioning. Partitioning je opravdu užitečný v případě, že pravidelně mažete extrémně velké množství řádků (týdně, měsíčně, kvartálně, ročně). Po opravdu masivní operaci DELETE je nutné zvakuovat datový soubor. Pravděpodobně bude nutné provést i reindexaci. Na opravdu velkých tabulkách (desítky, stovky miliónů řádků) jsou to extrémně náročné a dlouhé operace. Často ale můžeme efektivně použít partitioning a DELETE nahradit zrušením partition. Tak se elegantně zbavíme všech náročných operací. Odstranění partition je extrémně rychlá operace bez ohledu na množství odstraněných řádků.

== Pozor na hardware ==
Výkon hw je kritický. Problematická síťová karta může způsobovat pomalé připojení k databázi. Špatný výběr typu diskových polí může úplně zazdít výkon databáze bez ohledu na cenu a kvalitu serveru. <b>Nepoužívejte RAID 5!</b>. Jedno z doporučení (pro o.s. Linux) je: dva disky v RAID 1 pro pg_xlog (ext2), pro vlastní data RAID 10 (ext3). Výkon diskových polí je nutné vždy otestovat. Zdrojem potíží může být i firmware řadiče diskových polí. 

V případě, že máte rozsáhlé databázové schéma - desítky databází, stovky až tisíce tabulek tak I/O může vytěžovat i zápis souboru produkčních statistik. V tom případě se doporučuje vytvoření adresáře v ramdisku (o velikosti 20, 50, 100MB) a změnu ''stats_temp_directory'' na tento adresář (tento problém by měl být vyřešen v PostgreSQL 9.3):

<pre>
mkdir -p /mnt/tmpfs/pg_stat_tmp
mount -t tmpfs -o size=100M,mode=0755 tmpfs /mnt/tmpfs/pg_stat_tmp 

#konfigurace - postgresql.conf
stats_temp_directory = '/mnt/tmpfs/pg_stat_tmp'
</pre>

*http://www.coker.com.au/bonnie++/

== Používejte správné nástroje ==
Databáze PostgreSQL je navržena a optimalizována pro tzv. podnikové prostředí. To se vyznačuje požadavkem na maximální možnou spolehlivost a dobrý výkon při souběžném zpracování komplikovanějších SQL příkazů bez ohledu na objem dat. Kromě toho PostgreSQL nabízí více než obvyklý komfort a široký repertoár funkcí. Nelze nezmínit vynikající bezplatnou podporu (IRC, elektr. konference). Na druhou stranu existuje celá řada dalších open source databází, které jsou pro určité aplikace vhodnější. Namátkou: [http://www.mysql.com MySQL], [http://www.ibphoenix.cz/ Firebird], [http://www.sqlite.org SQLite] nebo [https://duckdb.org/ DuckDB]. Je proto třeba volit uvážlivě a brát v potaz celou řadu ať technických nebo netechnických faktorů. <b>Pokud nemusíte řešit analýzu nad dočasnými daty, pak tato data neukládejte do databáze (např. správa http sessions)</b>. Pro taková data použijte [http://memcached.org/ memcached].