Zobrazení zdroje stránky PostgreSQL 9.5 (2015) držte si klobouky, zrychlujeme

Na 9.5 se mi hůře hledá nějaké jedno zřetelné mediálně zajímavé téma. V předchozích verzích to nebyl problém - podpora JSONu, 
podpora index only scanu, <code>LATERAL</code> joinu, DDL triggerů, JSONB - to všechno jsou jasná výrazná témata. Na vývoji 9.5 vidím
snahu o získání maxima ze stávajícího kódu, 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ší, 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ích cíle: využívání více CPU pro zpočítání jednoho SQL dotazu,
podpora logické replikace (<i>BDR</i>) a inkrementální vylepšování možností <i>foreign data wrappers</i> API. Je to ale běh na dlouhou
trať, i to i 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, <code>INSERT ON CONFLICT DO</code> 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 <code>ON CONFLICT DO</code> příkazu <code>INSERT</code>===

Přiznám se, že smekám před zarputilostí, asertivitou autora tohoto patche Peterem Geogheganem. Před několika roky přišel s nápadem
implementace <i>UPSERTu</i> (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 definovanán v ANSI SQL jako příkaz <code>MERGE</code>. 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 <i>OLTP</i> 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:
<pre>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)</pre>

Patch zavádí nový alias <code>EXCLUDED</code> (kolizní řádek, který nelze vložit). Sloučení dvou tabulek lze
zapsat docela čitelně.
<pre>
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)</pre>

Při orientačních testech merge 10K řádků do 100K řádků trval 200 ms. Totéž pomocí
[http://www.postgresql.org/docs/9.4/static/queries-with.html <i>CTE</i>] trvalo cca 270 ms. Navíc zde není možná optimalizace redukce zbytečných UPDATE řádků.
<pre>
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);
</pre>
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 <code>UPDATE</code> 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.
<pre><code>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);</code></pre>

Interní implementace je dost jednoduchá - vede na variaci nested loopu (pro větší tabulky je nezbytný index na klíči):
<pre>
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)</pre>

Implementace přes <code>UPDATE FROM</code> 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árset řádek bude nový zápis pravděpodobně bez problémů).
<pre>
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)</pre>

===Klauzule <code>FOR UPDATE SKIP LOCKED</code> příkazu <code>SELECT</code>===

Klauzuli SKIP LOCKED (přeskoč zamčené řádky) bych už pár krát použil. Existoval [http://www.depesz.com/2013/08/30/pick-a-task-to-work-on/ workaround] založený na 
využití pomocných zámků. Počínaje 9.5 máme k dispozici jednoduché čisté řešení.

<pre>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)</pre>

Klient je triviální a lze jej provozovat paralelně (alespoň tuto část) - pseudokód:
<pre>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;</pre>

===Podpora vzorkování - klauzule <code>TABLESAMPLE</code>===

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 <code>TABLESAMPLE</code>. Díky Petru Jelínkovi tuto klauzuli budeme moci používat i v Postgresu. K dispozici jsou dvě
vzorkovací metody: <code>SYSTEM</code> (na základě datových stránek, čtou se celé datové stránky) a <code>BERNOULLI</code> (na základě
pravděpodobnosti řádků (skenuje celou tabulku)). Parametrem je desetiné číslo od 0 do 100 udávající kolik procent řádků tabulky bude vráceno:
<pre>
-- 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)</pre>

Klauzule <code>REPEATABLE</code> nastavuje počáteční stav generátoru náhodných čísel (<i>seed</i>) a zajišťuje opakovatelný výsledek dotazu.
Implementace klauzule <code>TABLESAMPLE</code> je rozšiřitelná o vlastní metody vzorkování, např. o použití pouze procentuální části všech stránek, atd 

===Podpora <code>GROUPING SETS</code>===

Díky <i>GROUPING SETS</i> 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-li 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ícu, č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):
<pre>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)</pre>

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ů:
<pre>
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)</pre>

Anebo můžeme použít operátory <code>GROUPING SETS</code>, <code>CUBE</code> nebo <code>ROLLUP</code>:
<pre>
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)</pre>

Je možné dohledat i složitější kombinace - operátor <code>CUBE</code> zobrazí všechny možné:
<pre>
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)</pre>

Někdy není nutné hledat všechny možné kombinace - pokud by nám pro tento příklad stačily agregace:
<code>((name, place),(name), ())</code>, tak můžeme použít operátor <code>ROLLUP</code>, který je úspornější - vystačí
si s jedním řazením:
<pre>
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)</pre>

