PostgreSQL 17 (2024)

Autor: Pavel Stěhule, 2024

PostgreSQL 17 (2024)

Už jsem to psal posledně, tím jak na Postgresu dělá víc a víc vývojářů z různých společností a zemí (firem), tak je vývoj Postgresu méně a méně čitelný a prakticky se nedá najít viditelnější trend. Velkou výhodou projektu jsou jasně nastavená, fungující a respektovaná pravidla, plus stabilní komunita vývojářů. Díky tomu se během roku udělá hromada práce, která je vidět, a kterou ocení (oceňují) i uživatelé.

Bohužel letos komunita o jednoho dlouholetého člena přišla. 26. 3. tragicky zahynul Simon Riggs (při pilotáži malého letadla). Simona jsme mohli několikrát potkat i v Praze na konferencích P2D2, PGConf.Eu a i jindy. Firma, kterou v roce 2001 založil - 2ndQuadrant, měla v Praze pobočku. Simon byl fajn člověk, výborný řečník, ale i programátor. Hodně posunul Postgres směrem k nasazení ve firemních kritických aplikacích - napsal podporu pro Point In Time Recovery, Hot Standby, synchronní replikaci (vývojáři ze 2ndQuadrantu poté napsali podporu logické replikace do Postgresu).

Tento vývojový cyklus byl bez jakýchkoliv kontroverzí. Je vidět snaha o dotáhnutí velkých patchů (jako např. SQL/JSON), a zároveň si nepodrazit zbytečně nohy rychlou integrací nového kódu. Neotevírají se nová velké témata (v rámci komunitního Postgresu). Výjimkou byl návrh Heikkiho Linnakangase na přechod z architektury postavené na procesech na architekturu používající vlákna. To je dost kontroverzní téma, které mělo přijít na přetřes před 10 roky. Architekturu Postgres přebírá ještě z databáze INGRES, což znamená, že je pomalu 50 let stará. V Postgresu je proces spojen s konkrétní databází a s konkrétním přihlášeným uživatelem. Bez ukončení procesu (a vytvoření nového) nejde změnit uživatele, nejde změnit databázi. Když máte intenzivně využívané aplikace se vyššími stovkami uživatelů, připojujících se do vyšších stovek databází, tak s Postgresem můžete mít problém. Řešení by mohla přinést změna architektury (využívání vláken). Před implementací podpory využití více CPU pro jeden dotaz by ale tento přechod byl o dost jednodušší, a také by se asi dost ušetřilo práce při implementaci komunikace mezi procesy workerů dotazu. Myslím si, že Postgres na vlákna přejde, ale bude to trvat roky. Problém není vlastní implementace (existují dva prototypy). Problém je, jak tuto velkou změnu rozložit v čase, a zároveň každý rok vydat verzi v očekávané kvalitě (a zároveň aby byl Postgres funkční a stabilní na všech systémech, které jsou podporovány).

Heikkiho iniciativa pravděpodobně souvisí s projektem Neon, v jehož týmu pracuje, a který spolu založil. Jde o open source velmi zajímavou alternativu Amazoního Aurora Postgresu. Jedná se o fork PostgreSQL upravený pro běh v cloudu. Oproti běžnému Postgresu, kde server řeší jak manipulaci s daty a správu cache (storage), tak vykonávaní dotazů (compute), jsou v Neonu storage a compute striktně odděleny, a běží v nezávislých VM. Management compute nody pak může být výrazně dynamičtější než s klasickým Postgresem (protože tam není ta část, která se stará o storage). Pro storage se používá speciální cloud multi-tenant storage systém, který podporuje i zálohování a archivaci.

Datové typy

Konstantu infinity lze nově použít pro datový typ interval. To se hodí pro implementaci různých rozsahů, kde infinity je praktičtější než NULL (není nutné v podmínkách používat COALESCE), a čitelnější než je speciální konstanty:

(2024-03-25 06:23:17) postgres=# SELECT timestamp 'infinity' - timestamp '1995-08-06 12:12:12';
┌──────────┐
│ ?column? │
╞══════════╡
│ infinity │
└──────────┘
(1 row)

(2024-03-25 06:23:24) postgres=# SELECT timestamp '-infinity' - timestamp '1995-08-06 12:12:12';
┌───────────┐
│ ?column?  │
╞═══════════╡
│ -infinity │
└───────────┘
(1 row)

Jednou z posledních chybějících funkcí standardu SQL/XML byla funkce xmltext. Tato funkce řeší escapeování textů. Tím je podpora SQL/XML v Postgresu rámcově dokončena. Bohužel vývoj knihovny libxml2 je zmrazen (nad touto knihovnou je podpora SQL/XML v Postgresu postavena), a tudíž se do této knihovny (stejně tak do Postgresu) nedostane podpora XPath vyšších verzí než 1.0. Což je limit podpory SQL/XML v pg. Plná podpora by byla samozřejmě lepší, na druhou stranu pro práci s XML v databázi je XPath 1.0 víc než dostačující, a PostgreSQL rozhodně nemá ambice konkurovat tzv XML databázím:

postgres=# SELECT xmltext('foo & <"bar">');
┌───────────────────────────────────┐
│              xmltext              │
╞═══════════════════════════════════╡
│ foo &amp; &lt;&quot;bar&quot;&gt; │
└───────────────────────────────────┘
(1 row)

postgres=# SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
┌─────────────────────────────────┐
│             xmltext             │
╞═════════════════════════════════╡
│ x&lt;P&gt;73&lt;/P&gt;0.42truej │
└─────────────────────────────────┘
(1 row)

Podpora SQL/JSON

Ve výrazech v JSONPath lze nyní používat konverzní metody: .bigint(), .boolean(), .date(), .decimal([precision [, scale]]), .integer(), .number(), .string(), .time(), .time_tz(), .timestamp(), a .timestamp_tz():

