Autor: Pavel Stěhule, 2023

PostgreSQL 16 (2023)

Bez zbytečného studu je určitě možné konstatovat, že vývoj Postgresu je chaotický. Pracuje se na několika frontách. Na vývoji se podílí full time placení vývojáři i dobrovolníci (placených vývojářů bude už drtivá většina), kteří se z větší části koordinují a řídí sami.Všichni se řídí filozofií "jsme rádi za každou pomocnou ruku, a ať každý dělá, to co ho baví a to co umí". Pořád platí, že vývoj Postgresu je práce kreativní a zajímavá. Nicméně vzhledem k rozsahu projektu, a k jeho rozšíření, je vývoj Postgresu práce docela náročná (a to i časově). a u některých patchů i zdlouhavá. Na druhou stranu, pečlivý přístup k vývoji se vrací. Vidím to u svých zákazníků, kteří používají Posgres. Dost často je to ta nejlépe fungující komponenta v jejich stacku. Provozních problémů s Postgresem je opravdu málo (což ale více méně platí i pro ostatní databáze, které mají kořeny v 80 letech - tento software už je dost starý, aby byl odladěný).

Hlavní motivy aktuálního vývoje Postgresu jsou:

vylepšování logické replikace (vzdáleným cílem je multi master)

snižování režie partitioningu
postupná plná implementace ANSI SQL/JSON
postupná plná integrace libICU
refaktoring optimalizátoru
přechod sestavovacího systému z autoconf na meson
další menší optimalizace a vylepšení napříč platformou

SQL

Oproti loňské verzi, která přišla s implementací příkazu MERGE letošní verze obsahuje pouze jednu drobnou, nicméně významnou novinku. Alias u poddotazu v klauzuli FROM bude volitelný. Povinný alias pil krev dost lidem, zvlášť pokud přecházeli z jiných SQL databází.

-- PostgreSQL 15
SELECT * FROM (SELECT 1) s

-- PostgreSQL 16
SELECT * FROM (SELECT 1)

Podobně jako u dalších DDL příkazů nebude u příkazu CREATE STATISTICS požadovaný název (pokud nebude zadán, vygeneruje se).

Reprezentace čísel

Nově lze zadat čísla v binární, osmičkové a hexadecimální soustavě:

(2023-04-14 12:58:14) postgres=# SELECT 0xFF, 0o273, 0b111;
┌──────────┬──────────┬──────────┐
│ ?column? │ ?column? │ ?column? │
╞══════════╪══════════╪══════════╡
│      255 │      187 │        7 │
└──────────┴──────────┴──────────┘
(1 row)

Z důvodu vyšší přehlednosti lze také v zápisu čísla použít znak podtržítko. Nesmí být na první a ani na poslední pozici, a nesmí se použít bezprostředně za sebou:

(2023-04-14 13:02:06) postgres=# SELECT 10_000, 1_000_000, 1.0_23;
┌──────────┬──────────┬──────────┐
│ ?column? │ ?column? │ ?column? │
╞══════════╪══════════╪══════════╡
│    10000 │  1000000 │    1.023 │
└──────────┴──────────┴──────────┘
(1 row)

Nové funkce

Můžeme používat několik nových funkcí. random_normal je generátor náhodných čísel s normálním (gausovým) rozdělením. Slouží pro simulace metodou Monte Carlo. Předpokládá se, že hlavní použití této funkce bude v úlohách nad geodaty v PostGISu. Další dvě nové funkce, které se mohou použít pro tyto simulace jsou array_sample a array_shuffle. První funkce vrací pole specifikované délky ze zadaného pole. Druhá funkce vrací náhodně seřazené vstupní pole. V obou případech se pracuje s první dimenzí zadaného pole:

(2023-04-14 19:10:59) postgres=# SELECT array_sample(ARRAY[1,2,3,4,5,6,7,8,9], 3);
┌──────────────┐
│ array_sample │
╞══════════════╡
│ {8,1,4}      │
└──────────────┘
(1 row)

(2023-04-14 19:11:04) postgres=# SELECT array_shuffle(ARRAY[1,2,3,4,5,6,7,8,9]);
┌─────────────────────┐
│    array_shuffle    │
╞═════════════════════╡
│ {1,6,3,8,7,5,4,9,2} │
└─────────────────────┘
(1 row)

(2023-04-14 19:12:12) postgres=# SELECT array_sample(ARRAY[[1,2,3],[4,5,6],[7,8,9]], 2);
┌───────────────────┐
│   array_sample    │
╞═══════════════════╡
│ {{1,2,3},{7,8,9}} │
└───────────────────┘
(1 row)

(2023-04-14 19:12:15) postgres=# SELECT array_shuffle(ARRAY[[1,2,3],[4,5,6],[7,8,9]]);
┌───────────────────────────┐
│       array_shuffle       │
╞═══════════════════════════╡
│ {{7,8,9},{4,5,6},{1,2,3}} │
└───────────────────────────┘
(1 row)

