Hlavní navigace

PostgreSQL 9.6: odteď paralelně na více CPU

Pavel Stěhule 27. 5. 2016

Netradičně se letos dočkáme ještě jedné velké verze PostgreSQL. Ta představuje zásadní milník ve vývoji, uživatelé mají 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  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.

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.

Ř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 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.or­g/wiki/Abbreviated_keys_glib­c_issue.

K dispozici je 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 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

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

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

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.

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

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.

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

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

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

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

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.

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ří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řes 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)

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

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

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

Příkaz 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.

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)

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:

  • 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ři vytváření FDW tabulek jsem pokaždé zapomně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

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

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.

Tom Lane přepsal část plánovače, která realizovala optimalizaci agregačních funkcí. Starý kód se jednak hůře udržoval a hlavně 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 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).

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

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 2× – 3×. 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í procesy, 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.

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 paralelního PostGISu.

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

Konečně je opravena chyba, která mne dlouho iritovala. PLpgSQL příkaz RAISE 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í:

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

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)

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

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

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)

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)

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)

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

Aktuálně to vypadá tak, že se vývoj Postgresu koncentruje zhruba ve třech firmách: 2ndQuadrant, EDB a 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í 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 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 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 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í 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 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, byť pomalu, adopce PostgreSQL i v hodně konzervativním korporátním prostředí – příkladem může být nasazení PostgreSQL v České spořitelně. Vlaštovky 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 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.

Našli jste v článku chybu?

27. 5. 2016 13:24

MySQL i MariaDB se také posouvají dopředu - v něčem tyto databáze konvergují, v něčem nikoliv. MySQL, MariaDB jsou spíš zaměřené na velice intenzivní jednořádkové jednoduché operace nad základními datovými typy, Postgres teď už inklinuje k OLAPu - složitější dotazy, složitější schémata, případně nabízí větší komfort v SQL - neatomické typy, podpora CTE, analytických funkcí, XML, JSON, vlastních typů, ...

U MariaDB mám problém sledovat vývoj - některé funkce přebírají z MySQL, něco si píší samy …

27. 5. 2016 13:11

Postgres je databáze - phpčkáři dost často potřebují jenom cache. Dneska se lze asi bez ACIDu obejít, ale potřebujete mnohem komplexnější infrastrukturu - data ukládáte redundantně, v případě výpadku musíte být schopný chybějící data zrekonstruovat, místo update operací děláte pouze append atd.

Navíc se víc kombinují ACID a ne ACID databáze - účetnictví a evidence se dělají v SQL databázích, monitoring, data mining v ne ACID databázích. Hodně záleží s jakými objemy dat se pracuje, jestli jsou k…

Podnikatel.cz: EET zvládneme, budou horší zákony

EET zvládneme, budou horší zákony

DigiZone.cz: ČRo rozšiřuje DAB do Berouna

ČRo rozšiřuje DAB do Berouna

120na80.cz: Pánové, pečujte o svoje přirození a prostatu

Pánové, pečujte o svoje přirození a prostatu

Lupa.cz: Propustili je z Avastu, už po nich sahá ESET

Propustili je z Avastu, už po nich sahá ESET

Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

Lupa.cz: UX přestává pro firmy být magie

UX přestává pro firmy být magie

Lupa.cz: Insolvenční řízení kvůli cookies? Vítejte v ČR

Insolvenční řízení kvůli cookies? Vítejte v ČR

Root.cz: Vypadl Google a rozbilo se toho hodně

Vypadl Google a rozbilo se toho hodně

Podnikatel.cz: Na poslední chvíli šokuje vyjímkami v EET

Na poslední chvíli šokuje vyjímkami v EET

Měšec.cz: Jak vymáhat výživné zadarmo?

Jak vymáhat výživné zadarmo?

Lupa.cz: Google měl výpadek, nejel Gmail ani YouTube

Google měl výpadek, nejel Gmail ani YouTube

Lupa.cz: Teletext je „internetem hipsterů“

Teletext je „internetem hipsterů“

Lupa.cz: Avast po spojení s AVG propustí 700 lidí

Avast po spojení s AVG propustí 700 lidí

Vitalia.cz: „Připluly“ z Německa a možná obsahují jed

„Připluly“ z Německa a možná obsahují jed

Podnikatel.cz: Prodává přes internet. Kdy platí zdravotko?

Prodává přes internet. Kdy platí zdravotko?

DigiZone.cz: Sony KD-55XD8005 s Android 6.0

Sony KD-55XD8005 s Android 6.0

Vitalia.cz: Chtějí si léčit kvasinky. Lék je jen v Německu

Chtějí si léčit kvasinky. Lék je jen v Německu

Vitalia.cz: Říká amoleta - a myslí palačinka

Říká amoleta - a myslí palačinka

Podnikatel.cz: Babiše přesvědčila 89letá podnikatelka?!

Babiše přesvědčila 89letá podnikatelka?!

Vitalia.cz: 9 největších mýtů o mase

9 největších mýtů o mase