postgres=# SELECT jsonb_path_query('"2023-08-15"', '$.time_tz()');
ERROR:  time_tz format is not recognized: "2023-08-15"
postgres=# SELECT jsonb_path_query('"12:34:56 +05:30"', '$.time_tz()');
┌──────────────────┐
│ jsonb_path_query │
╞══════════════════╡
│ "12:34:56+05:30" │
└──────────────────┘
(1 row)

Postupně se dokončuje podpora standardu SQL/JSON. K dispozici jsou tři nové funkce JSON, JSON_SCALAR a JSON_SERIALIZE. Praktický přínos těchto funkcí mi možná uniká (jedná se o jiný zápis přetypování), nicméně z důvodu úplnosti implementace standardu jsou k dispozici. V některých prostředích může být zajímavé protlacit JSON jako bytea z důvodu eliminace escapeování.

postgres=# SELECT JSON_SERIALIZE('{"a" : 10}' RETURNING bytea);
┌────────────────────────┐
│     json_serialize     │
╞════════════════════════╡
│ \x7b226122203a2031307d │
└────────────────────────┘
(1 row)

postgres=# SELECT JSON(JSON_SERIALIZE('{"a" : 10}' RETURNING bytea));
┌────────────┐
│    json    │
╞════════════╡
│ {"a" : 10} │
└────────────┘
(1 row)

Jako důležitější mi přijdou funkce JSON_EXISTS, JSON_QUERY a JSON_VALUE. Tyto funkce jsou standardním API pro práci s JSONPath výrazy.

Parametrem dotazovacích funkcí je JSON hodnota a JSONPath výraz. JSON_EXISTS vrací true, pokud výraz vrací alespoň jednu hodnotu. json_value vrací přesně jednu hodnotu, JSON_QUERY vrací objekt nebo pole. Dalšími klauzulemi lze reagovat na prázdný výsledek nebo chybu:

postgres=# SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y);
┌─────────────┐
│ json_exists │
╞═════════════╡
│ t           │
└─────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '"ahoj"', '$' RETURNING text);
┌────────────┐
│ json_value │
╞════════════╡
│ ahoj       │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
┌────────────┐
│  ?column?  │
╞════════════╡
│ 2017-03-01 │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '{"a": 1}', '$.a' RETURNING int);
┌────────────┐
│ json_value │
╞════════════╡
│          1 │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING int);
┌────────────┐
│ json_value │
╞════════════╡
│            │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING int DEFAULT 10 ON EMPTY);
┌────────────┐
│ json_value │
╞════════════╡
│         10 │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING int ERROR ON EMPTY ERROR ON ERROR);
ERROR:  no SQL/JSON item

Syntaxe těchto funkcí je dost košatá. Je to vidět u funkce JSON_QUERY a tuplem pak u JSON_TABLE:

postgres=# SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text);
┌────────────┐
│ json_query │
╞════════════╡
│ "aaa"      │
└────────────┘
(1 row)

postgres=# SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES);
┌────────────┐
│ json_query │
╞════════════╡
│ aaa        │
└────────────┘
(1 row)

postgres=# SELECT JSON_QUERY(jsonb '[{"a":10, "b": 20}, {"a": 30, "b":100}]', '$[*].a' );
┌────────────┐
│ json_query │
╞════════════╡
│            │
└────────────┘
(1 row)

postgres=# SELECT JSON_QUERY(jsonb '[{"a":10, "b": 20}, {"a": 30, "b":100}]', '$[*].a' ERROR ON ERROR );
ERROR:  JSON path expression in JSON_QUERY should return singleton item without wrapper
HINT:  use WITH WRAPPER clause to wrap SQL/JSON item sequence into array

postgres=# SELECT JSON_QUERY(jsonb '[{"a":10, "b": 20}, {"a": 30, "b":100}]', '$[*].a' WITH WRAPPER );
┌────────────┐
│ json_query │
╞════════════╡
│ [10, 30]   │
└────────────┘
(1 row)

Pokud JSONPath výraz vrací více než jednu hodnotu, tak výsledkem je chyba, která je ale by default ignorována (v laxním režimu). Pro vytvoření pole z vrácených hodnot se musí použít klauzule WITH WRAPPER (zkrácená forma pro WITH ARRAY WRAPPER). V tuto chvíli návratovou hodnotou může být pouze JSON pole, nikoliv pole v PostgreSQL.

Konečně se do upstreamu dostává i funkce JSON_TABLE:

CREATE TABLE my_films ( js jsonb );
INSERT INTO my_films VALUES (
'{ "favorites" : [
   { "kind" : "comedy", "films" : [
     { "title" : "Bananas",
       "director" : "Woody Allen"},
     { "title" : "The Dinner Game",
       "director" : "Francis Veber" } ] },
   { "kind" : "horror", "films" : [
     { "title" : "Psycho",
       "director" : "Alfred Hitchcock" } ] },
   { "kind" : "thriller", "films" : [
     { "title" : "Vertigo",
       "director" : "Alfred Hitchcock" } ] },
   { "kind" : "drama", "films" : [
     { "title" : "Yojimbo",
       "director" : "Akira Kurosawa" } ] }
  ] }');

SELECT jt.* FROM my_films,
                 JSON_TABLE (js, '$.favorites[*]'
                            COLUMNS (id FOR ORDINALITY,
                                     kind text PATH '$.kind',
                                     title text PATH '$.films[*].title' WITH WRAPPER,
                                     director text PATH '$.films[*].director' WITH WRAPPER)) AS jt;
┌────┬──────────┬────────────────────────────────┬──────────────────────────────────┐
│ id │   kind   │             title              │             director             │
╞════╪══════════╪════════════════════════════════╪══════════════════════════════════╡
│  1 │ comedy   │ ["Bananas", "The Dinner Game"] │ ["Woody Allen", "Francis Veber"] │
│  2 │ horror   │ ["Psycho"]                     │ ["Alfred Hitchcock"]             │
│  3 │ thriller │ ["Vertigo"]                    │ ["Alfred Hitchcock"]             │
│  4 │ drama    │ ["Yojimbo"]                    │ ["Akira Kurosawa"]               │
└────┴──────────┴────────────────────────────────┴──────────────────────────────────┘
(4 rows)

