PostgreSQL 11 (2018)

Autor: Pavel Stěhule, 2018

Jak už to u každoročních vydání PostgreSQL bývá, k uživatelům se dostává mix několika větších nových vlastností doplněných desítkami menších novinek a vylepšení. Tým vývojářů je stabilní, stabilní je i vývojový proces, který se překvapivě přísně dodržuje. Z těch výraznějších novinek bych především zmínil JIT pro výrazy, a příkaz CALL, a s tím spojené procedury s možností explicitně řídit transakce. Integrace JIT (ve výchozí konfiguraci vypnuté) je pro mne překvapením - v databázovém světě se jedná o hodně progresivní krok (o to větším překvapením je to v PostgreSQL, kde jsou vývojáři hodně konzervativní) a bezpochyby je PostgreSQL první open source SQL databází, která tuto technologii integruje. Pro uživatele budou zajímavé i další nové funkce - ať už ve využití více CPU pro jeden příkaz - paralel index scan, paralel hash join, postupné vylepšování partitioningu - defaultní partitions, hash partitioning, partition join, podpora přesunu záznamu mezi partitions při UPDATE atd.

Poznámka autora - pro zajímavost a pro názornost budu uvádět i odkazy na jednotlivé patche. Běžný čtenář se jim určitě věnovat nemusí. Pro někoho může být zajímavý styl a způsob kódování PostgreSQL.

psql

Nikdy jsem moc nechápal uživatele, kteří měli problém s ukončením psql. Obvyklé ^d na Linuxu funguje 100% (a windows jsem nikdy neřešil). Když si ale vzpomenu na svoje začátky, kdy jsem téměř vždy killoval vi, tak uznávám, že každý musel někdy začít a začátky nebývají lehké. Od nové verze bude psql reagovat i na slova quit a exit^p. Tato slova zafungují, pouze pokud jsou na začátku řádku jednořádkového příkazu.

Od minulé verze je možné v psql podmínit zpracování příkazů příkazem \if \elif \else \endif. Jelikož se psql běžně používá pro deployment v prostředích s různými verzemi PostgreSQL, je praktické nějak jednoduše odlišit provádění kódu podle verze PostgreSQL. K tomu by měla posloužit sada nových psql proměnných: SERVER_VERSION_NAME, SERVER_VERSION_NUM, VERSION a VERSION_NAME^p. K dokonalosti chybí možnost vyhodnotit základní výrazy pouze v psql (bez komunikace se serverem). Pro lokální vyhodnocení výrazu lze výraz zapsat mezi obrácené apostrofy. Pak se výraz vyhodnotí lokálně shellem:

\set myvar 20
\if `[ :myvar -le 10 ] && { echo yes; } || echo no;`
\echo "mensi rovno"
\else
\echo "vetsi"
\endif

To ovšem nemusí (a aktuálně nemůže) fungovat na windows. Samozřejmě, že jakýkoliv validní výraz lze vyhodnotit na straně serveru příkazem \gset:

SELECT :SERVER_VERSION_NUM >= 11000 AS ver_min_11 \gset
\if :ver_min_11
\echo "Server je 11 nebo novejsi"
\else
\echo "Server je starsi nez verze 11"
\endif

V nové verzi už je možné detekovat existenci klientské proměnné zápisem :{?nazev}^p. Nově také můžeme používat proměnné zobrazující status předchozího SQL příkazu: ERROR, LAST_ERROR_MESSAGE, LAST_ERROR_STATE or SQLSTATE^p.

V psql nikdy silný skriptovací jazyk nebude. Cokoliv složitějšího vždy bude nutné napsat v Perlu, Pythonu, ... . Je ale snahou, aby se v psql daly jednoduše řešit úlohy obvyklé při testování nebo nasazování.

Příkaz \gdesc] zobrazí popis výsledku dotazu (bez jeho provedení)^p</up>:

postgres=# SELECT oid, relname, relkind FROM pg_class \gdesc
┌─────────┬────────┐
│ Column  │  Type  │
╞═════════╪════════╡
│ oid     │ oid    │
│ relname │ name   │
│ relkind │ "char" │
└─────────┴────────┘
(3 rows)

-- může být zajímavé pro výrazy
postgres=# SELECT 10 + 20, 10 + 20.1 \gdesc
┌──────────┬─────────┐
│  Column  │  Type   │
╞══════════╪═════════╡
│ ?column? │ integer │
│ ?column? │ numeric │
└──────────┴─────────┘
(2 rows)

Prostředí uložených procedur

Procedury - příkaz CREATE PROCEDURE

