PostgreSQL 13 (2020)
Autor: Pavel Stěhule, 2020
PostgreSQL 13
Při vývoji databáze jdou proti sobě dvě síly. Tou první je snaha o co "nejchytřejší" optimalizátor, a co nejrobustnější, nejspolehlivější provoz podepřený co nejpodrobnějším monitoringem. Chytřejší optimalizátor si lépe poradí s komplikovanými dotazy. Bohužel chytřejší optimalizátor je pomalejší a náročnější na zdroje, a tudíž zpomalí vykonávání jednoduchých dotazů, kde se jeho možnosti neuplatní. Podrobnější monitoring je také náročnější na zdroje(IO, CPU).
Aby výkon databáze s každou novou verzí výrazně neklesal (nebo jen neklesal) neustále se reviduje kód a neustále se hledají optimalizace na všech úrovních. A to je ta druhá síla. Není až tak úplně komplikované vymyslet novou optimalizaci dotazu, ale je docela komplikované ji implementovat ve stávajícím systému, tak aby se drtivá většina dotazů nezpomalila (pro velké množství aplikací je 90% kvantil dotazů pod 10ms).
Mně osobně přijde, že letošní verze není není charakteristická nějakou výraznou novou funkcionalitou. Ale nejde si nevšimnout desítek poměrně důležitých interních optimalizací (na některých se pracovalo několik let - rozhodně se tedy nejedná pouze o mikro optimalizace).
Optimalizace
Deduplikace klíčů v indexu
Zvláště u indexů cizích klíčů se vyskytuje velké množství duplicitních hodnot. Postgres 13 podporuje
deduplikaci klíčů uložených v indexu. K deduplikaci (k redukci
duplicitních klíčů) dochází ve chvíli dělení koncové stránky indexu (neprovádí se okamžitě). Díky tomu
by měla mít mít minimální režii. Na mých testovacích datech (obce ČR) je velikost indexu nad cizím
klíčem (obce.okres_id) méně než poloviční 64kB (ve verzi 13) versus 160kB (ve verzi 12).
Incremental sort
Pokud jsem chtěl ve starších verzích urychlit více sloupcové řazení indexem, musel jsem mít odpovídající
více sloupcový index. A pokud jsem měl více kombinací, tak jsem musel mít víc více-sloupcových indexů.
Více indexů znamená pomalejší INSERT, UPDATE, pomalejší VACUUM.
Nová verze umí načíst data v určitém pořadí z indexu, a pak je do-seřadit.
Jelikož nemám velká data, tak si musím snížit work_mem (a tím si prodražít sort):
SET work_mem TO '64kB';
-- Postgres 12
postgres=> EXPLAIN SELECT *
FROM obce
ORDER BY okres_id, pocet_muzu + pocet_zen DESC;
┌────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════════════════╡
│ Sort (cost=916.18..931.80 rows=6250 width=45) │
│ Sort Key: okres_id, ((pocet_muzu + pocet_zen)) DESC │
│ -> Seq Scan on obce (cost=0.00..137.12 rows=6250 width=45) │
└────────────────────────────────────────────────────────────────┘
(3 rows)
postgres=> EXPLAIN SELECT *
FROM obce
ORDER BY okres_id, nazev;
┌────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════════════════╡
│ Sort (cost=900.55..916.18 rows=6250 width=41) │
│ Sort Key: okres_id, nazev │
│ -> Seq Scan on obce (cost=0.00..121.50 rows=6250 width=41) │
└────────────────────────────────────────────────────────────────┘
(3 rows)
-- Postgres 13
postgres=> EXPLAIN SELECT *
FROM obce
ORDER BY okres_id, pocet_muzu + pocet_zen DESC;
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════╡
│ Incremental Sort (cost=7.17..618.34 rows=6250 width=45) │
│ Sort Key: okres_id, ((pocet_muzu + pocet_zen)) DESC │
│ Presorted Key: okres_id │
│ -> Index Scan using obce_okres_id_idx on obce (cost=0.28..206.12 rows=6250 width=45) │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(4 rows)
postgres=> EXPLAIN SELECT *
FROM obce
ORDER BY okres_id, nazev;
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════╡
│ Incremental Sort (cost=6.97..602.71 rows=6250 width=41) │
│ Sort Key: okres_id, nazev │
│ Presorted Key: okres_id │
│ -> Index Scan using obce_okres_id_idx on obce (cost=0.28..190.49 rows=6250 width=41) │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(4 rows)
Tím, že se neřadí celá tabulka naráz, ale blokově, tak se zvyšuje šance, že se řazený blok vejde do paměti, a bude možné použít quick sort místo pomalejšího, náročnějšího external sortu.
HashAgg respektuje nastavení work_mem
Konfigurační proměnná work_mem se používá při plánování dotazu jako limit pro rozhodování mezi
paměťově náročnějšími nebo naopak úspornějšími algoritmy. Používá se také při vykonávání dotazů jako
limit paměťových bufferů. Po naplnění bufferů se začne zapisovat do dočasných souborů. Tak by se měly
chovat veškeré operace, které startuje executor dotazů. Výjimkou byla hash agregace (agregace, kdy
mezivýsledky agregace jsou uložené v hashovací tabulce). Popsané chování nebylo ve starších verzích
implementováno. Několikrát jsem se dostal do situace, kdy jsem musel penalizovat hash_agg, protože
jinak docházelo ke swapování nebo k zabití Postgresu OOM kilerem. Jednalo se často o SELECT DISTINCT
nad extrémně velkými tabulkami, kde muselo dojít ke špatnému ndistinct odhadu. V nové verzi již hash_agg
není výjimkou, a agregace by měla být bezpečná (a v některých případech bohužel pomalejší, jako
celek ale mnohem stabilnější):
Např
postgres=> SET work_mem to '200MB';
SET
postgres=> EXPLAIN ANALYZE SELECT DISTINCT 'AHOJ' || i FROM generate_series(1,1000000) g(i);
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ HashAggregate (cost=20000.00..20003.50 rows=200 width=32) (actual time=686.754..884.658 rows=1000000 loops=1) │
│ Group Key: ('AHOJ'::text || (i)::text) │
│ Peak Memory Usage: 98321 kB │
│ -> Function Scan on generate_series g (cost=0.00..17500.00 rows=1000000 width=32) (actual time=100.446..304.289 rows=1000000 loops=1) │
│ Planning Time: 0.178 ms │
│ Execution Time: 935.348 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)
Zde je vidět, že tento dotaz spotřeboval cca 100MB paměti. Zrovna tak by se choval ve starších verzích
Postgresu a to bez ohledu na nastavení work_mem.
postgres=> SET work_mem to '4MB';
SET
postgres=> EXPLAIN ANALYZE SELECT DISTINCT 'AHOJ' || i FROM generate_series(1,1000000) g(i);
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ HashAggregate (cost=20000.00..20003.50 rows=200 width=32) (actual time=594.709..1158.370 rows=1000000 loops=1) │
│ Group Key: ('AHOJ'::text || (i)::text) │
│ Peak Memory Usage: 5465 kB │
│ Disk Usage: 28848 kB │
│ HashAgg Batches: 116 │
│ -> Function Scan on generate_series g (cost=0.00..17500.00 rows=1000000 width=32) (actual time=110.250..334.606 rows=1000000 loops=1) │
│ Planning Time: 0.176 ms │
│ Execution Time: 1201.208 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)
Pokud omezím work_mem, tak stejně dojde k přetečení, nicméně skutečná hodnota se bezproblémově vejde
do dvojnásobku s kterým se tak nějak počítá. Na druhou stranu agregace je o 265ms pomalejší (cca 25%). Určitě může
dojít k situacím, kdy agregace (na verzi 13) bude o 25% pomalejší, ale nemělo by dojít k situaci, kdy
Vám agregace odrovná server (protože vyžere veškerou paměť). Pokud byl odhad počtu unikátních hodnot v pořádku, tak i starší verze
nepoužívaly hash_agg:
postgres=> SET work_mem to '64kB'; -- 12
SET
postgres=> EXPLAIN ANALYZE SELECT DISTINCT nazev
FROM foo;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Unique (cost=16385.34..16916.59 rows=5344 width=10) (actual time=291.662..326.179 rows=5342 loops=1) │
│ -> Sort (cost=16385.34..16650.96 rows=106250 width=10) (actual time=291.660..315.851 rows=106250 loops=1) │
│ Sort Key: nazev │
│ Sort Method: external merge Disk: 2192kB │
│ -> Seq Scan on foo (cost=0.00..2065.50 rows=106250 width=10) (actual time=0.021..13.926 rows=106250 loops=1) │
│ Planning Time: 0.099 ms │
│ Execution Time: 326.833 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)
-- zde se ukazuje Postgres 13 jako mnohem rychlejší
postgres=> SET work_mem to '64kB';
SET
postgres=> EXPLAIN ANALYZE SELECT DISTINCT nazev
FROM foo;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ HashAggregate (cost=6481.52..7572.52 rows=5341 width=10) (actual time=56.153..93.098 rows=5342 loops=1) │
│ Group Key: nazev │
│ Planned Partitions: 4 │
│ Peak Memory Usage: 93 kB │
│ Disk Usage: 6264 kB │
│ HashAgg Batches: 20 │
│ -> Seq Scan on foo (cost=0.00..2065.50 rows=106250 width=10) (actual time=0.012..11.785 rows=106250 loops=1) │
│ Planning Time: 0.982 ms │
│ Execution Time: 94.789 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)
Sort je výrazně pomalejší už kvůli použití implicitního collate , což je v mém případě "cs_CZ". V tomto ohledu
je implementace v Postgresu 13 ohromný skok kupředu (a jak to vypadá finální vyřešení roky trvajícího problému,
který nebyl příliš častý, ale pokud se vyskytl, tak byl velmi nepříjemný).
Vícesloupcové statistiky
Připravil jsem si pomocnou tabulku obce2 se sloupcem popisující velikost obce v textové podobě, a
sloupcem, který určuje jestli je obec okresní město nebo nikoliv.
postgres=> SELECT *
FROM obce2
LIMIT 10;
┌────┬──────────┬─────────────┬────────────┬───────────┬─────────────┬────────────────────┐
│ id │ okres_id │ nazev │ pocet_muzu │ pocet_zen │ velikost │ je_okresnim_mestem │
╞════╪══════════╪═════════════╪════════════╪═══════════╪═════════════╪════════════════════╡
│ 1 │ CZ0100 │ Praha │ 608316 │ 640710 │ extra velka │ t │
│ 2 │ CZ0201 │ Benešov │ 7875 │ 8507 │ stredni │ t │
│ 3 │ CZ0201 │ Bernartice │ 108 │ 115 │ mala │ f │
│ 4 │ CZ0201 │ Bílkovice │ 93 │ 89 │ mala │ f │
│ 5 │ CZ0201 │ Blažejovice │ 52 │ 48 │ mala │ f │
│ 6 │ CZ0201 │ Borovnice │ 39 │ 37 │ mala │ f │
│ 7 │ CZ0201 │ Bukovany │ 364 │ 372 │ mala │ f │
│ 8 │ CZ0201 │ Bystřice │ 2124 │ 2096 │ stredni │ f │
│ 9 │ CZ0201 │ Ctiboř │ 55 │ 50 │ mala │ f │
│ 10 │ CZ0201 │ Čakov │ 65 │ 60 │ mala │ f │
└────┴──────────┴─────────────┴────────────┴───────────┴─────────────┴────────────────────┘
(10 rows)
Zkusím napsat dotaz, ve kterém by se měl projevit efekt vícesloupcových statistik :
postgres=> EXPLAIN ANALYZE SELECT *
FROM obce2
WHERE velikost = 'stredni' AND je_okresnim_mestem;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2 (cost=0.00..133.12 rows=16 width=35) (actual time=0.032..2.371 rows=40 loops=1) │
│ Filter: (je_okresnim_mestem AND ((velikost)::text = 'stredni'::text)) │
│ Rows Removed by Filter: 6210 │
│ Planning Time: 0.140 ms │
│ Execution Time: 2.412 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
CREATE STATISTICS obce_velikost_je_okresnim_mestem(mcv) ON velikost, je_okresnim_mestem FROM obce2;
ANALYZE obce2;
postgres=> EXPLAIN ANALYZE SELECT *
FROM obce2
WHERE velikost ='stredni' AND je_okresnim_mestem;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2 (cost=0.00..133.12 rows=40 width=35) (actual time=0.027..2.440 rows=40 loops=1) │
│ Filter: (je_okresnim_mestem AND ((velikost)::text = 'stredni'::text)) │
│ Rows Removed by Filter: 6210 │
│ Planning Time: 0.481 ms │
│ Execution Time: 2.480 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
V nové verzi se více sloupcové statistiky uplatní i pro filtr seznamem hodnot:
postgres=> EXPLAIN ANALYZE SELECT *
FROM obce2
WHERE velikost IN ('stredni', 'velka') AND je_okresnim_mestem;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2 (cost=0.00..133.12 rows=67 width=35) (actual time=0.025..2.225 rows=67 loops=1) │
│ Filter: (je_okresnim_mestem AND ((velikost)::text = ANY ('{stredni,velka}'::text[]))) │
│ Rows Removed by Filter: 6183 │
│ Planning Time: 0.495 ms │
│ Execution Time: 2.266 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
nebo filtr podle existence prvku v poli:
postgres=> EXPLAIN ANALYZE SELECT *
FROM obce2
WHERE velikost = ANY (ARRAY['stredni','velka']) and je_okresnim_mestem;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2 (cost=0.00..133.12 rows=67 width=35) (actual time=0.028..2.241 rows=67 loops=1) │
│ Filter: (je_okresnim_mestem AND ((velikost)::text = ANY ('{stredni,velka}'::text[]))) │
│ Rows Removed by Filter: 6183 │
│ Planning Time: 0.178 ms │
│ Execution Time: 2.282 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
V nové verzi je také možné explicitně nastavit přesnost vícesloupcových statistik příkazem:
ALTER STATISTICS stat_name SET STATISTICS target_value;
Optimalizace evaluace výrazů v PLpgSQL
Letos se podařila docela výrazná optimalizace v interpretu PLpgSQL . Tento jazyk (prostředí) slouží (měl by sloužit) primárně jako lepidlo pro SQL dotazy, a sám o sobě téměř nikdy nepředstavuje úzké hrdlo. Nedoporučuje se provádět výpočetně náročné úlohy v tomto jazyku. Na druhou stranu toto prostředí není až tak pomalé, a po letošní optimalizaci už se nějaký ten výpočet dá zvládnout i v PLpgSQL. Pro ukázku jsem zvolil implementaci odhadu hodnoty π (což je zrovna to, co bych v PLpgSQL za normálních okolností nikdy nedělal):
CREATE OR REPLACE FUNCTION pi_est_1(n int)
RETURNS numeric AS $$
DECLARE
accum double precision DEFAULT 1.0;
c1 double precision DEFAULT 2.0;
c2 double precision DEFAULT 1.0;
BEGIN
FOR i IN 1..n
LOOP
accum := accum * ((c1 * c1) / (c2 * (c2 + 2.0)));
c1 := c1 + 2.0;
c2 := c2 + 2.0;
END LOOP;
RETURN accum * 2.0;
END;
$$ LANGUAGE plpgsql;
postgres=> select pi_est_1(1000000);
┌──────────────────┐
│ pi_est_1 │
╞══════════════════╡
│ 3.14159186819215 │
└──────────────────┘
(1 row)
Time: 332,436 ms
Milion iterací trvalo cca 0.3 sec na mé 9 let staré T520 (podobný kód v Lue trval 110ms a 290ms v Pythonu (v Pythonu s vyšší přesností)). V předchozí verzi ten samý kód běžel 835ms.
V běžném kódu takové zrychlení určitě nebude - úzkým hrdlem budou vždy operace s daty v databázi, načíst a připravit data z databáze je samozřejmě výrazně náročnější než spočítat pár výrazů.
psql
Ve verzi 13 jsou v psql pouze kosmetické změny. Nově je to příkaz \warn, kterým se zapíše řetězec
do stderr. Nově máme možnost parametrizovat zobrazení u variant příkazu \g. Použitá syntax pro zápis
seznamu parametrů formátu je docela specifická (oddělovačem je mezera) a náhodou koreluje se zápisem connection
stringu.
postgres=> SELECT 10, 'ahoj', 20; ┌──────────┬──────────┬──────────┐ │ ?column? │ ?column? │ ?column? │ ╞══════════╪══════════╪══════════╡ │ 10 │ ahoj │ 20 │ └──────────┴──────────┴──────────┘ (1 row) postgres=> SELECT 10, 'ahoj', 20 \g (format=csv tuples_only=on) 10,ahoj,20
K čemu se to hodí? Můžeme si snáz vytvářet psql makra, jako v následujícím příkladu. Makro, které
výsledek dotazu zobrazí v gplot(u):
postgres=> \set gnuplot '\\g (format=csv) |gnuplot -p -e "set datafile separator \',\'; set key autotitle columnhead; set terminal dumb enhanced;" -e'
postgres=> SELECT i, sin(i) FROM generate_series(0, 6.3, 0.05) g(i) :gnuplot "plot '-'with lines ls 1"
1 +--------------------------------------------------------------------+
| *** *** + + + + |
0.8 |-+ *** ** sin *******-|
| ** ** |
0.6 |-+ ** ** +-|
| * * |
0.4 |-+ * * +-|
| * * |
0.2 |-* * +-|
|* * |
0 |-+ * * +-|
| * * |
-0.2 |-+ * * +-|
| * * |
-0.4 |-+ * * +-|
| * * |
-0.6 |-+ * ** +-|
| ** ** |
-0.8 |-+ *** ** +-|
| + + + + *** +*** + |
-1 +--------------------------------------------------------------------+
0 1 2 3 4 5 6 7
Bohužel Gnome Terminal neumí jemnou grafiku (ReGIS nebo SIXEL ), takže žádná velká paráda se zde dělat nedá (třeba jednou v budoucnu se možnosti Gnome terminálu zlepší - terminál na mac Os by jemnou grafiku měl zvládat).
Zajímavou novinkou, která je vidět ve výchozí konfiguraci je zobrazení stavu transakce v promtu.
postgres=> \conninfo -- autocommit on
You are connected to database "postgres" as user "pavel" via socket in "/tmp" at port "5432".
postgres=> BEGIN;
BEGIN
postgres=*> SELECT 1; -- otevřená transakce - symbol *
┌──────────┐
│ ?column? │
╞══════════╡
│ 1 │
└──────────┘
(1 row)
postgres=*> SELECT x;
ERROR: column "x" does not exist
LINE 1: SELECT x;
^
postgres=!> ROLLBACK; -- poškozená transakce - symbol !
ROLLBACK
postgres=>
A když už popisuji nastavení promptu, počínaje touto verzí lze nastavit neviditelnou položku o stejné
šířce jako jako je PROMPT1 do PROMPT2 (vztahuje se na druhý řádek a následující řádky). Pokud ve výchozí
konfiguraci napíši dotaz přes více řádků, pak se bude aplikovat PROMPT2 a v psql uvidím následující
řádky:
postgres=> SELECT * postgres-> FROM obce;
PROMPT2 docela překáží při kopírování (z okna terminálu). Pokud bych si zase PROMPT2 nastavil na
prázdný řetězec, tak budu mít rozhozené formátování. V nové verzi lze nastavit neviditelný PROMPT2 s
šířkou která vždy odpovídá PROMPT1:
postgres=> \set PROMPT2 %w
postgres=> SELECT *
FROM obce;
Administrace
DROP DATABASE FORCE
I tak banální operace jako je dropnutí (odstranění) databáze může být někdy problém. Postgres neumožní
odstranění databáze, pokud je k ní někdo připojený. U malé aplikace nebude problém dohledat a zavřít
příslušné spojení. V systémech, kde do databází přistupují desítky různých aplikací navíc ještě s poolovanými
spojeními, to už může být těžší. Přikaz DROP DATABASE v nové verzi umožňuje další parametrizaci. Zatím
jediným parametrem je parametr FORCE. Tento parametr zajistí uzavření všech spojení a zajistí odstranění
databáze bez čekání na zavření spojení. Pozor na to. To, že DROP DATABASE ve starší verzi vždy čekalo,
až k databázi nebude nikdo připojený (5 sec a pak skončilo chybou), určitě mnohým DBA zachránilo kůži.
Možnost modifikace systémových tabulek a jejich parametrů
Je obecně přijímaným pravidlem, že na systémové tabulky se nesahá. Jako vždy a všude existují vyjímky.
Někdy je nutné nastavit mnohem agresivnější autovacuum a to i u systémových tabulek. Ve starších verzích
se muselo restartovat do single modu. V této verzi už může potřebný zásah udělat superuser po nastavení
proměnné allow_system_table_mods. Opět pozor - nevhodnou úpravou systémové tabulky můžete zrušit celou
databázi (možná opravitelnou v single modu) a s určitou dávkou štěstí nebo smůly i celou instanci.
postgres=# ALTER TABLE pg_catalog.pg_depend SET (autovacuum_vacuum_scale_factor = 0); ERROR: permission denied: "pg_depend" is a system catalog postgres=# ALTER TABLE pg_catalog.pg_depend SET (autovacuum_vacuum_threshold = 1000); ERROR: permission denied: "pg_depend" is a system catalog postgres=# set allow_system_table_mods = on; SET postgres=# ALTER TABLE pg_catalog.pg_depend SET (autovacuum_vacuum_scale_factor = 0); ALTER TABLE postgres=# ALTER TABLE pg_catalog.pg_depend SET (autovacuum_analyze_threshold = 1000); ALTER TABLE
Nyní bude tabulka pg_depend vacuovaná vždy po modifikaci jednoho tisíce řádků.
Bezpečné extenze (trusted extensions)
Drtivou většinu extenzí mohl aktivovat pouze superuser. Zřejmě u většiny extenzí je to zbytečně
přísné omezení, jelikož instalace samotných extenzí se provádí pod adminem operačního systému,
který tím pádem za instalované extenze zodpovídá. V nové verzi se proto zavádí nový pojem - bezpečná
(trusted) extenze, kterou může instalovat i běžný uživatel, který má právo CREATE pro danou databázi.
SELECT * FROM pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment) WHERE trusted;
Aktuálně se za bezpečné považují extenze: btree_gin, btree_gist, citext, cube, dict_int, earthdistance, fuzzystrmatch, hstore, intarray, isn, lo, ltree, pg_trgm, pgcrypto, plpgsql, seg, tablefunc, tcn, tsm_system_rows, tsm_system_time, unaccent.
Vzorkování logování dotazů
Vzorkování (sampling) dotazů se už dostalo do masteru předchozí verze, ale na poslední chvíli se tato
funkce revertovala. Jak se ukázalo, originální návrh šel proti stávajícímu logování (pomalých) dotazů.
Nově máme dvě konfigurační proměnné: log_min_duration_sample a log_statement_sample_rate (0.0 - 1.0).
Platilo, a bude platit, že pokud příkaz trvá déle než je hodnota log_min_duration_statement, tak se
vždy zaloguje. Takže např. pro log_min_duration_statement='200ms' jakýkoliv dotaz nad 200ms bude zalogován.
Logováním všech rychlejších dotazů, která jsou většinou častější (na soudobých serverech s OLTP
provozem je 95% kvantil dost často 20ms), bych si mohl způsobit vážné provozní problémy zahlcením IO
(ať už by šlo o výkon nebo kapacitu). Naopak, při zvážení rizik, je dobré, alespoň občas zalogovat
vše, nebo téměř vše, aby vývojáři měli přehled jaké dotazy generuje aplikace.
Riziko zahlcení IO můžeme redukovat vzorkováním (zalogujeme zhruba každý n-tý dotaz (filtrování je
založené na generování náhodného čísla od 0 do 1 a jeho porovnání s hodnotou log_statement_sample_rate)).
Předpokládá se, že log_min_duration_sample < log_min_duration_statement. Pokud by tomu bylo naopak, tak
log_min_duration_sample nemá žádný efekt.
# pro zalogování cca setiny všech dotazů do 200ms, a všech nad 200ms log_min_duration_sample = 0 log_min_duration_sample_rate = 0.01 log_min_duration_statement = '200ms'
Logování vázaných parametrů
V případě chyby dotazu, který používá vázané proměnné, může být problém, že se zaloguje chybný dotaz, ale už se nezalogují parametry dotazu tzv. vázané parametry (bind parameters):
2020-04-14 14:14:31.500 CEST [132468] ERROR: value too long for type character varying(4) 2020-04-14 14:14:31.500 CEST [132468] STATEMENT: insert into foo values($1)
Sice si nezahltíme logy, ale těžko budeme zjišťovat, co se vlastně stalo. V nové verzi můžeme poměrně pohodlně si nastavit logování parametrů tak, že nastavíme nenulový maximální limit pro zalogovaný paremetr (pokud je jeho hodnota větší, tak se ořízne):
SET log_parameter_max_length_on_error = 64 2020-04-14 14:34:19.769 CEST [132932] ERROR: value too long for type character varying(4) 2020-04-14 14:34:19.769 CEST [132932] CONTEXT: extended query with parameters: $1 = 'Ahoj svete' 2020-04-14 14:34:19.769 CEST [132932] STATEMENT: insert into foo values($1)
Pokud bych chtěl zalogovat parametry i u dotazů, které neskončily chybou, pak mohu nastavit
log_parameter_max_length. Výchozí hodnota je -1, což znamená, že runtime nemá řešit velikost a parametr
má vždy zobrazit. 0 v tomto parametru vypne logování parametrů a číslo vyšší než 0 udává maximální
velikost zalogované hodnoty v bajtech (po oříznutí).
log_min_duration_statement = 0 # zaloguj vše log_parameter_max_length = -1 # zaloguj kompletní obsah (default)
V logu:
2020-04-14 14:42:45.968 CEST [133091] LOG: duration: 0.444 ms parse <unnamed>: insert into foo values($1) 2020-04-14 14:42:45.968 CEST [133091] LOG: duration: 0.115 ms bind <unnamed>: insert into foo values($1) 2020-04-14 14:42:45.968 CEST [133091] DETAIL: parameters: $1 = 'Ahoj' 2020-04-14 14:42:45.968 CEST [133091] LOG: duration: 0.106 ms execute <unnamed>: insert into foo values($1) 2020-04-14 14:42:45.968 CEST [133091] DETAIL: parameters: $1 = 'Ahoj'
Progres report
Postupně, ke všem déle trvajícím úlohám, jsou k dispozici odpovídající progress pohledy. Ve verzi 13
je to pro příkaz ANALYZE pohled pg_stat_progress_analyze, a pro příkaz pg_base_backup pohled pg_stat_progress_basebackup.
Bylo by hezké, kdyby někdo se začínajících programátorů prointegrovali tyto pohledy s psqlkem.
Zneplatnění (invalidace) replikačních slotů
Relativně častým důvodem nedostatku místa na disku je zapomenutý replikační slot. Replikační slot v Postgresu je pojmenovaný databázový objekt, na který se odkazuje sekundární server, a který udržuje informaci o komunikaci mezi primárním serverem (replikovaným serverem) a sekundárním serverem (replikou). Pokud replikace běží vůči replikačnímu slotu, tak máme garantováno, že jde vždy obnovit přerušenou replikaci. Replikační slot na primárním serveru zabraňuje rotaci nezreplikovaných transakčních logů.
Určitě se několikrát stalo, že uživatel zastavil repliku a neodstranil replikační slot. Od toho momentu
se mu na primárním serveru začaly hromadit transakční logy. Buďto si toho všiml, a replikační slot odstranil,
nebo mu došlo místo na disku. V nové verzi je možné nastavit maximální velikost transakčních logů chráněných
před rotací replikačním slotem a to konfigurační proměnnou max_slot_wal_keep_size. Po dosažení této velikosti
je slot zneplatněn, a rezervované transakční logy budou odstraněné. Stav slotu je vidět ve sloupci
wal_status v pohledu pg_replication_slots. Pozor, ve výchozí konfiguraci je tato pojistka neaktivní.
pg_rewind
Pomocí nástroje (aplikace) pg_rewind můžeme "opravit" instanci, která se používala jako primární server,
a poté ji použít jako hot standby server. V nové verzi tato aplikace automaticky zjistí, jestli byl server
čistě vypnut. Pokud nebyl, tak jej nastartuje v single modu, aby na instanci mohla proběhnout obnova
po havárii (crash recovery).
Nově také implementuje přepínač -R (se stejným významem jako v pg_basebackup). Ve starších verzích
tato volba způsobila vygenerování souboru recovery.conf, který rovnou obsahoval konfiguraci, která
nám umožnila nahodit instanci jako standby server. Nově se už recovery.conf nepoužívá. Potřebná
konfigurace se zapíše do postgresql.auto.conf a vygeneruje se signální soubor.
Od verze 13 pg_rewind si také může stáhnout chybějící transakční logy (potřebné pro modifikaci instance)
z archívu transakčních logů (umí použít konfigurační proměnnou restore_command). Ve starších verzích
se transakční logy musely kopírovat ručně.
Ostatní
S volbou PARALLEL příkaz VACUUM může souběžně vacuuovat indexy nad vacuovanou tabulkou. Tuto volbu
nelze použít v příkazu VACUUM FULL.
V tabulce pg_stat_activity je nový sloupec leader_pid. Zde je
aktuální číslo procesu nebo číslo rodičovského procesu v případě pomocných procesů nastartovaných
pro paralelní zpracování dotazu.
Snad všichni známe funkci a smysl servisního procesu autovacuum. Monitoruje změny v tabulce, a při
dosažení určitého poměru změněných řádků vůči celkovému počtu řádků určité tabulky spustí VACUUM nebo ANALYZE
nad touto tabulkou. Na tabulkách, do kterých se pouze vkládalo, se ve starších verzích příkaz VACUUM
nespouštěl. Po dosažení určitého počtu transakcí se automaticky pustil příkaz VACUUM FREEZE, který mohl
nad takovými tabulkami nepříjemně dlouho intenzivně zatěžovat IO, právě proto, že tyto tabulky nikdy
nebyly vakuované běžným (líným) příkazem VACUUM. Od této verze se bude příkaz VACUUM pouštět automaticky
i nad tabulkami, kde se pouze vkládá.
Pro zajištění konzistence databáze, implementace transakcí (ACID) si databáze udržuje určitá interní data.
Rychlý přístup k těmto interním (perzistentním) datům umožňuje LRU cache (v Postgresu se používá zkratka
SRLU "S" jako Simple). Práce s těmito daty je pro uživatele transparentní. V případě, že se tato data
zvětší nad únosnou mez, může dojít k výkonnostním problémů. Doposud se tato situace dost obtížně
detekovala. S novým stat pohledem pg_stat_slru by to mělo být výrazně jednodušší.
Přes nový pohled pg_shmem_allocations se lze pohodlně podívat na alokace ve sdílené paměti.
postgres=> SELECT * FROM pg_shmem_allocations ORDER BY allocated_size DESC; ┌─────────────────────────────────────┬───────────┬───────────┬────────────────┐ │ name │ off │ size │ allocated_size │ ╞═════════════════════════════════════╪═══════════╪═══════════╪════════════════╡ │ Buffer Blocks │ 6442752 │ 134217728 │ 134217728 │ │ <anonymous> │ │ 4726400 │ 4726400 │ │ XLOG Ctl │ 53504 │ 4208272 │ 4208384 │ │ │ 147147008 │ 1922816 │ 1922816 │ │ Buffer Descriptors │ 5394176 │ 1048576 │ 1048576 │ │ clog │ 4262272 │ 529184 │ 529280 │ │ Buffer IO Locks │ 140660480 │ 524288 │ 524288 │ │ Checkpointer Data │ 146669056 │ 393280 │ 393344 │ │ Checkpoint BufferIds │ 141184768 │ 327680 │ 327680 │ │ subtrans │ 4925312 │ 267040 │ 267136 │ │ oldserxid │ 146098176 │ 133600 │ 133632 │ │ multixact_member │ 5259392 │ 133600 │ 133632 │ │ commit_timestamp │ 4791552 │ 133600 │ 133632 │ │ Backend Activity Buffer │ 146453376 │ 132096 │ 132096 │ │ shmInvalBuffer │ 146590208 │ 69464 │ 69504 │ │ async │ 147080064 │ 66848 │ 66944 │
Některé operace s daty mohou být akcelerovány přednačítáním (prefetch). Míru přednačítání určuje
proměnná effective_io_concurrency. Adekvátní nastavení této hodnoty je mně neznámá alchymie vyššího
zasvěcení (doporučuje se nastavit od hodnot 4, 200, 1000, ..). Prefetch má smysl i pro servisní
operace. Ve starších verzích se používala pofidérní heuristika, kterou v nové verzi nahradila
konfigurační proměnná maintenance_io_concurrency.
Pomocí pg_dumpu můžeme nově můžeme exportovat i obsah cizích tabulek (přepínač --include-foreign-data=název_cizího_serveru).
pg_basebackup nově generuje tzv backup manifest. To je soubor formátu JSON s informacemi popisujícími
záložní soubory (obsah zálohy). Na základě tohoto souboru pak nový nástroj pg_verifybackup může provést
kontrolu zálohy.
Logická replikace je v Postgresu třetím rokem (počínaje verzí 10), a nyní se pracuje na větší robustnosti
provozu. Jedním z problémů byla vysoká spotřeba paměti na primárním serveru v případě replikace
transakcí s velkým množstvím subtransakcí. V Postgresu k tomu stačí blok se zachytáváním výjimky v PLpgSQL.
V nové verzi by problém s pamětí alokovanou replikací (konkrétně procesem walsender) měl být redukován
limitem logical_decoding_work_mem. Po naplnění bufferů o této velikosti se pracovní data pro replikaci
začnou serializovat na disk. Výchozí hodnota je 64MB.
Upravila se reakce serveru na signál promote (přepnutí z režimu hot standby do režimu primárního). V předchozích verzích mohl tento signál čekat na ukončení pauzy (stav pause). Pokud byla replikace zapauzovaná, tak ani promote replikaci nerozjel, tudíž se server nemohl doreplikovat a nemohl změnit na primární server. V nové verzi promote zruší pauzu, server se doreplikuje a promote se provede bez v drtivé většině nechtěného odkladu.
Podpora Unicode
Podpora escape sekvencí v databázích s jiným než UTF8 kódováním
Escape zápis unicode znaků by je nyní možný i v těch případech, kdy databáze je v jiném kódování než UTF8
postgres=> CREATE DATABASE pokus WITH template='template0' encoding='latin2' locale='cs_CZ.latin2';
CREATE DATABASE
postgres=> \c pokus
You are now connected to database "pokus" as user "pavel".
pokus=# INSERT INTO foo VALUES(U&'\010D'); -- č
INSERT 0 1
pokus=# INSERT INTO foo VALUES(U&'\0424'); -- Ф
ERROR: character with byte sequence 0xd0 0xa4 in encoding "UTF8" has no equivalent in encoding "LATIN2"
LINE 1: INSERT INTO foo VALUES(U&'\0424');
^
Normalizace Unicode znaků
V znakové sadě Unicode může být jeden znak kódován různě. Např. dlouhé velké A "Á" může být zakódováno jako U00C1 nebo sekvence U0041, U0301 (pozn. naštěstí se druhá forma příliš nepoužívá). Před porovnáním řetězců na shodu by se řetězce měly tzv normalizovat.
postgres=> SELECT U&'\00C1' c1,U&'\0041\0301' c2, U&'\00C1' = U&'\0041\0301' eq; ┌────┬────┬────┐ │ c1 │ c2 │ eq │ ╞════╪════╪════╡ │ Á │ Á │ f │ └────┴────┴────┘ (1 row)
Jak vidíte, dva stejné znaky jsou pro Postgres různé znaky. V nové verzi můžeme použít funkci normalize
a test IS NORMALIZED:
postgres=> SELECT U&'\00C1' c1,
U&'\0041\0301' c2,
U&'\00C1' = U&'\0041\0301' eq,
U&'\00C1' IS NORMALIZED,
U&'\0041\0301' IS NORMALIZED,
normalize(U&'\00C1') = normalize(U&'\0041\0301') eq2;
┌────┬────┬────┬───────────────┬───────────────┬─────┐
│ c1 │ c2 │ eq │ is_normalized │ is_normalized │ eq2 │
╞════╪════╪════╪═══════════════╪═══════════════╪═════╡
│ Á │ Á │ f │ t │ f │ t │
└────┴────┴────┴───────────────┴───────────────┴─────┘
(1 row)
Partitioning
Oproti loňské verzi letos nejsou žádné dramatické změny v implementaci partitioningu. Určitě krokem
kupředu je podpora BEFORE triggerů nad partitiovanými tabulkami. Zde zůstává jedno omezení.
Záznam i po úpravě v before triggeru musí zůstat ve stejné partition.
Pro logickou replikace byly partitions samostatné tabulky a replikovaly se samostatně. Nově je možné nastavit
publikační sadě příznak publish_via_partition_root. Tím se replikace změn obsahu partitions přesměruje
na kořenovou partitiovanou tabulku. U takto nakonfigurované replikace může mít příjemce (konzument)
jinou strukturu partitions než má producent.
Postgres už dříve podporoval partitionwise join (snahou je spojovat menší partitions místo větších
partitiovaných tabulek). Ve starší verzi partitionwise join byl možný pouze tehdy, když počet partitions
spojovaných partitiovaných tabulek byl stejný. Nový algoritmus pouze vyžaduje, aby každá partition
partitiované tabulky byla joinována přesně s jednou partition druhé partitiované tabulky. Nově je
možné partitionwise join použít i pro FULL JOIN.
SQL
FETCH FIRST WITH TIES
Klauzule WITH TIES volitelně doplňuje klauzuli FETCH FIRST n, která je ANSI SQL analogií proprietární
kluzule LIMIT, která jak jistě víte, omezí počet řádků na výstupu. FETCH FIRST n WITH TIES omezí počet
řádků na n plus ještě ty řádky, kde hodnota podle které se řadí je stejná jako u n tého řádku.
Dejme tomu, že bych chtěl vidět 3 nejmenší obce z české republiky. Napíši jednoduchý dotaz
postgres=> SELECT *
FROM obce
ORDER BY pocet_zen + pocet_muzu
FETCH FIRST 3 ROW ONLY;
┌──────┬──────────┬─────────┬────────────┬───────────┬──────────┬─────────┐
│ id │ okres_id │ nazev │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞══════╪══════════╪═════════╪════════════╪═══════════╪══════════╪═════════╡
│ 5028 │ CZ0646 │ Březina │ 3 │ 0 │ 60.2 │ 0.0 │
│ 1244 │ CZ0311 │ Vlkov │ 9 │ 9 │ 55.3 │ 53.7 │
│ 2158 │ CZ0326 │ Čilá │ 8 │ 14 │ 46.6 │ 65.9 │
└──────┴──────────┴─────────┴────────────┴───────────┴──────────┴─────────┘
(3 rows)
Nicméně právě u tohoto dotazu výsledek není úplný. Existuje obec Vysoká Lhota se stejným počtem obyvatel
jako obec Čilá. V závislosti na fyzickém uložení, algoritmu řazení se nějak zvolila jedna obec a druhá
se nezobrazila. Pokud bych chtěl zabránit této situaci, tak mohu použít klauzuli WITH TIES:
postgres=> SELECT *
FROM obce
ORDER BY pocet_zen + pocet_muzu
FETCH FIRST 3 ROW WITH TIES;
┌──────┬──────────┬──────────────┬────────────┬───────────┬──────────┬─────────┐
│ id │ okres_id │ nazev │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞══════╪══════════╪══════════════╪════════════╪═══════════╪══════════╪═════════╡
│ 5028 │ CZ0646 │ Březina │ 3 │ 0 │ 60.2 │ 0.0 │
│ 1244 │ CZ0311 │ Vlkov │ 9 │ 9 │ 55.3 │ 53.7 │
│ 2158 │ CZ0326 │ Čilá │ 8 │ 14 │ 46.6 │ 65.9 │
│ 4225 │ CZ0633 │ Vysoká Lhota │ 10 │ 12 │ 58.0 │ 65.5 │
└──────┴──────────┴──────────────┴────────────┴───────────┴──────────┴─────────┘
(4 rows)
Nestane se mi, že bych v důsledku oříznutí přišel o nějaký řádek. Na druhou stranu nemusím dostat přesně
n řádků (v mezních případech mohu dostat signifikantně více řádků, což by bylo řešitelné zanořením a dalším
oříznutím). Čím víc nad tím přemýšlím, tak mi to přijde jako šikovná věc, a konečně důvod, abych se naučil
(a občas použil) ANSI SQL syntax (proprietární klauzule LIMIT má, přeci jen, výrazně kratší zápis).
Nové funkce a rozšíření stávajících funkcí
Nově jsou v Postgresu k dispozici funkce gcd (greatest common divisor) největší společný dělitel a lcm
(least common multiple) nejmenší společný násobek. Tyto funkce zatím nikomu nechyběly. Do Postgresu
se implementovaly spíš z důvodu úplnosti knihovny funkcí.
Naopak implementace funkcí trim_scale a num_scale byla dlouho na seznamu Postgresového ToDo (komunitní ToDo je spíš skrumáž nápadů, co by se někdy mohlo, než seriózně míněný seznam toho, co by se mělo udělat). trim_scale
ořezává zbytečné nuly za desetinou čárkou u čísel typu numeric:
postgres=> SELECT 10.20 * 10.40; ┌──────────┐ │ ?column? │ ╞══════════╡ │ 106.0800 │ └──────────┘ (1 row) postgres=> SELECT trim_scale(10.20 * 10.40), min_scale(10.20 * 10.40); ┌────────────┬───────────┐ │ trim_scale │ min_scale │ ╞════════════╪═══════════╡ │ 106.08 │ 2 │ └────────────┴───────────┘ (1 row)
min_scale vrací počet nezbytných desetinných čísel z čísla, tak aby nedošlo ke ztrátě informace.
Funkce to_char, to_timestamp nově pracují se formátovacím vzorem FF1-FF6. Tyto nové vzory slouží k
určení desetiné části sekundy (desetina sec, setina sec, tisícina sec, ...)
postgres=> SELECT to_char(current_timestamp, 'hh:mm:ss.ff1'); ┌────────────┐ │ to_char │ ╞════════════╡ │ 07:04:44.7 │ └────────────┘ (1 row)
Nově se také zavádí SSSSS, který vyjadřuje počet vteřin po půlnoci.
postgres=> SELECT to_char(current_timestamp, 'YYYY-MM-DD SSSSS'); ┌──────────────────┐ │ to_char │ ╞══════════════════╡ │ 2020-04-14 25378 │ └──────────────────┘ (1 row)
Funkce to_char roky podporuje vzor TM (translated mode), který zobrazuje hodnoty v národním prostředí.
Počínaje verzí 13 můžeme tento vzor použít i ve funkcích to_date a to_timestamp:
postgres=> SELECT to_char(current_date, 'DD.MM.YYYY TMDAY DD TMMONTH');
┌───────────────────────────┐
│ to_char │
╞═══════════════════════════╡
│ 14.04.2020 ÚTERÝ 14 DUBNA │
└───────────────────────────┘
(1 row)
postgres=> SELECT to_date('15 dub 2020', 'DD TMMON YYYY'), to_date('15 dubna 2020', 'DD TMMONTH YYYY');
┌────────────┬────────────┐
│ to_date │ to_date │
╞════════════╪════════════╡
│ 2020-04-15 │ 2020-04-15 │
└────────────┴────────────┘
(1 row)
Je to dost citlivé na zápis, takže pro češtinu to bude použitelné pouze pro parsování strojově generovaných dat, kde je dodržená konvence.
Vloni se vedla diskuze ohledně chování funkce jsonb_set.
Pro některé uživatele není jednoduché rozlišit mezi SQL NULLem (a odpovídajícím chováním) a JSON nullem a jeho jiným chováním.
U STRICT funkcí jako je např. jsonb_set se neřeší typ NULL hodnoty - NULL je NULL.
-- typový NULL, nicméně stále SQL NULL
postgres=> SELECT jsonb_set('{"f1":1,"f2":2}', '{f2}', null::jsonb);
┌───────────┐
│ jsonb_set │
╞═══════════╡
│ │
└───────────┘
(1 row)
-- použití JSONB nullu
postgres=> SELECT jsonb_set('{"f1":1,"f2":2}', '{f2}', 'null'::jsonb);
┌───────────────────────┐
│ jsonb_set │
╞═══════════════════════╡
│ {"f1": 1, "f2": null} │
└───────────────────────┘
(1 row)
Změnit nekompatibilním způsobem chování funkce je i pro vývojáře Postgresu i pro uživatele nepříjemná
záležitost, a tudíž jediným řešením je zavedení nové funkce. Tou je funkce jsonb_set_lax, která není
STRICT (a tudíž si ošetřuje NULL vstup sama), a kde uživatel může (skrze volitelné parametry funkce)
určit chování funkce v případě, že vstupní argument je NULL (výchozí je "use_json_null", alternativy
"raise_exception", "delete_key" a "return_target").
postgres=> select jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null);
┌─────────────────────────────────────────┐
│ jsonb_set_lax │
╞═════════════════════════════════════════╡
│ [{"f1": 99, "f2": null, "f3": null}, 2] │
└─────────────────────────────────────────┘
(1 row)
Změnil se algoritmus pro výpočet odmocniny logaritmu (nově se používá Karatsuba), který je přesnější (správně se zaokrouhluje poslední číslice) a rychlejší (3-5x u malých čísel a cca 10x u velkých čísel). Tuto změnu možná ocení uživatele PostGISu.
Nová třída polymorfních datových typů - anycompatible
Polymorfní typy v PostgreSQL jsou analogií generickým typům v programovacích jazycích, které podporu
generik mají. V předchozích verzích Postgresu byly tři základní třídy: "any" (musí se zapisovat v
uvozovkách, protože any je klíčové slovo), anyenum a anyelement. "any" je možné použít pouze pro vstupní
argumenty a pouze pro funkce implementované v jazyku C. Na typ anyelement navazují typy anyarray,
anynonarray a anyrange. Funkce, které používají typ anyelement jsou typově velice striktní, což je
někdy chtěná vlastnost, ale také často vlastnost nechtěná, omezující (např. většina vestavěných funkcí
se tak nechová):
CREATE OR REPLACE FUNCTION public.make_array(anyelement, anyelement)
RETURNS anyarray
AS $$
SELECT ARRAY[$1, $2];
$$
LANGUAGE sql IMMUTABLE;
postgres=> SELECT make_array(1, 1);
┌────────────┐
│ make_array │
╞════════════╡
│ {1,1} │
└────────────┘
(1 row)
postgres=> SELECT make_array(.1, .1);
┌────────────┐
│ make_array │
╞════════════╡
│ {0.1,0.1} │
└────────────┘
(1 row)
postgres=> SELECT make_array(.1, 1);
ERROR: function make_array(numeric, integer) does not exist
LINE 1: SELECT make_array(.1,1);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Na příkladu je vidět, že poslední volání funkce selhalo, protože typ numeric není stejným typem jako
typ integer. Pokud by funkce použila novou třídu anycompatible (pozor, je úplně nezávislá na anyelement),
tak se nejprve otestuje, zda-li jsou skutečné typy anycompatible argumentů ze stejné kategorie (text,
číslo, datum, ..) a pokud ano, tak se hodnoty těchto parametrů převedou na společný typ:
postgres=> SELECT make_array(.1, 1);
┌────────────┐
│ make_array │
╞════════════╡
│ {0.1,1} │
└────────────┘
(1 row)
postgres=> \gdesc
┌────────────┬───────────┐
│ Column │ Type │
╞════════════╪═══════════╡
│ make_array │ numeric[] │
└────────────┴───────────┘
(1 row)
postgres=> SELECT make_array(.1::double precision, 1);
┌────────────┐
│ make_array │
╞════════════╡
│ {0.1,1} │
└────────────┘
(1 row)
postgres=> \gdesc
┌────────────┬────────────────────┐
│ Column │ Type │
╞════════════╪════════════════════╡
│ make_array │ double precision[] │
└────────────┴────────────────────┘
(1 row)
Pomocí této třídy polymorfních typů se jednoduše implementují funkce jako nvl (z Oracle):
CREATE OR REPLACE FUNCTION nvl(anycompatible, anycompatible) RETURNS anycompatible AS $$ SELECT coalesce($1, $2) $$ LANGUAGE sql IMMUTABLE;
Nebude to sice absolutně kompatibilní s Oraclem, ale pro téměř všechny rozumné kombinace
to kompatibilní bude. Pozn. Oracle u této a podobných funkcích převede všechny parametry
na text, pokud je první parametr text. Postgres naopak implicitní přetypování na text nikdy
nedělá. Orafce si pomáhá přetížením funkcí (overloadingem):
CREATE OR REPLACE FUNCTION nvl(text, anyelement) RETURNS text AS $$ -- vynucené přetypování SELECT coalesce($1, $2::text) $$ LANGUAGE sql IMMUTABLE; -- první parametr určuje datový typ CREATE OR REPLACE FUNCTION nvl(numeric, text) RETURNS numeric AS $$ -- vynucené přetypování SELECT coalesce($1, $2::numeric) $$ LANGUAGE sql IMMUTABLE; CREATE OR REPLACE FUNCTION nvl(date, text) RETURNS date AS $$ -- vynucené přetypování SELECT coalesce($1, $2::date) $$ LANGUAGE sql IMMUTABLE;
To ale není úplně korektní (používá se nezdokumentovaná (negarantovaná) vlastnost Postgresu). Pro minimalizaci režie migrace z Oracle do Postgresu je to bohužel nutné (a nečekám, že by Postgres o tuto nezdokumentovanou vlastnost přišel). Typový systém Oracle je pro nejčastěji používané datové typy (varchar2, number a date) výrazně jednodušší a dynamičtější než u Postgresu, a nelze jej bez výjimky emulovat v Postgresu (kde se používá výrazně statičtější striktnější složitější typový systém).
Obecně se nedoporučuje přetěžovat polymorfní parametry funkcí. Pokud se přetížení funkcí neudělá správně, tak pak při volání funkce runtime nemůže (nemusí) jednoznačně určit instanci funkce, což vždy skončí runtime chybou (záměrně vytvořené nevhodné přetížení funkce nvl):
ERROR: function nvl(integer, numeric) is not unique
LINE 1: select nvl(1, 1.1);
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
Je bezpečnější se nespoléhat na implicitní přetypování, a v Postgresu si nekorektní (alespoň z pohledu
Postgresu) situace ošetřit explicitním přetypováním (tudíž mít pouze polymorfní funkci), tj výraz
nvl('AHOJ', 1) přepsat na nvl('AHOJ', 1::text).
Explain
Poměrně značného rozšíření se dočkal příkaz EXPLAIN (a odpovídajícím způsobem i extenze auto_explain).
S volbou SETTINGS se do explainu propíše veškeré nedefaultní nastavení, které ovlivňuje optimalizátor
Postgresu.
postgres=> EXPLAIN (ANALYZE, SETTINGS)
SELECT *
FROM obce
ORDER BY pocet_muzu + pocet_zen DESC
LIMIT 10;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Limit (cost=272.19..272.21 rows=10 width=45) (actual time=11.244..11.246 rows=10 loops=1) │
│ -> Sort (cost=272.19..287.81 rows=6250 width=45) (actual time=11.241..11.242 rows=10 loops=1) │
│ Sort Key: ((pocet_muzu + pocet_zen)) DESC │
│ Sort Method: top-N heapsort Memory: 27kB │
│ -> Seq Scan on obce (cost=0.00..137.12 rows=6250 width=45) (actual time=0.037..4.854 rows=6250 loops=1) │
│ Settings: work_mem = '64kB' │
│ Planning Time: 0.268 ms │
│ Execution Time: 11.323 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)
Naopak s volbou WAL se zobrazí informace o zápisu do transakčního logu:
postgres=> EXPLAIN (ANALYZE,WAL,BUFFERS)
INSERT INTO test SELECT generate_series(1,1000);
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Insert on test (cost=0.00..15.02 rows=1000 width=4) (actual time=1.675..1.675 rows=0 loops=1) │
│ Buffers: shared hit=1010 dirtied=5 written=5 │
│ WAL: records=1000 bytes=59000 │
│ -> ProjectSet (cost=0.00..5.02 rows=1000 width=4) (actual time=0.004..0.148 rows=1000 loops=1) │
│ -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1) │
│ Planning Time: 0.053 ms │
│ Execution Time: 1.703 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)
Zajímavou novou funkcí je zobrazení počtu přístupu k bufferům v době plánování dotazu (planning time). Několikrát jsem se setkal s tím, že v důsledku nafouklých (bloated) indexů se silně zpomalilo plánování dotazů. Postgres už v době plánování dotazů pracuje s potenciálními indexy a například dohledává minimum, maximum (a zejména pokud se jedná o dotazy na začátku session, tak si plní lokální (session) cache systémového katalogu).
postgres=> EXPLAIN (BUFFERS, ANALYZE)
SELECT *
FROM obce
WHERE okres_id = 'CZ0201';
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Index Scan using obce_okres_id_idx on obce (cost=0.28..14.49 rows=114 width=41) (actual time=0.026..0.057 rows=114 loops=1) │
│ Index Cond: ((okres_id)::text = 'CZ0201'::text) │
│ Buffers: shared hit=4 │
│ Planning Time: 1.188 ms │
│ Buffers: shared hit=124 │
│ Execution Time: 0.106 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)
Tak jak rozšířily možnosti příkazu EXPLAIN, tak obdobným způsobem se rozšířily možnosti extenzí
pg_stat_statements a auto_explain. V pohledu pg_stat_statements jsou nyní nově metriky kvantifikující
zápis do transakčního logu způsobený monitorovaným příkazem. Pokud se zapne pg_stat_statements.track_planning,
pak je možné v pg_stat_statements vidět režii planeru.
Pro vývojáře postgresu
Voláním funkce MemoryContextMemAllocated lze zjistit velikost alokované paměti v daném kontextu včetně
jeho potomků. StringInfo API - sada funkcí pro práci s dynamicky alokovanými řetězci lze používat i
v klientských aplikacích (frontend - pg_waldump, ..). Nová konfigurační proměnná backtrace_functions
umožňuje nastavit seznam funkcí, u kterých se v případě chyby vypíše backtrace do logu Postgresu.
set backtrace_functions = 'int4div';
postgres=> select 10/0;
ERROR: division by zero
2020-04-14 09:26:07.925 CEST [121307] ERROR: division by zero
2020-04-14 09:26:07.925 CEST [121307] BACKTRACE:
postgres: pavel postgres [local] SELECT(int4div+0x60) [0x84ec3b]
postgres: pavel postgres [local] SELECT() [0x65ffef]
postgres: pavel postgres [local] SELECT(ExecInterpExprStillValid+0x2e) [0x65d069]
postgres: pavel postgres [local] SELECT(evaluate_expr+0x80) [0x7324dc]
postgres: pavel postgres [local] SELECT() [0x732697]
postgres: pavel postgres [local] SELECT() [0x7345e8]
postgres: pavel postgres [local] SELECT() [0x7329cf]
postgres: pavel postgres [local] SELECT(expression_tree_mutator+0xa63) [0x6d062c]
postgres: pavel postgres [local] SELECT() [0x733abb]
postgres: pavel postgres [local] SELECT(expression_tree_mutator+0xb8c) [0x6d0755]
postgres: pavel postgres [local] SELECT() [0x733abb]
postgres: pavel postgres [local] SELECT(eval_const_expressions+0x40) [0x733c0c]
postgres: pavel postgres [local] SELECT() [0x71632d]
postgres: pavel postgres [local] SELECT(subquery_planner+0x373) [0x71ef8e]
postgres: pavel postgres [local] SELECT(standard_planner+0x181) [0x71fc06]
postgres: pavel postgres [local] SELECT(planner+0x19) [0x7201cb]
postgres: pavel postgres [local] SELECT(pg_plan_query+0x44) [0x7ec79a]
postgres: pavel postgres [local] SELECT(pg_plan_queries+0x40) [0x7ec849]
postgres: pavel postgres [local] SELECT() [0x7ecd1e]
postgres: pavel postgres [local] SELECT(PostgresMain+0x74e) [0x7eec5e]
postgres: pavel postgres [local] SELECT() [0x7644aa]
postgres: pavel postgres [local] SELECT() [0x7672bd]
postgres: pavel postgres [local] SELECT() [0x7674c1]
postgres: pavel postgres [local] SELECT(PostmasterMain+0xfa8) [0x76889a]
postgres: pavel postgres [local] SELECT(main+0x1e9) [0x6b8a52]
/lib64/libc.so.6(__libc_start_main+0xf2) [0x7f0d9a937042]
postgres: pavel postgres [local] SELECT(_start+0x2e) [0x47f2de]
2020-04-14 09:26:07.925 CEST [121307] STATEMENT: select 10/0;
Backtrace se zaloguje i v případě neplatného assertu. gdb backtrace to určitě nenahradí,
určitě ale nějaká informace v tom je.
Co se nestihlo
Myslím si, že ve čtrnáctce bychom se mohli dočkat podpory JSONu podle ANSI/SQL včetně funkce json_table.
Doufám, že snad se už do upstreamu dostane můj patch na schema variables (analogie globálních proměnných
v Oracle). Dost nadějně vypadá patch implementující globální dočasné tabulky. Pořád se pracuje na
index skip scanu. Tomáš pracuje na funkcionálních statikách. Dost daleko je patch implementující
online materializované pohledy. V další verzi by se měl objevit nový datový typ multirange.
Závěr
Na bývalém OHLOHu (dnes Synopsys) mne zaujalo hodnocení Postgresu . Pracnost kódu je odhadnuta na 601 člověko-let. Dnes má Postgres dva milióny řádek kódu. Při hrubém odhadu (a pokud bych vzal za základ platy v ČR) může být cena vývoje Postgresu přes miliardu korun.
Ve třináctce je pro uživatele viditelných nových funkcí minimum. Drtivá většina změn je pod povrchem. Čtrnáctka by mohla být, co se týče uživatelsky viditelných funkcí, opačná. Tudíž bude se na co těšit.
- 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 14 (2021)
- PostgreSQL 15 (2022)
- PostgreSQL 16 (2023)
- PostgreSQL 17 (2024)