Nechybí podpora funkce <code>grouping()</code>, která vrací jedničku nebo nulu, podle toho, jestli se aktuální řádek obsahuje mezisoučet
pro daný sloupec:
<pre>
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)</pre>

Na implementaci <code>CUBE</code> a <code>ROLLUP</code> si museli uživatele Postgresu počkat déle než uživatelé jiných databázích
(chybějící <code>GROUPING SETS</code> někdy komplikovaly migrace z Oracle). Nyní ale <code>GROUPING SETS</code> máme a je čas se naučit je používat.

=Nové funkce=

V 9.4 se zapomělo na některé konstruktory typu <i>JSONB</i>. Počínaje 9.5 můžeme používat funkce: <code>to_jsonb()</code>,
<code>jsonb_object()</code>, <code>jsonb_build_object()</code>, <code>jsonb_build_array()</code>, <code>jsonb_agg()</code>, 
<code>jsonb_object_agg()</code>.
<pre>
postgres=> SELECT jsonb_object('{{a,1},{b,2},{3,NULL},{"def","abc"}}');
┌───────────────────────────────────────────────┐
│                 jsonb_object                  │
╞═══════════════════════════════════════════════╡
│ {"3": null, "a": "1", "b": "2", "def": "abc"} │
└───────────────────────────────────────────────┘
(1 row)
</pre>

Pro typ JSONB jsou nyní k dispozici následující operace: přidání, náhrada nebo odstranění klíče:
<pre>
-- 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)</pre>

Funkce <code>generate_series</code> 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. 
<pre>
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)</pre>

===Vyhledávání v poli===

Funkce array_position vrací pozici prvku v poli (vrací NULL, pokud pole neobsahuje prvek). <code>array_positions</code> vrátí pole všechny
pozice prvku v poli (nebo prázdné pole).
<pre>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</pre>

===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 [http://www.postgresql.org/docs/devel/static/functions-math.html <code>width_bucket</code>]:
<pre>
postgres=> SELECT width_bucket(33, 1, 100, 10);
┌──────────────┐
│ width_bucket │
╞══════════════╡
│            4 │
└──────────────┘
(1 row)
</pre>
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.
<pre>
postgres=> SELECT width_bucket(33, ARRAY[0,20,30,40,60]);
┌──────────────┐
│ width_bucket │
╞══════════════╡
│            3 │
└──────────────┘
(1 row)</pre>

Pole musí být seřazené, z důvodu rychlosti nedochází s ř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.

===<code>json(b)_strip_null()</code>, <code>jsonb_pretty()</code>===

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):
<pre>
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)</pre>

Jak je z názvu patrné, funkce <code>jsonb_pretty</code> slouží k formátování JSONB dokumentů:
<pre>
postgres=> select jsonb_pretty('{"a":10, "b":{"x":10,"y":20}}');
┌──────────────────┐
│   jsonb_pretty   │
╞══════════════════╡
│ {               ↵│
│     "a": 10,    ↵│
│     "b": {      ↵│
│         "x": 10,↵│
│         "y": 20 ↵│
│     }           ↵│
│ }                │
└──────────────────┘
(1 row)</pre>

=Změny v PL/pgSQL a v PL=

Nově také další příkazy <code>COMMENT</code>, <code>SECURITY LABEL</code>, <code>GRANT</code>/<code>REVOKE</code> 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 <code>RAISE</code> již při validaci funkce.

[https://github.com/okbob/plpgsql_check 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 <i>IO přetypování</i> - 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í oprací 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 znamý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é:
<pre>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;
$$;</pre>

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)===

<i>Transformace</i> jsou zákaznické funkce, které konvertují data mezi SQL a prostředím ve kterém se volají externí uložené procedury. V <i>ANSI SQL</i>
v části <i>SQL/MM</i> 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 <i>hstore</i> 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í: <i>hstore_plperl</i>, <i>hstore_plptyhon</i> a <i>ltree_plpython</i>. 
Po aktivaci extenze hstore_plperl se automaticky vytvoří odpovídající transformace, kterou hned můžeme začít používat:

<pre>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 ms</pre>

Ve 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 <code>ASSERT</code>. Při implementaci jsme se inspirovali Cčkovým makrem Assert. Přikaz vyhodí
nezachytitelnou vyjímku v případě nepravdivého argumentu. K této vyjímce lze připojit komentář. V odladěném kódu lze kontroly
globálně vypnout:
<pre>
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</pre>

===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 "=&gt;", který se používá v Oracle (kde se přebírá z jazyku ADA). Postgresový
zápis bude podporován nadále:
<pre>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 20</pre>