Ve většina databází, kde byly implementovány tzv uložené procedury, se rozlišuje mezi funkcemi a procedurami. Rozdíl není pouze v zápisu. Typicky, ne nutně, se funkce používají v příkazu SELECT, a nelze v nich modifikovat obsah databáze. Procedury jsou aktivované příkazem CALL, nelze je používat v SELECTech, a kód v nich může modifikovat databázi, případně explicitně řídit transakce. Implementace uložených procedur v PostgreSQL je tou výjimkou potvrzující pravidlo. Až do verze 11, programátoři v Postgresu mohli použít pouze funkce (ovšem bez většiny omezení funkcí v ostatních db). Pokud byl kód psán přímo pro Postgres, tak odlišný model (pouze funkce versus procedury a funkce) nepůsobil žádné problémy. Když se kód portoval z jiných databází, tak už s tím občas problémy byly, a občas se část kódu musela přestěhovat do aplikace. Počínaje verzí 11 bude možné i v PostgreSQL psát procedury^p a v těchto procedurách explicitně volat příkazy COMMIT a ROLLBACK^p (s několika omezeními - např. transakce je možné kontrolovat, pouze pokud prováděný kód byl aktivován pouze příkazem CALL). Procedury mohou mít IN a INOUT parametry^p. Koncepčně je návrh procedur v Postgresu podobný Oracle případně DB2.

CREATE OR REPLACE PROCEDURE foo(a int, INOUT b int, INOUT c int)
AS $$
BEGIN
  b := 10 * a;
  c := 10 + a;
END 
$$ LANGUAGE plpgsql;

-- neinteraktivní volání
postgres=# DO $$
DECLARE b int; c int;
BEGIN
  CALL foo(12, b, c);
  RAISE NOTICE 'b: %, c: %', b, c;
END;
$$;
NOTICE:  b: 120, c: 22
DO

-- interaktivní volání
postgres=# CALL foo(13, NULL, NULL);
┌─────┬────┐
│  b  │ c  │
╞═════╪════╡
│ 130 │ 23 │
└─────┴────┘
(1 row)

Optimalizace práce s kompozitními typy v PL/pgSQL

Po klidných letech jsme se v PL/pgSQL dočkali výraznější revize kódu pro práci s kompozitními proměnnými. Do verze 11 v PL/pgSQL existovaly statické kompozitní proměnné (tzv ROW type) a dynamické kompozitní proměnné (tzv RECORD type). Důvod pro toto dělení byl historický - ROW typy byly implementovány jako první, a v Postgresu zůstávaly kvůli větší rychlosti práce s proměnnými tohoto typu. Většina ROW types vychází z tabulkek. Jejich metadata se uloží do cache při prvním spuštění funkce. Bohužel při změně tabulky se tato část cache neaktualizuje a nekonzistence cache může způsobit pád funkce v PL/pgSQL. To byl hlavní důvod proč se po změnách struktur tabulek doporučovalo zavřít všechny spojení do Postgresu. Od verze 11 se staré ROW typy nepoužívají. Poměrně velkým refaktoringem se pořešily výkonnostní problémy RECORD typů a tudíž není důvod proč používat ROW type proměnné^p. Měly by se tím redukovat poinstalační provozní problémy navázané na změny struktury tabulek.

Díky nové implementaci kompozitních typů bylo možné jednoduše přidat možnost jednoduše definovat výchozí hodnoty i pro kompozitní proměnné (dále je možné nastavit zda-li má být proměnná konstantní, případně NOT NULL)^p.

Partitioning

Oblastí, kde je v PostgreSQL 11 nejviditelnější pokrok, je partitioning. Ve verzi 11 se navazuje na nový partitioning z verze 10 a přidávají se nové funkce nebo odstraňují některé limity. Měl by se zrychlit INSERT do partition, a také se změnil algoritmus (partition pruning) pro identifikaci partitions se kterými se bude v SQL příkazu pracovat^p.

Pro mne asi nejzajímavější vlastností je možnost definovat defaultní partition^p a automatický přesun záznamu mezi partitions^p. Defaultní partition se použije, pokud neexistuje odpovídající cílová partition:

CREATE TABLE data(a text, vlozeno date) PARTITION BY RANGE(vlozeno);
CREATE TABLE data_2016 PARTITION OF data FOR VALUES FROM ('2016-01-01') TO ('2016-12-31');
CREATE TABLE data_2017 PARTITION OF data FOR VALUES FROM ('2017-01-01') TO ('2017-12-31');
CREATE TABLE data_other PARTITION OF DATA DEFAULT;

postgres=# INSERT INTO data VALUES('hello', '2015-06-08');
INSERT 0 1
postgres=# SELECT * FROM data_other ;
┌───────┬────────────┐
│   a   │  vlozeno   │
╞═══════╪════════════╡
│ hello │ 2015-06-08 │
└───────┴────────────┘
(1 row)

