Naposledy upraveno 27. 5. 2016 v 12:10

PostgreSQL 9.6 (2016) odteď paralelně

Verze z 27. 5. 2016, 12:10, kterou vytvořil Pavel (diskuse | příspěvky) (Založena nová stránka s textem „<p>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 v…“)

(rozdíl) ← Starší verze | zobrazit aktuální verzi (rozdíl) | Novější verze → (rozdíl)

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. <p>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. <p>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 <p>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).

Problémy s locales v 9.5.

<p>Ř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 <a href="https://bugzilla.redhat.com/show_bug.cgi?id=1320356">chybě</a> 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 <a href="https://wiki.postgresql.org/wiki/Abbreviated_keys_glibc_issue"> https://wiki.postgresql.org/wiki/Abbreviated_keys_glibc_issue</a>.

<p>K dispozici je <a href="http://www.postgresql.org/message-id/19132.1458688784@sss.pgh.pa.us">test locales</a>, 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 TRUST_STRXFRM. 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ý.

Administrace

<p>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 superusera. Aktuálně je vytvořena pouze jedna taková role pg_signal_backend, 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): pg_start_backup, pg_stop_backup, pg_stop_backup, pg_create_restore_point, pg_switch_xlog, pg_xlog_replay_pause, pg_xlog_replay_resume, pg_rotate_logfile, pg_reload_conf, pg_stat_reset, pg_stat_reset_shared, pg_stat_reset_single_table_counters, pg_stat_reset_single_function_counters.

<p>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 synchronnous_commit na hodnotu remote_apply. 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).

<p>Novým a pro mne hodně důležitým timeoutem je idle_in_transaction_session_timeout. 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.

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

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

<p>Pohled pg_stat_activity byl rozšířen o další sloupce popisující zámek na který se čeká:

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;           │
└──────────────────┴───────────────────────────────┘


Zobrazení postupu (progresu) provádění příkazu

<p>Pokud zrovna jako já musíte občas pustit příkaz VACUUM 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 VACUUM (pozor: příkaz VACUUM FULL není podporován). Předpokládám, že podpora pro další příkazy a možná i pro příkaz SELECT bude rychle následovat.

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             │
└────────────────────┴───────────────┘

<p>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ý.

Konzole

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

<p>Asi každý, kdo používá konzoli, zná volbu -c. 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 -X.

<p>Novým backslashovým příkazem je \errverbose. 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.

postgres=# select 10/0;
ERROR:  division by zero

postgres=# \errverbose 
ERROR:  22012: division by zero
LOCATION:  int4div, int.c:719

<p>Příkaz \gexec navazuje na příkaz \gset. Příkaz \gset provede příkaz a uloží výsledek do psql proměnné. Příkaz \gexec provede příkaz a výsledek chápe jako jeden nebo N příkazů, které jsou vykonány. Tento postup je znám jako meta programování. Zatím jsem si občas pomohl tak, že v jedné instanci psql jsem si vygeneroval SQL příkazy, které jsem přez rouru poslal do druhé instance psql.

psql -X -At -c "SELECT 'select current_date' FROM generate_series(1,3)" | psql -S

nebo

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)

<p>Šlehačkou na dortu je příkaz \crosstabview. Zobrazí křížovou sestavu pro naposledy zadaný dotaz.

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)

<p>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.

pg_dump

<p>Trochu nepříjemným překvapením bylo zjištění, že pg_dump tiše ignoruje špatně explicitně zadané názvy exportovaných tabulek (musí existovat alespoň jedna tabulka ze seznamu).

pg_dump -t existující_tabulka -t neexistující_tabulka 

neskončí chybou. Nová volba --strict-names zajistí restriktivnější chování. Pokud neexistuje tabulka se zadaným jménem, dump skončí s chybou. Totéž platí i pro pg_restore.

<p>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 pg_class případně pg_proc. Nově jsou tyto změny exportovány.