===Funkce <code>pg_event_trigger_ddl_commands()</code>===

Opět o něco jednodušší bude psaní event triggrů díky funkci <code>pg_event_trigger_ddl_commands()</code>, která vrací základní
informace o události, kdy došlo k modifikaci libovolného objektu:
<pre>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: <NULL>, name: xxx
CREATE SCHEMA</pre>

=Výkonnostní optimalizace=

Rozšiřuje se podpora index only scanu pro další typy indexů - <i>GiST</i> a <i>btree-GiST</i>, a pro další typy - <code>inet</code> a <code>range</code>.

Reimplementace lehkých zámků (<i>LWlocks</i> - 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 beží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í (kdy téměř všechen výkon se spálí v zámcích)).

K drobné optimalizaci došlo i v implementaci window funkcí v dotazu s klauzulí <code>WHERE</code>.

Na moderních CPU podporujících SSE 4.2 se použijí speciální instrukce <code>crc32b</code> a <code>crc32q</code> pro zrychlení výpočtu <i>CRC</i>. 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 pak se 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řičin pomalosti jednoho dotazu
jsme zjistili dost divné chování (jedna z těch situací, kdy si řikáte, že si z vás ty stroje dělají blázny). Zvyš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áš ["http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=30d7ae3c76d2de144232ae6ab328ca86b70e72c3 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===

[http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0b03e5951bf0a1a8868db13f02049cf686a82165 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 [http://www.slideshare.net/kaigai/gpgpu-accelerates-postgresql 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 hardwareovou 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.

===<i>BRIN</i> (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.
<pre>
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)</pre>

Velikost BRIN indexu je 48KB:
<pre>
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)</pre>

Dotaz s použitím Btree indexu je rychlejší (ale tady se dostávám už do prostoru, který je Btree doma).
<pre>
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)</pre>

Ale index má 10MB (a doba vytváření indexu je dvoj až trojnásobná).
<pre>
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)</pre>

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é <i>pages_per_range</i>. Čím více stránek je 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 <code>ALTER TABLE SET (LOGGED|UNLOGGED)</code>.
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 <code>CURRENT_USER</code> a <code>SESSION_USER</code> pro určení uživatele:
<pre>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';</pre>

Extenze <code>pgstattuple</code> byla rozšířena o funkci <code>pgstattuple_approx</code>, která je obdobou funkce <code>pgstattuple</code>.
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 <code>regclass</code>,
<code>regproc</code>, <code>regprocedure</code> a <code>regtype</code> přibyly typy <code>regrole</code> (přímo se nabízí použití pro <i>RLS</i> viz níže) a 
<code>regnamespace</code>. 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):
<pre>
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)</pre>

Možná reakci na nedávné problémy <i>ssl</i> byla implementace pohledu <code>pg_stat_ssl</code>, kde administrátor může 
vidět parametry ssl připojení:
<pre>
[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)</pre>

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:
<pre>ALTER SYSTEM RESET ALL;</pre>

Příkaz <code>vacuumdb</code> nově podporuje souběžný start a běh příkazů <code>VACUUM</code>. Pozor, neměl by se používat pro <code>VACUUM FULL</code> (skončí deadlockem).

Zjednoduší se reindexace indexů ve vybraném schématu použitím příkazu <code>REINDEX SCHEMA</code>.

Příkazy <code>CREATE TABLE AS</code>, <code>CREATE INDEX</code> a <code>CREATE MATERIALIZED VIEW</code> získali klauzuli <code>IF NOT EXISTS</code>.

Příkazy <code>CREATE TRIGGER</code>, <code>ALTER TABLE ENABLE TRIGGER</code>, <code>ALTER TABLE DISABLE TRIGGER</code>, <code>ALTER TABLE … ADD CONSTRAINT FOREIGN KEY</code> budou
nyní vyžadovat slabší zámek než dříve (nově <code>ShareRowExclusive</code>).

V plánech dotazů nyní uvidíme více podrobností o klauzuli <code>ORDER BY</code>
<pre>
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)</pre>

Hodně se zapracovalo na <code>pgbenchi</code>. <code>pgbench</code> dnes již není pouze TPC-B benchmark, ale už je to jednodušší univerzální
prostředí pro benchmarkování. <code>pgbench</code> podporuje podobný makrojazyk jako <code>psql</code> - proměnné, jednoduché výrazy a umožňuje
provozovat jednodušší skripty. Nově příkaz <code>\setrandom</code> podporuje normální a gausovo rozdělení.