Atributy zanořených objektů lze získat použitím klauzule NESTED PATH:

SELECT jt.* FROM my_films,
                 JSON_TABLE ( js,
                             '$.favorites[*] ? (@.films[*].director == $filter)'
                               PASSING 'Alfred Hitchcock' AS filter
                               COLUMNS (id FOR ORDINALITY,
                                        kind text PATH '$.kind',
                                        NESTED PATH '$.films[*]'
                                          COLUMNS (title text FORMAT JSON PATH '$.title' OMIT QUOTES,
                                                   director text PATH '$.director' KEEP QUOTES))) AS jt;
                                                   
┌────┬──────────┬─────────┬────────────────────┐
│ id │   kind   │  title  │      director      │
╞════╪══════════╪═════════╪════════════════════╡
│  1 │ horror   │ Psycho  │ "Alfred Hitchcock" │
│  2 │ thriller │ Vertigo │ "Alfred Hitchcock" │
└────┴──────────┴─────────┴────────────────────┘
(2 rows)

JSON_TABLE je analogická funkci XMLTABLE. Bohužel, syntax i chování se někdy málo, někdy docela dost liší. Implementace SQL/JSON ještě není úplná - chybí podpora tzv. plánů, ale ta se téměř 100% dostane do PostgreSQL 18. Už nyní je PostgreSQL databáze s velice dobrou podporou standardu. Po dokončení implementace JSON_TABLE by implementace standardu SQL/JSON v PostgreSQL měla být nejúplnější ze všech známých SQL databází.

Pro skalární datové typy stávající implementace funguje perfektně. Co není dořešeno, a co nefunguje úplně dobře, je například konverze z JSON pole do PostgreSQL pole. Mám pocit, že to není ani pokryté standardem, ale určitě to bude potřeba v budoucnu řešit. Drobnou vadou na kráse (standardu ANSI/SQL) je skutečnost, že původně JSON nebyl navržen jako samostatný datový typ. Byl to jen řetězec (uložený například ve varcharu). Díky tomu implicitní konverze v SQL/JSON funkcích postrádají určitou logiku, a například plnou konverzi do "normálního" řetězce si musíme vynutit speciální klauzulí OMIT QUOTES. PostgreSQL má datový typ pro JSON, nejnovější aktualizace ANSI/SQL také. Nicméně z důvodu zachování zpětné kompatibility originální koncept návrhu SQL/JSON funkcí zůstává (a z mého pohledu zbytečně komplikuje (v detailech) práci).

SQL

DDL

Nově lze upravit výraz generovaného sloupce (přidáno v Postgresu 12) příkazem ALTER TABLE ALTER COLUMN SET EXPRESSION. Pozor, příkaz způsobí přepis tabulky (na velkých tabulkách bude pomalý - bohužel Postgres nepodporuje virtuální (nematerializované) generované (počítané) sloupce).

DML

Příkazem MERGE lze měnit i modifikovatelné (updatable) pohledy. Nově také lze v příkazu MERGE použít klauzuli RETURNING. V ní lze použít funkci merge_action(), která vrací řetězec INSERT, UPDATE, nebo DELETE v závislosti jakým způsobem byl řádek upraven:

CREATE TABLE target(a int);
INSERT INTO target VALUES(10);

postgres=# MERGE INTO target
             USING (VALUES(10),(20)) s(b)
             ON a = b 
             WHEN MATCHED THEN
                  UPDATE SET a = b
             WHEN NOT MATCHED THEN
                  INSERT VALUES(b)
             RETURNING merge_action(), target.*;
┌──────────────┬────┐
│ merge_action │ a  │
╞══════════════╪════╡
│ UPDATE       │ 10 │
│ INSERT       │ 20 │
└──────────────┴────┘
(2 rows)

Počínaje verzí 17 můžeme použít podmínku WHEN NOT MATCHED BY SOURCE THEN a jako reakci použít DELETE nebo DO NOTHING:

TRUNCATE target;
INSERT INTO target VALUES(30);
INSERT INTO target VALUES(10);
postgres=# MERGE INTO target
             USING (VALUES(10),(20)) s(b)
             ON a = b
             WHEN MATCHED THEN
                  UPDATE SET a = b
             WHEN NOT MATCHED THEN
                  INSERT VALUES(b)
             WHEN NOT MATCHED BY SOURCE THEN
                  DELETE;
MERGE 3
postgres=# SELECT * FROM target;
┌────┐
│ a  │
╞════╡
│ 10 │
│ 20 │
└────┘
(2 rows)

V parametrech FORCE_NULL a FORCE_NOT_NULL příkazu COPY můžeme použít * ve smyslu použití na všechny sloupce. Nový parametr příkazu COPY ON_ERROR s volbou ignore umožňuje ignorovat chyby formátu při importu (ignoruje se celý řádek s nevalidní hodnotou):

CREATE TABLE check_ign_err (n int, m int[], k int);

postgres=# COPY check_ign_err FROM STDIN WITH (ON_ERROR ignore);
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.
>> 1, {10}, 10
>> 2, bbbb, uuu
>> \.
NOTICE:  2 rows were skipped due to data type incompatibility
COPY 0

S parametrem LOG_VERBOSITY verbose zobrazí podrobnější gnorovaný řádek:

(2024-04-03 14:33:01) postgres=# COPY check_ign_err FROM STDIN WITH (ON_ERROR ignore, LOG_VERBOSITY verbose);
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.
>> 1, {10}, 20
>> 1, {10}, zzz
>> sjsj,sksk, ksksk
>> \.
NOTICE:  skipping row due to data type incompatibility at line 1 for column n: "1, {10}, 20"
NOTICE:  skipping row due to data type incompatibility at line 2 for column n: "1, {10}, zzz"
NOTICE:  skipping row due to data type incompatibility at line 3 for column n: "sjsj,sksk, ksksk"
NOTICE:  3 rows were skipped due to data type incompatibility
COPY 0

Stávající implementace je jen vykopnutí tolerantního importu, který je často k dispozici v OLAP databázích.