postgres=# UPDATE data SET a = 'Hi' WHERE vlozeno = '2015-06-08';
UPDATE 1
postgres=# SELECT * FROM data_other ;
┌────┬────────────┐
│ a  │  vlozeno   │
╞════╪════════════╡
│ Hi │ 2015-06-08 │
└────┴────────────┘
(1 row)

postgres=# UPDATE data SET a = 'Nazdar', vlozeno = '2017-08-23' WHERE vlozeno = '2015-06-08';
UPDATE 1
postgres=# SELECT * FROM data_other ;
┌───┬─────────┐
│ a │ vlozeno │
╞═══╪═════════╡
└───┴─────────┘
(0 rows)

postgres=# SELECT * FROM data_2017;
┌────────┬────────────┐
│   a    │  vlozeno   │
╞════════╪════════════╡
│ Nazdar │ 2017-08-23 │
└────────┴────────────┘
(1 row)

V psql bylo vylepšeno zobrazení struktury partitiovaných tabulek^p:

postgres=# \d data
                Table "public.data"
┌─────────┬──────┬───────────┬──────────┬─────────┐
│ Column  │ Type │ Collation │ Nullable │ Default │
╞═════════╪══════╪═══════════╪══════════╪═════════╡
│ a       │ text │           │          │         │
│ vlozeno │ date │           │          │         │
└─────────┴──────┴───────────┴──────────┴─────────┘
Partition key: RANGE (vlozeno)
Number of partitions: 3 (Use \d+ to list them.)

postgres=# \d+ data
                                    Table "public.data"
┌─────────┬──────┬───────────┬──────────┬─────────┬──────────┬──────────────┬─────────────┐
│ Column  │ Type │ Collation │ Nullable │ Default │ Storage  │ Stats target │ Description │
╞═════════╪══════╪═══════════╪══════════╪═════════╪══════════╪══════════════╪═════════════╡
│ a       │ text │           │          │         │ extended │              │             │
│ vlozeno │ date │           │          │         │ plain    │              │             │
└─────────┴──────┴───────────┴──────────┴─────────┴──────────┴──────────────┴─────────────┘
Partition key: RANGE (vlozeno)
Partitions: data_2016 FOR VALUES FROM ('2016-01-01') TO ('2016-12-31'),
            data_2017 FOR VALUES FROM ('2017-01-01') TO ('2017-12-31'),
            data_other DEFAULT

S vyjímkou přesunu záznamu do jiné partition je podporovaný i merge - v PostgreSQL příkaz INSERT ON CONFLICT. Vždy by měla být funkční varianta INSERT ON CONFLICT DO NOTHING^p. Příkaz INSERT ON CONFLICT DO UPDATE by měl fungovat se zmíněným omezením^p.

Novinkou je základní podpora hash partitioningu^p:

CREATE TABLE pht1 (a int, b int, c text) PARTITION BY HASH(c);
CREATE TABLE pht1_p1 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE pht1_p2 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE pht1_p3 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 2);

Pokud se shoduje partitioning dvou joinovaných tabulek, pak lze použít tzv partition-wise join^p. Join na úrovni partition je úspornější. Na druhou stranu, detekce jestli je možné tento typ joinu použít, je výpočetně náročná úloha, a proto je defaultně vypnutá. Lze ji zapnout volbou enable_partition_wise_join = on. Podobný trik lze použít i pro agregaci, pokud agregujeme alespoň podle jednoho klíče použitého pro řízení partitioningu^p. Tím, že se agregace provede na úrovni partitions (a nikoliv tabulky) jakoby zmenšíme velikost vstupních dat, a tudíž je možné, že se lépe vejdeme do operační paměti a použije se rychlejší (paměťově náročnější) algoritmus. Také tato optimalizace je kvůli náročnosti na plánování defaultně vypnutá a pro její aktivaci je nutné ji zapnout konfigurací enable_partitionwise_aggregate = on.

Nově také můžeme psát FOR EACH ROW after triggery i pro partitiované tabulky^p.

Od PostgreSQL 11 můžeme vytvářet indexy nad partitiovanými tabulkami^p. S vytvořením indexu na partitiované tabulce se index automaticky vytvoří i na všech partitions (v Postgresu, rekurzivně na všech tabulkách, které se používají jako partition). Partitiované tabulky mohou obsahovat i unikátní index^p, případně na těchto tabulkách může být definovaný primární klíč. Platí ovšem podmínka, že unikátní index případně primární klíč musí obsahovat atribut(y), který(é) řídí partitioning:

CREATE UNIQUE INDEX ON data(a, vlozeno);
ALTER TABLE data ADD PRIMARY KEY (a, vlozeno);

