PostgreSQL 15 (2022): Porovnání verzí
Bez shrnutí editace |
|||
| (Není zobrazeno 5 mezilehlých verzí od stejného uživatele.) | |||
| Řádek 1: | Řádek 1: | ||
<i>Autor: Pavel Stěhule, | <i>Autor: Pavel Stěhule, 2022</i> | ||
V článku věnovaném PostgreSQL 14 jsem napsal, že novinky v této verzi | V článku věnovaném PostgreSQL 14 jsem napsal, že novinky v této verzi | ||
| Řádek 99: | Řádek 97: | ||
===JSON=== | ===JSON=== | ||
<i>Následující kapitola neplatí - tyto patche byly těsně před ukončením vývoje | |||
revertovány. Plná podpora SQL/JSON bude až v PostgreSQL 16.</i> | |||
V Postgresu 15 můžeme používat funkce pro práci s typem <code>JSON</code> | V Postgresu 15 můžeme používat funkce pro práci s typem <code>JSON</code> | ||
| Řádek 394: | Řádek 395: | ||
(3 rows) | (3 rows) | ||
</pre> | </pre> | ||
===regexp funkce=== | ===regexp funkce=== | ||
| Řádek 514: | Řádek 514: | ||
friendly". Od patnáctky máme možnost nastavit konfigurační proměnnou psql | friendly". Od patnáctky máme možnost nastavit konfigurační proměnnou psql | ||
<code>PSQL_WATCH_PAGER</code>, tak aby obsahovala volání pageru s | <code>PSQL_WATCH_PAGER</code>, tak aby obsahovala volání pageru s | ||
příslušným nastavením. Například pro | příslušným nastavením. Například pro [https://github.com/okbob/pspg <code>pspg</code>] je nutné použít | ||
přepínač <code>--stream</code>. | přepínač <code>--stream</code>. | ||
| Řádek 569: | Řádek 569: | ||
multi recordsety může omezovat, komplikovat portace aplikací z MSSQL | multi recordsety může omezovat, komplikovat portace aplikací z MSSQL | ||
(případně Sybase) do Postgresu. To by se v příštích verzích mělo změnit. | (případně Sybase) do Postgresu. To by se v příštích verzích mělo změnit. | ||
==Administrace== | ==Administrace== | ||
| Řádek 829: | Řádek 828: | ||
hodnotu <code>server-gzip</code> (nebo <code>server-lz4</code>, která | hodnotu <code>server-gzip</code> (nebo <code>server-lz4</code>, která | ||
vynutí komprimaci dat serveru už na straně serveru. Zvlášť na pomalých | vynutí komprimaci dat serveru už na straně serveru. Zvlášť na pomalých | ||
sítích nebo na VPNkách komprimace na straně serveru | sítích nebo na VPNkách komprimace na straně serveru [http://rhaas.blogspot.com/2022/02/server-side-lz4-backup-compression.html razantně zrychluje klonování]. | ||
klonování | |||
===Replikace=== | ===Replikace=== | ||
Aktuální verze z 17. 5. 2024, 04:45
Autor: Pavel Stěhule, 2022
V článku věnovaném PostgreSQL 14 jsem napsal, že novinky v této verzi
nejsou pro uživatele Postgresu extra viditelné. Většina vylepšení byla
ukryta "pod kapotou". U patnáctky to rozhodně neplatí. Téměř úplná podpora ANSI SQL/JSON
je bomba, a úplností tohoto implementace standardu je Postgres v čele
SQL databází (také se na tomto patchi pracovalo od roku 2017).
Implementace příkazu MERGE, opět naprosto jasně uživatelsky viditelná
funkce. Podpora ICU collations pro celé databáze, atd atd. Další rok
vývoje je dobře vidět.
SQL
MERGE
SQL rozeznává základní DML přkazy: INSERT, UPDATE, DELETE.
Kombinací těchto příkazů je příkaz, který se často označuje jako
UPSERT. V Postgresu už pěknou řádku let máme možnost použít klauzuli
ON CONFLICT DO příkazu INSERT, což je efektivně právě příkaz UPSERT. V
ANSI/SQL je UPSERTem příkaz MERGE. INSERT ON CONFLICT DO má o něco
jednodušší syntax, jedná se ale o proprietární rozšíření SQL v
PostgreSQL. Syntaxe příkazu MERGE je definovaná standardem (a není
extra složitá). Navíc podporuje i mazání řádků.
postgres=# SELECT * FROM foo;
┌────┬────┐
│ id │ v │
╞════╪════╡
│ 1 │ 10 │
└────┴────┘
(1 row)
postgres=# SELECT * FROM boo;
┌────┬────┐
│ id │ v │
╞════╪════╡
│ 1 │ 20 │
│ 2 │ 30 │
└────┴────┘
(2 rows)
postgres=# MERGE INTO foo
USING boo ON foo.id = boo.id
WHEN MATCHED THEN UPDATE SET v = boo.v
WHEN NOT MATCHED THEN INSERT VALUES(id, v);
MERGE 2
postgres=# SELECT * FROM foo;
┌────┬────┐
│ id │ v │
╞════╪════╡
│ 1 │ 20 │
│ 2 │ 30 │
└────┴────┘
(2 rows)
V Postgresu můžeme také použít příkaz INSERT ON CONFLICT:
-- zapis pres korelovany poddotaz
INSERT INTO foo
SELECT id, v FROM boo b
ON CONFLICT(id)
DO UPDATE SET v = (SELECT v FROM boo WHERE foo.id = boo.id);
-- zapis pres exluded
INSERT INTO foo
SELECT boo.id, boo.v FROM boo
LEFT JOIN foo ON boo.id = foo.id
ON CONFLICT(id)
DO UPDATE SET v = EXCLUDED.v;
Ačkoliv MERGE a INSERT ON CONFLICT DO dělají
skoro totéž, nejsou implementovány stejně, a na různých datech mohou být
jinak rychlé. Na jednoduchých příkladech, které jsem si dělal, jsem
si nevšiml signifikantních rozdílů v rychlosti. Spíš tam vnímám drobnou
nuanci v sémantice. MERGE je primárně hromadný příkaz.
Syntaxe INSERT ON CONFLICT DO se hodí pro práci s jedním řádkem.
Hlavní benefit je nejspíš pro datové analytiky, kteří znají příkaz
MERGE z jiných databází, a nemusí se přepínat na proprietární
příkaz v Postgresu.
Omezení (constraint) UNIQUE NULLS DISTINCT
Omezení UNIQUE zaručuje unikátní hodnoty ve sloupci. Za
unikátní hodnotu se ale nepovažuje NULL. Což je to, co
většinou chceme. V některých případech můžeme požadovat, aby hodnota
NULL byla ve sloupci pouze jednou. Tento požadavek můžeme
řešit funkčním indexem a zrovna tak i podmíněným indexem. I když tento
index bude malý (bude vždy obsahovat max. jednu hodnotu), bude to další
index navíc. V Postgresu 15 můžeme u omezení UNIQUE použít
frázi NULLS DISTINCT, čímž dosáhneme požadovaného chování
omezení (NULL bude považováno za hodnotu, která má být
unikátní).
JSON
Následující kapitola neplatí - tyto patche byly těsně před ukončením vývoje revertovány. Plná podpora SQL/JSON bude až v PostgreSQL 16.
V Postgresu 15 můžeme používat funkce pro práci s typem JSON
definované v ANSI/SQL 2016 v části SQL/JSON. Prototyp implementace byl k
dispozici relativně brzo. Tuším už v roce 2018. Byl to ale mega patch,
který bylo potřeba rozdělit na menší části, zrevidovat a dočistit.
Většina kódu je poměrně jednoduchá vyjma implementace JSONPath a
implementace funkce JSON_TABLE. Podpora JSONPath se dostala
do Postgresu 12. Zbytek, včetně JSON_TABLE, se dostal do nové
verze. V MySQL je už asi dva, možná tři roky implementace
JSON_TABLE, která obsahuje základ, který je dostačující více
než na 99%. Patch s implementací do Postgresu obsahoval úplnou
implementaci standardu včetně tzv plánů (což dost zkomplikovalo review
patche). V Postgresu jsme na podporu SQL/JSON museli čekat relativně
dlouho. Nyní má ale Postgres jednu z nejúplnějších implementací SQL/JSON.
Nové funkce lze rozdělit do několika kategorií: dotazovací funkce
(JSON_EXISTS, JSON_QUERY,
JSON_VALUE), funkce pro vytvoření a serializaci JSONu
(JSON, JSON_SCALAR, JSON_SERIALIZE),
funkci pro transformaci JSONu na relaci (JSON_TABLE) a
operátor IS JSON.
Začnu samopopisnou ukázkou operátoru IS JSON ...
postgres=# SELECT '[1,2,3]' IS JSON ARRAY;
┌──────────┐
│ ?column? │
╞══════════╡
│ t │
└──────────┘
(1 row)
postgres=# SELECT '{"a":1, "b":"ahoj"}' IS JSON OBJECT;
┌──────────┐
│ ?column? │
╞══════════╡
│ t │
└──────────┘
(1 row)
postgres=# SELECT '"ahoj"' IS JSON SCALAR;
┌──────────┐
│ ?column? │
╞══════════╡
│ t │
└──────────┘
(1 row)
Funkce json provádí přetypování na typ JSON z
typu text, bytea, ... json_scalar generuje json ze ze
základních SQL skalárních typů. json_serialize umožňuje
serializovat hodnotu do bytea a umožňuje změnit kódování nebo formát . V
tuto chvíli je podporován pouze kódování UTF8 a formát JSON. V dalších
verzích mohou být podporována jiná kódování nebo formáty (např. BSON):
postgres=# SELECT json('{"a":1}');
┌─────────┐
│ json │
╞═════════╡
│ {"a":1} │
└─────────┘
(1 row)
postgres=# SELECT JSON_SCALAR('ahoj'), JSON_SCALAR(current_date), JSON_SCALAR(1);
┌─────────────┬──────────────┬─────────────┐
│ json_scalar │ json_scalar │ json_scalar │
╞═════════════╪══════════════╪═════════════╡
│ "ahoj" │ "2022-04-27" │ 1 │
└─────────────┴──────────────┴─────────────┘
(1 row)
postgres=# SELECT JSON_SERIALIZE(json('{"a":1}') RETURNING bytea);
┌──────────────────┐
│ json_serialize │
╞══════════════════╡
│ \x7b2261223a317d │
└──────────────────┘
(1 row)
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 docela bohatá. 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, by default, ignorována (v laxním režimu). Pro
vytvoření pole z vrácených hodnot musíme 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.
Před třemi roky jsem dělal review, teď jsem si s těmito funkcemi chvíli hrál. Základ funguje dobře, ale zatím nejsou využité možnosti, které v Postgresu jsou.Tady to určitě bude chtít ještě pár let práce. Navíc, díky tomu, že standard SQL/JSON je relativně nový, a relativně komplexní, tak je hrozně málo lidí, kteří by měli potřebné zkušenosti, znalosti a i třeba názor, jak by implementace v Postgresu měla vypadat.
Funkce JSON_TABLE je obdobou funkce XMLTABLE na
steroidech. Oproti XMLTABLE podporuje nesting, a navíc
umožňuje určit, jak se zanořená data mají reprezentovat (V JSONu máme
denormalizovaná data, a ve zmíněné funkci máme několik způsobů, jak je
normalizovat). To, co jsme museli v XMLTABLE řešit na SQL
úrovni (JOIN, vícenásobné volání XMLTABLE), to můžeme v
JSON_TABLE udělat přímo. Výhodou je rychlost. Nevýhodou
naopak možnost narazit na limity paměti, a mnohem komplikovanější syntax
JSON_TABLE (i když i tuto funkci lze také používat jednoduše):
postgres=# SELECT * FROM foo;
┌────────────────────────────────────────────────────────────────────────────────┐
│ a │
╞════════════════════════════════════════════════════════════════════════════════╡
│ {"a": "ahoj", "b": [10, 20, 30], "c": {"name": "pavel", "surname": "stehule"}} │
└────────────────────────────────────────────────────────────────────────────────┘
(1 row)
postgres=# SELECT JSON_TABLE.*
FROM foo,
JSON_TABLE(a, '$' COLUMNS (a text, b int[]));
┌──────┬────────────┐
│ a │ b │
╞══════╪════════════╡
│ ahoj │ {10,20,30} │
└──────┴────────────┘
(1 row)
postgres=# SELECT json_table.*
FROM foo,
JSON_TABLE(a, '$' COLUMNS (a text,
NESTED PATH '$.b[*]' COLUMNS (b int path '$'),
NESTED PATH '$.c' COLUMNS (name text, surname text)));
┌──────┬────┬───────┬─────────┐
│ a │ b │ name │ surname │
╞══════╪════╪═══════╪═════════╡
│ ahoj │ 10 │ ∅ │ ∅ │
│ ahoj │ 20 │ ∅ │ ∅ │
│ ahoj │ 30 │ ∅ │ ∅ │
│ ahoj │ ∅ │ pavel │ stehule │
└──────┴────┴───────┴─────────┘
(4 rows)
JSON se zpracovává postupně - napřed cesta učená primárním JSONPath výrazem, poté se
zpracovávají sourozenecké zanořené cesty. Sloupce, které generuje jiná zanořená cesta
se doplňují hodnotou NULL. V poslední ukázce jsou dvě
zanořené sloupce. V prvním případě je nastavený sloupec b a
sloupce name a surname jsou NULL. V
druhém případě (pro druhou zanořenou cestu) je to přesně opačně.
Popsaný způsob vytváření výsledné relace z primární cesty a ze zanořených cest nemusí vždy vyhovovat. Pomocí tzv plánů lze hodně volně specifikovat jiný způsob:
postgres=# SELECT json_table.*
FROM foo,
JSON_TABLE(a, '$' AS p0 COLUMNS (a text,
NESTED PATH '$.b[*]' AS p1 COLUMNS (b int path '$'),
NESTED PATH '$.c' as p2 COLUMNS (name text, surname text))
PLAN (p0 INNER (p1 CROSS p2)));
┌──────┬────┬───────┬─────────┐
│ a │ b │ name │ surname │
╞══════╪════╪═══════╪═════════╡
│ ahoj │ 10 │ pavel │ stehule │
│ ahoj │ 20 │ pavel │ stehule │
│ ahoj │ 30 │ pavel │ stehule │
└──────┴────┴───────┴─────────┘
(3 rows)
Lze vybrat pouze určité kombinace z plánů INNER, OUTER,
UNION a CROSS. INNER a
OUTER plány (analogie k INNER JOIN a LEFT
OUTER JOIN) se používají pro vazbu primární a nested cesty.
UNION a CROSS se používají pro vazbu mezi nested
cestami (analogie k FULL OUTER JOIN a CROSS
JOIN). Výchozí plány jsou OUTER a UNION, a
lze je změnit v klauzuli PLAN DEFAULT:
postgres=# SELECT json_table.*
FROM foo,
JSON_TABLE(a, '$' AS p0 COLUMNS (a text,
NESTED PATH '$.b[*]' AS p1 COLUMNS (b int path '$'),
NESTED PATH '$.c' AS p2 COLUMNS (name text, surname text))
PLAN DEFAULT(cross));
┌──────┬────┬───────┬─────────┐
│ a │ b │ name │ surname │
╞══════╪════╪═══════╪═════════╡
│ ahoj │ 10 │ pavel │ stehule │
│ ahoj │ 20 │ pavel │ stehule │
│ ahoj │ 30 │ pavel │ stehule │
└──────┴────┴───────┴─────────┘
(3 rows)
regexp funkce
V Postgresu budou 4 nové funkce pro práci s regulárními výrazy:
regexp_count, regexp_instr,
regexp_like a regexp_substr. Stávající funkce
regexp_replace je rozšířena o další volitelné parametry. API
je téměř kompatibilní s Oraclem, odkud je převzato. Což znamená, že je to
trochu Oraclovština (není úplně konzistentní). Na druhou stranu, když už
něco vypadá jako funkce převzatá z Oracle, tak mi přijde lepší, když se
bude chovat jako funkce převzatá z Oracle. Rozdíl vůči Oracle je v
přístupu k NULL (Oracle nerozlišuje mezi NULLem a
prázdným řetězcem). Pokud byste chtěli použít tyto funkce ve starších
verzích Postgresu, najdete je v Orafce, navíc upravené tak,
aby poskytovaly maximální možnou kompatibilitu s Oraclem (včetně přístupu
k NULL):
postgres=# SELECT regexp_instr('abcabcabc', 'a.c', 2);
┌──────────────┐
│ regexp_instr │
╞══════════════╡
│ 4 │
└──────────────┘
(1 row)
postgres=# SELECT regexp_like('Steven', '^Ste(v|ph)en$');
┌─────────────┐
│ regexp_like │
╞═════════════╡
│ t │
└─────────────┘
(1 row)
Jinak nové funkce pro práci s regexpama mi přijdou docela užitečné, a buďto zjednodušují některé operace s řetězci nebo umožňují operace, které se starším API nebylo možné provést. Po pravdě řečeno, dřív když chtěl člověk něco extra s regexpy v Postgresu, tak si napsal funkci v PL/Perl, a nic neřešil. Na druhou stranu se dnes PostgreSQL provozuje v cloudu nebo v prostředích, kde není dostupná veškerá funkcionalita, a kde si člověk nemůže doinstalovat "svoje" extenze, a tudíž implementace v jádře dává smysl.
psql
Ve verzi 15 můžeme používat dva nové backslash příkazů. Posledním
implementovaným je \dconfig, který bez dalších parametrů
zobrazí konfigurační hodnoty změněné na daném serveru. Parametr u tohoto
příkazu funguje podobně jako u ostatních \d* příkazů. V
pluskové variantě zobrazí i přístupová práva:
postgres=# \dconfig
List of non-default configuration parameters
┌──────────────────────────────────┬──────────────────────────────────────────────┐
│ Parameter │ Value │
╞══════════════════════════════════╪══════════════════════════════════════════════╡
│ application_name │ psql │
│ client_encoding │ UTF8 │
│ client_min_messages │ warning │
│ config_file │ /usr/local/pgsql/master/data/postgresql.conf │
│ data_directory │ /usr/local/pgsql/master/data │
│ DateStyle │ ISO, DMY │
│ hba_file │ /usr/local/pgsql/master/data/pg_hba.conf │
│ check_function_bodies │ off │
│ ident_file │ /usr/local/pgsql/master/data/pg_ident.conf │
│ lc_collate │ cs_CZ.UTF-8 │
│ lc_ctype │ cs_CZ.UTF-8 │
│ lc_messages │ cs_CZ.UTF-8 │
│ lc_monetary │ cs_CZ.UTF-8 │
│ lc_numeric │ cs_CZ.UTF-8 │
│ lc_time │ cs_CZ.UTF-8 │
│ log_timezone │ Europe/Prague │
│ max_stack_depth │ 2MB │
│ search_path │ public, pg_catalog │
│ server_encoding │ UTF8 │
│ shared_memory_size │ 143MB │
│ shared_memory_size_in_huge_pages │ 72 │
│ TimeZone │ Europe/Prague │
│ wal_buffers │ 4MB │
└──────────────────────────────────┴──────────────────────────────────────────────┘
(23 rows)
postgres=# \dconfig *mem*
List of configuration parameters
┌──────────────────────────────────┬───────┐
│ Parameter │ Value │
╞══════════════════════════════════╪═══════╡
│ autovacuum_work_mem │ -1 │
│ dynamic_shared_memory_type │ posix │
│ enable_memoize │ on │
│ hash_mem_multiplier │ 2 │
│ logical_decoding_work_mem │ 64MB │
│ maintenance_work_mem │ 64MB │
│ min_dynamic_shared_memory │ 0 │
│ shared_memory_size │ 143MB │
│ shared_memory_size_in_huge_pages │ 72 │
│ shared_memory_type │ mmap │
│ work_mem │ 4MB │
└──────────────────────────────────┴───────┘
(11 rows)
Druhým novým backslash příkazem je \getenv, kterým můžeme
zkopírovat hodnotu prostředí do psql proměnné:
postgres=# \getenv home HOME postgres=# \echo :home /home/pavel
Spíš bonbonkem je možnost použití pageru ve výstupu generovaném příkazem
\watch. Tento backslash příkaz provede každých n sec
zadaný příkaz a zobrazí jeho výsledek. V předchozích verzích
výstup šel přímo do terminálu, což funguje, ale nemusí to být vždy "user
friendly". Od patnáctky máme možnost nastavit konfigurační proměnnou psql
PSQL_WATCH_PAGER, tak aby obsahovala volání pageru s
příslušným nastavením. Například pro pspg je nutné použít
přepínač --stream.
tab-complete v psql by nyní měl zachovávat velikost písmen
(pokud nepíšeme název objektu).
Specifickou vlastností Postgresu jsou vícenásobné příkazy
(multicommands). Vícenásobné příkazy se posílají v jednom packetu a
Postgres je vykoná v rámci jedné transakce. U historických verzí Postgresu se příkazy
zapsané na jedné řádce oddělené středníkem vykonaly jako vícenásobný
příkaz. Výhodou je o něco rychlejší zpracování (redukuje se síťový
provoz), nevýhodou je zobrazení pouze výsledku posledního příkazu. U
moderních verzí už středník odděluje příkaz bez ohledu jestli jsou
zapsané na jedné řádce nebo nejsou. Pokud chcete v psql
vytvořit vícenásobný příkaz, tak se pro oddělení příkazů uvnitř
vícenásobného příkazu používá symbol \;. Od verze 15 pak se
zobrazuje výstup všech příkazů multi příkazu:
postgres=# select 1\;select 2; ┌──────────┐ │ ?column? │ ╞══════════╡ │ 1 │ └──────────┘ (1 row) ┌──────────┐ │ ?column? │ ╞══════════╡ │ 2 │ └──────────┘ (1 row) -- PostgreSQL 14 postgres=# select 1\;select 2; ┌──────────┐ │ ?column? │ ╞══════════╡ │ 2 │ └──────────┘ (1 row)
Samo o sobě to velký smysl nemá. Drtivá většina uživatelů vůbec netuší o existenci vícenásobných příkazů. Tato funkce je příprava pro podporu procedur vracející multi recordset. To je typická vlastnost T-SQL (MSSQL nebo Sybase). Když jsem před nějakými jedna dvaceti roky s MS SQL začínal, tak mi to přišlo jako docela zajímavá funkce, dnes bych byl opatrnější v jejím používání. S multirecordsetem pak už v čistém SQL nic neuděláte, a způsob který používá postgres (tabulkové funkce nebo v terminologii Postgresu Set Returning Functions) mi přijde mnohem praktičtější. V každém případě chybějící podpora procedur vracejících multi recordsety může omezovat, komplikovat portace aplikací z MSSQL (případně Sybase) do Postgresu. To by se v příštích verzích mělo změnit.
Administrace
Změna výchozích práv k schématu public
K tomuto kroku vývojáři Postgresu dlouho sbírali odvahu a sám jsem moc
nevěřil, že by se takový krok někdy uskutečnil. V PostgreSQL má
dominantní pozici schéma public. Většina aplikací ukládá data
do tohoto schématu. Pravděpodobně všechny legacy aplikace. Ve výchozím
nastavení kdokoliv může v tomto schématu dělat cokoliv (se svými
objekty). Schéma v Postgresu neurčuje vlastníka (oproti Oracle). Nemůžete
číst data z objektů, které si vytvořil jiný uživatel (pokud vám k nim
nedal práva), ale můžete si vytvořit tabulku, a tu plnit dokud nedojde
místo na disku.
Proto se doporučuje odstranit práva PUBLIC z tohoto schématu:
REVOKE ALL ON SCHEMA public FROM PUBLIC
Počínaje verzí 15 už bude výchozí nastavení k schématu public
restriktivnější - přístup bude mít pouze vlastník databáze. Pokud by
aplikace vyžadovala PUBLIC přístup, tak jednoduše se dá
nagrantovat buďto přímo v konkrétní databázi nebo v
template1. Samozřejmě, že lepší je opravit aplikaci, tak aby
běžela s explicitně nastavenými právy.
Podpora collations
PostgreSQL může pro porovnávání řetězců použít dvě metody. Buďto využít funkcionalitu locales v glibc nebo použít funkce z knihovny libicu. Výchozí a po roky osvědčenou metodou bylo používání knihovny glibc. V posledních několika letech bohužel došlo k úpravám implementace v glibc. Nová verze glibc řadí v některých locales jinak než stará verze. To má velice nepříjemný dopad na indexy. Všechny indexy nad textovými položkami je nutné reindexovat, jinak může dojít k chybám a poškození konzistence indexů a potažmo konzistence databáze. Těmto problémům lze do jisté míry zabránit sledováním verze knihovny, která se používá k porovnání řetězců. Předpokládá se, že stejná verze garantuje stejné řazení. Verzování již dříve bylo dostupné pro libicu. Nyní je dostupné i pro glibc (plus ručně volaná kontrola kompatibility).
postgres=# ALTER DATABASE postgres REFRESH COLLATION VERSION; NOTICE: version has not changed ALTER DATABASE
Možnosti knihovny glibc ohledně collations jsou relativně omezené. S knihovnou libicu můžeme vytvářet nová deterministická (případně nedeteministická) collations. Teprve ale od nové verze je možné použít collation postavené nad libicu jako výchozí collation databáze. V nové verzi můžeme výchozí collation databáze nastavit i vůči libicu (pouze ale pro deterministické collation (řetězce jsou stejné pokud jejich binární obsah je stejný)). V následujícím příkladu je vytvořena databáze s collate, kde číselné hodnoty jsou řazené numericky a velká písmena jsou upřednostněna před malými písmeny:
postgres=# CREATE DATABASE omega
locale_provider=icu
icu_locale='cs-u-kn-true-kf-upper'
lc_collate="C" TEMPLATE='template0';
CREATE DATABASE
postgres=# \c omega
omega=# VALUES('Chrnek'),('Crha'),('Drulák'),('Horák'),
('Malý'),('malý'),('a1'),('a2'),('a100')
ORDER BY 1;
┌─────────┐
│ column1 │
╞═════════╡
│ a1 │
│ a2 │
│ a100 │
│ Crha │
│ Drulák │
│ Horák │
│ Chrnek │
│ Malý │
│ malý │
└─────────┘
(9 rows)
omega=# VALUES('Chrnek'),('Crha'),('Drulák'),('Horák'),
('Malý'),('malý'),('a1'),('a2'),('a100')
ORDER BY column1 COLLATE "cs-x-icu";
┌─────────┐
│ column1 │
╞═════════╡
│ a1 │
│ a100 │
│ a2 │
│ Crha │
│ Drulák │
│ Horák │
│ Chrnek │
│ malý │
│ Malý │
└─────────┘
(9 rows)
Vygenerovat konfigurační string pro libicu je trochu porod (pro mne, kdo s tím v životě nedělal), ale pak už to funguje. Porovnávání řetězců by mělo být přes libicu rychlejší. Jedna ze zásadních optimalizací je vůči glibc (z důvodu chyby v glibc) blokovaná. O kolik - záleží na délkách řetězců, použitém locales, a samozřejmě i velikosti dat. Zatímco verzování v glibc je workaround, verzování v libicu je postavené na nativní vlastnosti libicu.
Security invoker pohledy
Pohledy se z pohledu identity uživatele chovají podobně jako
security definer funkce. Pokud se dívám na nějakou relaci
skrz pohled, tak používám přístupová práva vlastníka pohledu. V drtivé
většině případů se chování pohledů z této perspektivy řešit nemusí, a
ostatně já sám si nevzpomínám, že bych to někdy během let co dělám s
Postgresem řešil. Pokud se ale začne používat Row Level Security, tak
změna identity (ztráta identity) znemožňuje použití pohledů. Od této
verze lze vytvářet security invoker pohledy:
CREATE VIEW new_type_view with (security_invoker = true ) AS SELECT * FROM source_data;
EXPLAIN a pg_stat_statements
Skoro v každé z nedávných verzí došlo k rozšíření zobrazovaných (a
sbíraných) detailů o exekuci dotazů. V nové verzi (s flagem
BUFFERS) můžeme v explainu vidět velikost operací nad
dočasnými soubory. Počet operací nad dočasnými soubory je vidět i v
reportu pg_stat_statements (pro vynucení external sortu dávám
work_mem na minimum):
postgres=# SET work_mem TO '64kB'; SET postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT DISTINCT nazev FROM obce ORDER BY nazev; ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Unique (cost=781.01..812.26 rows=5342 width=10) (actual time=24.102..27.916 rows=5342 loops=1) │ │ Buffers: shared hit=59, temp read=26 written=32 │ │ -> Sort (cost=781.01..796.63 rows=6250 width=10) (actual time=24.099..26.300 rows=6250 loops=1) │ │ Sort Key: nazev │ │ Sort Method: external merge Disk: 112kB │ │ Buffers: shared hit=59, temp read=26 written=32 │ │ -> Seq Scan on obce (cost=0.00..121.50 rows=6250 width=10) (actual time=0.031..1.895 rows=6250 loops=1) │ │ Buffers: shared hit=59 │ │ Planning Time: 0.195 ms │ │ Execution Time: 28.517 ms │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (10 rows)
Zajímavou informací může být velikost zápisu do wal logu:
postgres=# EXPLAIN (ANALYZE, WAL, BUFFERS) INSERT INTO foo VALUES(10),(20); ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Insert on foo (cost=0.00..0.03 rows=0 width=0) (actual time=0.085..0.087 rows=0 loops=1) │ │ Buffers: shared hit=2 │ │ WAL: records=2 bytes=118 │ │ -> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=4) (actual time=0.011..0.020 rows=2 loops=1) │ │ Planning Time: 0.081 ms │ │ Execution Time: 0.133 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (6 rows)
V pg_stat_statements také nově najdeme souhrné statistiky JIT výrazů (Just In Time kompilace výrazů). JIT u komplexnějších déletrvajících dotazů může výrazně pomoct, ale naopak u složitějších krátkých dotazů má nepříjemně velkou režii. Je zcela patrné, že aktivace JIT poze na základě ceny dotazu nestačí. Uvažuje se i kalkulaci ceny JITu na základě komplexity dotazu. Než se problém s neadekvátním použitím JITu vyřeší, můžete nyní pomocí pg_stat_statements snadno identifikovat dotazy, kde je doba JIT kompilace neadekvátní celkové době exekuce.
Možnost povolit přístup ke konfiguračním proměnným původně zpřístupněných pouze super uživateli
Konfigurace Postgresu je postavená na používání tzv konfiguračních proměnných. Často se setkáte se zkratkou GUC (Grand Unified Configuration). Tyto proměnné se typicky dělí podle možnosti nastavení: nastavení pouze v konfigu, nastavení superuserem, nastavení běžným uživatelem. I některé nekritické parametry vyžadují super usera. Od nové verze je možné povolit přístup slabším rolím k těmto parametrům.
postgres=# CREATE ROLE tom LOGIN; CREATE ROLE postgres=# GRANT SET ON PARAMETER autovacuum_max_workers TO tom ; GRANT
Pozor. Efektivně tento mechanizmus funguje pouze na proměnných, kde pro
modifikaci je vyžadovaný superuser. Na ostatních konfiguračních
proměnných lze práva nastavovat, ale nemá to žádný reálný efekt.
Například by někdo mohl chtít znepřístupnit změnu work_mem
běžným uživatelům. To nejde. V commit message je to zdůvodněno nemožností
zajištění omezeného přístupu k GUC definovanými extenzemi. Diskuzi k
tomuto patchi jsem nesledoval, takže netuším nakolik je toto omezení
finální.
Informace o využití sdílené paměti
V Postgresu se masivně používá sdílená pamět. Drtivá většina této paměti se používá jako sdílená cache datových stránek. Ve sdílené paměti je řada dalších cache, a nyní i provozní statistiky. Většinou chcete znát velikost sdílené paměti, protože ji chcete dostat do huge pages (pozor - na některých virtualizačních platformách a při určité zátěži to možná nechcete). V PostgreSQL 15 se jednoduše dostaneme k velikosti sdílené paměti i k počtu nezbytných huge pages:
postgres=# SHOW shared_memory_size; ┌────────────────────┐ │ shared_memory_size │ ╞════════════════════╡ │ 145MB │ └────────────────────┘ (1 row) postgres=# SHOW shared_memory_size_in_huge_pages; ┌──────────────────────────────────┐ │ shared_memory_size_in_huge_pages │ ╞══════════════════════════════════╡ │ 73 │ └──────────────────────────────────┘ (1 row)
Logování ve formátu JSON
Co si vzpomínám, tak parsování logů Postgresu nebylo úplně triviální, a
to i když se použil formát CSV. Dneska je formát JSON podporován všude, a
od verze 15 Postgres umí logovat v tomto formátu. V konfiguráku je
potřeba zapnout log_destination = 'jsonlog',
logging_collector = on, a restartovat Postgres. V dedikovaném
souboru s příponou .json je pak log ve stylu:
{"timestamp":"2022-04-25 10:07:31.784 CEST","user":"pavel","dbname":"postgres","pid":199522,"remote_host":"[local]","session_id":"626656c1.30b62","line_num":1,"ps":"SELECT","session_start":"2022-04-25 10:07:29 CEST","vxid":"3/3","txid":0,"error_severity":"ERROR","state_code":"22012","message":"division by zero","statement":"select 0/0;","application_name":"psql","backend_type":"clientbackend","query_id":-8981983488509566257}
Nastavení komprimace
Výchozí komprimační metodou v PostgreSQL je PGLZ. Pokud si vybavuji, tak komprimace se v Postgresu aplikuje při zápisu větších než 2KB hodnot, při zápisu do transakčního logu a u backupů. V předchozí verzi přibyla možnost nastavit komprimaci TOAST hodnot metodou LZ4. V syntetických benchmarkách (realita může být jiná) komprimuje LZ4 cca o 10% hůře než PGLZ, ale 2x rychleji. V patnáctce můžeme LZ4 použít i pro komprimaci transakčních logů.
Komprimovat data umí také pg_basebackup s hodnotou
gzip volby --compression. Nově můžeme použít
hodnotu server-gzip (nebo server-lz4, která
vynutí komprimaci dat serveru už na straně serveru. Zvlášť na pomalých
sítích nebo na VPNkách komprimace na straně serveru razantně zrychluje klonování.
Replikace
Skrz logickou replikaci se nyní protlačí dvou fázový commit. To umožňuje použít logickou replikaci i pro datově kritické aplikace. Co jsem četl na blogu autora, tak se spíš jedná o první krok k budoucí možnosti budovat nad Postgresem a logickou replikací distribuované databáze, než o reálně použitelnou kompletní funkci. I když na druhou stranu 2PC opravdu výrazně navyšuje garanci konzistence dat v distribuované databázi.
Zjednodušuje se zveřejnění všech tabulek ve schématu klauzulí FOR
ALL TABLES IN SCHEMA příkazu CREATE PUBLICATION. Pokud
se použije tato klauzule, tak nově přidaná tabulka se automaticky přidá
do seznamu zveřejněných tabulek. Pro refresh odběru je nutné zavolat příkaz
ALTER SUBSCRIPTION REFRESH PUBLICATION.
Novinkou v PostgreSQL 15 je možnost publikovat (replikovat) pouze vybrané
sloupce tabulky. Představuji si, že užitek to může mít jak bezpečnostní,
tak výkonnostní. Do účetnictví nepotřebuji posílat oskenovanou fakturu
uloženou jako hodnotu bytea typu.
Nově Postgres obsahuje pohled pg_stat_subscription_workers se
statiskami odběrů (statistiku uvidíme na straně konzumenta logické
replikace).
Optimalizace
Optimalizace filtrování monotonních window funkcí
Planner nyní dokáže rozpoznat monotonní window funkce jako je row_number
nebo rank, a vygenrovat plán s uzlem Run Condition:, který
dokáže efektivněji filtrovat (filtr se posune v prováděcím plánu níže)
řádky s výsledkem použité window funkce. Největší přínos této
optimalizace je v případech, kdy není použita klauzule PARTITION BY.
postgres=# EXPLAIN ANALYZE SELECT * FROM (SELECT row_number() OVER (), * FROM obce) s WHERE row_number <= 3; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞══════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ WindowAgg (cost=0.00..199.62 rows=6250 width=49) (actual time=0.050..0.060 rows=3 loops=1) │ │ Run Condition: (row_number() OVER (?) <= 3) │ │ -> Seq Scan on obce (cost=0.00..121.50 rows=6250 width=41) (actual time=0.028..0.030 rows=4 loops=1) │ │ Planning Time: 0.309 ms │ │ Execution Time: 0.138 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (5 rows) -- PostgreSQL 14: postgres=# EXPLAIN ANALYZE SELECT * FROM (SELECT row_number() OVER (), * FROM obce) s WHERE row_number <= 3; ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Subquery Scan on s (cost=0.00..277.75 rows=2083 width=49) (actual time=0.081..11.640 rows=3 loops=1) │ │ Filter: (s.row_number <= 3) │ │ Rows Removed by Filter: 6247 │ │ -> WindowAgg (cost=0.00..199.62 rows=6250 width=49) (actual time=0.077..10.270 rows=6250 loops=1) │ │ -> Seq Scan on obce (cost=0.00..121.50 rows=6250 width=41) (actual time=0.058..1.996 rows=6250 loops=1) │ │ Planning Time: 0.998 ms │ │ Execution Time: 11.725 ms │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (7 rows)
I u komplexnějších použití window funkcí (s klauzulí
PARTITION BY) tato optimalizace může pomoci (s vytvořeným
vícesloupcovým funkčnm indexem create index on obce(okres_id,
(pocet_muzu + pocet_zen));):
postgres=# EXPLAIN ANALYZE SELECT *
FROM (SELECT row_number() OVER (PARTITION BY okres_id
ORDER BY pocet_muzu + pocet_zen), *
FROM obce) s
WHERE row_number <= 3;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Subquery Scan on s (cost=0.28..540.67 rows=6250 width=49) (actual time=0.100..14.362 rows=227 loops=1) │
│ -> WindowAgg (cost=0.28..478.17 rows=6250 width=53) (actual time=0.098..14.228 rows=227 loops=1) │
│ Run Condition: (row_number() OVER (?) <= 3) │
│ -> Index Scan using obce_okres_id_expr_idx on obce (cost=0.28..353.17 rows=6250 width=45) (actual time=0.061..10.190 rows=6250 loops=1) │
│ Planning Time: 0.357 ms │
│ Execution Time: 14.554 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)
-- PostgreSQL 14:
postgres=# EXPLAIN ANALYZE SELECT *
FROM (SELECT row_number() OVER (PARTITION BY okres_id
ORDER BY pocet_muzu + pocet_zen), *
FROM obce) s
WHERE row_number <= 3;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Subquery Scan on s (cost=0.28..556.29 rows=2083 width=49) (actual time=0.083..26.563 rows=227 loops=1) │
│ Filter: (s.row_number <= 3) │
│ Rows Removed by Filter: 6023 │
│ -> WindowAgg (cost=0.28..478.17 rows=6250 width=53) (actual time=0.079..25.158 rows=6250 loops=1) │
│ -> Index Scan using obce_okres_id_expr_idx on obce (cost=0.28..353.17 rows=6250 width=45) (actual time=0.051..9.041 rows=6250 loops=1) │
│ Planning Time: 0.315 ms │
│ Execution Time: 26.698 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)
Další optimalizace
Podporu optimalizátoru dostala i funkce starts_with (všimněte
si mnohem lepšího odhadu v prováděcím plánu):
postgres=# EXPLAIN ANALYZE SELECT * FROM obce WHERE starts_with(nazev, 'Be'); ┌───────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞═══════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Seq Scan on obce (cost=0.00..137.12 rows=67 width=41) (actual time=0.056..6.676 rows=48 loops=1) │ │ Filter: starts_with((nazev)::text, 'Be'::text) │ │ Rows Removed by Filter: 6202 │ │ Planning Time: 0.405 ms │ │ Execution Time: 6.774 ms │ └───────────────────────────────────────────────────────────────────────────────────────────────────┘ (5 rows) -- postgresql 14 postgres=# EXPLAIN ANALYZE SELECT * FROM obce WHERE starts_with(nazev, 'Be'); ┌───────────────────────────────────────────────────────────────────────────────────────────────────── │ QUERY PLAN ╞═════════════════════════════════════════════════════════════════════════════════════════════════════ │ Seq Scan on obce (cost=0.00..137.12 rows=2083 width=41) (actual time=0.030..5.704 rows=48 loops=1) │ Filter: starts_with((nazev)::text, 'Be'::text) │ Rows Removed by Filter: 6202 │ Planning Time: 0.144 ms │ Execution Time: 5.756 ms └───────────────────────────────────────────────────────────────────────────────────────────────────── (5 rows)
Od předchozí verze se dohledání hodnoty v delším seznamu hodnot (val IN
(var1, var2, ...)) efektivněji nahrazuje hledáním v hashovací tabulce. Ve
verzi 15 se používá stejná optimalizace i na operaci NOT IN
(seznam).
V implementaci operace sort došlo v PostgreSQL 15 k
několika mikrooptimalizacím. Na menších datech zrychlení bude spíš jenom
v procentech. Na větších datech by to mohlo být zajímavější. Zvětšením
bufferu používaného pro komunikaci mezi paralelně bežícími procesy
zpracování jednoho dotazu by mělo dojít k zajímavému zrychlení výpočtu
paralelních dotazů. Nezkoušel jsem, ale nemám důvod nevěřit autorům.
Jednou z důležitých interních struktu je tzv tuplestore. Do
velikosti work_mem si drží data v paměti. Pokud je potřeba
uložit víc dat než je nastavení work_mem, tak data ukládá do
dočasného souboru. Od nové verze se používá jiný mechanismus alokace
paměti, díky kterému stejná data zaberou méně paměti. Asi největšího
efektu se dosáhne u 4 bajtových integerů - 6000 hodnot v 14ce vyžaduje
915kB, v 15ce pouze 806kB. Tím se mírně zvyšuje šance, že data zůstanou
čistě v RAMce, a že při sortu se použije rychlejší quick sort namísto
external sortu. V reálu ten efekt nemusí být tak velký, ale poskytuje to
vývojářům určitou rezervu. Jiné nové optimalizace mohou mít jinak
nastavený worst case, a díky různým byť malým mikrooptimalizacím je nová
verze ve všech ohledech rychlejší nebo alespoň stejně rychlá.
Odhady rekurzivních dotazů pracují s magickou konstantou 10.
Nově je možné změnit tuto konstantu v konfigurační proměnné
recursive_worktable_factor.
Ostatní
Uložené procedury lze v Postgresu psát v několika programovacích jazycích. Nejčastější je PL/pgSQL, který vychází z Oraclovského PL/SQL. Výrazně méně se používá PL/Perl a PL/Python. I když se používají méně, mají své využití. V nové verzi došlo k odstranění podpory dvojkového Pythonu. Tudíž procedury napsané v Pythonu 2 je nutné přemigrovat do Pythonu 3.
V provozních statistikách si Postgres udržuje informace o počtu operací nad tabulkami, indexy, atd. Doposud se tato data držela v paměti dedikovaného procesu a v dočasných souborech. U databází s větším počtem databázových objektů tyto dočasné soubory mohly dosáhnout megabajtů a mohly se až 2x za sekundu přepisovat (v GD jsme s tím měli docela velký problém, a adresář se statistikami jsme linkovali na efemeral disky). Komunikace s procesem dedikovaným pro uložení statistik probíhala prostřednictvím UDP. Od verze 15 jsou provozní statistiky uložené ve sdílené paměti.
Nově také příkaz CREATE DATABASE nevyžaduje checkpointy
(starší verze provedly na jednu novou databázi dva checkpointy).
Checkpointy mohou být nepříjemně náročné na databázích s větším provozem
a větší share_buffers. Pokud je ale zdrojová databáze
(default template1) větší, pak nová metoda může být naopak
výrazně pomalejší. Ke staršímu způsobu se lze vrátit použitím klauzule
STRATEGY
Od verze 14 je v Postgresu funkce
pg_log_backend_memory_contexts, která do logu Postgresu
vypíše seznam a velikost používaných paměťových kontextů. Tato informace
může být zajímavá pro diagnostiku problémů s pamětí, případně pro
diagnostiku memory leaků. Dříve tuto funkci mohl volat pouze uživatel s
právy superusera. Od nové verze může super user nagrantovat exekuci této
funkce i uživatelům bez superusera.
Nově také Postgres umí při zpracování dotazu na cizím serveru provést
vzdáleně výrazy obsahující CASE. Optimalizátor by měl také
častěji používat asynchronní čtení dat z cizího serveru.
Pro zobrazení obsahu transakčních logů máme v Postgresu nástroj
pg_waldump. Nově tento nástroj doplňuje extenze
pg_walinspect, která umožňuje prohlížet obsah transakčního
logu z SQL.
postgres=# select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_type, record_length, main_data_length, fpi_length, description from pg_get_wal_records_info('0/14F9A30', '0/15011D7');
start_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | main_data_length | fpi_length | description
-----------+-----------+-----------+-----+------------------+--------------+---------------+------------------+------------+---------------------
0/14FA118 | 0/14FB4B0 | 0/14F9958 | 725 | Btree | INSERT_LEAF | 5013 | 2 | 4960 | off 246
0/14FB4B0 | 0/14FD050 | 0/14FA118 | 725 | Btree | INSERT_LEAF | 7045 | 2 | 6992 | off 130
0/14FD050 | 0/14FD0A8 | 0/14FB4B0 | 725 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
0/14FD0A8 | 0/14FD0F0 | 0/14FD050 | 725 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 155
0/14FD0F0 | 0/14FD138 | 0/14FD0A8 | 725 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 134
0/14FD138 | 0/14FD210 | 0/14FD0F0 | 725 | Heap | INSERT | 211 | 3 | 0 | off 11 flags 0x00
0/14FD210 | 0/14FD250 | 0/14FD138 | 725 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 246
Hodně šikovné neatomické typy v Postgresu jsou typ range a
multirange (od PostgreSQL 14). Nově je možné použít funkci
range_agg i pro typ multirange (to je spíš
dodělávka implementace multirange z minulé verze):
postgres=# SELECT range_agg(nmr) FROM (values ('{[1,2]}'::nummultirange), ('{[5,6]}'::nummultirange)) t(nmr);
┌───────────────┐
│ range_agg │
╞═══════════════╡
│ {[1,2],[5,6]} │
└───────────────┘
(1 row)
postgres=# SELECT range_agg(nmr) FROM (values ('{[1,2]}'::nummultirange), ('{[2,3]}'::nummultirange)) t(nmr);
┌───────────┐
│ range_agg │
╞═══════════╡
│ {[1,3]} │
└───────────┘
(1 row)
Kontinuální backupy se v Postgresu implementují skrze shellový příkaz
zadaný v konfigurační proměnné archive_command. Na zatížených
serverech už může mít volání shellu nezanedbatelnou režii a způsobovat
další latence. Od PostgreSQL 15 je možné nahradit
archive_command kódem v extenzi (s callbackem). Extenze se
aktivuje uvedením jejího názvu v konfigurační proměnné
archive_library.
Po mnoha letech se "dokončila" implementace unlogged tabulek, a to podporou unlogged sekvencí.
Co se nestihlo
Rozdělané práce je pořád dost. Do PostgreSQL 15 se nedostala replikace
sekvencí. Plánuje se přepis implementace kontroly referenční integrity.
Letos se trochu posunul vývoj implementace session proměnných, ale je na
tom ještě dost práce. Uvažuje se o velké změně v implementaci jsonu.
Stávající typ jsonb by se měl přejmenovat na json
(aby to odpovídalo ANSI/SQL), a stávající typjson by měl být
implementován jen jako varianta jsonb (s jinou serializací).
Tomáš Vondra pracuje na mezi tabulkových statistikách, pracuje se na
integraci šifrování. Na tak velkém projektu jako je PostgreSQL a
implementace ANSI/SQL je práce pořád dost.
Kvůli rizikům spojeným s covidem se přesunula konference P2D2 z únorového termínu na červen. Díky tomu Vás mohu pozvat na letos 14 ročník této konference. Koná se pod patronací FSV v budově Fakulty architektury v Dejvicích 2. 6. 2022. Setkat se můžete s našimi i zahraničními vývojáři PostgreSQL (z hierarchie vývojářů dvou top vývojářů s právem commitu do repozitáře Postgresu). Detaily naleznete na webové stránce https://p2d2.cz/rocnik-2022.
- 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 16 (2023)
- PostgreSQL 17 (2024)