SELECT

Klauzule AT LOCAL je zkratka AT TIME ZONE aktuální časová zóna session

postgres=# SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP AT LOCAL;
┌───────────────────────────────┬────────────────────────────┐
│       current_timestamp       │          timezone          │
╞═══════════════════════════════╪════════════════════════════╡
│ 2024-04-01 10:00:36.969875+02 │ 2024-04-01 10:00:36.969875 │
└───────────────────────────────┴────────────────────────────┘
(1 row)

postgres=# SELECT timestamp '2001-02-16 20:38:40' AT LOCAL, timestamp '2001-02-16 20:38:40';
┌────────────────────────┬─────────────────────┐
│        timezone        │      timestamp      │
╞════════════════════════╪═════════════════════╡
│ 2001-02-16 20:38:40+01 │ 2001-02-16 20:38:40 │
└────────────────────────┴─────────────────────┘
(1 row)

Optimalizace

Optimalizace dotazů

Implementace UNIONu bude o něco chytřejší - kromě operace append bude možné použít i operaci merge append (pokud jsou vstupní data již seřazená, tak není nutný další sort a unique). Planner nyní vidí statistiky zanořené CTE. Inkrementální řazení (implementováno v PostgreSQL 13) je možné použít i vůči GiST a SP-GiST indexům. To může být užitečné pro KNN dotazy, kde se řadí i podle neoindexovaného sloupce. V případě více sloupcové klauzule GROUP BY optimalizátor vyzkouší více kombinací řazení, tak aby minimalizoval jeho náročnost (aby bylo možné použít inkrementální řazení).

Postgres nyní podporuje tzv index skip scan. To znamená, že pokud v indexu vyhledáváte seznam hodnot, tak oproti dřívější implementaci, která n krát vyhledávala konkrétní hodnotu, a n krát opakovala traverzování napříč indexem. Nově se dohledaných listech (koncové uzly indexu) zkouší dohledat další hodnoty ze seznamu.

V nové verzi je vylepšena podpora operace hledání položky v poli indexem. V předchozích verzích nebylo možné tuto operaci kombinovat s jinými operacemi (při hledávání v indexu), a a tak se přesouvala do filtru.

-- PostgreSQL 16
(2024-04-07 07:35:40) postgres=# explain select * from foo3 where b = any(array[10,20,30]) and a < 100;
┌───────────────────────────────────────────────────────────────────────────────┐
│                                  QUERY PLAN                                   │
╞═══════════════════════════════════════════════════════════════════════════════╡
│ Index Only Scan using foo3_a_b_idx on foo3  (cost=0.42..33.44 rows=1 width=8) │
│   Index Cond: (a < 100)                                                       │
│   Filter: (b = ANY ('{10,20,30}'::integer[]))                                 │
└───────────────────────────────────────────────────────────────────────────────┘
(3 rows)

-- PostgreSQL 17
postgres=# explain select * from foo3 where b = any(array[10,20,30]) and a < 100;
┌───────────────────────────────────────────────────────────────────────────────┐
│                                  QUERY PLAN                                   │
╞═══════════════════════════════════════════════════════════════════════════════╡
│ Index Only Scan using foo3_a_b_idx on foo3  (cost=0.42..21.99 rows=1 width=8) │
│   Index Cond: ((a < 100) AND (b = ANY ('{10,20,30}'::integer[])))             │
└───────────────────────────────────────────────────────────────────────────────┘
(2 rows)

Operátory <@ a @> pro konstantní range jsou transformovány na operace porovnání:

postgres=# EXPLAIN (VERBOSE, COSTS OFF)
SELECT daterange('2002-09-25'::date, 'Infinity', '[]') @> current_date
postgres-# ;
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                       QUERY PLAN                                        │
╞═════════════════════════════════════════════════════════════════════════════════════════╡
│ Result                                                                                  │
│   Output: ((CURRENT_DATE >= '2002-09-25'::date) AND (CURRENT_DATE <= 'infinity'::date)) │
└─────────────────────────────────────────────────────────────────────────────────────────┘
(2 rows)

Optimalizace práce s pamětí

Jedna z důležitých interních struktur je tzv ResourceOwner. Pomocí sad proměnných tohoto typu se sleduje použití a zejména uvolnění paměti, zámků v Postgresu. Pokud je resource owner ukončen, a nějaký zdroj v jeho evidenci není uvolněn, tak se vypíše varovaní. Ve verzi 17 byla práce s resource ownery refaktorována, tak aby bylo možné snáze rozšiřovat evidované zdroje a aby tuto strukturu mohly používat extenze i pro vlastní zdroje.

Funkce BumpContextCreate() vytvoří nový paměťový kontext typu "Bump". Jestli by to bylo možné přeložit jako "redukovaný". Tento kontext podporuje pouze alokaci, a nepodporuje uvolnění paměti jiným způsobem než zrušením kontextu. S ukazatelem na paměť z kontextu tohoto typu nelze zavolat funkci pfree nebo repalloc. Díky "redukci" je alokace paměti efektivnější (odpadá hlavička) a rychlejší (jelikož se nezkouší hledat v seznamu uvolněné paměti (freelist)). Pro některé úlohy jako řazení je ideální - a také řazení v PostgreSQL 17 tento typ paměťového kontextu používá.

Optimalizace (možnost konfigurace velikosti) SLRU cache

V SLRU se ukládají perzistentní provozní data - id transakcí, id subtransakcí, id rodičovských transakcí, notifikace. Tato data jsou perzistentní. Ukládají se na disk. Z důvodu výkonu se naposledy použitá ještě drží v dedikované cache ve sdílené paměti. V některých případech (např. extrémně zatížená aplikace generuje delší transakce s velkým počtem subtransakcí (časté volání příkazu SAVEPOINT)) se ukázalo, že tato cache velikostně nestačí, a častou invalidací dat z cache se hodně ztrácí výkon (a hodně zatěžuje CPU).