Zatím nelze vytvořit cizí klíče vůči partitiované tabulce, nicméně alespoň již můžeme v partitiované tabulce používat cizí klíče^p.

Ve starších verzích detekce použitých partitions proběhla pouze v čase plánování dotazu. Pokud při plánování dotazu nebyly k dispozici potřebné informace, tak planner použil všechny partitions. Od nové verze Postgres dokáže eliminovat partitions i v době zpracování dotazu ^p. Měly by být podporované operace append, nested loop a init plans (První poznámka: ještě to trochu kulhá, implementace si neporadí s funkcemi a pseudo konstantami (CURRENT_DATE, CURRENT_TIMESTAMP. Druhá poznámka: existuje patch, který tuto situaci opravuje. Není ale zřejmé, jestli bude aplikován v jedenáctce nebo až v další verzi.).

Administrace

Již v předchozích verzích byl příkaz ALTER TABLE ADD COLUMN extrémně rychlý, a to díky tomu, že došlo pouze ke změně metadat. Na data se vůbec nesahalo. Počínaje verzí 11 to platí i pro příkaz ALTER TABLE ADD COLUMN DEFAULt expr^p.

Nový příkaz pg_verify_checksums slouží k offline ověření kontrolních součtů datových stránek.

Nově si pg_prewarm díky svému Background Work procesu periodicky ukládá stav shared buffers (cache datových stránek v PostgreSQL). Po restartu serveru druhým BG procesem na základě uloženého stavu obnoví obsah cache^p.

Přepínačem --create-slot příkazu pg_basebackup lze vynutit vytvoření trvalého replikačního slotu, jehož název je určen přepínačem --slot.

Novým parametrem tabulky toast_tuple_target v rozmezí 128 bajtů a 8KB můžeme určit velikost hodnot, které se budou ukládat externě - v tzv TOAST tabulkách (výchozí hodnota odpovídá 2KB).

Optimalizace výkonu

Použití více CPU pro příkaz

Velké úsilí stála implementace paralelního hash joinu^p. Pro uživatele s většími databázemi, a náročnějšími dotazy by zrychlení provádění dotazů mělo být výrazné. V nové verzi PostgreSQL může použít více CPU i pro dotazy obsahující poddotazy (což v předchozích verzích zpracování přes více CPU blokovalo)^p. Paralelní zpracování dotazů je nyní možné i pro dotazy příkazem CREATE TABLE AS SELECT. Zparalelizována byla i klauzule UNION ALL^p:

SET LOCAL parallel_setup_cost = 10;
EXPLAIN (COSTS OFF)
SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1
UNION ALL
SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1;
                     QUERY PLAN                     
----------------------------------------------------
 Gather
   Workers Planned: 4
   ->  Parallel Append
         ->  Parallel Seq Scan on tenk1
               Filter: (fivethous = (tenthous + 1))
         ->  Parallel Seq Scan on tenk1 tenk1_1
               Filter: (fivethous = (tenthous + 1))
(7 rows)

Paralelizovat lze i řazení^p. Zrychlení by mělo být vidět při vytváření indexů CREATE INDEX.

Statistiky, odhady, a optimalizace prováděcích plánů

Nově se při kalkulaci odhadů rozlišuje] mezi operátorem < a operátorem <=^p. Ve starších verzích se <= převádělo na <. Totéž platí a platilo pro > a >=. Existovaly situace, kdy starší zjednodušená implementace dávala špatné odhady, např. když hodnoty a a b ve výrazu x BETWEEN a AND b byly blízko sebe.

V PostgreSQL se ke každému sloupci udržuje seznam nejčastějších hodnot (Most Common Values) včetně pravděpodobnosti výskytu těchto hodnot. Do verze 11 se do tohoto seznamu ukládaly hodnoty jejichž pravděpodobnost výskytu bylo o 25% vyšší než pravděpodobnost výskytu všech not null hodnot. Nově je seznam nejčastějších hodnot definován jako co největší množina hodnot, jejichž pravděpodobnost výskytu je větší než průměrná pravděpodobnost výskytu hodnot nezařazených do seznamu MCV. Nová kalkulace by měla se lépe chovat v případech, kdy několik málo hodnot má větší než průměrnou pravděpodobnost výskytu. Pro rovnoměrné rozdělení by měl být seznam MCV menší a pro nerovnoměrná rozdělení naopak větší^p.

Změnil se i výpočet ceny za bitmap index scan^p. Nová kalkulace je bližší realitě, a měla by prodražit bitmap index scan v situacích, kdy se vytvořená bitmapa nevejde do work_mem, a místo s mapou řádek se pracuje s mapou stránek.

Práce s indexy