V běžné databázové aplikaci si nedovedu dost dobře představit použití. Tyto funkce jsou ale navržené pro PostGIS, který databázi, uložené procedury používá hodně svébytným způsobem, který ovšem funguje (PostGIS je jedna z nejúspěšnějších a nejrozšířenějších aplikací nad Postgresem).

Trochu zvláštní mi přijde ANSI SQL agregační funkce ANY_VALUE, která vrací nějakou ne NULLovou hodnotu z agregované skupiny. Používat by se měla hlavně v analytice při práci s denormalizovanými daty. Náročnost agregace je úměrná počtu řádků, a počtu sloupců uvedených v klauzuli GROUP BY. A zmíněná funkce umožňuje redukovat klauzuli GROUP BY o jeden sloupec.

-- vytvoreni denormalizovanych dat
CREATE TABLE obce_okresy AS
   SELECT ok.id, ok.nazev AS nazev_okresu,
          ob.pocet_zen + ob.pocet_muzu AS pocet_obyvatel,
          ob.nazev AS nazev_obce
     FROM obce ob JOIN okresy ok ON ob.okres_id = ok.id;

-- bez funkce ANY_VALUE
SELECT sum(pocet_obyvatel), id, nazev_okresu
  FROM obce_okresy
 GROUP BY id, nazev_okresu;
 
-- s pouzitim funkce ANY_VALUE - rychlejsi, uspornejsi
SELECT sum(pocet_obyvatel), id, ANY_VALUE(nazev_okresu)
  FROM obce_okresy
 GROUP BY id;

Nové jsou také funkce pro práci s timestampem s časovou zónou: date_add, date_subtract a generate_series. Tyto funkce mají volitelný argument, kterým umožňuje nastavit časovou zónu, vůči které proběhne výpočet. Pokud se tento argument nenastaví, tak se počítá vůči časové zóně určené konfigurační proměnnou timezone.

Tím, že nemusíme nastavovat timezone si můžeme trochu zjednodušit život. Tato konfigurační proměnná totiž ovlivňuje výsledek 2x. Jednak určuje kdy nastane přechod ze zimního na letní čas (a naopak), druhak se timestamp s časovou zónou vždy pro zobrazení převádí do výchozí nastavené časové zóny.

(2023-04-15 12:43:08) postgres=# SET TimeZone to 'UTC' ;                                     
SET
(2023-04-15 12:43:29) postgres=# SELECT date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw');
┌────────────────────────┐
│        date_add        │
╞════════════════════════╡
│ 2021-10-31 23:00:00+00 │
└────────────────────────┘
(1 row)

(2023-04-15 12:43:59) postgres=# SELECT date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval);
┌────────────────────────┐
│        date_add        │
╞════════════════════════╡
│ 2021-10-31 22:00:00+00 │
└────────────────────────┘
(1 row)

Výsledný kód (bez nutnosti měnit konfiguraci Postgresu) bude přehlednější a robustnější. Vždy se doporučuje preferovat kód, který bude funkční bez ohledu na aktuální konfiguraci. Jsem člověk, který téměř nevytáhne paty ze své časové zóny (a změnu času nevnímám), a musím se hodně koncentrovat, abych si uvědomil, že například přechod časů z letního na zimní (a naopak) není konstanta - mění se v historii, mění se napříč časovými zónami (např. zimní čas na letní se letos v USA měnil 12.3, v EU až 26.3)

Extenze fuzzystrmatch obsahuje novou funkci daitch_mokotoff. Jedná se o modernizovanou soundex funkci, která by měla výrazně lépe pracovat s ne anglickými jmény (vyžaduje UTF). Rychlou zkouškou nad databází obcí mi spíš přišlo, že starší funkce soundex se pro češtinu chovala lépe (autor implementace daitch_mokotoff je z Norska a sponzorem je "Finance Norway", tudíž je dost možné, že funkce je odladěná pro transkripci používanou v Norsku).

(2023-04-14 20:24:03) postgres=# SELECT * FROM obce WHERE soundex(nazev) = soundex('Říčany');
┌──────┬──────────┬────────┬────────────┬───────────┬──────────┬─────────┐
│  id  │ okres_id │ nazev  │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞══════╪══════════╪════════╪════════════╪═══════════╪══════════╪═════════╡
│  827 │ CZ0209   │ Říčany │       6603 │      6847 │     37.6 │    40.6 │
│ 4819 │ CZ0643   │ Říčany │        875 │       923 │     39.3 │    42.9 │
└──────┴──────────┴────────┴────────────┴───────────┴──────────┴─────────┘
(2 rows)

