Zobrazení zdroje stránky PostgreSQL 9.6 (2016) odteď paralelně



Netradičně se letos dočkáme ještě jedné velké verze PostgreSQL a to verze 9.6. Ta představuje
zásadní milník ve vývoji Postgresu. V této verzi mají uživatelé poprvé možnost využít více CPU
pro spočítání jednoho SQL příkazu. Planer nyní dokáže zpracování úlohy rozdělit mezi několik
procesů a použít paralelní sekvenční čtení, paralelní spojování, paralelní agregaci. Je to první
viditelný krok na delší cestě (na implementaci se pracovalo 3 roky) k lepší podpoře analytických
úloh. PostgreSQL je a bude primárně OLTP databází, to se nezmění. Nelze ale nevidět snahu o 
posunutí limitů pro OLAP úlohy k stovkám gigabajtů nebo jednotkám terabajtů. Vážně se mluví o 
úpravách exekutoru, možnosti uložit data po sloupcích, využití GPU. Něco z toho je ve stádiu úvah,
něco již existuje v experimentálních prototypech.

V 9.6 se vývojáři soustředili na dopracování paralelní exekuce a všechno větší ostatní šlo stranou.
Ve vývoji PostgreSQL není nikdy nic jisté, ale s velkou jistotou lze odhadnout, že v budoucí verzi
(označené číslem deset) bude integrovaná logická replikace, více sloupcové statistiky, optimalizace
btree indexů  a možná i přepracovaný partitioning. Další plány mi přijdou příliš fantastické na to,
aby se daly realizovat během roku, roku a půl.