Příkaz VACUUM vytváří (nastavuje) bitmapu stránek, které neobsahují smazané záznamy (visibility map). Pokud je tato mapa k dispozici, a pokud index obsahuje všechny potřebné sloupce k vykonání dotazu, pak optimalizátor může použít přístupovou metodu (k datům) index only scan. Tím se ušetří náhodné přístupy do tabulky. Od verze jedenáct se stejná optimalizace může použít i pro bitmap scan (sekvenční čtení souboru akcelerované indexem)^p.

Index only scan nelze použít, pokud index neobsahuje všechny potřebné hodnoty použité v dotazu. Od verze 11 je možné index vytvořit včetně tzv neklíčových položek (nepoužijí se pro vyhledávání, ale jsou uložené v indexu)^p. Pro indexy obsahující neklíčové položky se také používá název covering indexes:

CREATE UNIQUE INDEX ON upsert(lower(key)) INCLUDE (payload);
CREATE TABLE lcku_table (id INTEGER, value TEXT, PRIMARY KEY (id) INCLUDE (value));

V případě, že se aktualizuje neoindexovaný sloupec, tak PostgreSQL použije tzv hot-update (Heap Only). Tento způsob aktualizace je výrazně rychlejší, jelikož nevyžaduje aktualizaci indexů nad tabulkou. V nové verzi PostgreSQL použije hot-update i po aktualizaci indexovaného sloupce funkcionálním indexem, pokud nedojde ke změně výsledku zaindexované vypočítané hodnoty^p. Tato novinka by měla zrychlit aktualizace řádků s XML, případně JSON dokumenty, kde se používá funkcionální index pro přístup k vybraným klíčovým položkám (které jsou často neměnné).

Nový operátor ^@ je akcelerován SP-GiST indexem a může být použitý jako alternativa pro vyhledávání podle prefixu řetězce^p:

-- stara klasika, nefunguje pro ^@
CREATE INDEX ON obce(nazev varchar_pattern_ops);
postgres=# EXPLAIN SELECT * FROM obce WHERE nazev like 'Ben%';
┌──────────────────────────────────────────────────────────────────────────────────────┐
│                                      QUERY PLAN                                      │
╞══════════════════════════════════════════════════════════════════════════════════════╡
│ Index Scan using obce_nazev_idx on obce  (cost=0.28..8.30 rows=1 width=41)           │
│   Index Cond: (((nazev)::text ~>=~ 'Ben'::text) AND ((nazev)::text ~<~ 'Beo'::text)) │
│   Filter: ((nazev)::text ~~ 'Ben%'::text)                                            │
└──────────────────────────────────────────────────────────────────────────────────────┘
(3 rows)

-- nova moznost
CREATE INDEX ON obce USING spgist(nazev);

postgres=# EXPLAIN SELECT * FROM obce WHERE nazev like 'Ben%';
┌──────────────────────────────────────────────────────────────────────────────────────┐
│                                      QUERY PLAN                                      │
╞══════════════════════════════════════════════════════════════════════════════════════╡
│ Index Scan using obce_nazev_idx on obce  (cost=0.15..8.17 rows=1 width=41)           │
│   Index Cond: (((nazev)::text ~>=~ 'Ben'::text) AND ((nazev)::text ~<~ 'Beo'::text)) │
│   Filter: ((nazev)::text ~~ 'Ben%'::text)                                            │
└──────────────────────────────────────────────────────────────────────────────────────┘
(3 rows)

-- novy operator
postgres=# EXPLAIN SELECT * FROM obce WHERE nazev ^@ 'Ben';
┌────────────────────────────────────────────────────────────────────────────┐
│                                 QUERY PLAN                                 │
╞════════════════════════════════════════════════════════════════════════════╡
│ Index Scan using obce_nazev_idx on obce  (cost=0.15..8.17 rows=1 width=41) │
│   Index Cond: ((nazev)::text ^@ 'Ben'::text)                               │
└────────────────────────────────────────────────────────────────────────────┘
(2 rows)

Ostatní

Doménové typy jsou jakési aliasy pro ostatní datové typy. Na základě typu bigint si mohu vytvořit doménový typ pro identifikátory:

CREATE DOMAIN id_type AS bigint NOT NULL;

Pak kdekoliv mohu používat id_type ve smyslu názvu datového typu. Nově lze vytvářet domény z kompozitních typů^p, a také lze vytvářet pole doménového typu^p. Doménové typy jsou ANSI/SQL alternativou ku referencovaným typům používaným v Oracle.