Monitoring SQL příkazů <code>pg_stat_statements</code> nově zobrazuje min, max, střední hodnotu a směrodatnou odchylku.

Některé zajímavé contrib moduly byly přesunuty do aplikací klienta - <code>pg_archivecleanup</code>, <code>pg_test_fsync</code>,
<code>pgbench</code>, <code>pg_upgrade</code>, <code>pg_xlogdump</code>.

===Row Level Security - nastavení práv pro jednotlivé řádky===

Za implementací <i>RLS</i> 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.
<pre>
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');</pre>

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:
<pre>
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)</pre>

Uživatel Tom uvidí pouze svoje data. 
<pre>
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)</pre>

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 (<i>security bariers</i>). Implicitní filtr zajistí, že Tom nemůže modifikovat Petrova data.
Zároveň se kontroluje, jestli nově přidaná data nezmizela s dosahu uživatele - tudíž nemůže přidávat data jako Petr
(tuto kontrolu lze vyblokovat, pokud chceme a máme práva).
<pre>
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"
</pre>
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).
<pre>
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)</pre>

RLS je možné vypnout (pokud jste superuser) anebo pokud máte právo <code>BYPASSRLS</code> - nastavíte session proměnnou
<pre>
set row_security to off;
</pre>
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šecho je pryč - zůstává hotový kód.

===Extenze <code>pg_audit</code>===
<blockquote style="background-color: #eeeee0; border: solid thin lightgrey; padding:15px;  width:70%;">
Tato extenze byla vyjmuta a bude distribuovaná samostatně
</blockquote>
Chybějící audit log byl překážkou v nasazení Postgresu v některých specifických prostředích. S extenzí <code>pg_audit</code>
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:
<pre>
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;,<not logged>
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);",<not logged>
2015-05-15 11:49:31.486 CEST pavel postgres: LOG:  AUDIT: SESSION,3,1,WRITE,INSERT,,,"INSERT INTO foo VALUES(10,20);",<not logged>
2015-05-15 11:49:33.446 CEST pavel postgres: LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,SELECT * FROM foo WHERE a = 10;,<not logged></pre>

Audit lze omezit na vybrané objekty určením role auditora. Potom budou aduditovány ty objekty a ty operace, ke kterym
má práva uživatel specifikovaný konfigurační proměnnou <code>pg_audit.role</code>.

===Změna konfigurace checkpointů===

To je asi pro mne jednou z nejvýraznějších změn v konfiguraci Postgresu za poslední roky. <code>CHECKPOINT</code> 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 <code>CHECKPOINT</code> zavolal jednak periodicky,
druhak 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 <i>checkpoint_segments</i> 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ě:
<i>min_wal_size</i> (určuje recyklaci souborů segmentů transakčního logu) a <i>max_wal_size</i> (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ě <i>wal_keep_segments</i> 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 <code>pg_file_settings</code>===

Pokud jste někdy řešili, co je nakonfigurováno eplicitně, a co je výchozí nastavení, pak pohled <code>pg_file_settings</code>
je tady přesně pro Vás. Pohled obsahuje seznam změn v konfiguraci vůči výchozímu nastavení. U mne např.:
<pre>
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)</pre>
   
===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?
<pre>
\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)</pre>

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. 
<pre>
postgres=> \set ON_ERROR_ROLLBACK 
interactive  off          on            
</pre>
Pro úplnost dodávám, že uživatelsky příjemná konfigurace psql s vypnutým autocommitem je následující:
<pre>
\set AUTOCOMMIT off
\set ON_ERROR_ROLLBACK on
</pre>
Další pomůckou je nápověda názvu sloupce, pokud je ze zápisu zřejmý název tabulky.
<pre>
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".
</pre>
Nově je jedním z možných výstupních formátů i [http://www.methods.co.nz/asciidoc/chunked/ch23.html asciidoc]:
<pre>
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
</pre>
Režim <code>"--echo-errors"</code>  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 <code>"\db"</code> nově se symbolem <code>"+"</code> zobrazí další informace včetně velikost tablespace.

===Import cizího schématu===

Vytváření katalogu pro cizí datové objekty (<i>FDW</i>) zjednoduší možnost importu cizího schématu:
<pre>
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)</pre>

===pg_rewind===

<code>pg_rewind</code> 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 <code>pg_rewind</code>. 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. 
<code>pg_rewind</code> 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).
<pre>
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)</pre>

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.

=Závěr=

Trochu jsem zklamaný z přijetí <i>background workers</i>. 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.

----------
<DPL>category=Verze PostgreSQL</DPL>
[[Category:Články]]
[[Category:Verze PostgreSQL]]