Samozřejmě, že ne všechno, co bylo v plánu, se stihlo. Stihla se ale implementace několika důležitých
interních API, jejichž význam se ukáže v budoucnu. Předně se refaktorovalo API pro indexy. S ním by
se měla zjednodušit implementace vlastních plno funkčních indexů (ukázkou je nový contrib modul - 
bloom filter (index). Konečně by nic nemělo bránit dokončení implementace Hash indexu, díky
které by se tento index mohl stát prakticky použitelným. N

V 9.6 se pro aplikačního programátora mnoho nemění. Zásadní změny jsou ve větších možnostech
administrace a v lepším výkonu (poznámka: vzhledem k rozsahu a hloubce změn v 9.6 bude hodně důležité
nepodcenit testování před produkčním použitím).

<h3>Problémy s locales v 9.5.</h3>

Řazení v PostgreSQL používá a závisí na implementaci v systému (na Linuxu tzv locales, které
jsou implementovány knihovnou glibc). Pro některé jazyky (včetně českého jazyka) je porovnání
dvou řetězců relativně pomalé a proto se hledala cesta, jak tuto operaci zrychlit. V 9.5.0 se přišlo
s implementací tzv redukovaných klíčů (Abbreviated keys). Poměrně rychle se ale zjistilo, že velké
množství uživatelů používá starší verze glibc, které díky
[https://bugzilla.redhat.com/show_bug.cgi?id=1320356 chybě] pro některé hodnoty vrátí špatný
výsledek, což pak přeneseně způsobí nevalidní řazení. Proto byla tato optimalizace v 9.5.2 rychle
vypnuta. Bohužel, pokud používáte glibc s touto chybou, tak je nutné provést reindexaci. Více
se dozvíte na wiki stránce https://wiki.postgresql.org/wiki/Abbreviated_keys_glibc_issue.

K dispozici je [http://www.postgresql.org/message-id/19132.1458688784@sss.pgh.pa.us test
locales], které tuto chybu odhalí. České locales na RHEL 6.5 a 7 je v pořádku.
Pokud je Vámi používané locales validní, a chcete používat optimalizaci řazení, pak je nutné si
přeložit Postgres a použít konfigurační volbu <code>TRUST_STRXFRM</code>. Smysl to má tehdy, když řadíte milióny řetězců v
národním locales. Pro desítky tisíc řádků by asi rozdíl v rychlostech měl být zanedbatelný.
(Aktualizace 22.3.2020 - testy na FC32 ukazují, že i české locales je pro TRUST_STRXFRM nepoužitelné).

<h2>Administrace</h2>

Jednou z největších změn z pohledu administrátora je možnost používat speciálně vytvořené role
pro administraci a omezit tak použití účtů s právem <i>superusera</i>. Aktuálně je vytvořena pouze jedna
taková role <i>pg_signal_backend</i>, která umožňuje přerušit dotazy, a ukončovat spojení. Další role si ale 
můžeme sami, a těmto rolím přidat práva k následujícím funkcím podle potřeby (následující funkce
dříve vyžadovaly uživatele s právem superuser): <code>pg_start_backup</code>, <code>pg_stop_backup</code>,
<code>pg_stop_backup</code>, <code>pg_create_restore_point</code>, <code>pg_switch_xlog</code>, 
<code>pg_xlog_replay_pause</code>, <code>pg_xlog_replay_resume</code>, <code>pg_rotate_logfile</code>,
<code>pg_reload_conf</code>, <code>pg_stat_reset</code>, <code>pg_stat_reset_shared</code>,
<code>pg_stat_reset_single_table_counters</code>, <code>pg_stat_reset_single_function_counters</code>.

Do 9.6 synchronní replikace garantovala doručení změn na jednu z vybraných replik. Počínaje 9.6
je možné určit parametrem num_sync počet replik, které budou mít garantované doručení změn. Jelikož
nyní už není rozdíl mezi úrovněmi "archive" a "hot_standby", zavádí se nová úroveň "replica" ve smyslu
cokoliv, co není logickou replikací. Konfigurace synchronní replikace se rozrostla o možnost nastavit
<code>synchronnous_commit</code> na hodnotu <code>remote_apply</code>. Ta způsobí, že master čeká,
dokud nebudou změny skutečně aplikovány na všech synchronních replikovaných serverech - je garantován viditelný obsah
synchronizovaných replik (což má smysl pro vyrovnávání zátěže).

Novým a pro mne hodně důležitým timeoutem je <code>idle_in_transaction_session_timeout</code>. Díky němu mohu
nastavit maximální dobu ne-aktivity uvnitř transakce a nepotřebuji k tomu cron. Otevřená transakce,
kde se nic neděje, je podezřelá. Při delší době (dny) může efektivně vyblokovat VACUUM, což
vede k zásadní degradaci výkonu databáze.

V 9.6 je vestavěná podpora pro BSD Authentication service (k dispozici na OpenBSD). V <i>pg_hba.conf</i>
použijte volbu <code>bsd</code>. 

Počínaje 9.6 je nativně podporován systemd. Klauzule <code>CASCADE</code> příkazu <code>CREATE EXTENSION</code> vynutí
automatickou instalaci všech nezbytných extenzí pro instalovanou extenzi. 

Pohled <code>pg_stat_activity</code> byl rozšířen o další sloupce popisující zámek na který se čeká:
<pre>
postgres=# SELECT * FROM pg_stat_activity limit 1;
┌─[ RECORD 1 ]─────┬───────────────────────────────┐
│ datid            │ 13356                         │
│ datname          │ postgres                      │
│ pid              │ 20411                         │
│ usesysid         │ 16384                         │
│ usename          │ pavel                         │
│ application_name │ psql                          │
│ client_addr      │ ( null )                      │
│ client_hostname  │ ( null )                      │
│ client_port      │ -1                            │
│ backend_start    │ 2016-05-22 11:33:49.052893+02 │
│ xact_start       │ 2016-05-22 20:55:09.762732+02 │
│ query_start      │ 2016-05-22 20:55:09.762732+02 │
│ state_change     │ 2016-05-22 20:55:09.762737+02 │
│ wait_event_type  │ Lock                          │
│ wait_event       │ relation                      │
│ state            │ active                        │
│ backend_xid      │ ( null )                      │
│ backend_xmin     │ 5480                          │
│ query            │ select * from obce;           │
└──────────────────┴───────────────────────────────┘
</pre>

    
<h3>Zobrazení postupu (progresu) provádění příkazu</h3>

Pokud zrovna jako já musíte občas pustit příkaz <code>VACUUM</code> nad desítky gigabajtů velkými tabulkami,
pak jistě oceníte možnost si nechat zobrazit informaci o postupu. V 9.6 je připravená infrastruktura
a implementována podpora pro monitorování příkazu <code>VACUUM</code> (pozor: příkaz <code>VACUUM FULL</code> není podporován).
Předpokládám, že podpora pro další příkazy a možná i pro příkaz <code>SELECT</code> bude rychle následovat. 
<pre>
postgres=# SELECT * FROM pg_stat_progress_vacuum;
┌─[ RECORD 1 ]───────┬───────────────┐
│ pid                │ 30627         │
│ datid              │ 13356         │
│ datname            │ postgres      │
│ relid              │ 23430         │
│ phase              │ scanning heap │
│ heap_blks_total    │ 73530         │
│ heap_blks_scanned  │ 65843         │
│ heap_blks_vacuumed │ 0             │
│ index_vacuum_count │ 0             │
│ max_dead_tuples    │ 11184810      │
│ num_dead_tuples    │ 0             │
└────────────────────┴───────────────┘
</pre>
Nápad je to dobrý, ale realizace je teprve na začátku. Tady je ještě hodně práce. V Postgresu
je ale kolikrát nejnáročnější diskuze o návrhu, a ta už je uzavřena, takže další vývoj by
mohl být relativně rychlý.

<h3>Konzole</h3>

Na příkazech <code>\ev</code> (edit view) a <code>\sv</code> (show view) není co vysvětlovat.

Asi každý, kdo používá konzoli, zná volbu <code>-c</code>. Oproti předchozím verzím tuto volbu můžeme použít
opakovaně, což může zjednodušit psaní skriptů. S novou implementací přichází nekompatibilní změna.
Dříve použití tohoto přepínače automaticky blokovalo načítání resource souboru psqlrc. Počínaje
9.6 je nutné explicitně načítání blokovat volbou <code>-X</code>.

Novým backslashovým příkazem je <code>\errverbose</code>. Běžně si v konzoli snižujeme verbositu (ukecanost).
V případě chyby nám pak, samozřejmě, chybí informace. Řešením je výše zmíněný příkaz. Pro
poslední chybu vypíše veškeré informace.
<pre>
postgres=# select 10/0;
ERROR:  division by zero

postgres=# \errverbose 
ERROR:  22012: division by zero
LOCATION:  int4div, int.c:719
</pre>
Příkaz <code>\gexec</code> navazuje na příkaz <code>\gset</code>. Příkaz <code>\gset</code> provede příkaz a uloží výsledek do 
psql proměnné. Příkaz <code>\gexec</code> provede příkaz a výsledek chápe jako jeden nebo N příkazů, které
jsou vykonány. Tento postup je znám jako <i>meta programování</i>. Zatím jsem si občas pomohl tak, že v
jedné instanci <code>psql</code> jsem si vygeneroval SQL příkazy, které jsem přez rouru poslal do druhé instance
<code>psql</code>.
<pre>
psql -X -At -c "SELECT 'select current_date' FROM generate_series(1,3)" | psql -S
</pre>
nebo
<pre>
postgres=# SELECT 'select current_date' FROM generate_series(1,3);
┌─────────────────────┐
│      ?column?       │
╞═════════════════════╡
│ select current_date │
│ select current_date │
│ select current_date │
└─────────────────────┘
(3 rows)

postgres=# SELECT 'select current_date' FROM generate_series(1,3)\gexec
┌────────────┐
│    date    │
╞════════════╡
│ 2016-05-22 │
└────────────┘
(1 row)

┌────────────┐
│    date    │
╞════════════╡
│ 2016-05-22 │
└────────────┘
(1 row)

┌────────────┐
│    date    │
╞════════════╡
│ 2016-05-22 │
└────────────┘
(1 row)
</pre>

Šlehačkou na dortu je příkaz <code>\crosstabview</code>. Zobrazí křížovou sestavu (nebo kontingenční tabulku) pro naposledy zadaný dotaz. 
<pre>
postgres=# SELECT sum(amount), customer, EXTRACT(month FROM closed) AS closed_month 
              FROM data 
              GROUP BY customer, EXTRACT(month FROM closed)
              ORDER BY 2;
  sum  │ customer  │ closed_month 
═══════╪═══════════╪══════════════
  1200 │ Borland   │            4
  2620 │ Borland   │            9
  2792 │ Borland   │            8
  1632 │ IBM       │            8
  4416 │ IBM       │            7
  2976 │ IBM       │            9
  2688 │ IBM       │            2
  2256 │ IBM       │           10
  3936 │ IBM       │            4
 13536 │ IBM       │            3
  2016 │ IBM       │           11
  6048 │ IBM       │            5
  5424 │ IBM       │            6
  3020 │ IBM       │            1
  4150 │ Microsoft │            6
  1300 │ RedHat    │           10
   320 │ RedHat    │           11
  1260 │ RedHat    │            7
  5368 │ Siemens   │            5
(19 rows)


postgres=# \crosstabview 2 3 1 3
 customer  │  1   │  2   │   3   │  4   │  5   │  6   │  7   │  8   │  9   │  10  │  11  
═══════════╪══════╪══════╪═══════╪══════╪══════╪══════╪══════╪══════╪══════╪══════╪══════
 Borland   │      │      │       │ 1200 │      │      │      │ 2792 │ 2620 │      │     
 IBM       │ 3020 │ 2688 │ 13536 │ 3936 │ 6048 │ 5424 │ 4416 │ 1632 │ 2976 │ 2256 │ 2016
 Microsoft │      │      │       │      │      │ 4150 │      │      │      │      │     
 RedHat    │      │      │       │      │      │      │ 1260 │      │      │ 1300 │  320
 Siemens   │      │      │       │      │ 5368 │      │      │      │      │      │     
(5 rows)
</pre>

Prvním parametrem je číslo sloupce s první dimenzí, druhým parametrem pak číslo sloupce s druhou dimenzí. Pak následuje číslo
sloupce s vlastní hodnotou, a posledním volitelným parametrem je číslo sloupce s pořadím pro druhou dimenzi. psql nemá ambice
nahradit systémy pro tvorbu reportů - na druhou stranu je to jedna z mála možností, jak vygenerovat takový report v textovém
režimu.

<h3>pg_dump</h3>

Trochu nepříjemným překvapením bylo zjištění, že <code>pg_dump</code> tiše ignoruje špatně explicitně zadané
názvy exportovaných tabulek (musí existovat alespoň jedna tabulka ze seznamu).
<pre>
pg_dump -t existující_tabulka -t neexistující_tabulka 
</pre>
neskončí chybou. Nová volba <code>--strict-names</code> zajistí restriktivnější chování. Pokud neexistuje
tabulka se zadaným jménem, dump skončí s chybou. Totéž platí i pro <code>pg_restore</code>.

Další změna v pg_dumpu souvisí s možností delegovat některá práva uživatele postgres jiným neprivilegovaným
uživatelům. Pokud jste si v předchozích verzích nastavili přístupová práva k systémovým tabulkám, tak, bohužel,
pg_dump tyto změny ignoroval - viděl jsem odstranění public access z tabulek <code>pg_class</code> případně <code>pg_proc</code>.
Nově jsou tyto změny exportovány.


<h2>Foreign Data Wrappers (FDW)</h2>

Změn je tolik, že si zaslouží více než samostatný odstavec. Většina je výsledkem snahy přesunout
co možná největší část zpracování dotazu na cizí server.

Nyní lze:
<ul>
<li>definovat seznam extenzí jejichž immutable funkce a operátory budou provedeny na cizím serveru,
<li>spojení a řazení lze přesunout na cizí server:
<pre>
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: t1.c1, t2.c1, t1.c3
   ->  Sort
         Output: t1.c1, t2.c1, t1.c3
         Sort Key: t1.c3, t1.c1
         ->  Foreign Scan
               Output: t1.c1, t2.c1, t1.c3
               Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
               Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
(9 rows)

EXPLAIN (VERBOSE, COSTS false)
   SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Limit
   Output: t1.c1, t2."C 1"
   ->  Merge Join
         Output: t1.c1, t2."C 1"
         Merge Cond: (t1.c1 = t2."C 1")
         ->  Foreign Scan on public.ft2 t1
               Output: t1.c1
               Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
         ->  Index Only Scan using t1_pkey on "S 1"."T 1" t2
               Output: t2."C 1"
(10 rows)
</pre>
<li>UPDATE a DELETE se dříve vykonávaly po řádku. Nyní tyto příkazy lze v jednodušších případech přesunout na cizí
server:
<pre>
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;  -- can be pushed down
                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on public.ft2
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   ->  Foreign Update on public.ft2
         Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 400), c3 = (c3 || '_update7'::text) WHERE ((("C 1" % 10) = 7)) RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
(4 rows)
</pre>
</ul>
Při vytváření FDW tabulek jsem pokaždé zapoměl na vytvoření mapování pro uživatele. Nyní je
mapování nepovinné:
<pre>
CREATE EXTENSION file_fdw;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE obce_fdw (
    id integer NOT NULL,
    okres_id character varying(6),
    nazev character varying(40),
    pocet_muzu integer,
    pocet_zen integer,
    vek_muzu numeric(3,1),
    vek_zen numeric(3,1)
) SERVER FILE_SERVER OPTIONS (FILENAME '/tmp/obce.csv', FORMAT 'csv');

postgres=# SELECT * FROM obce_fdw LIMIT 10;
┌────┬──────────┬─────────────┬────────────┬───────────┬──────────┬─────────┐
│ id │ okres_id │    nazev    │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞════╪══════════╪═════════════╪════════════╪═══════════╪══════════╪═════════╡
│  1 │ CZ0100   │ Praha       │     608316 │    640710 │     39.8 │    43.2 │
│  2 │ CZ0201   │ Benešov     │       7875 │      8507 │     39.2 │    41.9 │
│  3 │ CZ0201   │ Bernartice  │        108 │       115 │     45.9 │    43.3 │
│  4 │ CZ0201   │ Bílkovice   │         93 │        89 │     41.4 │    46.8 │
│  5 │ CZ0201   │ Blažejovice │         52 │        48 │     44.6 │    50.8 │
│  6 │ CZ0201   │ Borovnice   │         39 │        37 │     45.6 │    49.5 │
│  7 │ CZ0201   │ Bukovany    │        364 │       372 │     38.1 │    38.8 │
│  8 │ CZ0201   │ Bystřice    │       2124 │      2096 │     38.5 │    41.1 │
│  9 │ CZ0201   │ Ctiboř      │         55 │        50 │     38.6 │    42.0 │
│ 10 │ CZ0201   │ Čakov       │         65 │        60 │     36.4 │    40.6 │
└────┴──────────┴─────────────┴────────────┴───────────┴──────────┴─────────┘
(10 rows)
</pre>
Odpadl jeden často zbytečný krok.

<h2>Výkon</h2>

Změn zaměřených na vyšší výkon je v této nové verzi hodně. Zatím ponechám stranou využití více
CPU pro jeden dotaz a budu se v krátkosti věnovat těm ostatním.

Mělo by dojít k výraznému zrychlení commitů při souběžném commitování více klientů (znatelné 
mezi 64..256 počty připojení).

Pro optimalizaci dotazů je kriticky důležitá kvalita odhadů. Počínaje 9.6 může plánovač použít
statistiky funkcionálních indexů:
<pre>
postgres=# create index on obce((pocet_muzu + pocet_zen));
CREATE INDEX

-- pozor: statistiky jsou neaktuální - špatný odhad
postgres=# EXPLAIN SELECT * FROM obce WHERE pocet_muzu + pocet_zen < 10000;
┌────────────────────────────────────────────────────────────────────────────────┐
│                                   QUERY PLAN                                   │
╞════════════════════════════════════════════════════════════════════════════════╡
│ Bitmap Heap Scan on obce  (cost=44.43..134.67 rows=2083 width=41)              │
│   Recheck Cond: ((pocet_muzu + pocet_zen) < 10000)                             │
│   ->  Bitmap Index Scan on obce_expr_idx  (cost=0.00..43.91 rows=2083 width=0) │
│         Index Cond: ((pocet_muzu + pocet_zen) < 10000)                         │
└────────────────────────────────────────────────────────────────────────────────┘
(4 rows)

postgres=# analyze ;
ANALYZE

-- index se sice nepoužije, ale odhady souhlasí
postgres=# EXPLAIN ANALYZE  SELECT * FROM obce WHERE pocet_muzu + pocet_zen < 10000;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                              QUERY PLAN                                               │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce  (cost=0.00..152.75 rows=6114 width=41) (actual time=0.041..4.376 rows=6118 loops=1) │
│   Filter: ((pocet_muzu + pocet_zen) < 10000)                                                          │
│   Rows Removed by Filter: 132                                                                         │
│ Planning time: 0.268 ms                                                                               │
│ Execution time: 5.184 ms                                                                              │
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
</pre>
Bylo by pěkné přidávat jenom statistiky, nikoliv indexy (údržba indexu je drahá). Ale toho
dost možná dočkáme v následující verzi.

Tom Lane přepsal část plánovače, která realizovala optimalizaci agregačních funkcí. Starý kód se
jednak hůře udržoval, druhak neumožňoval žádný další rozvoj. Pokud něco z této změny je vidět i
pro běžného uživatele, pak jsou to dotazy nad FDW daty. 

Nyní je také plánovač chytřejší a častěji bude používat index only scan. Také se změnil vzorec
pro odhad počtu unikátních hodnot (výsledek se pak použije jako odhad pro agregaci).
Nový vzorec by měl méně podstřelovat - to by mělo zajistit bezpečné provedení hash aggregace 
(v případě špatného odhadu může dojít k vyčerpání volné paměti).

Postgres také nyní dovoluje sdílení mezivýpočtů agregačních funkcí - pokud například počítáte funkci
<code>sum</code> a funkci <code>avg</code> v jednom dotazu, pak se součet využije i pro výpočet průměru. Pro běžné OLTP dotazy
bude efekt minimální, ale pokud jsou data rychle k dispozici (RAM, SSD) a pokud máme složité výrazy,
desítky miliónů řádků, tak může být úspora výpočtů nezanedbatelná (zvlášť pro typ Numeric).

Na běžných konfiguracích by se měla projevit optimalizace řazení - external sort dostane data 
předřazená quick sortem. 

<h3>Paralelizace výpočtu dotazu</h3>

Pokud máte k dispozici dostatečně výkonné železo, a dostatečně velká data, tak schopnost Postgresu
využít více CPU by měla zrychlit dotazy 2x - 3x. Zde extrémně záleží jaké jsou rezervy ve výkonu 
vašeho serveru. Pokud paralelní procesu budou muset čekat na IO, tak se moc velkého zrychlení 
nedočkáte. Také záleží jak se s Vašimi dotazy trefíte do toho, co je Postgresem podporováno. Připomínám,
že se jedná o první milník, několik zásadních funkcí stále ještě chybí - jako například paralelní index scan,
paralelní hashjoin.
<pre>
postgres=# SET max_parallel_degree TO 0;
SET
Time: 0.964 ms
postgres=# EXPLAIN ANALYZE VERBOSE SELECT sum(a), avg(a) FROM bigdata;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                            QUERY PLAN                                                            │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Aggregate  (cost=223527.96..223527.97 rows=1 width=40) (actual time=2022.457..2022.457 rows=1 loops=1)                           │
│   Output: sum(a), avg(a)                                                                                                         │
│   ->  Seq Scan on public.bigdata  (cost=0.00..173528.64 rows=9999864 width=4) (actual time=1.429..789.822 rows=10000010 loops=1) │
│         Output: a, b, c, d, e, f, g                                                                                              │
│ Planning time: 0.177 ms                                                                                                          │
│ Execution time: 2022.552 ms                                                                                                      │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)