Historicky ten problém byl ještě o to větší, že byl obtížně detekovatelný. Počínaje Postgresem 13 máme k dispozici statistiku v pohledu pg_stat_slru, takže nějaké problémy se dají celkem snadno detekovat (ale nedaly se řešit na úrovni databáze):

(2024-03-28 08:06:22) postgres=# SELECT * FROM pg_stat_slru ;
┌──────────────────┬─────────────┬──────────┬───────────┬──────────────┬─────────────┬─────────┬───────────┬───────────────────────────────┐
│       name       │ blks_zeroed │ blks_hit │ blks_read │ blks_written │ blks_exists │ flushes │ truncates │          stats_reset          │
╞══════════════════╪═════════════╪══════════╪═══════════╪══════════════╪═════════════╪═════════╪═══════════╪═══════════════════════════════╡
│ commit_timestamp │           0 │        0 │         0 │            0 │           0 │       6 │         0 │ 2024-03-28 06:01:46.854646+01 │
│ multixact_member │           1 │        0 │         0 │            1 │           0 │       6 │         0 │ 2024-03-28 06:01:46.854646+01 │
│ multixact_offset │           1 │        1 │         2 │            3 │           0 │       6 │         0 │ 2024-03-28 06:01:46.854646+01 │
│ notify           │           0 │        0 │         0 │            0 │           0 │       0 │         0 │ 2024-03-28 06:01:46.854646+01 │
│ serializable     │           0 │        0 │         0 │            0 │           0 │       0 │         0 │ 2024-03-28 06:01:46.854646+01 │
│ subtransaction   │           5 │        0 │         0 │            3 │           0 │       6 │         6 │ 2024-03-28 06:01:46.854646+01 │
│ transaction      │           2 │     3824 │         2 │            5 │           0 │       6 │         0 │ 2024-03-28 06:01:46.854646+01 │
│ other            │           0 │        0 │         0 │            0 │           0 │       0 │         0 │ 2024-03-28 06:01:46.854646+01 │
└──────────────────┴─────────────┴──────────┴───────────┴──────────────┴─────────────┴─────────┴───────────┴───────────────────────────────┘
(8 rows)

V PostgreSQL 17 se dřívější jedna cache rozdělila, a každá její separátní část (bank) má vlastní zámek.

Výchozí velikosti jsou plus mínus podobné jako dříve, ale lze je konfigurovat nastavením proměnných commit_timestamp_buffers, multixact_member_buffers, multixact_offset_buffers, notify_buffers, serializable_buffers, subtransaction_buffers, transaction_buffers.

Ostatní

O něco rychleji by se měla provádět změna proměnné search_path díky cache oid schémat. Datový typ ltree (z extenze ltree) má nově tzv hashovací funkci - tudíž se na něm může provést hashjoin, hashagg. Měla by se výrazně redukovat potřeba CPU způsobená režii fronty čekajících procesů (v praxi se jednalo o hraniční případy, kdy fronta čekajících procesů byla extrémně dlouhá).

Nově lze jako PARALLEL SAFE označit i PL/pgSQL funkce obsahující zachycení chyby.

Administrace

Zálohování

Doposud inkrementální zálohu umožňovaly pouze backup systémy třetích stran. Počínaje verzí 17 lze udělat inkrementální zálohu i s nástrojem pg_basebackup s volbou --incremental=MANIFEST. Rozdílová záloha je prakticky standardní záloha s výjimkou některých datových souborů, které jsou (v backupu) pojmenované INCREMENTAL.${původní název}. Novým příkazem pg_combinebackup lze zrekonstruovat datový adresář z prvního fullbackupu a posloupnosti inkrementálních backupů.

pg_basebackup -cfast -Dx
pg_basebackup -cfast -Dy --incremental x/backup_manifest
pg_combinebackup x y -o z

Díky inkrementálním backupům můžeme dělat častější periodické backupy i na větších a zápisem zatížených databázích - např. 1x za hodinu přičemž nejsou takové nároky na diskovou kapacitu pro archivaci transakčních logů. Také obnova by měla být výrazně rychlejší.

Patch pro parametrizaci pg_dumpu, který jsem 40x přepisoval, se konečně dostal do upstreamu. Od verze 17 můžete filtrovací parametry pg_dumpu uložit do souboru a z něj načíst. Výhodou je neomezená velikost oproti relativně hodně omezené velikosti příkazové řádky. Práce na této funkcionalitě je dobrou ukázkou někdy extrémně náročného nalezení řešení v komunitě. Problém není ani tak vlastní implementace (i když zde částečně ano). Patch je primitivní. Víceméně všichni souhlasili, že stávající parametrizace pg_dumpu je problém. Na to jak ten problém vyřešit už byly dost rozdílné názory - od použití JSONu, TOML, vlastní syntaxe. Jestli se má implementovat pouze filtrování nebo genericky konfigurace všech utilit ze souboru. Jestli se má použít ručně psaný parser nebo použít generátor parserů. Prototyp, který se bezproblémově používal u zákazníka jsem měl napsán za 1 den. Do upstreamu se ntá verze dostala možná po pěti letech (, která se zas až tak nelišila (designem) od mého prototypu). To je občas frustrující. Zadání je jasné, dobře ohraničené - a ve chvíli, kdy už by se měly řešit implementační detaily, tak někdo přijde s novou ideou, a začnou se řešit vzdušné zámky. Naštěstí, komunita, v tomto ohledu, funguje a rychle přijde obrana "dej mi reálný smysluplný use-case", takže se až tolik času nepropálí, nicméně implementace některých jednoduchých funkcí trvá zbytečně dlouho. Někdy je problém i v tom, že je k dispozici víc adekvátních řešení, ale musí se vybrat jedno.

echo "include table foo" | /usr/local/pgsql/master/bin/pg_dump --filter -

Nově pg_dump podporuje parametr --exclude-extension pro vyřazení extenze ze zálohy. Backup blobů (large objects) by měl být výrazně rychlejší.

Právo MAINTAIN a role pg_maintain