(2023-04-14 20:27:15) postgres=# SELECT * FROM obce WHERE daitch_mokotoff(nazev) && daitch_mokotoff('Říčany');
┌──────┬──────────┬─────────┬────────────┬───────────┬──────────┬─────────┐
│  id  │ okres_id │  nazev  │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞══════╪══════════╪═════════╪════════════╪═══════════╪══════════╪═════════╡
│  827 │ CZ0209   │ Říčany  │       6603 │      6847 │     37.6 │    40.6 │
│  889 │ CZ020A   │ Jeneč   │        573 │       628 │     39.0 │    41.3 │
│ 3105 │ CZ0522   │ Jičín   │       8221 │      8425 │     39.4 │    43.0 │
│ 3903 │ CZ0631   │ Jeřišno │        157 │       151 │     40.4 │    43.0 │
│ 4027 │ CZ0632   │ Ježená  │         69 │        59 │     35.7 │    42.8 │
│ 4440 │ CZ0635   │ Jámy    │        290 │       272 │     37.2 │    39.2 │
│ 4819 │ CZ0643   │ Říčany  │        875 │       923 │     39.3 │    42.9 │
└──────┴──────────┴─────────┴────────────┴───────────┴──────────┴─────────┘
(7 rows)

Datové typy

Implementace standardu SQL/JSON se posunula o něco dále. K dispozici jsou standardní konstruktory JSON_ARRAY, JSON_ARRAYAGG, JSON_OBJECT a JSON_OBJECTAGG:

(2023-04-14 06:08:12) postgres=# SELECT JSON_OBJECT('a' VALUE 2 + 3),
                                        JSON_OBJECT('a': 2 + 3);;
┌─────────────┬─────────────┐
│ json_object │ json_object │
╞═════════════╪═════════════╡
│ {"a" : 5}   │ {"a" : 5}   │
└─────────────┴─────────────┘
(1 row)

(2023-04-14 06:08:37) postgres=# SELECT JSON_ARRAY('aaa', 111, true, array[1,2,3], NULL, json '{"a": [1]}', jsonb '["a",3]');
┌─────────────────────────────────────────────────────┐
│                     json_array                      │
╞═════════════════════════════════════════════════════╡
│ ["aaa", 111, true, [1, 2, 3], {"a": [1]}, ["a", 3]] │
└─────────────────────────────────────────────────────┘
(1 row)

(2023-04-14 06:10:05) postgres=# SELECT JSON_ARRAY(NULL, NULL, 'b' ABSENT ON NULL);
┌────────────┐
│ json_array │
╞════════════╡
│ ["b"]      │
└────────────┘
(1 row)

(2023-04-14 06:10:28) postgres=# SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING text));
┌───────────────────────────────┐
│          json_array           │
╞═══════════════════════════════╡
│ ["[\"{ \\\"a\\\" : 123 }\"]"] │
└───────────────────────────────┘
(1 row)

Implementaci, která se drží standardu, komplikuje původní návrh SQL/JSON, který nepočítal se specializovaným datovým typem. Ve standardu z roku 2016 se počítalo s uložením do generických textových nebo binárních typů, a až dodatečně se přidával nový datový typ JSON. Navíc je tato část standard hodně "barokní". Také je toho hodně, co lze dělat s JSONem.

Nové jsou také operátory IS JSON VALUE, IS JSON ARRAY, IS JSON OBJECT a IS JSON SCALAR:

(2023-04-14 06:21:18) postgres=# SELECT 'ahoj' IS JSON SCALAR,
                                        '"ahoj"' IS JSON SCALAR;
┌──────────┬──────────┐
│ ?column? │ ?column? │
╞══════════╪══════════╡
│ f        │ t        │
└──────────┴──────────┘
(1 row)

Bohužel se nestihla dokončit podpora důležité funkce JSON_TABLE (analogie funkce XMLTABLE), která by implementaci standardu ANSI SQL/JSON dala punc úplnosti. Na druhou stranu, vyřešila se zásadní (pro tuto funkci) prerekvizita ("soft errors"), která vlastně blokovala integraci patchů několik let. Myslím si, že je hodně pravděpodobné, že v Postgresu 17 funkce JSON_TABLE bude, a implementace standardu bude kompletní.

Počínaje touto verzí je možné bez extenze formátovat XML dokument. Používá se syntax z ANSI SQL/XML a již hotová funkcionalita knihovny libxml2:

(2023-04-14 16:50:04) postgres=# SELECT xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val></bar></foo>' AS varchar INDENT);
┌─────────────────────────┐
│      xmlserialize       │
╞═════════════════════════╡
│ <foo>                  ↵│
│   <bar>                ↵│
│     <val x="y">42</val>↵│
│   </bar>               ↵│
│ </foo>                 ↵│
│                         │
└─────────────────────────┘
(1 row)

Opět drobnost - pro všechny typy, které podporují hodnotu infinity je možné použít zápis +infinity.

Administrace

Zabezpečení a přístupová práva

V pg_hba.conf lze používat regulární výrazy (zápisem za lomítko):

#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   sameuser        all                                     md5
local    all            /^.*helpdesk$                           md5
local   "/^db\d{2,4}$"  all             localhost               trust
local   all             @admins                                 md5
local   all             +support                                md5