Nově jsou B-tree indexy optimalizovány pro vkládání zvyšujících se hodnot^p. Pokud byla vložena aktuálně největší hodnota, pak se její pozice a hodnota uloží do cache. V případě, že vkládáme další ještě větší hodnotu, pak se neprohledává strom indexu, ale rovnou se použije pozice z cache. Na desítce je vložení zvyšujících se hodnot o něco málo rychlejší než vkládání náhodných dat. V PostgreSQL 11 je vložení cca 1M zvyšujících se hodnot o ⅓ rychlejší než vložení náhodných hodnot. Jaký bude výsledek v praxi jde těžko odhadnout - většinou je nutné obsluhovat více indexů, a zvyšující se posloupnost je většinou pouze u několika málo sloupců.

Přidáním podpory klauzule RANGE offset PRECEDING/FOLLOWING je v PostgreSQL kompletní podpora window funkcí, tak jak ji definuje ANSI/SQL^p. Pro většinu uživatelů tato skutečnost bude téměř neviditelná.

Naopak, uživatelé, kteří používají logickou replikaci, a těch je docela hodně, uvítají, že v nové verzi logická replikace podporuje i příkaz TRUNCATE^p.

Pro uživatele fulltextu v Postgresu bude zajímavá funkce websearch_to_tsquery a možná i funkce phraseto_tsquery^p. První umožňuje specifikovat výběr zápisem obvyklým pro webové aplikace - např. "sad cat" or "fat rat". Tato funkce je také tolerantní k chybám zápisu hledaného výrazu. Druhá funkce vyhledává fráze - slova ve správném pořadí:

-- vyžaduje speciální syntax
postgres=# SELECT * FROM obce WHERE to_tsvector(nazev) @@ to_tsquery('mladá boleslav');
ERROR:  syntax error in tsquery: "mladá boleslav"

-- nevyžaduje speciální syntax
postgres=# SELECT * FROM obce WHERE to_tsvector(nazev) @@ websearch_to_tsquery('mladá boleslav');
┌─────┬──────────┬────────────────┬────────────┬───────────┬──────────┬─────────┐
│ id  │ okres_id │     nazev      │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞═════╪══════════╪════════════════╪════════════╪═══════════╪══════════╪═════════╡
│ 613 │ CZ0207   │ Mladá Boleslav │      22462 │     22288 │     38.9 │    42.1 │
└─────┴──────────┴────────────────┴────────────┴───────────┴──────────┴─────────┘
(1 row)

postgres=# SELECT * FROM obce WHERE to_tsvector(nazev) @@ websearch_to_tsquery('mladá boleslav or mladá vožice');
┌──────┬──────────┬────────────────┬────────────┬───────────┬──────────┬─────────┐
│  id  │ okres_id │     nazev      │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞══════╪══════════╪════════════════╪════════════╪═══════════╪══════════╪═════════╡
│  613 │ CZ0207   │ Mladá Boleslav │      22462 │     22288 │     38.9 │    42.1 │
│ 1705 │ CZ0317   │ Mladá Vožice   │       1335 │      1392 │     39.2 │    41.4 │
└──────┴──────────┴────────────────┴────────────┴───────────┴──────────┴─────────┘
(2 rows)

postgres=# SELECT * FROM obce WHERE to_tsvector(nazev) @@ websearch_to_tsquery('mladá boleslav or vožice');
┌──────┬──────────┬─────────────────────────┬────────────┬───────────┬──────────┬─────────┐
│  id  │ okres_id │          nazev          │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞══════╪══════════╪═════════════════════════╪════════════╪═══════════╪══════════╪═════════╡
│  613 │ CZ0207   │ Mladá Boleslav          │      22462 │     22288 │     38.9 │    42.1 │
│ 1705 │ CZ0317   │ Mladá Vožice            │       1335 │      1392 │     39.2 │    41.4 │
│ 1712 │ CZ0317   │ Nová Ves u Mladé Vožice │         91 │        86 │     40.9 │    43.8 │
│ 1764 │ CZ0317   │ Zhoř u Mladé Vožice     │         44 │        51 │     40.1 │    38.2 │
└──────┴──────────┴─────────────────────────┴────────────┴───────────┴──────────┴─────────┘
(4 rows)

Zajímavě vypadá funkce strict_word_similarity, která se podobá funkci word_similarity, ale na rozdíl od ní bere v potaz hranice slov^p.

FDW API umožňovalo úpravy dat cizích tabulek, jelikož se ale jelo po jednom záznamu nad primárním klíčem, tak tyto operace byly relativně náročné. Počínaje novou verzí je možné distribuovat příkazy UPDATE, DELETE na cizí server, a který je může provést efektivně jako hromadnou operaci^p.