S právem MAINTAIN (pro relaci) nebo s rolí pg_maintain (na všechny relace) můžeme provádět údržbu tabulek (indexů a pohledů - obecně relací), a nemusíme být vlastníky nebo superusery (jedná se o příkazy VACUUM, ANALYZE, REINDEX, REFRESH, CLUSTER a LOCK). Jednoduchá, šikovná, praktická a hlavně bezpečná funkce.

Vestavěné C.UTF8 locale

Řazení řetězců se řeší v PostgreSQL pomocí funkcí externích knihoven. Buďto starší glibc nebo novější libicu. Jelikož pořadí řetězců je "materializováno" v indexech, tak je Postgres extrémně citlivý na zpětnou kompatibilitu těchto knihoven. V tomto ohledu je problém hlavně s glibc, kde jednak došlo během posledních let k nekompatibilním změnám, a kde to, že došlo k porušení zpětné kompatibility není jednoduše detekovatelné. U libicu je možné zjistit verzi implementace řazení a detekovat změnu. To, a že libicu poskytuje mnohem větší funkcionalitu, je důvod proč se postupně komunita odklání od glibc (pouze v podpoře collation). Řazení je dost častá operace (může se použít pro agregaci, pro joinování) a dost často nepotřebuje řazení podle jazykových norem. Takové řazení je výrazně rychlejší a lze jej jednoduše implementovat, a z důvodu snížení závislosti na externí implementaci se C collation (tak se označují) implementovalo interně (nicméně lze stále používat externí implementaci):

CREATE COLLATION _pg_c_utf8 (
  provider = builtin, locale = 'C.UTF8');

Případně lze použít připravené collation pg_c_utf8 nebo ucs_basic pro ascii (alternativa pro C):

postgres=# CREATE TABLE foo(a varchar COLLATE "pg_c_utf8");
CREATE TABLE
postgres=# INSERT INTO foo VALUES('Příliš žluťoučký kůň se napil žluté vody');
INSERT 0 1
postgres=# SELECT upper(a), initcap(a) FROM foo;
┌──────────────────────────────────────────┬──────────────────────────────────────────┐
│                  upper                   │                 initcap                  │
╞══════════════════════════════════════════╪══════════════════════════════════════════╡
│ PŘÍLIŠ ŽLUŤOUČKÝ KŮŇ SE NAPIL ŽLUTÉ VODY │ Příliš Žluťoučký Kůň Se Napil Žluté Vody │
└──────────────────────────────────────────┴──────────────────────────────────────────┘
(1 row)

Lze použít i C collate, s C.UTF8 fungují funkce upper, lower a initcap pokud používáte kódování UTF8.

Replikace

V pohledu pg_replication_slots se ve sloupci conflict_reason zobrazuje důvod konfliktu. Aktuálně se zobrazuje pouze důvod vedoucí k zneplatnění slotu (wal_removed, rows_removed, wal_level_insufficient). Dalším novým sloupcem je inactive_since, kde je čas, kdy byl slot naposledy aktivně použit.

Nově sloty pro logickou replikaci přežijí upgrade (dříve je příkaz pg_upgrade zrušil).

Logický replikační slot na standby serveru může být nastavený jako synchronizovaný voláním funkce pg_sync_replication_slots. Tento replikační slot pak lze použít failoveru nebo plánovaném switoveru. Slot je synchronizován novým typem background workeru slot sync worker.

Standby replikační sloty uvedené v konfigurační proměnné standby_slot_names budou synchronizované vždy před synchronizací logické replikace. Tím by se mělo garantovat, že budoucí primární server bude vždy napřed před konzumentem (subscriber) logické replikace. Sloty vyjmenované v standby_slot_names musí být synchronizované

Nový příkaz pg_createsubsriber konvertuje hotstandby server (fyzická replikace) do primárního serveru s nastavenou logickou replikací. Důvodem je skutečnost, že inicializace logické replikace (zvláště u velkých databází) je náročnější a pomalejší než vytvoření hotstandby klonu (fyzické repliky). Konverzí z klonu by se výrazně měla redukovat náročnost na vytvoření logické repliky.

Výrazně by se měla zrychlit implementace aplikace zpracování subtransakcí (eviction) při dekódování logické replikace. Stávající implementace způsobovala velké prodlevy (lagy) pokud transakce měla velké množství subtransakcí. V popisu patche je uvedeno cca 30 násobné zrychlení u transakce s 100 tisíci subtransakcemi.

Partitioning

V PostgreSQL 17 můžeme partišny rozdělit příkazem ALTER TABLE SPLIT PARTITION nebo sloučit příkazem ALTER TABLE MERGE PARTITIONS. Příkazy vyžadují exkluzivní zámek ACCESS EXCLUSIVE nad partitiovanou tabulkou, tudíž pro vykonání těchto příkazů je nutná servisní odstávka aplikace. Dá se předpokládat, že v ne úplně vzdálené budoucnosti, dojde k podpoře paralelismu a k snížení úrovně zamykání (poznámka - patch implementující tuto funkcionalitu byl revertnut před vydáním - důvodem je problém s bezpečností).

ALTER TABLE sales_range SPLIT PARTITION sales_all INTO
  (PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'),
   PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
   PARTITION sales_others DEFAULT);

Vylučovací omezení (exclusion constraints) je možné aplikovat nyní i na partišnách (pokud omezení obsahuje všechny sloupce řídící partitioning). Nově také mohou partitions podědit sloupce s identitou partišnované tabulky:

CREATE TABLE pitest1 (f1 int, f2 text, f3 bigint generated always as identity) PARTITION BY RANGE (f1);
CREATE TABLE pitest1_p1 PARTITION OF pitest1 FOR VALUES FROM (10) TO (20);
CREATE TABLE pitest1_p2 PARTITION OF pitest1 FOR VALUES FROM (21) TO (30);
INSERT INTO pitest1 VALUES(10, 'ahoj');
INSERT INTO pitest1 VALUES(25, 'Nazdar');