V pg_hba.conf (a pg_ident.conf) můžeme použít tři nová klíčová slova pro vkládání souborů (include, include_if_exists, include_dir).

Nově lze blokovat změnu identity příkazem SET ROLE TO. Jedním z cílů je zabránit vytváření databázových objektů pod "chybnou" identitou:

CREATE ROLE tom LOGIN
CREATE ROLE petr LOGIN;
GRANT tom TO petr;
SET ROLE TO tom;
CREATE TABLE foo(a int);

Při této konfiguraci má uživatel "petr" všechna práva uživatele "tom", a kromě jiného si může explicitně změnit identitu na Toma (a pokud pak vytvoří nějaké objekty, tak jejich vlastníkem bude "tom"):

(2023-04-17 06:28:08) postgres=> SELECT current_user, session_user;
┌──────────────┬──────────────┐
│ current_user │ session_user │
╞══════════════╪══════════════╡
│ petr         │ petr         │
└──────────────┴──────────────┘
(1 row)

(2023-04-17 06:28:10) postgres=> SELECT * FROM foo;
┌───┐
│ a │
╞═══╡
└───┘
(0 rows)

(2023-04-17 06:28:15) postgres=> SET ROLE TO tom;
SET
(2023-04-17 06:28:20) postgres=> SELECT current_user, session_user;
┌──────────────┬──────────────┐
│ current_user │ session_user │
╞══════════════╪══════════════╡
│ tom          │ petr         │
└──────────────┴──────────────┘
(1 row)

Klauzulí WITH SET FALSE

GRANT tom TO petr WITH SET FALSE;

vyblokujeme příkaz SET ROLE:

(2023-04-17 06:34:23) postgres=> SELECT current_user, session_user;
┌──────────────┬──────────────┐
│ current_user │ session_user │
╞══════════════╪══════════════╡
│ petr         │ petr         │
└──────────────┴──────────────┘
(1 row)

(2023-04-17 06:34:28) postgres=> SELECT * FROM foo;
┌───┐
│ a │
╞═══╡
└───┘
(0 rows)

(2023-04-17 06:34:33) postgres=> SET ROLE TO tom;
ERROR:  permission denied to set role "tom"

K dispozici jsou nové systémové role pg_vacuum_all_tables a pg_vacuum_all_tables, které o něco redukují potřebu používat superusera. Ve starších verzích mohl tabulku vakuovat a analyzovat pouze její vlastník (nebo superuser). Nově lze grantovat práva VACUUM a ANALYZE.

V Postgresu lze u databázových účtů nastavit atributy INHERIT a GRANT. Plnou kontrolu nad tímto nastavením v příkazu CREATE ROLE má pouze superuser. Ostatní uživatelé musí použít ještě ALTER ROLE. V nové verzi můžeme nastavit do proměnné createrole_self_grant seznam implicitních atributů (např. 'set, inherit'), který příkaz CREATE ROLE přebírá.

Nová konfigurační proměnná reserved_connections umožňuje rezervovat spojení pro uživatele, účty s rolí pg_use_reserved_connections. U starších verzí byla možnost rezervace spojení pouze pro superusera nastavením superuser_reserved_connections.

Možnost nastavení konfigurace skrze parametry příkazu `initdb`

Asi ještě ne každý používá pro editaci konfigurace nástroje jako je ansible nebo puppet. Potom se docení možnost nechat si vygenerovat "hotovou" konfiguraci příkazem initdb:

[pavel@localhost ~]$ mkdir test
[pavel@localhost ~]$ /usr/local/pgsql/master/bin/initdb -D test -c work_mem="100MB" -c  shared_buffers="2GB"
The files belonging to this database system will be owned by user "pavel".
This user must also own the server process.

Using default ICU locale "cs".
Using language tag "cs" for ICU locale "cs".
The database cluster will be initialized with this locale configuration:
  provider:    icu
  ICU locale:  cs
  ...

a ve vygenerovaném postgresql.conf dostaneme:

# - Memory -
                    
shared_buffers = 2GB            # min 128kB
work_mem = 100MB            # min 64kB

Je to šikovná funkce, kterou používám ve svém vývojovém prostředí.

Možnost nastavit velikost bufferu (alokace v shared_buffers) příkazů `VACUUM` a `ANALYZE`

Přikaz VACUUM podobně jako některé další příkazy používají kruhový buffer s výchozí velikostí 256KB. Použití kruhového bufferu garantuje, že příkaz nevytlačí další zajímavá data ze sdílené paměti. Na druhou stranu 256KB může být dnes zbytečně málo, a s vyšší hodnotou může běžet příkaz rychleji. Je dost těžké odhadnout reálný efekt, protože Postgres ještě využívá file systémovou cache. Dost těžko se to dá testovat, navíc nemám ani k dispozici železo a reálná data, na kterých bych to mohl testovat, takže vůbec nedokáži posoudit přínos tohoto nastavení:

