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).
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.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 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ý.
(Aktualizace 22.3.2020 - testy na FC32 ukazují, že i české locales je pro TRUST_STRXFRM nepoužitelné).
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_counters, pg_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ř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)
Šlehačkou na dortu je příkaz \crosstabview. Zobrazí křížovou sestavu (nebo kontingenční tabulku) 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
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é 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
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, 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
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 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_workers_per_gather (původně 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 koncetruje 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í [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 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, 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 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
- Novinky 2006 (PostgreSQL 8.2)
- Slon nezapomíná (co nás čeká v PostgreSQL 8.3)
- PostgreSQL v roce 2009 (PostgreSQL 8.4)
- PostgreSQL 9.0 - nový začátek
- PostgreSQL 9.1 - aneb stále vpřed
- PostgreSQL 9.2 (2012)
- PostgreSQL 9.3 (2013)
- PostgreSQL 9.4 (2014): transakční sql json databáze
- PostgreSQL 9.5 (2015) držte si klobouky, zrychlujeme
- PostgreSQL 10 (2017) - drsně rozběhnutý slon
- PostgreSQL 11 (2018)
- PostgreSQL 12 (2019)
- PostgreSQL 13 (2020)
- PostgreSQL 14 (2021)
- PostgreSQL 15 (2022)
- PostgreSQL 16 (2023)
- PostgreSQL 17 (2024)