postgres=# SELECT * FROM pitest1;
┌────┬────────┬────┐
│ f1 │   f2   │ f3 │
╞════╪════════╪════╡
│ 10 │ ahoj   │  1 │
│ 25 │ Nazdar │  2 │
└────┴────────┴────┘
(2 rows)

Ostatní

V Postgresu bylo možné nastavit dvanáct různých timeoutů. Ve verzi 17 přibyl transaction_timeout. Tento timeout se nevztahuje na 2PC transakce. Tímto tiemeoutem je např. možné nastavit maximální čas držení zámku. Pozor - tento timeout provede disconnect.

Nastavením proměnné allow_alter_system na false (v postgresql.conf), lze zablokovat příkaz ALTER SYSTEM (tudíž konfigurace PostgreSQL bude možná pouze skrze postgresql.conf).

V příkazu EXPLAIN lze volbou MEMORY zobrazit spotřebu paměti planneru:

(2024-04-01 11:30:14) postgres=# EXPLAIN (MEMORY) SELECT * FROM pg_class ;
┌─────────────────────────────────────────────────────────────┐
│                         QUERY PLAN                          │
╞═════════════════════════════════════════════════════════════╡
│ Seq Scan on pg_class  (cost=0.00..18.15 rows=415 width=263) │
│ Planning:                                                   │
│   Memory: used=22960 bytes  allocated=32768 bytes           │
└─────────────────────────────────────────────────────────────┘
(3 rows

Sledovat spotřebu paměti plannerem může mít smysl u extrémně komplikovaných dotazů s velkým počtem relací (např. vetším počtem partitions) - nebo při investigaci memory leaků.

Volbou SERIALIZE v příkazu EXPLAIN ANALYZE si lze vynutit transformaci dat do formátu protokolu (nicméně data se neposílají na klienta). Výsledkem jsou (v některých případech) realističtější data z důvodu vynuceného deTOASTu.

V pg_stat_statements jsou nové sloupce. jit_deform_count zobrazuje počet "tuple deform" funkcí kompilovaných JIT. "tuple deform" je proces, který převádí řádková data z formátu v jakém jsou uložena na disku do formátu s kterým se pracuje interně v Postgresu. jit_deform_time je celkový čas JIT kompilace tuple deform funkcí. JIT deform funkcí by měl pomoct u dotazů do širokých tabulek s velkým počtem řádků. Pokud se použije nevhodně (díky špatným odhadům), tak naopak má dost nepříjemnou režii JIT kompilace (pro výsledek JIT kompilace zatím neexistuje cache). stat_since je čas, kdy se pro SQL příkaz vytvořila řádek v pg_stat_statements (kdy se daný SQL příkaz poprve spustil po resetu statistik). minmax_stat_since je čas, kdy resetovaly min/max statistiky. Nově lze nastavením parametrů funkce pg_stat_statements_reset vyresetovat pouze statistiky konkrétního dotazu (případně min/max statistiky min_plan_time, max_plan_time, min_exec_time a max_exec_time).

Statistiky ohledně checkpointů se přesunuly do vlastního pohledu pg_stat_checkpointer.

Extenze pg_buffercache nově obsahuje funkci pg_buffercache_evict(bufferid), která odstraní zadaný buffer ze sdílené paměti.

Pelmel

login trigger

Novinkou v uložených procedurách je možnost vytvářet login triggery. Některé konkurenční databáze tuto funkcionalitu měly desetiletí, a pokud se z těchto databází migrovalo do Postgresu, tak to byl trochu problém (mimo cloud relativně jednoduše řešitelný extenzí např. <a href="https://github.com/okbob/session_exec">session_exec</a>). S vestavěnou podporou extenze nejsou potřeba. Pozor, i integrované řešení má režii, a zpomalí connect. Ale s tím se musí počítat. V rámci login triggeru (patří do kategorie event triggerů) můžete dynamicky nastavit práva, vytvořit dočasné tabulky pro session, nastavit GUC proměnné:

CREATE OR REPLACE FUNCTION public.init_session()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  RAISE NOTICE 'login % at %', session_user, current_timestamp;
END;
$function$

CREATE EVENT TRIGGER init_session ON LOGIN EXECUTE FUNCTION init_session();

Chyba v triggeru může způsobit nemožnost přihlášení se, a nemožnost tuto chybu opravit. Pojistkou může být blokace triggeru ALTER EVENT TRIGGER init_session DISABLE anebo nastavením proměnné event_triggers na off v connection stringu. Takže pozor, ten kdo nastavuje connection string, má možost vyblokovat login trigger (tudíž login trigger nemůže (a není to cílem) zastoupit zabezpečení databáze).

Nové funkce

Vestavěná funkce random() vrací desetinné číslo 0.0 <= x < 1.0. Prakticky vždy se vrácené číslo mapuje na nějaký interval. Pro jednodušší práci se přidaly dvouparametrické funkce random(min, max), které generují číslo min <= x <= max. Použitý typ určuje typ výsledku (int, bigint, numeric):

postgres=# SELECT random(1, 10);
┌────────┐
│ random │
╞════════╡
│      9 │
└────────┘
(1 row)

Popis typu v Postgresu se skládá ze dvou aktributů - type a typmod. V typmodu může být zakódována maximální délka (varchar) nebo přesnost (numeric). Poznámka - u varcharu je to počet znaků zvýšený o hodnotu 4 (z historických důvodů). Interně má Postgres funkce převod typu na text a text na typ. První se používá například v chybových hlášeních, druhé v parsování SQL. K těmto interním funkcím dosud neexistovalo SQL API, tudíž je nebylo možné použít z PL/pgSQL. Od verze 17 už je tato funkcionalita k dispozici i z SQL:

postgres=# SELECT format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'));
┌───────────────────────┐
│      format_type      │
╞═══════════════════════╡
│ character varying(32) │
└───────────────────────┘
(1 row)

postgres=# SELECT to_regtypemod('varchar(32)');
┌───────────────┐
│ to_regtypemod │
╞═══════════════╡
│            36 │
└───────────────┘
(1 row)

postgres=# SELECT to_regtype('varchar(32)');
┌───────────────────┐
│    to_regtype     │
╞═══════════════════╡
│ character varying │
└───────────────────┘
(1 row)

Historií dost podobná je funkce pg_basetype, která vrací základní typ doménového typu. Tyto funkce mají dost úzkou oblast nasazení - myslím si, že je budou používat pouze autoři extenzí.

Nejspíš pro úplnost se implementovaly funkce to_bin (převod do binarní soustavy) a to_oct (převod do osmičkové soustavy):

postgres=# SELECT to_oct(-1234) AS "37777775456";
┌─────────────┐
│ 37777775456 │
╞═════════════╡
│ 37777775456 │
└─────────────┘
(1 row)

Interně jsou tyto funkce v Postgresu odjakživa. Nyní se pouze zpřístupnily z SQL.

Nové funkce lze použít i pro datový typ uuid, uuid_extract_timestamp vrátí čas vytvoření uuid typu 1. uuid_extract_version vrací pro uuid generované funkcí gen_random_uuid vrací 4 (pro ostatmí NULL).

Registr dynamické sdílené paměti

Perzistentní data (které mají přežít session) nebo sdílená data v paměti musí být v Postgresu uložená v tzv. sdílené paměti. Extenze, které chtějí používat sdílenou paměť musí být aktivované při startu Postgresu, jindy už není možné sdílenou paměť alokovat. Extenze, která nepotřebuje sdílenou paměť se aktivuje až v případě potřeby. To, že mohu zavést extenzi pouze při startu Postgresu je potíž - znamená to nutnost restartu, a to v některých provozech je problém (minimálně v tom, že se musí čekat na servisní okno). Je to klasika, řešíte hořák u klienta, a potřebovali byste urgentně extenzi pg_stat_statements, která ale není instalovaná a jejíž instalace vyžaduje restart, který ale z provozních důvodů není možný (rada - instalujte extenzi pg_stat_statements) by default).