VACUUM (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;
ANALYZE (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;

Monitoring

V pohledech pg_stat_user_tables a pg_stat_user_indexes jsou nové sloupce last_seq_scan a last_idx_scan obsahující čas posledního použití tabulky nebo indexu.

Nový je také pohled pg_stat_io agregující metriky vztahující se k IO podle typu procesu. Můžeme zde zjistit, jak aktivní bylo autovacuum, checkpointer nebo walsender. Dobře jsou zde vidět zápisy do dočasných souborů, využití cache, počty a časy IO operací.

V pohledu pg_stat_user_tables je nový sloupec n_tup_newpage_upd obsahující čítač kolikrát byla nová verze řádku uložena do nové (jiné) datové stránky než předchozí verze.

Optimalizace

Počítání agregačních funkcí string_agg a array_agg může být na větších datech rychlejší díky podpoře paralelizace. Paralelizaci nyní umožní right případně full hash join.

Postupně se rozšiřuje podpora tzv inkrementální řazení (v této verzi o SELECT DISTINCT). Při inkrementálním řazení se typicky načítají seřazená data z indexu podle sloupce a, a nad těmito daty se provede řazení podle sloupců a, b, .... Inkrementální řazení snižuje potřebu vícesloupcových indexů.

Při optimalizaci dotazů optimalizátor čte minimum, maximum sloupce z indexu (rozsah). Zlepšuje si tím odhady (je to další informace k sloupcovým statistikám). Přečtení rozsahu indexu by měla být rychlá operace (pokud degradace indexu není extrémní). V praxi se ale setkáme s bloatingem (naředěním) indexu. Můžeme se setkat i s tím, že odkazy indexu vedou na velké množství mrtvých verzí, což zase zpomaluje načítání dat z indexu (VACUUM tyto mrtvé klíče identifikuje a označí). U běžného dotazu to nemusí být takový problém, u optimalizace dotazu, která by měla být hodně pod 1ms to už problém být může. Zvlášť, když vykonání dotazu by mělo být také pod 1ms. S popisovaným problémem se můžeme setkat u tabulek, které nemusí být velké, ale intenzivně se do nic zapisuje (a posouvá se maximum), a intenzivně se z nich maže (a posouvá se minimum) (typicky implementace fronty). V nové verzi je operace získání rozsahu indexu get_actual_variable_range omezena na přečtení 100 datových stránek (jedná stránka má 8KB, celkem 800KB). Pokud v tomto počtu stránek nedojde k získání rozsahu, tak se funkce ukončí a pracuje se pouze se statistikami.

Z 16 na 8 bajtů se snížila režie alokace paměti. To by mělo pomoct v dotazech, kdy se alokuje velké množství malých bloků. Ve výsledku se do paměti vejde víc dat, a vygeneruje se méně dočasných souborů. U některých dotazů (a specifických dat) to může pomoct výrazně, tam kde doposud nebyly problémy s pamětí si této optimalizace nevšimnete.

Snížila se režie CPU při čištění fronty procesů čekajících na zámek. Tato optimalizace by měla pomoct v situacích, kdy větší počet session čeká na stejný zámek (kratší dobu - např. při zápisu do transakčního logu). Pokud máte peaky s desítkami tisíc write transakcí za sec, tak Vám tato optimalizace pomůže k plynulejšímu zvládnutí peaku.

Optimalizací prošel kód starající se o zvětšení souboru tabulky (nebo indexu). Benefit budou mít opět uživatelé s velkou zátěží (s vyššími desítkami aktivních write spojení), kteří mají k dispozici extrémně výkonné IO. Pro nás ostatní se nic nemění - brzdou bude IO.

VACUUM FREEZE nyní může "zafreezovat" celou datovou stránku. To by mělo redukovat objem zápisu do transakčního logu.

Příkaz ANALYZE nad cizí tabulkou může běžet vzdáleně. U starších verzí běžel vždy lokálně, což způsobovalo zbytečné přenášení dat po síti:

ALTER SERVER loopback OPTIONS (analyze_sampling 'auto');
ANALYZE analyze_table;
ALTER SERVER loopback OPTIONS (SET analyze_sampling 'system');
ANALYZE analyze_table;
ALTER SERVER loopback OPTIONS (SET analyze_sampling 'bernoulli');
ANALYZE analyze_table;
ALTER SERVER loopback OPTIONS (SET analyze_sampling 'random');
ANALYZE analyze_table;
ALTER SERVER loopback OPTIONS (SET analyze_sampling 'off');
ANALYZE analyze_table;

Replikace

Nově můžeme rozjet logickou replikaci vůči serveru v standby režimu. Ve starších verzích Postgresu se změny dat prováděly pod uživatelem s právy superusera, nyní pod uživatelem vlastníka tabulky (a pro subscripci je vyžadováno, aby uživatel měl práva provést SET ROLE TO vlastnik_tabulky).

Ochrana proti zacyklení

Nově je možné u subscripce nastavit atribut origin. Tento atribut je možné nastavit na any nebo none, Origin je jednoznačný identifikátor zdroje změny dat (zapisuje se do transakčního logu). Logická replikace funguje tak, že čte data z transakčního logu a generuje protokol změn. Ten čte protistrana, a na základě něj mění obsah databáze. Pokud záznam v transakčním logu vznikl lokálně (nikoliv skrz replikaci), tak atribut není origin nastavený. Jako ochrana proti zacyklení to ale může fungovat pouze v případě, že není použitá kaskádová replikace (nebo mi ještě něco uniká).

Možnost počáteční synchronizace v binárním formátu

Počáteční synchronizace zatím probíhala vždy skrze textový protokol (binární hodnoty se musí serializovat do textu). Nově pokud je u subscripce nastavený binární protokol, tak i počáteční synchronizace bude používat binární protokol.

Ostatní

Tato kapitola bude rozsahem větší. Drobných vylepšení (stojících za zmínění) je letos opravdu hodně.

Podpora soft errors

Zde začnu trochu zeširoka. Ačkoliv PostgreSQL je napsán v letitém Cčku C99, je psán relativně moderně (včetně objektových přístupů jako zapouzdření, použití metod, dědičnosti nebo řešení obsluhy chyb pomocí výjimek). V Postgresu se velká část chyb řeší skrze výjimky (které si Postgres implementuje sám (nad longjump API)). Ve starších verzích každá chyba (v něčem, co by se dalo označit jako user space) skončila výjimkou, a každá výjimka musí (v Postgresu) skončit rollbackem (úplným nebo k definovanému safe pointu). Toto chování je jedním z pilířů stability Postgresu. Je to jednoduchý a jasně uchopitelný koncept. Tento koncept je také ale hodně striktní. Například nelze jednoduše ignorovat chyby na vstupu (lze je zachytávat, ale za relativně vysokou cenu - režii spojenou s vytvářením a rušením safe pointů).

Z teoretického hlediska nikdy nechceme ignorovat chyby na vstupu (vstup má být vždy korektní a úplný). Z praktického hlediska je chceme ignorovat dost často. Datoví analytici mi dají jistě za pravdu, že musí pracovat, s tím co dostanou od svých zákazníků, a jsou ještě rádi, že mají data. V komerční sféře si navíc nebudete odhánět zákazníky tím, že byste je chtěli vychovávat, a chtěli aby vám dodávali 100% korektní data.

Počínaje verzí 16 mají vývojáři (Postgresu) možnost ukončit funkci s takzvanou měkkou chybou. Měkká chyba může být ošetřena bez nutnosti rollbacku. Nevalidní hodnota může být nahrazena hodnotou NULL, kontrolní funkce může vrátit false, vstupní řádek může být ignorován. Zatím se v Postgresu soft errors téměr nevyužívají, a zatím vždy končí výjimkou, a zmiňovaným rollbackem. V následující verzi Postgresu by se mohl objevit tolerantní příkaz COPY (nevalidní řádky ignoruje, nevalidní hodnoty nahrazuje NULL), a funkce JSON_TABLE, které je možné parametrizací určit, jestli odkazy na chybějící klíče skončí chybou nebo budou nahrazeny jinou specifikovanou hodnotou nebo hodnotou NULL (vloni se kvůli chybícím soft errors na poslední chvíli revertovala podpora SQL/JSON).

Zatím lze se setkat s podporou soft errors pouze ve funkcích kontroly formátu:

(2023-04-14 07:17:01) postgres=# SELECT pg_input_is_valid('34.5', 'float4');
┌───────────────────┐
│ pg_input_is_valid │
╞═══════════════════╡
│ t                 │
└───────────────────┘
(1 row)

(2023-04-14 07:17:10) postgres=# SELECT pg_input_is_valid('1e400', 'float4');
┌───────────────────┐
│ pg_input_is_valid │
╞═══════════════════╡
│ f                 │
└───────────────────┘
(1 row)

(2023-04-14 12:51:27) postgres=# SELECT * FROM  pg_input_error_info('1e400', 'float4');
┌───────────────────────────────────────┬────────┬──────┬────────────────┐
│                message                │ detail │ hint │ sql_error_code │
╞═══════════════════════════════════════╪════════╪══════╪════════════════╡
│ "1e400" is out of range for type real │ ∅      │ ∅    │ 22003          │
└───────────────────────────────────────┴────────┴──────┴────────────────┘
(1 row)

Zobrazení generického prováděcího plánu

Nově můžeme v příkazu EXPLAIN použít přepínač GENERIC_PLAN, který způsobí zobrazení tzv generického plánu. V Postgresu se pracuje s dvěma kategoriemi prováděcích plánů, s generickými a zákaznickými plány. Zákaznický (custom) prováděcí plán je jednorázový prováděcí plán optimalizovaný se znalostí parametrů dotazu. Naopak generický prováděcí plán je opakovaně použitelný prováděcí plán optimalizovaný bez znalosti parametrů dotazu. Jelikož neznáme parametry, tak se při odhadu vychází z analýzy kardinalit. Generické prováděcí plány používají před připravené dotazy (na straně serveru) nebo vložené SQL příkazy v PL/pgSQL. Cílem generických plánů je redukce režie planneru. Docela to funguje, ale jsou situace, kdy je generický plán patologicky špatný, a pak potřebujeme jej zobrazit, abychom mohli zjistit, proč je dotaz pomalý. Ve starších verzích existovaly workaroundy jak si generický plán prohlédnout. Od 16tky je získání generického prováděcího plánu jednoduché:

(2023-04-14 15:27:53) postgres=# EXPLAIN (GENERIC_PLAN) SELECT * FROM obce WHERE okres_id = $1;
┌────────────────────────────────────────────────────────────────────────────────┐
│                                   QUERY PLAN                                   │
╞════════════════════════════════════════════════════════════════════════════════╡
│ Index Scan using obce_okres_id_idx on obce  (cost=0.28..9.79 rows=81 width=41) │
│   Index Cond: ((okres_id)::text = $1)                                          │
└────────────────────────────────────────────────────────────────────────────────┘
(2 rows)

Příkaz `GET DIAGNOSTICS o = PG_ROUTINE_OID`

Příkaz GET DIAGNOSTICS programovacího procedurálního jazyka PL/pgSQL byl rozšířen o metriku PG_ROUTINE_OID. Ta vrací oid (unikátní číselný identifikátor) aktuálně běžící funkce. Tento identifikátor lze použít pro generování chybových nebo ladících hlášení. Dosud se tento identifikátor musel "pracně" separovat ze call stacku:

(2023-04-14 15:45:41) postgres=# \sf test
CREATE OR REPLACE FUNCTION public.test()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE o oid;
BEGIN
  GET DIAGNOSTICS o = PG_ROUTINE_OID;
  RAISE NOTICE 'Jsem uvnitr funkce %', o::regprocedure;
END;
$function$

(2023-04-14 15:45:45) postgres=# SELECT test();
NOTICE:  Jsem uvnitr funkce test()
┌──────┐
│ test │
╞══════╡
│      │
└──────┘
(1 row)

`pg_dump`

K dříve podporované komprimaci metodou gzip je nově podpora metod lz4 a zstd. Lze nastavit i úroveň komprimace. Výhodou metody lz4 by měla být rychlost. Naopak u zstd dobrý kompresní poměr a dobrá rychlost (efektivita komprimace je o něco málo horší než gzip rychlostně je ovšem výrazně lepší).

pg_dump má nové tři přepínače, které umožní snazší export (dump) partišnovaných tabulek: --table-and-children, --exclude-table-and-children a --exclude-table-data-and-children. Názvy přepínačů vycházejí z modelu staršího partitioningu založeného na dědičnosti. Mělo by to ale fungovat i s novým deklarativním partitioningem.

`psql`

Oproti předchozím verzím příkaz \df+ nezobrazí zdrojový kód funkce, ale jen tzv interní název. Je to mnohem praktičtější. Plusková varianta se používá pro zobrazení vlastníka, přístupových práv, a zobrazení třeba i dost dlouhého zdrojového kódu rušilo. Pro zobrazení zdrojového kódu je už roky k dispozici příkaz \sf.

V psql je letitý příkaz \!, který umožňuje spustit příkaz shellu. Nyní se zjednoduší získání návratového kódu. K dispozici jsou proměnné (psql proměnné) SHELL_ERROR a SHELL_EXIT_CODE. První obsahuje true nebo false, a je připravená pro použití v příkazu \if. Druhá obsahuje klasický celočíselný exit status (0 je typicky ok).

V příkazu \watch lze použít druhý parametr (první určuje časový interval), kterým nastavíme počet iterací.

Definice extenze

Schéma, ve kterém se budou vytvářet objekty extenze, může být dynamicky definované uživatelem. Pomocí nového tagu @extschema:name@ se můžeme na toto schéma odkazovat. Tag se uplatní pouze při vytvoření (registraci) extenze příkazem CREATE EXTENSION:

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit

-- This formulation can handle relocation of the required extension.
CREATE FUNCTION dep_req2() RETURNS text
BEGIN ATOMIC
  SELECT @extschema:test_ext_req_schema1@.dep_req1() || ' req2';
END;

Pokud by dodatečně došlo k přesunu extenze do jiného schématu příkazem ALTER EXTENSION, tak reference vytvořená tagem bude neplatná. Aby k tomu nemohlo dojít, lze volbou no_relocate blokovat přesun odkazovaných extenzí (v řídícím souboru extenze):

comment = 'Test schema referencing of 2 required extensions'
default_version = '1.0'
relocatable = true
requires = 'test_ext_req_schema1, test_ext_req_schema2'
no_relocate = 'test_ext_req_schema1'

Možnost vložit defaultní hodnotu příkazem `COPY`

Nově lze u příkazu COPY definovat symbol pro defaultní hodnotu:

(2023-04-17 07:15:27) postgres=# CREATE TABLE boo(id serial, v int);
CREATE TABLE
(2023-04-17 07:18:34) postgres=# COPY boo FROM STDIN (DEFAULT '\D');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> \D   10
>> \D   20
>> \.
COPY 2
(2023-04-17 07:19:10) postgres=# SELECT * FROM boo;
┌────┬────┐
│ id │ v  │
╞════╪════╡
│  3 │ 10 │
│  4 │ 20 │
└────┴────┘
(2 rows)

Téhož se dalo dříve dosáhnout výčtem vkládaných sloupců:

(2023-04-17 07:19:17) postgres=# TRUNCATE boo;
TRUNCATE TABLE
(2023-04-17 07:20:42) postgres=# COPY boo(v) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 10
>> 20
>> \.
COPY 2
(2023-04-17 07:21:03) postgres=# SELECT * FROM boo;
┌────┬────┐
│ id │ v  │
╞════╪════╡
│  5 │ 10 │
│  6 │ 20 │
└────┴────┘
(2 rows)

Zobrazení postupu operace

Novým přepínačem -P příkazu pg_verifybackup si vynutíme zobrazení stavu kontroly co jednu sekundu.

V případě příkazů CREATE INDEX nebo REINDEX se nově v tabulce pg_stat_progres_create_index zobrazují sloupce partitions_total a partitions_done.

Podpora load balancingu v libpq

Knihovna libpq poskytuje API pro komunikaci s Postgresem, a je výkonnou částí většiny driverů pro Postgres (pro růné programovací jazyky). Počínaje PostgreSQL 10 je možné v connection stringu uvést přihlašovací údaje pro více serverů. Tato možnost se používala pro zajištění vyšší dostupnosti bez nutnosti používat proxy. Nově můžeme použít parametr load_balance_hosts s hodnotou random. S tímto nastavením se připojení provede náhodně na jeden ze serverů uvedených v connection stringu.

Závěr

Ve frontě je aktuálně 209 patchů v různém stupni rozpracovanosti (práce minimálně na rok, kdyby nic nového nevznikalo). Někdy se obtížně hledá kompromis ohledně návrhu (např integrace šifrování nebo obsluha signálů), jindy je implementace natolik komplikovaná, že naprogramovat a integrovat kód je časově i lidsky náročné (JSON_TABLE nebo pokročilé vlastnosti replikace).

U velkých patchů je náročná integrace. Rok představuje relativně krátké časové okno, a každý rok musí vyjít verze, která je funkční a neobsahuje nedodělky, a neobsahuje kód, který by v budoucnu představoval problém se zpětnou kompatibilitou. Stále se ale daří udržovat vývoj v docela vysokém tempu, a hlavně zdravou atmosféru jak mezi vývojáři, tak i v uživatelské komunitě.

PostgreSQL 16 (2023)

Obsah

PostgreSQL 16 (2023)

SQL

Reprezentace čísel

Nové funkce

Datové typy

Administrace

Zabezpečení a přístupová práva

Možnost nastavení konfigurace skrze parametry příkazu `initdb`

Možnost nastavit velikost bufferu (alokace v shared_buffers) příkazů `VACUUM` a `ANALYZE`

Monitoring

Optimalizace

Replikace

Ochrana proti zacyklení

Možnost počáteční synchronizace v binárním formátu

Ostatní

Podpora soft errors

Zobrazení generického prováděcího plánu

Příkaz `GET DIAGNOSTICS o = PG_ROUTINE_OID`

`pg_dump`

`psql`

Definice extenze

Možnost vložit defaultní hodnotu příkazem `COPY`

Zobrazení postupu operace

Podpora load balancingu v libpq

Závěr

Navigační menu

PostgreSQL 16 (2023)

PostgreSQL 16 (2023)

SQL

Reprezentace čísel

Nové funkce

Datové typy

Administrace

Zabezpečení a přístupová práva

Možnost nastavení konfigurace skrze parametry příkazu initdb

Možnost nastavit velikost bufferu (alokace v shared_buffers) příkazů VACUUM a ANALYZE

Monitoring

Optimalizace

Replikace

Ochrana proti zacyklení

Možnost počáteční synchronizace v binárním formátu

Ostatní

Podpora soft errors

Zobrazení generického prováděcího plánu

Příkaz GET DIAGNOSTICS o = PG_ROUTINE_OID

pg_dump

psql

Definice extenze

Možnost vložit defaultní hodnotu příkazem COPY

Zobrazení postupu operace

Podpora load balancingu v libpq

Závěr

Navigační menu

Hledat

Možnost nastavení konfigurace skrze parametry příkazu `initdb`

Možnost nastavit velikost bufferu (alokace v shared_buffers) příkazů `VACUUM` a `ANALYZE`

Příkaz `GET DIAGNOSTICS o = PG_ROUTINE_OID`

`pg_dump`

`psql`

Možnost vložit defaultní hodnotu příkazem `COPY`