Time: 2023.553 ms
postgres=# SET max_parallel_degree TO 2;
SET
Time: 1.004 ms
postgres=# EXPLAIN ANALYZE VERBOSE SELECT sum(a), avg(a) FROM bigdata;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                      QUERY PLAN                                                                      │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Finalize Aggregate  (cost=137029.37..137029.38 rows=1 width=40) (actual time=770.474..770.475 rows=1 loops=1)                                        │
│   Output: sum(a), avg(a)                                                                                                                             │
│   ->  Gather  (cost=137029.15..137029.36 rows=2 width=40) (actual time=770.406..770.462 rows=3 loops=1)                                              │
│         Output: (PARTIAL sum(a)), (PARTIAL avg(a))                                                                                                   │
│         Workers Planned: 2                                                                                                                           │
│         Workers Launched: 2                                                                                                                          │
│         ->  Partial Aggregate  (cost=136029.15..136029.16 rows=1 width=40) (actual time=766.011..766.012 rows=1 loops=3)                             │
│               Output: PARTIAL sum(a), PARTIAL avg(a)                                                                                                 │
│               Worker 0: actual time=763.917..763.918 rows=1 loops=1                                                                                  │
│               Worker 1: actual time=764.206..764.207 rows=1 loops=1                                                                                  │
│               ->  Parallel Seq Scan on public.bigdata  (cost=0.00..115196.10 rows=4166610 width=4) (actual time=0.481..305.462 rows=3333337 loops=3) │
│                     Output: a, b, c, d, e, f, g                                                                                                      │
│                     Worker 0: actual time=0.062..306.078 rows=3261688 loops=1                                                                        │
│                     Worker 1: actual time=0.063..303.584 rows=3346618 loops=1                                                                        │
│ Planning time: 0.197 ms                                                                                                                              │
│ Execution time: 775.185 ms                                                                                                                           │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(16 rows)