Při paralelní zpracování dotazu se implementovala tzv. dynamická sdílená paměť DSM. DSM se používá jako fronta pro komunikaci mezi rodičovským procesem a obslužnými procesy dotazu (předávají se parametry, zpět se přebírají výsledky). Omezením DSM byla jednosměrnost jeho použití. Rodičovský proces vytvořil DSM, které mohly používat procesy potomků, a po zániku rodičovského procesu došlo k zrušení i použitého DSM segmentu. To je pro drtivou většinu extenzí nepraktické. Řešením je registr dynamické sdílené paměti. Voláním funkce GetNamedDSMSegment dostaneme ukazatel na dynamickou sdílenou paměť (můžeme si o ni požádat v libovolném čase). Parametrem je název. Určitě je to šikovná funkce, kterou použiji v profileru v plpgsql_checku.

libpq

libpq je knihovna, která se stará o komunikaci (obsluhu - implementaci protokolu) mezi klientem a serverem. Nově lze funkcemi PQclosePrepared, PQclosePortal, PQsendClosePrepared, a PQsendClosePortal explicitně zavřít vykonávání příkazu (nejedná se o rozšíření protokolu, pouze o rozšíření API). Pokud se tyto funkce použijí, tak pravděpodobně při implementaci nějaký proxy nebo poolerů místo volání příkazu DEALLOCATE.

Nově knihovna libpq podporuje neblokující (asynchronní) přerušení příkazu díky novému API tvořeného funkcemi PQcancelCreate, PQcancelStart, PQcancelPoll a PQcancelReset.

Knihovna také nově podporuje blokové načítání řádků (chunked rows mode). V tomto režimu se načítají data do paměti v blocích po n řádcích (n je určeno voláním funkce PQsetChunkedRowsMode). Tento režim se použil pro reimplementaci postupného načítání dat v psql, které se aktivuje nastavením proměnné FETCH_COUNT. Cílem fetchování je redukovat potřebu operační paměti zejména v neinteraktivních skriptech, kde se nepoužívá formát formátované tabulky.

Ostatní

Ve formátovacích řetězcích (funkce to_char, to_timestamp) lze použít formátovací kódy TZ (časová zóna nebo offset) nebo OF (offset).

Konfigurační proměnné huge_pages_status zobrazí stav použítí huge pages:

postgres=# SHOW huge_pages_status;
┌───────────────────┐
│ huge_pages_status │
╞═══════════════════╡
│ off               │
└───────────────────┘
(1 row)

Při deklaraci proměnné v PL/pgSQL můžeme typ specifikovat referencí (na jinou proměnnou). Nově lze použít syntax [] nebo ARRAY[] a ze reference na skalární typ vytvořit referenci na pole:

do $$
declare`
  v int;
  v1 v%type;
  v2 v%type[];
  v3 v%type array;
  v10 pg_catalog.pg_class%rowtype[];
begin
  raise notice '%', pg_typeof(v1);
  raise notice '%', pg_typeof(v2);
  raise notice '%', pg_typeof(v3);
  raise notice '%', pg_typeof(v10);
end;
$$;

NOTICE:  integer
NOTICE:  integer[]
NOTICE:  integer[]
NOTICE:  pg_class[]
DO

O podobné funkcionalitě se již mluvilo několik let, do upstreamu se dostává teď. Za 25 let, co dělám s PL/pgSQL jsem asi 2x potřeboval něco podobného, a musel jsem použít workaround. Není to nic extra, co by v Postgresu chybělo, ale je hezké (z důvodu úplnosti), že tato syntaxe je podporována.

Závěr

Oproti předchozím verzím je viditelných změn v PostgreSQL 17 o dost víc (dokončují se práce zahájené i před rokem 2020). Osobně si myslím, že tempo vývoje se během několika let zmírní - příští rok to ale nebude, rozdělaných patchů ve frontě je stále hodně. Pracuje se na logické replikaci, optimalizacích, možnosti použití streaming IO.

---

• 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 11 (2018)
• PostgreSQL 12 (2019)
• PostgreSQL 13 (2020)
• PostgreSQL 14 (2021)
• PostgreSQL 15 (2022)
• PostgreSQL 16 (2023)