I v minulých verzích bylo možné napsat extenze, které provedly určitou činnost při přihlášení a odhlášení do databáze. Jelikož zachycení těchto událostí nebylo explicitně podporováno, tak tyto extenze byly postavené tak trochu na vodě. V PostgreSQL 11 je možné využít hooky session_start_hook a session_end_hook, a extenzi s tímto chováním napsat čistě a jednoduše^p.

Ve verzi 11 je lze příkazem LOCK explicitně zamknout pohled podobně jako tabulku^p. Zamknutí pohledu vedek k rekurzivnímu zamknutí tabulek použitých v pohledu.

Pokračuje se v trendu separovat práva superuživatele a umožnit některá jeho práva nastavit běžnému uživateli. Role pg_read_server_files, pg_write_server_files a pg_execute_server_program umožní uživateli použít funkce pro čtení souborů serveru, případně pro zápis do souboru nebo spuštění programu ze serveru - příkazy COPY a COPY TO/FROM PROGRAM na straně serveru^p.

pgbench je aplikace pro většinu uživatelů PostgreSQL absolutně nezajímavá. Každým rokem ovšem sdílí více a více kódu s SQL konzolí psql. A proto může být zajímavé, že od nové verze je k dispozici relativně široká nabídka matematických a logických operátorů^p, které jak doufám, se dostanou i do psql:

\set c0 debug(1.0 = 0.0 and 1.0 != 0.0)
\set c1 debug(0 = 1 Or 1.0 = 1)
\set c4 debug(case when 0 < 1 then 32 else 0 end)

TimescaleDB & PipelineDB

Nad kódem PostgreSQL vznikla řada dalších databází optimalizovaných, rozšířených pro specifické úlohy. BSD licence, se kterou je licencován kód PostgreSQL, ničemu takovému nebrání, a stejně tak se těmto forkům (většinou komerčním) nebrání komunita. Je to způsob, jak se další a další vývojáři mohou seznámit s kódem Postgresu, a případně začít psát i pro upstream. Navíc složitější kód se nevyplácí držet mimo upstream - pozdější synchronizace je nepříjemná a náročná práce. Aktuálně asi nejpopulárnější forky PostgreSQL jsou EnterpriseDB (cílí na uživatele Oracle) a Citus (OLAP a MPP). Staršími forky jsou např. databáze Netezza, Pivotal Greenplum database nebo Amazon Redshift.

Pokud se jedná o více změn, tak pak údržba nebo vývoj vlastního forku PostgreSQL není levná záležitost (není problém mít několik i možná desítek kratších patchů). Starší forky jsou úplně odtržené od Postgresu a mnohokrát z hlediska SQL, případně uživatelského komfortu jsou zastaralé. Greenplum už cca 2 roky migruje z PostgreSQL 8.2, a aktuálně jsou na 9.0. V CitusData byli první, kdo svůj fork přepsali do formátu extenze PostgreSQL. Extenze v PostgreSQL mají svůj kód izolovaný od kódu PostgreSQL a tudíž údržba extenze je výrazně jednodušší a méně pracná než údržba forku. Další databází, která byla původně forkem a nyní se transformovala do extenze je PipelineBD. Stejně tak, jak je údržba extenze jednodušší než údržba forku, tak je používání extenze jednodušší než používání forku.

TimescaleDB

TimescaleDB je relativně nová komerční (s otevřeným kódem) extenze, která do PostgreSQL přidává některé vlastnosti tzv timeseries databází. Rozšiřuje partitioning v PostgreSQL o automaticky vytvářené partitions (definované časovým obdobím). Kromě jedné povinné dimenze pro partitioning (časové období) lze vytvářet partitions ještě podle jednoho vybraného atributu. Ve výsledku můžeme dostat 2D partitioning (např. podle času a kódu země). Jelikož je partitioning v TimescaleDB předepsán hodně natvrdo, tak může být a je lépe optimalizován než obecný partitioning v Postgresu (ten má problémy s větším počtem patitions (cca 100 je ještě ok)).

Kromě toho umí v dotazu identifikovat některé vybrané funkce (např. date_trunc) a lépe optimalizovat dotazy, které tyto funkce používají. Pro Postgres jsou téměř všechny funkce black box, a tudíž optimalizátor nemůže být příliš agresivní (navíc ani správně nedokáže odhadnout výsledek).

S touto extenzí Postgres automaticky vytváří množství menších partitions. Jelikož se indexy nad menšími tabulkami dobře vejdou do RAM, tak je aktualizace indexu rychlou operací a tudíž se TimescaleDB může pochlubit relativně stabilní rychlostí vkládání dat do db. Některé časté dotazy z oblasti timeseries databází jsou pak díky rozpoznání chování funkce a následné optimalizaci znatelně rychlejší. Na některých úlohách může být vidět, že TimescaleDB je hybridní databáze a nativní timeseries databáze budou výrazně rychlejší. Nicméně v mnoha případech díky TimescaleDB bude výkon Postgresu pro danou úlohu dostačující a naopak díky Postgresu uživatel získá komfort v podobě silného SQL (a v případě, že již zná Postgres, tak si nemusí svůj stack rozšiřovat o další technologii).