postgres=# EXPLAIN ANALYZE VERBOSE SELECT sum(a), avg(a) FROM bigdata GROUP BY b;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                            QUERY PLAN                                                                            │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Finalize GroupAggregate  (cost=773618.01..776792.77 rows=97685 width=44) (actual time=6210.844..6449.800 rows=100001 loops=1)                                    │
│   Output: sum(a), avg(a), b                                                                                                                                      │
│   Group Key: bigdata.b                                                                                                                                           │
│   ->  Sort  (cost=773618.01..774106.43 rows=195370 width=44) (actual time=6210.824..6282.132 rows=300003 loops=1)                                                │
│         Output: b, (PARTIAL sum(a)), (PARTIAL avg(a))                                                                                                            │
│         Sort Key: bigdata.b                                                                                                                                      │
│         Sort Method: external merge  Disk: 18416kB                                                                                                               │
│         ->  Gather  (cost=688256.09..750436.04 rows=195370 width=44) (actual time=3766.425..6020.076 rows=300003 loops=1)                                        │
│               Output: b, (PARTIAL sum(a)), (PARTIAL avg(a))                                                                                                      │
│               Workers Planned: 2                                                                                                                                 │
│               Workers Launched: 2                                                                                                                                │
│               ->  Partial GroupAggregate  (cost=687256.09..729899.04 rows=97685 width=44) (actual time=3935.437..5347.911 rows=100001 loops=3)                   │
│                     Output: b, PARTIAL sum(a), PARTIAL avg(a)                                                                                                    │
│                     Group Key: bigdata.b                                                                                                                         │
│                     Worker 0: actual time=3767.449..4986.689 rows=100001 loops=1                                                                                 │
│                     Worker 1: actual time=4273.012..6084.175 rows=100001 loops=1                                                                                 │
│                     ->  Sort  (cost=687256.09..697672.62 rows=4166610 width=8) (actual time=3935.420..4695.720 rows=3333337 loops=3)                             │
│                           Output: b, a                                                                                                                           │
│                           Sort Key: bigdata.b                                                                                                                    │
│                           Sort Method: external merge  Disk: 48088kB                                                                                             │
│                           Worker 0: actual time=3767.439..4441.376 rows=2693072 loops=1                                                                          │
│                           Worker 1: actual time=4272.995..5219.585 rows=4572794 loops=1                                                                          │
│                           ->  Parallel Seq Scan on public.bigdata  (cost=0.00..115196.10 rows=4166610 width=8) (actual time=0.601..606.274 rows=3333337 loops=3) │
│                                 Output: b, a                                                                                                                     │
│                                 Worker 0: actual time=0.046..617.476 rows=2693072 loops=1                                                                        │
│                                 Worker 1: actual time=0.085..582.353 rows=4572794 loops=1                                                                        │
│ Planning time: 0.269 ms                                                                                                                                          │
│ Execution time: 6470.162 ms                                                                                                                                      │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(28 rows)
</pre>
Všimněte si důležitého uzlu <code>Gather</code>, který startuje paralelní procesu, a od nich pak přebírá výsledky. 
Přepínač <code>max_parallel_workers_per_gather</code> (původně <code>max_parallel_degree</code>) nastavuje míru paralelismu dotazu - 0 .. žádná, 1 .. jeden hlavní a jeden
pomocný proces, 2 .. jeden hlavní a dva pomocné procesy, .. Hlavní proces, kromě toho, že startuje
workery ještě vykonává tu stejnou práci jako workery.

