Vidím snahu o eliminaci výrazných typických úzkých hrdel zpomalujících zpracování SQL dotazu. Na 9.5 pracovalo asi nejvíc vývojářů v historii PostgreSQL (a do vývoje se zapojují další), takže počet různých vylepšení - větších nebo menších, různých optimalizací, se určitě blíží ke stovce.
Za poslední tři, čtyři roky došlo k zásadní proměně vývojářské komunity - nyní už většina kódu přijde od vývojářů, kteří se PostgreSQL věnují na plný úvazek. Z většiny jsou to vývojáři, kteří se Postgresu věnují dlouhodobě a nyní se jim jejich koníček stal zaměstnáním. Vývoj se koncetruje na tři hlavní cíle: využívání více CPU pro spočítání jednoho SQL dotazu, podpora logické replikace (BDR) a inkrementální vylepšování možností foreign data wrappers API. Je to ale běh na dlouhou trať, a to přestože na každém z těchto cílů pracuje více vývojářů několik let. Jejich práce je ale vidět, a někteří "early adopters" uživatelé již nyní produkčně používají některé tyto funkce (hlavně BDR).
Mimo tyto tři cíle přicházejí patche (relativně nahodile, tak, jak se řeší provozní problémy) z firem, které aktivně používají
PostgreSQL. Příkladem může optimalizace hashjoinu, kterou napsal Tomáš Vondra pro GoodData, nebo optimalizace přetypování
v PL/pgSQL, kterou napsal Tom Lane pro Salesforce. Takových patchů jsou desítky a zaměřují se hlavně na optimalizaci výkonu
při větší zátěži. Dalších pár patchů doslova "uzrálo" a po několikaletém vývoji se konečně dostalo do upstreamu (např. row level
security, INSERT ON CONFLICT DO nebo typové transformační funkce).
Opticky bylo vývojové okno kratší, jelikož vývoj 9.4 se protáhl zhruba o 4 měsíce a letos byla snaha vrátit konec vývoje zpět k začátku léta. Slůvko "opticky" dodávám, protože práce na této verzi probíhaly částečně souběžně s prací na dokončení 9.4. Po ukončení vývoje začne tříměsíční testovací cyklus a pokud nedojde k nějakým nečekaným změnám, tak by 9.5 měla být oficiálně uvolněna koncem září.
Rozšíření SQL
Klauzule ON CONFLICT DO příkazu INSERT
Přiznám se, že smekám před zarputilostí a asertivitou autora tohoto patche Peterem Geogheganem. Před několika roky přišel s nápadem implementace UPSERTu (UPDATE or INSERT) v prostředí multigenerační databáze. Stál si za svým názorem, trpělivě argumentoval, dokázal respektovat protiargumenty, a hlavně psal desítky variací, oprav a úprav patche, který je hodně komplexní a kde se musí precizně domýšlet důsledek každého detailu. Osobně bych se do takto rozsáhlého kódování asi nepustil a určitě by mi chyběla jeho zarputilost.
UPSERT je definován v ANSI SQL jako příkaz MERGE. Je definovaná relativně čitelná syntaxe, je definováno
relativně jednoduché chování. Tento příkaz je implementován v dalších databázích - např. v MSSQL. Po důkladnějším zkoumání se ale zjistilo,
že standard neřeší možné konflikty, ke kterým může docházet v OLTP databázi a není jednoznačné, jak tento příkaz
implementovat v mutigenerační databázi, kterou je Postgres. Ve chvíli, kdy implementujete něco trochu jiného než je ve standardu,
tak je rozumné se zamyslet, jestli je vhodné použít syntax, kterou předepisuje standard. Někdo by preferoval ANSI SQL syntax,
s tím, že pár odchylek vůči standardu je možné zanedbat, jiný zas trvá na tom, že pokud se neimplementuje chování popsané
standardem, tak by se neměla použít syntaxe standardu. A ačkoliv by všichni UPSERT chtěli (opravdu chyběl), strhla se nekončící
diskuze. Nakonec (možná více než po roce) zvítězil názor - nechová se to podle standardu, tudíž nelze ani použít syntax standardu.
A začala další dlouhá diskuze, jakou že tedy zvolit syntax. Osobně jsem byl hodně skeptický, a ani jsem nedoufal, že se najde
řešení. K mému příjemnému překvapení se řešení našlo:
CREATE TABLE foo(a int, b text); INSERT INTO foo VALUES(10, 'AHOJ'),(20, 'NAZDAR'); CREATE UNIQUE index on foo(a);-- bežný INSERT selže postgres=> INSERT INTO foo VALUES(10, 'SERVUS'); ERROR: duplicate key value violates unique constraint "foo_a_idx" DETAIL: Key (a)=(10) already exists. Time: 1.098 ms -- Duplicitní data můžeme ignorovat postgres=>INSERT INTO foo VALUES(10, 'SERVUS') ON CONFLICT(a) DO NOTHING; INSERT 0 0 postgres=> SELECT * FROM foo; ┌────┬────────┐ │ a │ b │ ╞════╪════════╡ │ 20 │ NAZDAR │ │ 10 │ AHOJ │ └────┴────────┘ (2 rows) -- Nebo můžeme provést UPDATE postgres=>INSERT INTO foo VALUES(10, 'SERVUS') ON CONFLICT(a) DO UPDATE SET b = 'SERVUS'; UPSERT 0 1 postgres=> SELECT * FROM foo; ┌────┬────────┐ │ a │ b │ ╞════╪════════╡ │ 20 │ NAZDAR │ │ 10 │ SERVUS │ └────┴────────┘ (2 rows)
Patch zavádí nový alias EXCLUDED (kolizní řádek, který nelze vložit). Sloučení dvou tabulek lze
zapsat docela čitelně.
postgres=> SELECT * FROM t1;
┌────┬───┐
│ id │ v │
╞════╪═══╡
└────┴───┘
(0 rows)
postgres=> SELECT * FROM t2;
┌────┬────┐
│ id │ v │
╞════╪════╡
│ 1 │ 10 │
│ 2 │ 20 │
└────┴────┘
(2 rows)
-- v případě kolize provádím update, pokud se cílová a zdrojová hodnota liší
-- úspora: trigger, MGA, WAL
postgres=> INSERT INTO t1
SELECT * FROM t2
ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v
WHERE t1.v <> EXCLUDED.v;
UPSERT 0 2
postgres=> INSERT INTO t1
SELECT * FROM t2
ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v
WHERE t1.v <> EXCLUDED.v;
UPSERT 0 0
postgres=> SELECT * FROM t1;
┌────┬────┐
│ id │ v │
╞════╪════╡
│ 1 │ 10 │
│ 2 │ 20 │
└────┴────┘
(2 rows)
postgres=> TRUNCATE t2; INSERT INTO t2 VALUES(1,100),(10,10000);
TRUNCATE TABLE
INSERT 0 2
postgres=> INSERT INTO t1
SELECT * FROM t2
ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v
WHERE t1.v <> EXCLUDED.v;
UPSERT 0 2
postgres=> SELECT * FROM t1;
┌────┬───────┐
│ id │ v │
╞════╪═══════╡
│ 2 │ 20 │
│ 1 │ 100 │
│ 10 │ 10000 │
└────┴───────┘
(3 rows)Při orientačních testech merge 10K řádků do 100K řádků trval 200 ms. Totéž pomocí CTE trvalo cca 270 ms. Navíc zde není možná optimalizace redukce zbytečných UPDATE řádků.
WITH updated AS
(UPDATE t1 SET v = t2.v FROM t2
WHERE t1.id = t2.id RETURNING t2.id)
INSERT INTO t1 SELECT * FROM t2 e
WHERE NOT EXISTS(SELECT * FROM t2 WHERE e.id = t2.id);
Nová konstrukce zpřehlední kód a zároveň je rychlejší. Během několika dalších let uvidíme, jak se nová syntaxe osvědčí v praxi. Zatím můžete narazit na omezení, která se patrně během následujících roků odstraní - jedním z takových omezení je blokování vícenásobného UPDATE řádky během jednoho příkazu.
Multiupdate
Nově PostgreSQL podporuje zápis příkazu UPDATE s tzv vícenásobným přiřazením, kdy se seznam sloupců aktualizuje seznamem hodnot,
např. vícesloupcovým subselectem. Tento zápis ocení hlavně vývojáři portující aplikace z Oracle.
CREATE TABLE cil(id int, a int, b int);
CREATE TABLE zdroj(id int, a int, b int);
INSERT INTO cil SELECT i FROM generate_series(1,10) g(i);
INSERT INTO zdroj SELECT i, random()*10, random()*10 FROM generate_series(1,10) g(i);
UPDATE cil SET (a,b) = (SELECT a,b FROM zdroj WHERE cil.id = zdroj.id);Interní implementace je dost jednoduchá - vede na variaci nested loopu (pro větší tabulky je nezbytný index na klíči):
postgres=> EXPLAIN ANALYZE UPDATE cil SET (a,b) = (SELECT a,b FROM zdroj WHERE cil.id = zdroj.id); ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Update on cil (cost=0.00..12.35 rows=10 width=10) (actual time=0.272..0.272 rows=0 loops=1) │ │ -> Seq Scan on cil (cost=0.00..12.35 rows=10 width=10) (actual time=0.095..0.177 rows=10 loops=1) │ │ SubPlan 1 (returns $1,$2) │ │ -> Seq Scan on zdroj (cost=0.00..1.12 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=10) │ │ Filter: (cil.id = id) │ │ Rows Removed by Filter: 9 │ │ Planning time: 0.232 ms │ │ Execution time: 0.347 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (8 rows)
Implementace přes UPDATE FROM není tak přímočará a je proprietární - je ale lépe optimalizovaná, a proto
doporučuji se ji držet (tj pro masivnější změny používat starší zápis, pro změnu pár set řádek bude nový zápis pravděpodobně bez problémů).
postgres=> EXPLAIN ANALYZE UPDATE cil SET a = zdroj.a, b = zdroj.b FROM zdroj WHERE cil.id = zdroj.id; ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Update on cil (cost=1.23..2.46 rows=10 width=24) (actual time=0.171..0.171 rows=0 loops=1) │ │ -> Hash Join (cost=1.23..2.46 rows=10 width=24) (actual time=0.056..0.078 rows=10 loops=1) │ │ Hash Cond: (cil.id = zdroj.id) │ │ -> Seq Scan on cil (cost=0.00..1.10 rows=10 width=10) (actual time=0.014..0.026 rows=10 loops=1) │ │ Filter: id │ │ -> Hash (cost=1.10..1.10 rows=10 width=18) (actual time=0.022..0.022 rows=10 loops=1) │ │ Buckets: 1024 Batches: 1 Memory Usage: 9kB │ │ -> Seq Scan on zdroj (cost=0.00..1.10 rows=10 width=18) (actual time=0.004..0.015 rows=10 loops=1) │ │ Planning time: 0.738 ms │ │ Execution time: 0.273 ms │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (10 rows)
Klauzule FOR UPDATE SKIP LOCKED příkazu SELECT
Klauzuli SKIP LOCKED (přeskoč zamčené řádky) bych už párkrát použil. Existoval workaround založený na využití pomocných zámků. Počínaje 9.5 máme k dispozici jednoduché čisté řešení.
CREATE TABLE tasks(id integer, task_desc text, start timestamp, finish timestamp);
INSERT INTO tasks VALUES(1, 'task 1');
INSERT INTO tasks VALUES(2, 'task 2');
postgres=> SELECT * FROM tasks;
┌────┬───────────┬───────┬────────┐
│ id │ task_desc │ start │ finish │
╞════╪═══════════╪═══════╪════════╡
│ 1 │ task 1 │ │ │
│ 2 │ task 2 │ │ │
└────┴───────────┴───────┴────────┘
(2 rows)Klient je triviální a lze jej provozovat paralelně (alespoň tuto část) - pseudokód:
BEGIN;
$id = (SELECT id FROM tasks WHERE start IS NULL ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE tasks SET start = clock_timestamp() WHERE id = $1;
...
UPDATE tasks SET finish = clock_timestamp() WHERE id = $1;
COMMIT;Podpora vzorkování - klauzule TABLESAMPLE
Dovedu si představit situace, kdy potřebujeme rychle dostat výsledek SQL příkazu, který nemusí být 100% správný, musí být ovšem rychle a musí
vypadat alespoň trochu věrohodně. Diskuzní fóra jsou ještě plná dotazů typu - "jak získat N náhodných řádků tabulky?". Řešení, které nabízí
ANSI SQL, je klauzule TABLESAMPLE. Díky Petru Jelínkovi tuto klauzuli budeme moci používat i v Postgresu. K dispozici jsou dvě
vzorkovací metody: SYSTEM (na základě datových stránek, čtou se celé datové stránky) a BERNOULLI (na základě
pravděpodobnosti řádků (skenuje celou tabulku)). Parametrem je desetinné číslo od 0 do 100 udávající, kolik procent řádků tabulky bude vráceno:
-- 0.1% obcí v ČR: postgres=> SELECT * FROM obce TABLESAMPLE BERNOULLI (0.1) REPEATABLE(0.5); ┌──────┬──────────┬──────────────────┬────────────┬───────────┬──────────┬─────────┐ │ id │ okres_id │ nazev │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │ ╞══════╪══════════╪══════════════════╪════════════╪═══════════╪══════════╪═════════╡ │ 736 │ CZ0208 │ Sloveč │ 276 │ 267 │ 42.3 │ 43.1 │ │ 1524 │ CZ0315 │ Radhostice │ 91 │ 83 │ 38.8 │ 43.3 │ │ 2104 │ CZ0325 │ Město Touškov │ 1003 │ 1045 │ 37.1 │ 39.5 │ │ 2454 │ CZ0421 │ Vilémov │ 470 │ 472 │ 39.2 │ 44.5 │ │ 3072 │ CZ0521 │ Vysoká nad Labem │ 601 │ 587 │ 35.6 │ 37.1 │ │ 4646 │ CZ0641 │ Ráječko │ 627 │ 609 │ 39.3 │ 41.3 │ │ 5223 │ CZ0647 │ Tulešice │ 98 │ 89 │ 43.7 │ 47.5 │ └──────┴──────────┴──────────────────┴────────────┴───────────┴──────────┴─────────┘ (7 rows)
Klauzule REPEATABLE nastavuje počáteční stav generátoru náhodných čísel (seed) a zajišťuje opakovatelný výsledek dotazu.
Implementace klauzule TABLESAMPLE je rozšiřitelná o vlastní metody vzorkování, např. o použití pouze procentuální části všech stránek, atd
Podpora GROUPING SETS
Díky GROUPING SETS můžeme relativně jednoduše vygenerovat i komplexní agregační dotazy. Opět se jedná o téma, které je otevřené delší dobu - první prototyp jsem napsal v roce 2008. Tehdy šlo hlavně o to, zda je relativně složitá syntaxe implementovatelná v SQL parseru PostgreSQL - a také jsem se chtěl seznámit s touto funkcí. Implementace nebyla triviální - zabrala by pár měsíců, čekal jsem na sponzora, který by zaplatil vývoj. Na někoho ochotného jsem ovšem nenarazil. Tématu se ale chytil Andrew Gierth a Atri Sharma, a napsali implementaci, kterou už bylo možné integrovat (s vydatnou pomocí Andrease Freunda).
Pro ukázku použiji příklad, který jsem napsal před sedmi roky (čísla jsou vycucaná z prstu):
CREATE TABLE CARS(name text, place text, count integer);
INSERT INTO CARS VALUES
('skoda', 'czech rep.', 10000),
('skoda', 'germany', 5000),
('bmw', 'czech rep.', 100),
('bmw', 'germany', 1000),
('opel', 'czech rep.', 7000),
('opel', 'germany', 7000);
postgres=> SELECT * FROM cars;
┌───────┬────────────┬───────┐
│ name │ place │ count │
╞═══════╪════════════╪═══════╡
│ skoda │ czech rep. │ 10000 │
│ skoda │ germany │ 5000 │
│ bmw │ czech rep. │ 100 │
│ bmw │ germany │ 1000 │
│ opel │ czech rep. │ 7000 │
│ opel │ germany │ 7000 │
└───────┴────────────┴───────┘
(6 rows)Pokud bychom chtěli nad těmi daty dělat základní analytiku bez GROUPING SETS, tak napíšeme několik agregačních dotazů:
postgres=> SELECT name, sum(count) FROM cars GROUP BY 1; ┌───────┬───────┐ │ name │ sum │ ╞═══════╪═══════╡ │ bmw │ 1100 │ │ skoda │ 15000 │ │ opel │ 14000 │ └───────┴───────┘ (3 rows) postgres=> SELECT place, sum(count) FROM cars GROUP BY 1; ┌────────────┬───────┐ │ place │ sum │ ╞════════════╪═══════╡ │ germany │ 13000 │ │ czech rep. │ 17100 │ └────────────┴───────┘ (2 rows) postgres=> SELECT sum(count) FROM cars; ┌───────┐ │ sum │ ╞═══════╡ │ 30100 │ └───────┘ (1 row)
Anebo můžeme použít operátory GROUPING SETS, CUBE nebo ROLLUP:
postgres=> SELECT name, place, SUM(count)
FROM cars
GROUP BY GROUPING SETS(name, place, ()) -- GROUP BY name, GROUP BY place, TOTAL
ORDER BY 1 NULLS LAST, 2 NULLS LAST;
┌───────┬────────────┬───────┐
│ name │ place │ sum │
╞═══════╪════════════╪═══════╡
│ bmw │ │ 1100 │
│ opel │ │ 14000 │
│ skoda │ │ 15000 │
│ │ czech rep. │ 17100 │
│ │ germany │ 13000 │
│ │ │ 30100 │
└───────┴────────────┴───────┘
(6 rows)
Je možné dohledat i složitější kombinace - operátor CUBE zobrazí všechny možné:
postgres=> SELECT name, place, SUM(count)
FROM cars
GROUP BY CUBE(name, place)
ORDER BY 1 NULLS LAST, 2 NULLS LAST;
┌───────┬────────────┬───────┐
│ name │ place │ sum │
╞═══════╪════════════╪═══════╡
│ bmw │ czech rep. │ 100 │
│ bmw │ germany │ 1000 │
│ bmw │ │ 1100 │
│ opel │ czech rep. │ 7000 │
│ opel │ germany │ 7000 │
│ opel │ │ 14000 │
│ skoda │ czech rep. │ 10000 │
│ skoda │ germany │ 5000 │
│ skoda │ │ 15000 │
│ │ czech rep. │ 17100 │
│ │ germany │ 13000 │
│ │ │ 30100 │
└───────┴────────────┴───────┘
(12 rows)
Někdy není nutné hledat všechny možné kombinace - pokud by nám pro tento příklad stačily agregace:
((name, place),(name), ()), tak můžeme použít operátor ROLLUP, který je úspornější - vystačí
si s jedním řazením:
postgres=> SELECT name, place, SUM(count)
FROM cars
GROUP BY ROLLUP(name, place)
ORDER BY 1 NULLS LAST, 2 NULLS LAST;
┌───────┬────────────┬───────┐
│ name │ place │ sum │
╞═══════╪════════════╪═══════╡
│ bmw │ czech rep. │ 100 │
│ bmw │ germany │ 1000 │
│ bmw │ │ 1100 │
│ opel │ czech rep. │ 7000 │
│ opel │ germany │ 7000 │
│ opel │ │ 14000 │
│ skoda │ czech rep. │ 10000 │
│ skoda │ germany │ 5000 │
│ skoda │ │ 15000 │
│ │ │ 30100 │
└───────┴────────────┴───────┘
(10 rows)
postgres=> EXPLAIN SELECT name, place, SUM(count) FROM cars GROUP BY ROLLUP(name, place) ORDER BY 1 NULLS LAST, 2 NULLS LAST;
┌─────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════════════════╡
│ GroupAggregate (cost=1.14..1.27 rows=7 width=18) │
│ Group Key: name, place │
│ Group Key: name │
│ Group Key: () │
│ -> Sort (cost=1.14..1.15 rows=6 width=18) │
│ Sort Key: name, place │
│ -> Seq Scan on cars (cost=0.00..1.06 rows=6 width=18) │
└─────────────────────────────────────────────────────────────────┘
(7 rows)
Nechybí podpora funkce grouping(), která vrací jedničku nebo nulu, podle toho, jestli se aktuální řádek obsahuje mezisoučet
pro daný sloupec:
postgres=> SELECT name, place, grouping(name) AS subtot_name, grouping(place) AS subtot_place, SUM(count)
FROM cars
GROUP BY ROLLUP(name, place)
ORDER BY 1 NULLS LAST, 2 NULLS LAST;
┌───────┬────────────┬─────────────┬──────────────┬───────┐
│ name │ place │ subtot_name │ subtot_place │ sum │
╞═══════╪════════════╪═════════════╪══════════════╪═══════╡
│ bmw │ czech rep. │ 0 │ 0 │ 100 │
│ bmw │ germany │ 0 │ 0 │ 1000 │
│ bmw │ │ 0 │ 1 │ 1100 │
│ opel │ czech rep. │ 0 │ 0 │ 7000 │
│ opel │ germany │ 0 │ 0 │ 7000 │
│ opel │ │ 0 │ 1 │ 14000 │
│ skoda │ czech rep. │ 0 │ 0 │ 10000 │
│ skoda │ germany │ 0 │ 0 │ 5000 │
│ skoda │ │ 0 │ 1 │ 15000 │
│ │ │ 1 │ 1 │ 30100 │
└───────┴────────────┴─────────────┴──────────────┴───────┘
(10 rows)
Na implementaci CUBE a ROLLUP si museli uživatelé Postgresu počkat déle než uživatelé jiných databází
(chybějící GROUPING SETS někdy komplikovaly migrace z Oracle). Nyní ale GROUPING SETS máme a je čas se naučit je používat.
Nové funkce
V 9.4 se zapomnělo na některé konstruktory typu JSONB. Počínaje 9.5 můžeme používat funkce: to_jsonb(),
jsonb_object(), jsonb_build_object(), jsonb_build_array(), jsonb_agg(),
jsonb_object_agg().
postgres=> SELECT jsonb_object('{{a,1},{b,2},{3,NULL},{"def","abc"}}');
┌───────────────────────────────────────────────┐
│ jsonb_object │
╞═══════════════════════════════════════════════╡
│ {"3": null, "a": "1", "b": "2", "def": "abc"} │
└───────────────────────────────────────────────┘
(1 row)
Pro typ JSONB jsou nyní k dispozici následující operace: přidání, náhrada nebo odstranění klíče:
-- případně funkce jsonb_concat
postgres=> select '{"x":20}'::jsonb || '{"z":10}';
┌────────────────────┐
│ ?column? │
╞════════════════════╡
│ {"x": 20, "z": 10} │
└────────────────────┘
(1 row)
-- případně funkce jsonb_delete
postgres=> select '{"a": "b", "c":10}'::jsonb - 'a';
┌───────────┐
│ ?column? │
╞═══════════╡
│ {"c": 10} │
└───────────┘
(1 row)
postgres=> select jsonb_replace('{"a":10, "b":{"x":10,"y":20}}'::jsonb, ARRAY['b','x'], '[10,20,30]');
┌──────────────────────────────────────────────┐
│ jsonb_replace │
╞══════════════════════════════════════════════╡
│ {"a": 10, "b": {"x": [10, 20, 30], "y": 20}} │
└──────────────────────────────────────────────┘
(1 row)
Funkce generate_series nyní existuje i pro typ numeric.
Agregace pole
Doposud PostgreSQL neuměl agregovat pole (uživatelé si museli psát vlastní agregační funkce). To se s 9.5 mění a v 9.5 již můžeme agregovat pole se stejnými dimenzemi.
postgres=> SELECT * FROM test_a;
┌────────────┐
│ a │
╞════════════╡
│ {10,20,3} │
│ {20,30,30} │
└────────────┘
(2 rows)
postgres=> SELECT array_agg(a) FROM test_a;
┌────────────────────────┐
│ array_agg │
╞════════════════════════╡
│ {{10,20,3},{20,30,30}} │
└────────────────────────┘
(1 row)
Vyhledávání v poli
Funkce array_position vrací pozici prvku v poli (vrací NULL, pokud pole neobsahuje prvek). array_positions vrátí pole všechny
pozice prvku v poli (nebo prázdné pole).
postgres=> DO $$
DECLARE
p int;
a int[] := ARRAY[1,2,2,3,1,2,1];
BEGIN
p := array_position(a, 1);
WHILE p IS NOT NULL
LOOP
RAISE NOTICE 'jednicka je na % pozici', p;
p := array_position(a, 1, p + 1);
END LOOP;
RAISE NOTICE 'pozice jednicky v poli %', array_positions(a, 1);
END;
$$;
NOTICE: jednicka je na 1 pozici
NOTICE: jednicka je na 5 pozici
NOTICE: jednicka je na 7 pozici
NOTICE: pozice jednicky v poli {1,5,7}
DO
Zatřídění prvku do tříd definovaných seznamem hraničních prvků
Pro dohledání třídy prvku z tříd stejné šířky můžeme používat standardní SQL funkci width_bucket:
postgres=> SELECT width_bucket(33, 1, 100, 10); ┌──────────────┐ │ width_bucket │ ╞══════════════╡ │ 4 │ └──────────────┘ (1 row)
Hodnota 33 je ve čtvrté třídě z rozsahu 1 až 100 po 10 prvcích (1..10, 11..20, 21..30, 31..40, ..)
Tento zápis je nepoužitelný v případech, kdy máme třídy o různé velikosti. Petr Jelínek proto rozšířil tuto funkci o možnost zadat hranice tříd jako prvky pole.
postgres=> SELECT width_bucket(33, ARRAY[0,20,30,40,60]); ┌──────────────┐ │ width_bucket │ ╞══════════════╡ │ 3 │ └──────────────┘ (1 row)
Pole musí být seřazené, z důvodu rychlosti nedochází k řazení, stejně tak testu na správné pořadí. Vyhledává se metodou půlení intervalu - i v desítkách tisíc tříd lze rychle dohledat odpovídající třídu.
json(b)_strip_null(), jsonb_pretty()
V dokumentech typu JSON je zbytečné ukládat hodnotu NULL (ve struktuře), a je možné bezpečně JSON redukovat (užitečné v případě ukládání JSONu nebo posílání JSONu po síti):
postgres=> SELECT json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
┌──────────────────┐
│ json_strip_nulls │
╞══════════════════╡
│ {"a":{},"d":{}} │
└──────────────────┘
(1 row)
-- NULL v poli redukovat nelze
postgres=> SELECT json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
┌────────────────────────────────────┐
│ json_strip_nulls │
╞════════════════════════════════════╡
│ {"a":1,"c":[2,null,3],"d":{"e":4}} │
└────────────────────────────────────┘
(1 row)
Jak je z názvu patrné, funkce jsonb_pretty slouží k formátování JSONB dokumentů:
postgres=> select jsonb_pretty('{"a":10, "b":{"x":10,"y":20}}');
┌──────────────────┐
│ jsonb_pretty │
╞══════════════════╡
│ { ↵│
│ "a": 10, ↵│
│ "b": { ↵│
│ "x": 10,↵│
│ "y": 20 ↵│
│ } ↵│
│ } │
└──────────────────┘
(1 row)
Změny v PL/pgSQL a v PL
Nově také další příkazy COMMENT, SECURITY LABEL, GRANT/REVOKE startují event triggery (DDL triggery). Odstranilo se jedno z omezení
PL/Pythonu - nyní umí vracet pole kompozitních typů.
V nové verzi se kontroluje správnost formátovacího řetězce příkazu RAISE již při validaci funkce.
plpgsql_check si docela dobře žije mimo upstream (na integraci do upstreamu jsem rezignoval). Opravilo se pár chyb a hlavně se zlepšila podpora pro MS Windows - při troše štěstí není nutná kompilace - lze si stáhnout připravenou DLL knihovnu (pro Win 7).
Neviditelnou a přesto naprosto zásadní změnou je změna způsobu převádění hodnot z jednoho typu do druhého. Doposud se v PL/pgSQL používalo tzv IO přetypování - tedy hodnota se převedla ze zdrojového typu na text, a pak z textu do cílového typu. IO přetypování funguje jednoduše, funguje dobře (až na pár vyjímek), ale je pomalé. Tom Lane napsal patch, který umožňuje použití existujících binárních konverzí, které jsou rychlejší a bez nežádoucích vedlejších efektů. Další pro uživatele neviditelnou změnou je výkonnostní optimalizace práce s doménovými typy.
Zrychlení operací s prvky pole
V Postgresu se strukturovaná data udržují v kompaktním formátu, který zjednoduše serializaci na disk. Kompaktní formát není ideální pro režim, kdy se k větším datům přistupuje opakovaně (např. v cyklu z PL/pgSQL). Dobře známým problémem je rychlost práce s prvky pole v PL/pgSQL. Při každém přístupu dochází k rozbalení z kompaktního formátu, při každé změně dochází k zabalení do kompaktního formátu. Tom Lane napsal patch, který umožňuje data rozbalit a držet je rozbalená, expandovaná. Zatím existuje podpora pouze pro pole a pouze pro jazyk PL/pgSQL. Výsledky jsou ale fantastické:
DO $$
DECLARE a int[] = array_fill(10, ARRAY[10000]);
BEGIN
FOR i IN 1 .. 10000 LOOP
a[i] := 0;
END LOOP;
END;
$$;
DO $$
DECLARE a int[] = '{}';
BEGIN
FOR i IN 1 .. 10000 LOOP
a := a || 10;
END LOOP;
END;
$$;Na 9.5 běží tyto smyčky 6 a 5 ms. Na 9.4 450 a 220 ms. Na pár workaroundů budeme moci zapomenout.
Podpora transformací (transforms)
Transformace jsou zákaznické funkce, které konvertují data mezi SQL a prostředím ve kterém se volají externí uložené procedury. V ANSI SQL v části SQL/MM patří transformace k fundamentálním prvkům. V Postgresu transformace zatím nebyly, resp. byly (implicitně). Byly zakódované v handlerech obalujících interprety jazyků pro uložené procedury. S větším rozšířením externího datového typu hstore se ukázalo, že by se transformace mohly uplatnit i v PostgreSQL. Doposud se hstore do procedur předával jako text - jedná se o externí typ, a proto jej handlery PL neznají. Bylo pak prací programátora, aby tento text převedl na hash. Aktivní transformace může udělat tuto práci za vás. Navíc řešení s transformacemi je zpětně kompatibilní. Pokud programátor u funkce explicitně nespecifikuje typy, pro které se mají volat transformace, tak se použije původní mechanismus předání parametru jako text.
Jedná se o docela starý patch (první verze je z května 2012), který se nedařilo dotáhnout do úspěšného konce (řešily se hlavně otázky ohledně zpětné kompatibility). Transformace jsou natolik okrajová záležitost - zvlášť koncept z SQL/MM, že existuje minimum lidí, kteří by o nich něco málo věděli a měli motivaci je implementovat. Pokud se tato tematika přeloží jako "chytřejší způsob předávání hstore do PL (Perlu, Pythonu)", tak už je hned jiná motivace. Výsledkem je implementace nezbytné infrastruktury a tří extenzí: hstore_plperl, hstore_plptyhon a ltree_plpython. Po aktivaci extenze hstore_plperl se automaticky vytvoří odpovídající transformace, kterou hned můžeme začít používat:
CREATE EXTENSION hstore;
CREATE EXTENSION plperlu;
CREATE EXTENSION hstore_plperlu;
-- bez transformace
CREATE OR REPLACE FUNCTION public.test1(val hstore)
RETURNS integer
LANGUAGE plperlu
AS $function$
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;
elog(INFO, Dumper($_[0]));
return scalar(keys %{$_[0]});
$function$
-- s trasnformací hstore z/do Perlu
CREATE FUNCTION test2(val hstore) RETURNS int
LANGUAGE plperlu
TRANSFORM FOR TYPE hstore
AS $$
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;
elog(INFO, Dumper($_[0]));
return scalar(keys %{$_[0]});
$$;
postgres=> SELECT test1('a=>10, b=>20');
INFO: $VAR1 = '"a"=>"10", "b"=>"20"';
CONTEXT: PL/Perl function "test1"
┌───────┐
│ test1 │
╞═══════╡
│ 0 │
└───────┘
(1 row)
Time: 1.213 ms
postgres=> SELECT test2('a=>10, b=>20');
INFO: $VAR1 = {
'a' => '10',
'b' => '20'
};
CONTEXT: PL/Perl function "test2"
┌───────┐
│ test2 │
╞═══════╡
│ 2 │
└───────┘
(1 row)
Time: 1.018 msVe výstupu z druhé funkce je vidět, že transformace zajistila konverzi do perlovského hashe. To v samotném Perlu není úplně triviální, protože i když výstupní formát ideově vychází z perlu, není s ním kompatibilní.
Podpora transformace je docela nenápadná vlastnost - ale pro vývojáře, kteří píší uložené procedury i v jiném jazyce než je plpgsql, je to jedna z nejdůležitějších nových vlastností za posledních pět let. Alespoň za mne velké díky autoru tohoto patche, kterým je Peter Eisentraut.
Asertace
Další novinkou je PL/pgSQL příkaz ASSERT. Při implementaci jsme se inspirovali Cčkovým makrem Assert. Přikaz vyhodí
nezachytitelnou výjimku v případě nepravdivého argumentu. K této výjimce lze připojit komentář. V odladěném kódu lze kontroly
globálně vypnout:
postgres=> DO $$ BEGIN ASSERT false; END $$; ERROR: assertion failed CONTEXT: PL/pgSQL function inline_code_block line 1 at ASSERT postgres=> DO $$ BEGIN ASSERT false, 'něco je špatně'; END $$; ERROR: něco je špatně CONTEXT: PL/pgSQL function inline_code_block line 1 at ASSERT
ANSI SQL zápis pojmenovaných parametrů funkce
Implementací pojmenovaných parametrů funkce jsme předstihli standardizaci a implementovali proprietární syntax založenou na symbolu ":=". Ve standardu se používá symbol "=>", který se používá v Oracle (kde se přebírá z jazyku ADA). Postgresový zápis bude podporován nadále:
CREATE OR REPLACE FUNCTION foo(a int, b int)
RETURNS void AS $$
BEGIN
RAISE NOTICE '% %', a, b;
END;
$$ LANGUAGE plpgsql;
postgres=> SELECT foo(b := 20, a := 10); -- postgresová syntaxe
NOTICE: 10 20
postgres=> SELECT foo(b => 20, a => 10); -- standard
NOTICE: 10 20Funkce pg_event_trigger_ddl_commands()
Opět o něco jednodušší bude psaní event triggerů díky funkci pg_event_trigger_ddl_commands(), která vrací základní
informace o události, kdy došlo k modifikaci libovolného objektu:
CREATE OR REPLACE FUNCTION test_ddl_deparse()
RETURNS event_trigger AS $$
DECLARE r record;
BEGIN
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
RAISE NOTICE 'tag:%, object type:%, schema: %, name: %', r.command_tag, r.object_type, r.schema_name, r.object_identity;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER test_ddl_deparse
ON ddl_command_end EXECUTE PROCEDURE test_ddl_deparse();
postgres=> CREATE TABLE t1(a int, b int);
NOTICE: tag:CREATE TABLE, object type:table, schema: public, name: public.t1
CREATE TABLE
postgres=> CREATE SCHEMA xxx;
NOTICE: tag:CREATE SCHEMA, object type:schema, schema: , name: xxx
CREATE SCHEMAVýkonnostní optimalizace
Rozšiřuje se podpora index only scanu pro další typy indexů - GiST a btree-GiST, a pro další typy - inet a range.
Reimplementace lehkých zámků (LWlocks - interní zámky v postgresu používané pro synchronizaci procesů v Postgresu (např pro přístup do sdílené paměti)) by se měla projevit zvýšeným výkonem v silně konkurenčních databázích běžících na výkonných serverech za předpokladu, že převažuje čtení dat. Došlo i na optimalizaci managementu sdílené paměti (získání bloku). Běžný uživatel si těchto optimalizací asi nevšimne - ale pokud provozujete server s tisíci dotazy za sekundu (kolem 8K dotazů a výše) s desítkami jader a data zůstávají v operační paměti, tak nárůst výkonu může být ohromující (samozřejmě, že by se tím měl významně posunout i bod, kdy dramaticky padá výkon z důvodu přetížení, tj. kdy se téměř všechen výkon spálí v zámcích).
K drobné optimalizaci došlo i v implementaci window funkcí v dotazu s klauzulí WHERE.
Na moderních CPU podporujících SSE 4.2 se použijí speciální instrukce crc32b a crc32q pro zrychlení výpočtu CRC. Zdroje na internetu
uvádějí dvou až třínásobné zrychlení vůči implementaci bez použití speciálních instrukcí. To by mohlo zajímavě snížit režii kontroly
datových stránek pomocí kontrolních součtů (defaultně vypnuto). U sebe na notebooku jsem žádný zvláštní efekt neviděl, což není překvapivé
- používám šifrovaný souborový systém, který představuje hlavní úzké hrdlo, které pro testování CRC datových stránek nelze obejít.
krácení klíčů (Abbreviated keys)
Krácení klíčů je technika, která by měla výrazně zrychlit operaci sort. Pro řazení řetězců se v PostgreSQL používají systémové funkce porovnávající dva řetězce podle zadaného locales. Toto porovnání řetězců je poměrně náročné a pomalé, zvláště pro některé locales jako je např. to naše české. V minulé verzi došlo k otimalizaci testu rovnosti a nerovnosti řetězců (což má smysl např. pro hashjoin), a to použitím porovnání obsahu paměti. V 9.5 se pokračuje i v optimalizaci porovnávání a to tak, že se na základě prvních n znaků řetězce (8 na 64bitech, 4 na 32bitech) vytvoří binární řetězec (zkrácený klíč), který se porovnová pomocí porovnání obsahu paměti. Pokud se binární klíče rovnají, tak se pak přejde na obvyklé porovnání řetězců. Algoritmus je adaptivní, pokud krácení klíčů nepomáhá, přestane se používat. V testu pro české locales (seznam pošt) jsem se dostal na poloviční časy dotazů (data byla už v RAM).
Podobný problém a podobné řešení můžeme použít i pro řazení hodnot typu numeric. Operace nad tímto typem (řazení nevyjímaje) jsou výrazně pomalejší než nad ostatními číselnými typy (což by nikoho nemělo překvapovat - je to daň za přesnost). Něco ještě optimalizovat lze, a tím v 9.5 bylo řazení pomocí krácení klíčů. V mém syntetickém testu došlo zhruba k 50% zrychlení.
Rehash
V 9.5 jsou desítky různých optimalizací a oprav, které by se měly pozitivně projevit i na výkonu. Jednou z oprav je oprava hashjoinu, kterou napsal Tomáš Vondra ještě v GoodData. Kromě jiného je platforma GoodData jednou velkou databázovou laboratoří. Máme desítky tisíc různých databází různých velikostí nad kterými generujeme téměř všechny možné rozumné variace SELECTů (také máme relativně netrpělivé úživatele). Po třech letech produkčního provozu si dovolím tvrdit, že pokud je v PostgreSQL nějaký problém s výkonem, tak v platformě GoodData se musí ukázat během několika měsíců. Při zjišťování příčin pomalosti jednoho dotazu jsme zjistili dost divné chování (jedna z těch situací, kdy si říkáte, že si z vás ty stroje dělají blázny). Zvýšením paměti (work_mem) se dotaz výrazně zpomalil. To je v databázovém světě neobvyklý úkaz, který rozhodně stál za detailní průzkum. Narazili jsme na jeden z hraničních případů (corner case) stávající implementace hashjoinu (nešikovná kombinace dat a velké paměti vedla k neefektivní interní implementaci). Za několik hodin měl Tomáš patch. Když už se Tomáš díval na hashjoin, zoptimalizoval i způsob, jakým se alokuje paměť data v hash tabulce (místo individuální alokace použil 32KB prealokaci). To vede k výrazné úspoře paměti. Výrazné úspory paměti dosáhl i úpravou funkce array_agg.
Použití 128bit integeru jako akumulátoru funkcí sum(int64bit) a avg(int64bit)
Integerové funkce sum a avg používají o úroveň delší integer jako akumulátor. Pro 8 bajtový integer se historicky používal typ Numeric. Operace nad Numericem jsou minimálně dvakrát-třikrát pomalejší - a tak 8bajtové funkce sum a avg byly výrazně pomalejší než jejich 4bajtové varianty. Novější překladače již podporují 16bajtový integer, který se zatím využívá jen jako akumulátor pro zmíněné agregační funkce, tam kde je podporován. Zároveň se připravila základní infrastruktura pro 128bitové typy. V mých testech došlo zhruba k 50% zrychlení.
CHECK constraints, partitioning ve FDW
Optimalizátor v PostgreSQL se umí rozhodovat i v závislosti na tabulkových omezení (constraints). Doposud FDW tabulky omezení nepodporovaly, takže se optimalizovalo tím nejjednoduším (někdy neefektivním) způsobem. Nově lze definovat omezení i na cízí tabulky, a také lze cizí tabulky použít jako partitions. S partitioningem v PostgreSQL souvisí dědičnost tabulek - nově lze dědičnost používat i vůči cizím tabulkám.
Rozšiřitelnost optimalizátoru a executoru o vlastní metody
Patch sám o sobě nepřináší žádný užitek, ale má v sobě velký potenciál do budoucna. KaiGai Kohei už několik let pracuje na využití výkonu GPU (procesorů grafických karet) pro akceleraci zpracování dotazu. KaiGai už testuje prototypy GpuHashJoinu, GpuHashAgg a celá jeho práce vypadá hodně zajímavě. Pokud vše vyjde, tak by mohl mít PostgreSQL hardwarovou akceleraci dotazů během dvou tří let. Samozřejmě, GPU hw akcelerace nepomůže všem - bude limitovaná velikostí RAM grafické karty - tichým předpokladem je podpora uložení dat po sloupcích.
BRIN (Block Range Indexes) Indexy
V loňském roce bylo ohledně BRIN indexů (tehdy ještě nazývaných Maxmin indexů) rušno. Byl to možná prvotní šok z vlastností tohoto indexu. Pokud máte štěstí a vaše data jsou primárně přírůstková, kdy posloupnost hodnot přibližně kopíruje fyzické uložení dat v tabulce, tak je tento index rychlý a překvapivě malý. Jeho integrace do core se do 9.4 nestihla, a spadla až do vývojového okna 9.5. S integrací opadlo i vzrušení, které jistě znova nastane, až se začne 9.5 anoncovat.
Vrátil jsem se k databázi letů v USA z minulého článku. Výběr letů z jednoho dne v roce trvá 30 ms.
postgres=> CREATE INDEX fl_brin_y ON x USING brin (x,y,z); CREATE INDEX Time: 231.660 ms postgres=> EXPLAIN ANALYZE SELECT count(*) FROM x WHERE y = 2013 AND m = 12 AND d = 15; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Aggregate (cost=2247.92..2247.93 rows=1 width=0) (actual time=27.879..27.880 rows=1 loops=1) │ │ -> Bitmap Heap Scan on x (cost=23.36..2245.69 rows=891 width=0) (actual time=16.880..27.692 rows=880 loops=1) │ │ Recheck Cond: ((y = 2013) AND (m = 12) AND (d = 15)) │ │ Rows Removed by Index Recheck: 47430 │ │ Heap Blocks: lossy=640 │ │ -> Bitmap Index Scan on fl_brin_y (cost=0.00..23.14 rows=891 width=0) (actual time=0.240..0.240 rows=6400 loops=1) │ │ Index Cond: ((y = 2013) AND (m = 12) AND (d = 15)) │ │ Planning time: 0.210 ms │ │ Execution time: 28.018 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (9 rows)
Velikost BRIN indexu je 48KB:
postgres=> \di+ fl_brin_y
List of relations
┌────────┬───────────┬───────┬───────┬───────┬───────┬─────────────┐
│ Schema │ Name │ Type │ Owner │ Table │ Size │ Description │
╞════════╪═══════════╪═══════╪═══════╪═══════╪═══════╪═════════════╡
│ public │ fl_brin_y │ index │ pavel │ x │ 48 kB │ │
└────────┴───────────┴───────┴───────┴───────┴───────┴─────────────┘
(1 row)
Dotaz s použitím Btree indexu je rychlejší (ale tady se dostávám už do prostoru, který je Btree doma).
postgres=> EXPLAIN ANALYZE SELECT count(*) FROM x WHERE y = 2013 AND m = 12 AND d = 15; ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Aggregate (cost=1466.38..1466.39 rows=1 width=0) (actual time=1.247..1.247 rows=1 loops=1) │ │ -> Index Only Scan using fl_btree_y on x (cost=0.42..1464.16 rows=891 width=0) (actual time=0.183..1.025 rows=880 loops=1) │ │ Index Cond: ((y = 2013) AND (m = 12) AND (d = 15)) │ │ Heap Fetches: 880 │ │ Planning time: 0.522 ms │ │ Execution time: 1.344 ms │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (6 rows)
Ale index má 10MB (a doba vytváření indexu je dvoj- až trojnásobná).
postgres=> \di+ fl_btree_y
List of relations
┌────────┬────────────┬───────┬───────┬───────┬───────┬─────────────┐
│ Schema │ Name │ Type │ Owner │ Table │ Size │ Description │
╞════════╪════════════╪═══════╪═══════╪═══════╪═══════╪═════════════╡
│ public │ fl_btree_y │ index │ pavel │ x │ 10 MB │ │
└────────┴────────────┴───────┴───────┴───────┴───────┴─────────────┘
(1 row)
Dotaz bez indexů trvá 100ms - tudíž BRIN index zrychlí 3x dotaz nad 35MB tabulkou (300K řádků) s použitím 50KB soubůrku. Brin indexy jsou extrémně malé, protože jsou extrémně ztrátové - data v tabulce se rozdělí do oblastí a do indexu se uloží pouze hraniční prvky oblastí. Ztrátovost indexu lze konfigurovat pomocí proměnné pages_per_range. Čím je více stránek na oblast, tím je index menší (ale způsobí přečtení více zbytečných dat). Výchozí hodnotou je 128 stránek na jednu uloženou oblast (1MB).
BRIN index je k dispozici pro geometrické typy, pro typ inet a pro range typy.
Administrace
Trochu práce ušetří možnost změnit chování tabulky ohledně zápisu do transakčního logu jednoduchým příkazem ALTER TABLE SET (LOGGED|UNLOGGED).
Implementace je založena na klonování a přejmenování tabulky - může jít o časově náročnou úlohu. V příkazech, kde se mění konfigurace nebo práva
vybraného uživatele, nově můžeme používat pseudo konstanty CURRENT_USER a SESSION_USER pro určení uživatele:
GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER;
ALTER TABLE testtab1 OWNER TO CURRENT_USER;
ALTER ROLE CURRENT_USER SET application_name to 'FOO';Extenze pgstattuple byla rozšířena o funkci pgstattuple_approx, která je obdobou funkce pgstattuple.
Na rozdíl od této funkce neskenuje datové soubory, ale vrací odhad založený na datech z free space map a visibility map. Výsledné údaje by
měly být věrohodné a na větších tabulkách samozřejmě výrazně rychleji k dispozici.
Práci s databází ulehčují speciální typy navázané na systémový katalog. K existujícím (zmíním nejpoužívanější) typům regclass,
regproc, regprocedure a regtype přibyly typy regrole (přímo se nabízí použití pro RLS viz níže) a
regnamespace. Některé časté dotazy do katalogu se mohou příjemně zjednodušit (a i zrychlit, protože se přistupuje přímo do systémové cache místo do
tabulek systémového katalogu):
postgres=> SELECT relnamespace::regnamespace, relname FROM pg_class WHERE relkind = 'r' LIMIT 3; ┌──────────────┬──────────────┐ │ relnamespace │ relname │ ╞══════════════╪══════════════╡ │ pg_catalog │ pg_statistic │ │ pg_catalog │ pg_type │ │ pg_catalog │ pg_authid │ └──────────────┴──────────────┘ (3 rows) postgres=> SELECT CURRENT_USER::regrole::oid; ┌──────────────┐ │ current_user │ ╞══════════════╡ │ 16384 │ └──────────────┘ (1 row)
Možná reakci na nedávné problémy ssl byla implementace pohledu pg_stat_ssl, kde administrátor může
vidět parametry ssl připojení:
[pavel@localhost regress]$ psql "sslmode=require host=localhost dbname=postgres" psql (9.5devel) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. postgres=> select * from pg_stat_ssl ; ┌───────┬─────┬─────────┬─────────────────────────────┬──────┬─────────────┬──────────┐ │ pid │ ssl │ version │ cipher │ bits │ compression │ clientdn │ ╞═══════╪═════╪═════════╪═════════════════════════════╪══════╪═════════════╪══════════╡ │ 30625 │ t │ TLSv1.2 │ ECDHE-RSA-AES256-GCM-SHA384 │ 256 │ f │ │ └───────┴─────┴─────────┴─────────────────────────────┴──────┴─────────────┴──────────┘ (1 row)
9.4 přišla s možností vzdálené konfigurace. V 9.5 můžeme libovolný parametr resetovat, případně můžeme resetovat všechny parametry:
ALTER SYSTEM RESET ALL;Příkaz vacuumdb nově podporuje souběžný start a běh příkazů VACUUM. Pozor, neměl by se používat pro VACUUM FULL (skončí deadlockem).
Zjednoduší se reindexace indexů ve vybraném schématu použitím příkazu REINDEX SCHEMA.
Příkazy CREATE TABLE AS, CREATE INDEX a CREATE MATERIALIZED VIEW získaly klauzuli IF NOT EXISTS.
Příkazy CREATE TRIGGER, ALTER TABLE ENABLE TRIGGER, ALTER TABLE DISABLE TRIGGER, ALTER TABLE … ADD CONSTRAINT FOREIGN KEY budou
nyní vyžadovat slabší zámek než dříve (nově ShareRowExclusive).
V plánech dotazů nyní uvidíme více podrobností o klauzuli ORDER BY
postgres=> EXPLAIN SELECT * FROM pg_class ORDER BY relpages DESC LIMIT 3; ┌─────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞═════════════════════════════════════════════════════════════════════════╡ │ Limit (cost=18.90..18.91 rows=3 width=201) │ │ -> Sort (cost=18.90..19.65 rows=301 width=201) │ │ Sort Key: relpages DESC │ │ -> Seq Scan on pg_class (cost=0.00..15.01 rows=301 width=201) │ └─────────────────────────────────────────────────────────────────────────┘ (4 rows)
Hodně se zapracovalo na pgbenchi. pgbench dnes již není pouze TPC-B benchmark, ale už je to jednodušší univerzální
prostředí pro benchmarkování. pgbench podporuje podobný makrojazyk jako psql - proměnné, jednoduché výrazy a umožňuje
provozovat jednodušší skripty. Nově příkaz \setrandom podporuje normální a Gaussovo rozdělení.
Monitoring SQL příkazů pg_stat_statements nově zobrazuje min, max, střední hodnotu a směrodatnou odchylku.
Některé zajímavé contrib moduly byly přesunuty do aplikací klienta - pg_archivecleanup, pg_test_fsync,
pgbench, pg_upgrade, pg_xlogdump.
Row Level Security - nastavení práv pro jednotlivé řádky
Za implementací RLS se skrývá ohromný boj a ohromné úsilí všech zúčastněných. Hodně dlouho trvalo nalezení shody ohledně návrhu. Hodně práce zabrala vlastní implementace - jedná se o jeden z větších patchů v historii PostgreSQL. Je tu jistá vazba na editovatelné pohledy - při použití RLS pracujeme jakoby s implicitně vytvořeným pohledem na tabulku. RLS se musí explicitně povolit pro tabulku, kde se bude aplikovat.
CREATE TABLE foo(userid regrole DEFAULT CURRENT_USER::regrole, data text);
CREATE ROLE tom LOGIN;
CREATE ROLE peter LOGIN;
GRANT ALL ON foo TO tom, peter;
INSERT INTO foo VALUES('tom'::regrole, 'Tomova data'),
('peter'::regrole,'Petrova data');
Nyní potřebuji nějak jednoduše zajistit, aby si uživatelé nepřepisovali data. Já bych si napsal trigger a asi bych si nekomplikoval život - ale ne všichni uživatelé chtějí být programátory. S RLS mám možnost deklarativního nastavení práv pomocí sady bezpečnostních politik. Nejdříve ovšem musím nad tabulkou povolit RLS:
ALTER TABLE foo ENABLE ROW LEVEL SECURITY; -- FOR ALL ve smyslu SELECT | INSERT | UPDATE | DELETE CREATE POLICY sobesam ON foo FOR ALL TO PUBLIC USING (userid = CURRENT_USER::regrole); postgres=# SELECT * FROM foo; ┌────────┬──────────────┐ │ userid │ data │ ╞════════╪══════════════╡ │ tom │ Tomova data │ │ peter │ Petrova data │ └────────┴──────────────┘ (2 rows)
Uživatel Tom uvidí pouze svoje data.
postgres=# SET role TO tom; postgres=> SELECT * FROM foo; ┌────────┬─────────────┐ │ userid │ data │ ╞════════╪═════════════╡ │ tom │ Tomova data │ └────────┴─────────────┘ (1 row) postgres=> EXPLAIN SELECT * FROM foo; ┌─────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞═════════════════════════════════════════════════════════════════╡ │ Seq Scan on foo (cost=1.01..26.89 rows=6 width=36) │ │ Filter: (userid = $0) │ │ InitPlan 1 (returns $0) │ │ -> Seq Scan on pg_authid (cost=0.00..1.01 rows=1 width=4) │ │ Filter: (rolname = "current_user"()) │ └─────────────────────────────────────────────────────────────────┘ (5 rows)
A to díky tomu, že se nejdříve aplikuje filter z RLS politiky. Využívá se mechanismu již dříve implementovaného pod názvem bezpečnostní bariéry (security bariers). Implicitní filtr zajistí, že Tom nemůže modifikovat Petrova data. Zároveň se kontroluje, jestli nově přidaná data nezmizela z dosahu uživatele - tudíž nemůže přidávat data jako Petr (tuto kontrolu lze vyblokovat, pokud chceme a máme práva).
postgres=> SELECT current_user; ┌──────────────┐ │ current_user │ ╞══════════════╡ │ tom │ └──────────────┘ (1 row) postgres=> INSERT INTO foo SELECT 16405, current_user; ERROR: new row violates WITH CHECK OPTION for "foo"
Pozor na kombinace politik. Pokud je nad tabulkou více politik, tak se používá součet, nikoliv součin politik (takže jednou politikou můžete negovat efekt jiné politiky).
CREATE POLICY muzevse_dopoledne ON foo FOR ALL TO public USING (current_time < '12:00:00');
postgres=> EXPLAIN ANALYZE SELECT * FROM foo;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on foo (cost=1.01..36.42 rows=428 width=36) (actual time=0.061..0.088 rows=4 loops=1) │
│ Filter: ((userid = $0) OR (('now'::cstring)::time with time zone < '12:00:00+02'::time with time zone)) │
│ InitPlan 1 (returns $0) │
│ -> Seq Scan on pg_authid (cost=0.00..1.01 rows=1 width=4) (actual time=0.022..0.026 rows=1 loops=1) │
│ Filter: (rolname = "current_user"()) │
│ Rows Removed by Filter: 4 │
│ Planning time: 0.710 ms │
│ Execution time: 0.179 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)
RLS je možné vypnout (pokud jste superuser) anebo pokud máte právo BYPASSRLS - nastavíte session proměnnou
set row_security to off;
Na to, jak jednoduše RLS vypadá, a jednoduše se používá, byla implementace RLS překvapivě pracná, náročná, občas i hodně emotivní (a to jsem byl nestranný pozorovatel). Ale to všechno je pryč - zůstává hotový kód.
Extenze pg_audit
Chybějící audit log byl překážkou v nasazení Postgresu v některých specifických prostředích. S extenzí pg_audit
můžeme dohledat, kdo, kdy a co dělal s databází. Loguje se do postgresového logu. S nekomplikovanou extenzí lze přesměrovat
zápis do libovolného souboru nebo systémového logu:
shared_preload_libraries = 'pg_audit' pg_audit.log = 'read, write, ddl'; 2015-05-15 11:49:25.046 CEST pavel postgres: LOG: AUDIT: SESSION,1,1,DDL,DROP TABLE,,,drop table foo;, 2015-05-15 11:49:28.291 CEST pavel postgres: LOG: AUDIT: SESSION,2,1,DDL,CREATE TABLE,,,"CREATE TABLE foo(a int, b int);", 2015-05-15 11:49:31.486 CEST pavel postgres: LOG: AUDIT: SESSION,3,1,WRITE,INSERT,,,"INSERT INTO foo VALUES(10,20);", 2015-05-15 11:49:33.446 CEST pavel postgres: LOG: AUDIT: SESSION,4,1,READ,SELECT,,,SELECT * FROM foo WHERE a = 10;,
Audit lze omezit na vybrané objekty určením role auditora. Potom budou auditovány ty objekty a ty operace, ke kterým
má práva uživatel specifikovaný konfigurační proměnnou pg_audit.role.
Změna konfigurace checkpointů
To je asi pro mne jednou z nejvýraznějších změn v konfiguraci Postgresu za poslední roky. CHECKPOINT je příkaz, který
vynutí zápis modifikovaných datových stránek v cache do datových souborů. Po checkpointu můžeme recyklovat segmenty
transakčního logu - případně redukovat transakční log. Ve starších verzích se CHECKPOINT zavolal jednak periodicky, za druhé v závislosti na provozu - po naplnění N segmentů transakčního logu - N bylo určeno konfigurační proměnnou
checkpoint_segments. Příliš nízká hodnota checkpoint_segments způsobovala časté checkpointy (redukuje úspěšnost
write cache), příliš velká pak zbytečně prodlužovala velikost transakčního logu (a tím i případnou obnovu po havárii).
Ve výsledku checkpoint_segments určovala jak frekvenci checkpointů, tak i počet rotujících segmentů transakčního logu. Databázoví administrátoři jsou zvyklí uvažovat v limitech diskového prostoru alokovaného nějakému účelu než v trochu abstraktní hodnotě checkpoint_segments. Proto došlo k redesignu. Místo jedné proměnné máme proměnné dvě: min_wal_size (určuje recyklaci souborů segmentů transakčního logu) a max_wal_size (definuje horní velikost transakčního logu, zpětně se z ní dopočítává nyní již jenom interní checkpoint_segments). Pozor - max_wal_size není nepřekročitelná hranice - při extrémní zátěži, při chybě archivace logů nebo při velké hodnotě wal_keep_segments může být překročena.
Změnili se i výchozí hodnoty: max_wal_size je 1GB ... což odpovídá dřívější hodnotě 25 v checkpoint_segments (kde výchozí hodnota byla 3), min_wal_size je 80MB (to odpovídá dřívější konfiguraci).
Pohled pg_file_settings
Pokud jste někdy řešili, co je nakonfigurováno explicitně a co je výchozí nastavení, pak pohled pg_file_settings
je tady přesně pro vás. Pohled obsahuje seznam změn v konfiguraci vůči výchozímu nastavení. U mne např.:
postgres=# SELECT * FROM pg_file_settings ; ┌───────────────────────────────────────┬────────────┬───────┬────────────────────────────┬────────────────────┐ │ sourcefile │ sourceline │ seqno │ name │ setting │ ╞═══════════════════════════════════════╪════════════╪═══════╪════════════════════════════╪════════════════════╡ │ /usr/local/pgsql/data/postgresql.conf │ 59 │ 1 │ listen_addresses │ * │ │ /usr/local/pgsql/data/postgresql.conf │ 64 │ 2 │ max_connections │ 100 │ │ /usr/local/pgsql/data/postgresql.conf │ 116 │ 3 │ shared_buffers │ 512MB │ │ /usr/local/pgsql/data/postgresql.conf │ 127 │ 4 │ work_mem │ 20MB │ │ /usr/local/pgsql/data/postgresql.conf │ 131 │ 5 │ dynamic_shared_memory_type │ posix │ │ /usr/local/pgsql/data/postgresql.conf │ 440 │ 6 │ log_lock_waits │ on │ │ /usr/local/pgsql/data/postgresql.conf │ 446 │ 7 │ log_timezone │ Europe/Prague │ │ /usr/local/pgsql/data/postgresql.conf │ 533 │ 8 │ datestyle │ iso, mdy │ │ /usr/local/pgsql/data/postgresql.conf │ 535 │ 9 │ timezone │ Europe/Prague │ │ /usr/local/pgsql/data/postgresql.conf │ 548 │ 10 │ lc_messages │ en_US.UTF-8 │ │ /usr/local/pgsql/data/postgresql.conf │ 550 │ 11 │ lc_monetary │ en_US.UTF-8 │ │ /usr/local/pgsql/data/postgresql.conf │ 551 │ 12 │ lc_numeric │ en_US.UTF-8 │ │ /usr/local/pgsql/data/postgresql.conf │ 552 │ 13 │ lc_time │ en_US.UTF-8 │ │ /usr/local/pgsql/data/postgresql.conf │ 555 │ 14 │ default_text_search_config │ pg_catalog.english │ └───────────────────────────────────────┴────────────┴───────┴────────────────────────────┴────────────────────┘ (14 rows)
psql
Začnu legráckou - stylováním unicode rámečků. Místo jednoduché čáry je možné použít zdvojenou čáru. Je to hračka, ale proč ne?
\pset linestyle unicode \pset unicode_header_linestyle double \pset border 2 postgres=# SELECT * FROM pg_user; ┌──────────┬──────────┬─────────────┬──────────┬─────────┬──────────────┬──────────┬──────────┬───────────┐ │ usename │ usesysid │ usecreatedb │ usesuper │ userepl │ usebypassrls │ passwd │ valuntil │ useconfig │ ╞══════════╪══════════╪═════════════╪══════════╪═════════╪══════════════╪══════════╪══════════╪═══════════╡ │ postgres │ 10 │ t │ t │ t │ t │ ******** │ │ │ │ pavel │ 16384 │ t │ t │ f │ f │ ******** │ │ │ └──────────┴──────────┴─────────────┴──────────┴─────────┴──────────────┴──────────┴──────────┴───────────┘ (2 rows)
Kdyby nic jiného, tak určitě v jednom má PostgreSQL navrch nad všemi svými konkurenty - má nejhezčí rámečky tabulek v textové konzoli (byl bych ale nerad, kdybyste si pamatovali 9.5ku jenom kvůli rámečkům).
Další pomocnou funkcí je rozšíření nápovědy a automatického doplňování (autocomplete) pro konfigurační proměnné konzole. Skrz tyto proměnné můžeme vyblokovat autocommit, zapnout automatické generování savepointů a rollbacků, a další. Bohužel málokdo o těchto funkcí ví (kdo čte dokumentaci?). A aby se tyto konfigurační proměnné zpřístupnily více uživatelům, přidala se podpora nápovědou (\? variables) a automatickým doplňováním.
postgres=> \set ON_ERROR_ROLLBACK interactive off on
Pro úplnost dodávám, že uživatelsky příjemná konfigurace psql s vypnutým autocommitem je následující:
\set AUTOCOMMIT off \set ON_ERROR_ROLLBACK on
Další pomůckou je nápověda názvu sloupce, pokud je ze zápisu zřejmý název tabulky.
postgres=> select namu from customers ;
ERROR: column "namu" does not exist
LINE 1: select namu from customers ;
^
HINT: Perhaps you meant to reference the column "customers"."name".
Nově je jedním z možných výstupních formátů i asciidoc:
postgres=> \pset format asciidoc Output format is asciidoc. postgres=> \o /tmp/pokus.ad postgres=> SELECT * FROM pg_proc; Time: 30.797 ms [pavel@localhost ~]$ asciidoctor -o ~/pokus.html /tmp/pokus.ad
Režim "--echo-errors" pomůže při dávkovém režimu. V předchozích verzích bylo možné zobrazit nebo skrýt prováděné SQL příkazy.
S novým režimem se zobrazí pouze ty SQL příkazy, které zhavarují. Tím by nemělo dojít k zahlcení logu, a zároveň, v případě chyby,
nemusíme lovit chybný SQL příkaz z logu Postgresu. Příkaz "\db" nově se symbolem "+" zobrazí další informace včetně velikost tablespace.
Import cizího schématu
Vytváření katalogu pro cizí datové objekty (FDW) zjednoduší možnost importu cizího schématu:
postgres=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION postgres=# CREATE SERVER remote_postgres FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'db1', use_remote_estimate 'true'); CREATE SERVER postgres=# CREATE USER MAPPING FOR PUBLIC SERVER remote_postgres OPTIONS (password ''); CREATE USER MAPPING postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER remote_postgres INTO public; IMPORT FOREIGN SCHEMA Time: 171.001 ms postgres=# SELECT * FROM customers; ┌────┬───────┬─────────┐ │ id │ name │ surname │ ╞════╪═══════╪═════════╡ │ 1 │ Pavel │ Stehule │ └────┴───────┴─────────┘ (1 row)
pg_rewind
pg_rewind působí jako totální magie - umožňuje sesynchronizovat master a slave po vzájemném rozpojení. V Postgresu slave musí
být identickou kopií masteru. Díky tomu na něj lze aplikovat transakční log z masteru - tím se přenáší změny dat z masteru na
slave a slave tak zůstává synchronizovaný s masterem. Slave je read only - nemůže na něm dojít ke změně dat. Jakmile povýšíme
slave na master, tak se zruší identita masteru a slavea (nyní také mastera) - přičemž není cesty zpět. Původní master je nyní
k nepotřebě. K tomu abychom z něj udělali slave musíme stávající data zahodit a znovu naklonovat databázi. U větších databází
to může být i docela časově náročné. Řešením tohoto problému je pg_rewind. Na základě analýzy transakčního logu zjistí, kdy
došlo k rozdělení časové linie masteru a slavea a které datové stránky se případně ještě změnily na původním masteru. Tyto
datové stránky si načte z aktuálního masteru. Pak ještě zjistí, které datové stránky se změnily na novém masteru a ty také
zkopíruje na původní (nyní již neaktivní) master. Tím dojde k sesynchronizování serverů (k vytvoření identického klonu) a po
změně konfigurace můžeme mít velice rychle (po zkopírování pouze několika MB) nový slave.
Název této utilitky je trošku klamavý, v každém případě to některým lidem může vytrhnout trn z paty.
pg_rewind existoval jako samostatná aplikace. Integrací do upstreamu se zpřístupní všem uživatelům.
Online komprimace transakčního logu
Nastavením konfigurační proměnné wal_compression na true můžeme zapnout komprimaci transakčního logu. Transakční log většinou lze efektivně komprimovat a tak za drobné zvýšení zátěže CPU můžeme výrazně snížit zátěž IO. A pokud ještě transakční logy archivujeme, tak komprimaci logu oceníme hned dvakrát. U mne na pomalém (šifrovaném) disku zvýšil výkon v pgbenchi o cca 30%.
Skutečný efekt bude silně závislý na použitém hw. Pokud někdo používá rychlý dedikovaný disk pro transakční log, tak asi žádný nárůst výkonu neuvidí. V testu používám menší databázi než mám RAM (2.5GB), tak aby byl vidět víc vliv zápisu do transakčního logu (můj notebook s pomalým IO je pro testování této funkce ideální, tak jak je naprosto nevhodný pro provoz databáze).
RAM 8GB asynchronní commit shared buffers 1GB bench scale 200 (velikost databáze 2.5GB, 20M záznamů) /usr/local/pgsql/bin/pgbench -c 12 -j 4 -T 600 -P 10 -v bench scaling factor: 200 query mode: simple number of clients: 12 number of threads: 4 duration: 600 s number of transactions actually processed: 186325 latency average: 38.650 ms latency stddev: 1678.233 ms tps = 310.455587 (including connections establishing) tps = 310.459723 (excluding connections establishing) -- komprimace logu query mode: simple number of clients: 12 number of threads: 4 duration: 600 s number of transactions actually processed: 271258 latency average: 26.873 ms latency stddev: 1543.686 ms tps = 446.488867 (including connections establishing) tps = 446.498118 (excluding connections establishing)
Z testu je vidět, že komprimace logu významně pomohla. Tato čísla se, samozřejmě, musí brát s ohledem na syntetičnost testu a specifičnost hardware.
Trochu jsem zklamaný z přijetí background workers. Zatím nevím o žádné produkčně používané extenzi, která by tuto vlastnost používala. Ale je docela dobře možné, že je tím, že je to příliš čestvá novinka, a že její autoři jsou plně vytížení implementací paralelismu do zpracování SQL příkazu. Naopak docela dobře jede FDW (Foreign Data Wrappers). Máme plně funkční nové drivery pro Oracle, MySQL, pro různé souborové formáty.
Když zpětně hodnotím uplynulý rok, je vidět kus práce. Příští rok by měl být asi o něco klidnější (všechna kontroverzní témata jsou vyřešena) a, da-li se to tak říci, přelomový díky paralelizaci výpočtu dotazu. Podpora používání více CPU pro zpracování jednoho SQL příkazu by příští rok mohla být hotová, a to si myslím, že jsem realista. Zrovna tak je vysoce reálná i integrace BDR (Bi-Directional Replication) - omezené BDR lze rozběhnout již nyní bez nutnosti patchování kódu. S trochou štěstí bychom mohli mít více sloupcové statistiky. Rozhodně je tedy na co se těšit.