PipelineDB

Existence proudových databází (stream databases) je drtivé většině uživatelů dobře utajena, ačkoliv by se řadě uživatelů perfektně hodily. O těchto databázích se hodně mluvilo na přelomu milénia, bohužel bez větší odezvy uživatelů. Dnes se k těmto databázím dostáváme oklikou přes architektury založené na zpracování proudů dat (např. Apache Kafka). PipelineDB je pokus přiblížit tyto databáze uživatelům - do klasického Postgresu zavádí nové objekty a operace: stream buffer, continuous aggregation, sliding window queries, atd.

CREATE STREAM wiki_stream (hour timestamp, project text, title text, view_count bigint, size bigint);
CREATE CONTINUOUS VIEW wiki_stats AS
SELECT hour, project,
        count(*) AS total_pages,
        sum(view_count) AS total_views,
        min(view_count) AS min_views,
        max(view_count) AS max_views,
        avg(view_count) AS avg_views,
        percentile_cont(0.99) WITHIN GROUP (ORDER BY view_count) AS p99_views,
        sum(size) AS total_bytes_served
FROM wiki_stream
GROUP BY hour, project;"

Kontinuální pohled, jestli je to možné tak přeložit, je jakási obdoba neustále aktualizovaného materializovaného pohledu. INSERT do proudu wiki_stream způsobí chytrou aktualizaci pohledu. Neprovádí se klasická agregace, ale pouze aktualizace čítačů. Díky tomu bez větších nároků na CPU a IO můžeme mít k dispozici on-line agregovaná data.

Letošní rok je pro PipelineDB přelomový. Z forku Postgresu se mění na extenzi Postgresu. Po způsobu Citusu a TimescaleDB. Bohužel v extenzích PostgreSQL není možné rozšiřovat SQL - zatím nikdo nepřišel jak dynamicky rozšiřovat parser generovaný bisonem. Tudíž PipelineDB přijde o svá rozšíření SQL (která podle mne zjednodušují a zpřehledňují zápis), na oplátku se zbaví nutnosti udržovat své vlastní buildy PostgreSQL. Údržba extenze je výrazně omezenější, jednoduší. Naštěstí jazyk a prostředky v PostgreSQL jsou dostatečně silné, takže požadovanou funkcionalitu je možné implementovat:

-- původní zápis
CREATE STREAM <stream>
-- nový zápis
CREATE FOREIGN TABLE s (x integer, ...) SERVER pipelinedb;

-- původní zápis
CREATE CONTINUOUS VIEW continuous_view AS SELECT count(*) FROM s;
-- nový zápis
CREATE VIEW continuous_view WITH (action=materialize, ...) AS SELECT count(*) FROM s;

Mám rád bohaté barokní SQL. Líbí se mi stará syntaxe, která je názorná, nekomplikovaná. Naprosto ale rozumím důvodům, proč PipelineDB přechází na jiný formát. Extenzi je nepoměrně jednodušší udržovat i doručovat uživatelům, případně budoucím zákazníkům. Nicméně, kdyby někdo dokázal dynamicky rozšiřovat parser generovaný z gramatiky bisonu, tak ať se mi ozve. Rád mu zaplatím pivko.

Závěr

PostgreSQL má za sebou dobré roky, kdy se začíná postupně a úspěšně nasazovat i v korporátním prostředí, ve kterém se o open source databáze ještě před několika lety nezavadilo, a stávající uživatelé jsou vesměs spokojeni. Veškerá energie, co jde do vývoje a práce vývojářů je vidět.

Co můžeme čekat od verze 12? Pravděpodobně podporu JSONu podle ANSI/SQL, nové techniky optimalizace dotazu (přepis podmínek OR na UNION, natlačení agregace před JOIN, nové typy úložišť (např. zheap, které není nutné vacuuovat), možná session proměnné, skoro určitě MERGE podle ANSI/SQL, možná transparentní šifrování. Plánů, prototypů i téměř hotových patchů je hodně. Uvidíme, co bude příští rok.

---

• 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 9.6 (2016) odteď paralelně
• PostgreSQL 10 (2017) - drsně rozběhnutý slon
• PostgreSQL 12 (2019)
• PostgreSQL 13 (2020)
• PostgreSQL 14 (2021)
• PostgreSQL 15 (2022)
• PostgreSQL 16 (2023)
• PostgreSQL 17 (2024)