Pro OLTP paralelizace nemá velký význam - více CPU nemusí znamenat žádné zrychlení, protože se hodně času 
spálí mezi procesovou komunikací. Je tu oblast, pro kterou je paralelizace extrémně zajímavá, a to je
Geoinformatika. Paul Ramsey - autor PostGISu se o paralelní zpracování hodně zajímá - možná se během několika málo let
dočkáme [http://blog.cleverelephant.ca/2016/03/parallel-postgis.html paralelního PostGISu].

<h2>Změny pro aplikační vývojáře</h2>

Konečně je opravena chyba, která mne dlouho iritovala. PLpgSQL příkaz <code>RAISE</code>
nyní korektně nastaví chybový kontext. 

PLPythonu je jedním z jazyků pro externí uložené procedury. Už ani nevím jak dlouho je možnost vyhodit
postgresovou výjimku. Nebylo ale možné nastavit všechny atributy výjimky. Počínaje 9.6 toto omezení 
mizí:
<pre>
CREATE FUNCTION raise_custom_exception() RETURNS void AS $$
  plpy.error("custom exception message", detail = "some info about exception", hint = "hint for users")
$$ LANGUAGE plpythonu;

postgres=# SELECT raise_custom_exception();
ERROR:  XX000: plpy.Error: custom exception message
DETAIL:  some info about exception
HINT:  hint for users
CONTEXT:  Traceback (most recent call last):
  PL/Python function "raise_custom_exception", line 2, in &lt;module&gt;
	plpy.error("custom exception message", detail = "some info about exception", hint = "hint for users")
PL/Python function "raise_custom_exception"
LOCATION:  PLy_elog, plpy_elog.c:132
</pre>
Funkce <code>parse_ident</code> pomůže s separací SQL identifikátoru:
<pre>
postgres=# SELECT parse_ident('"divne schema".nazev_tabulky');
           parse_ident           
════════════════════════════════
 {"divne schema",nazev_tabulky}
(1 row)
</pre>
Funkce <code>num_nulls</code> a <code>num_nonnulls</code> vrací počet NULL a ne NULL parametrů funkce. Lze je použít např.
pro podmínku typu - N sloupců musí být vyplněno:
<pre>
postgres=# CREATE TABLE foo(a int, b int, CHECK(num_nonnulls(a,b) = 2));
CREATE TABLE

postgres=# INSERT INTO foo(a) VALUES(10);
ERROR:  new row for relation "foo" violates check constraint "foo_check"
DETAIL:  Failing row contains (10, null).
</pre>
Určitě znáte funkci <code>pg_size_pretty</code>. Funkce <code>pg_size_bytes</code> je k této funkci inverzní - z textového
řetězce s jednotkami generuje číselnou hodnotu v bajtech. Může se hodit například pro filtrování
výpisu velikosti tabulek:
<pre>
postgres=# SELECT relname FROM pg_class WHERE pg_table_size(oid) > pg_size_bytes('100kB');
             relname             
═════════════════════════════════
pg_statistic
pg_type
pg_toast_2618
pg_attribute_relid_attnam_index
pg_depend_depender_index
</pre>

Nově lze extenzi <code>pg_trgm</code> použít i pro vyhledávání podobných slov. Můžeme použít novou funkci 
<code>word_similarity(text,text)</code> a známý operátor vzdálenosti <code>&lt;-&gt;</code>.
<pre>
postgres=# SELECT word_similarity('Skalice','Skála');
 word_similarity 
═════════════════
            0.25
(1 row)
</pre>
K několika změnám došlo i v implementaci fulltextu. Je podporován novější formát Hunspellu a došlo
i k zvýšení limitů. Navíc lze vyhledávat fráze:
<pre>
postgres=# SELECT phraseto_tsquery('simple','Příliš žlutý kůň');
		  phraseto_tsquery          
════════════════════════════════════
 ( 'příliš' <-> 'žlutý' ) <-> 'kůň'
(1 row)
</pre>
Zavádí se nová syntaxe pro práci s poli. Lze určit jen dolní nebo horní index:
<pre>
postgres=# SELECT d[2:], d[:2] FROM (SELECT ARRAY[1,2,3] as d) s;
   d   │   d   
═══════╪═══════
 {2,3} │ {1,2}
(1 row)
</pre>
Byla opravena chyba ve funkci <code>EXTRACT</code>, která se projevovala vrácením nuly pro hodnotu
infinity. Nyní, pokud to má smysl, je vrácena hodnota infinity (nebo NULL, pokud by to smysl
nemělo):
<pre>
SELECT EXTRACT(YEAR FROM DATE 'infinity');    --  Infinity
 date_part 
-----------
  Infinity
(1 row)
</pre>

<h3>Závěr</h3>

Aktuálně to vypadá tak, že se vývoj Postgresu koncetruje zhruba ve třech firmách: [http://2ndquadrant.com/ 2ndQuadrant],
[http://www.enterprisedb.com/ EDB] a [https://postgrespro.com/ Postgres Professional]
(řazeno abecedně). Tyto firmy představují bezpečné (spolehlivé) zázemí pro
vývojáře a je tak prostor pro realizaci komplexních a mnoholetých projektů. Postgres Professional
(firma z Moskvy) se snaží i vychovat další generaci vývojáři Postgresu a rozjíždí [https://wiki.postgresql.org/wiki/Postgres_Professional_roadmap hodně zajímavých 
projektů] (JIT kompilaci, komprimaci na úrovni stránek, strojové učení). 

Další firmy, které pracovaly s uzavřeným forkem PostgreSQL svůj kód otevřely - EMC Greenplumn, který
investuje do modernizace a integrace nejnovější verze Postgresu nebo [https://www.citusdata.com/ Citus Data], který díky pokroku v API
může dodávat svou aplikaci jako extenzi Postgresu (dříve museli forkovat celý kód PostgreSQL).

2ndquadrant revitalizoval [http://2ndquadrant.com/en/resources/postgres-xl/ PostgreSQL-XL], což
je MPP cluster postavený nad Postgresem (navazuje na dřívější projekty: GridSQL, PostgreSQL-XC, StormDB).
Cílený je na OLAP a intenzivní zápis. V 2ndquadrant se také pracuje na implementaci logické replikace.
Ochutnávkou je [http://2ndquadrant.com/en/resources/pglogical/pglogical-docs/ pglogical] -
extenze implementující logickou replikaci (používání vestavěné replikace by mělo být ještě o něco jednodušší).
Klienti 2ndquadrant používají logickou replikaci například k bezvýpadkové migraci.

Za vyzkoušení stojí [https://www.pipelinedb.com/ PipelineDB] - proudová (streaming) databáze
postavená nad PostgreSQL 9.4 umožňující online agregaci, online analytické dotazy. Poznámka: u proudových
databází je trvale uložen pouze [http://docs.pipelinedb.com/continuous-views.html výsledek] nikoliv vlastní data. Díky tomu tyto databáze dokáží generovat
online reporty, přičemž zvládají desítky až stovky tisíc insertů za sekundu.

Zde v České Republice postupuje, sice pomalu, adopce PostgreSQL v i v hodně konzervativním korporátním
prostředí - příkladem může být nasazení PostgreSQL v České Spořitelně. Vlašťovky se najdou i ve státní správě. Zatímco ještě před třemi roky
jsem školil vývojáře především z menších firem a v menších firmách, tak teď už je to spíš opačně.

Jsem zvědavý, co přinese příští rok. Plánů je hodně. V OLAPu se Postgres teprve postavil na startovní 
čáru. Díky tomu, že ANSI SQL 2011 kodifikovalo [https://en.wikipedia.org/wiki/Temporal_database temporální data], tak je prostor a práce na roky. Po
dvaceti letech vývoje (nebo třiceti (včetně akademické éry)) je Postgres pořád na cestě a cíl je zatím
v nedohlednu. 
----------
Související články
<ncl>Category:Verze PostgreSQL</ncl>
[[Category:Články]]
[[Category:Verze PostgreSQL]]