Foreign Data Wrappers (FDW)

<p>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.

<p>Nyní lze:

  • definovat seznam extenzí jejichž immutable funkce a operátory budou provedeny na cizím serveru,
  • spojení a řazení lze přesunout na cizí server:
    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)
    
  • 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:
    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)
    

<p>Při vytváření FDW tabulek jsem pokaždé zapoměl na vytvoření mapování pro uživatele. Nyní je mapování nepovinné:

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)

Odpadl jeden často zbytečný krok.

Výkon

<p>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.

<p>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í).

<p>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ů:

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)

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.

<p>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.

<p>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).

<p>Postgres také nyní dovoluje sdílení mezivýpočtů agregačních funkcí - pokud například počítáte funkci sum a funkci avg 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).

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

Paralelizace výpočtu dotazu

<p>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.

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)

Všimněte si důležitého uzlu Gather, který startuje paralelní procesu, a od nich pak přebírá výsledky. Přepínač max_parallel_degree 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.

<p>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 <a href="http://blog.cleverelephant.ca/2016/03/parallel-postgis.html">paralelního PostGISu</a>.

Změny pro aplikační vývojáře

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

<p>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í:

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 <module>
	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

<p>Funkce parse_ident pomůže s separací SQL identifikátoru:

postgres=# SELECT parse_ident('"divne schema".nazev_tabulky');
           parse_ident           
════════════════════════════════
 {"divne schema",nazev_tabulky}
(1 row)

<p>Funkce num_nulls a num_nonnulls 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:

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).

<p>Určitě znáte funkci pg_size_pretty. Funkce pg_size_bytes 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:

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

<p>Nově lze extenzi pg_trgm použít i pro vyhledávání podobných slov. Můžeme použít novou funkci word_similarity(text,text) a známý operátor vzdálenosti <->.

postgres=# SELECT word_similarity('Skalice','Skála');
 word_similarity 
═════════════════
            0.25
(1 row)

<p>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:

postgres=# SELECT phraseto_tsquery('simple','Příliš žlutý kůň');
		  phraseto_tsquery          
════════════════════════════════════
 ( 'příliš' <-> 'žlutý' ) <-> 'kůň'
(1 row)

<p>Zavádí se nová syntaxe pro práci s poli. Lze určit jen dolní nebo horní index:

postgres=# SELECT d[2:], d[:2] FROM (SELECT ARRAY[1,2,3] as d) s;
   d   │   d   
═══════╪═══════
 {2,3} │ {1,2}
(1 row)

<p>Byla opravena chyba ve funkci EXTRACT, 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):

SELECT EXTRACT(YEAR FROM DATE 'infinity');    --  Infinity
 date_part 
-----------
  Infinity
(1 row)

Závěr

<p>Aktuálně to vypadá tak, že se vývoj Postgresu koncetruje zhruba ve třech firmách: <a href="http://2ndquadrant.com/">2ndQuadrant</a>, <a href="http://www.enterprisedb.com/">EDB</a> a <a href="https://postgrespro.com/">Postgres Professional</a> (ř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í <a href="https://wiki.postgresql.org/wiki/Postgres_Professional_roadmap">hodně zajímavých projektů</a> (JIT kompilaci, komprimaci na úrovni stránek, strojové učení).

<p>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 <a href="https://www.citusdata.com/">Citus Data</a>, 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 <a href="http://2ndquadrant.com/en/resources/postgres-xl/">PostgreSQL-XL</a>, 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 <a href="http://2ndquadrant.com/en/resources/pglogical/pglogical-docs/">pglogical</a> - 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í <a href="https://www.pipelinedb.com/">PipelineDB</a> - 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 <a href="http://docs.pipelinedb.com/continuous-views.html">výsledek</a> 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.

<p>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ě.

<p>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 <a href="https://en.wikipedia.org/wiki/Temporal_database">temporální data</a>, 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.