PostgreSQL 10 (2017) - drsně rozběhnutý slon: Porovnání verzí

Z PostgreSQL
Přejít na: navigace, hledání
(Zrušena verze 3636 od uživatele Pavel (diskuse))
 
Řádka 1: Řádka 1:
=PostgreSQL 11=
+
<h1>PostgreSQL 10</h1>
*Autor: Pavel Stěhule, 2018*
+
  
Jak už to u každoročních vydání PostgreSQL bývá, k uživatelům se dostává mix několika větších nových vlastností doplněných desítkami menších novinek a vylepšení. Tým vývojářů je stabilní, stabilní je i vývojový proces, který se překvapivě přísně dodržuje. Z těch výraznějších novinek bych především zmínil [https://en.wikipedia.org/wiki/LLVM JIT] pro výrazy, a příkaz <code>CALL</code>, a s tím spojené procedury s možností explicitně řídit transakce. Integrace JIT (ve výchozí konfiguraci vypnuté) je pro mne překvapením - v databázovém světě se jedná o hodně progresivní krok (o to větším překvapením je to v PostgreSQL, kde jsou vývojáři hodně konzervativní) a bezpochyby je PostgreSQL první open source SQL databází, která tuto technologii integruje. Pro uživatele budou zajímavé i další nové funkce - ať už ve využití více CPU pro jeden příkaz - paralel index scan, paralel hash join, postupné vylepšování partitioningu - defaultní partitions, hash partitioning, partition join, podpora přesunu záznamu mezi partitions při <code>UPDATE</code> atd.  
+
Po roce tu máme opět novou verzi PostgreSQL, a to verzi 10. Rok vývoje proběhl relativně v klidu - dodržel se časový plán, takže zůstalo dost času na stabilizaci a dokonce i na dovolené top vývojářů. Zatím skoro pokaždé jsem mohl napsat, že nová verze obsahuje více novinek, a pracovalo na ní více vývojářů než na předchozí verzi. Nic se nezměnilo. Na Postgres se nabalují další a další projekty, a celý projekt se dostává do, pro mně, neuvěřitelného tempa. Je vidět, že infrastruktura, komunita funguje velice dobře. Neřeší se nesmysly - vše je soustředěné na práci a na konečný výsledek. Jako každý rok mohu napsat, že práce na některých funkcích finišují a na jiných teprve začínají. Už je vidět hodně velký pokrok v podpoře použití více CPU pro jeden dotaz. Na hodně vysoké úrovni je také optimalizace přístupu k cizím zdrojům ([https://wiki.postgresql.org/wiki/Foreign_data_wrappers <i>FDW</i>]). Desítka přináší novou implementaci partitioningu a s desítkou je také k dispozici logická replikace.
  
Poznámka autora - pro zajímavost a pro názornost budu uvádět i odkazy na jednotlivé patche. Běžný čtenář se jim určitě věnovat nemusí. Pro někoho může být zajímavý styl a způsob kódování PostgreSQL.
+
Touto verzí se přechází na nový systém číslování. Následující verze bude 11, další 12, atd. Jedná se pouze o kosmetickou změnu - nový způsob verzování je jednoznačný a není k němu nutná diskuze, zda-li má nebo nemá smysl zvýšit číslo linie. Vzhledem k způsobu vývoje bylo první číslo nic neříkající - rozdíl mezi 9.5 a 9.6 je výrazně větší než např. rozdíl mezi 7.4 a 8.0.
==psql==
+
Nikdy jsem moc nechápal uživatele, kteří měli problém s ukončením <code>psql</code>. Obvyklé <code>^d</code> na Linuxu funguje 100% (a windows jsem nikdy neřešil). Když si ale vzpomenu na svoje začátky, kdy jsem téměř vždy killoval <code>vi</code>, tak uznávám, že každý musel někdy začít a začátky nebývají lehké. Od nové verze bude <code>psql</code> reagovat i na slova <code>quit</code> a <code>exit</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=df9f599bc6f14307252ac75ea1dc997310da5ba6 p]</small></sup>. Tato slova zafungují, pouze pokud jsou na začátku řádku jednořádkového příkazu.
+
  
Od minulé verze je možné v [https://www.postgresql.org/docs/11/static/app-psql.html <code>psql</code>] podmínit zpracování příkazů příkazem <code>\if \elif \else \endif</code>. Jelikož se <code>psql</code> běžně používá pro deployment v prostředích s různými verzemi PostgreSQL, je praktické nějak jednoduše odlišit provádění kódu podle verze PostgreSQL. K tomu by měla posloužit sada nových psql proměnných: <code>SERVER_VERSION_NAME</code>, <code>SERVER_VERSION_NUM</code>, <code>VERSION</code> a <code>VERSION_NAME</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9ae9d8c1549c384dbdb8363e1d932b7311d25c56 p]</small></sup>. K dokonalosti chybí možnost vyhodnotit základní výrazy pouze v <code>psql</code> (bez komunikace se serverem). Pro lokální vyhodnocení výrazu lze výraz zapsat mezi obrácené apostrofy. Pak se výraz vyhodnotí lokálně shellem:
+
<h2>Přihlašování do PostgreSQL</h2>
 +
 
 +
Nově lze do connection stringu zadat vícenásobné URI, případně více serverů (každý server může mít ještě specifikován port). V případě, že se nepovede připojení na první server, klient se zkusí připojit na druhý, atd . Do connection stringu lze ještě přidat požadavek na na připojení typu read/write klíčem <code>target_session_attrs=read-write</code>. Tím se zablokuje připojení na repliky, které jsou read only, a kde by aplikace mohla mít problémy.  
 +
 
 +
Používání md5 k ověřování přístupu se dnes již nepovažuje za bezpečné a implementují se modernější metody. V 10ce je to implementace <i>SCRAM-SHA-256</i>:
 
<pre>
 
<pre>
\set myvar 20
+
-- v pg_hba.conf musí být pro uživatele foorole nastavena ověřovací metoda scram
\if `[ :myvar -le 10 ] && { echo yes; } || echo no;`
+
SET password_encryption = 'scram';
\echo "mensi rovno"
+
CREATE ROLE foorole PASSWORD 'foo';
\else
+
\echo "vetsi"
+
\endif
+
 
</pre>
 
</pre>
  
To ovšem nemusí (a aktuálně nemůže) fungovat na windows. Samozřejmě, že jakýkoliv validní výraz lze vyhodnotit na straně serveru příkazem <code>\gset</code>:
+
<h2>Změny v psql</h2>
 +
 
 +
Konečně je v <code>psql</code> možnost použít základní jednoduché skriptování - příkazy <code>\if</code>, <code>\elif</code>, <code>\else</code> a <code>\endif</code>. Tento programovací jazyk není určený k psaní aplikací. Je určený k psaní konfiguračních skriptů (např. uložených v <code>.psqlrc</code>), alter skriptů nebo k psaní jednodušších testů a testovacích scénářů.
 
<pre>
 
<pre>
SELECT :SERVER_VERSION_NUM >= 11000 AS ver_min_11 \gset
+
SELECT pg_is_in_recovery() as is_slave \gset
\if :ver_min_11
+
\if :is_slave
\echo "Server je 11 nebo novejsi"
+
    \set PROMPT1 '\nslave %x$ '
 
\else
 
\else
\echo "Server je starsi nez verze 11"
+
    \set PROMPT1 '\nmaster %x$ '
 
\endif
 
\endif
 
</pre>
 
</pre>
 +
A jelikož je k dispozici alespoň základní logika, tak se také hodí nové vestavěné psql proměnné popisující verzi klienta a verzi serveru:
 +
<pre>
 +
SERVER_VERSION_NAME = '10rc1'
 +
SERVER_VERSION_NUM = '100000'
 +
VERSION = 'PostgreSQL 10rc1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.1 20170915 (Red Hat 7.2.1-2), 64-bit'
 +
VERSION_NAME = '10rc1'
 +
VERSION_NUM = '100000'
 +
</pre>
 +
psql zatím nemá k dispozici vlastní interpret výrazů - veškeré kalkulace se musí řešit přes server a SQL nebo přes shell operačního systému. Text mezi zpětnými apostrofy se vyhodnocuje shellem - nově lze uvnitř shellového výrazu použít i psql proměnné:
 +
<pre>
 +
postgres=# \set a 10
 +
postgres=# \set b 20
 +
postgres=# \set c `echo $((:a + :b))`
 +
postgres=# \echo :c
 +
30
 +
</pre>
 +
Další poměrně viditelnou změnou je rozšíření výpisu doby běhu dotazu (původně pouze ms) o zobrazení ve formátu intervalu:
 +
<pre>
 +
postgres=# SELECT pg_sleep(160);
 +
pg_sleep
 +
----------
 +
 +
(1 row)
  
V nové verzi je možné detekovat existenci klientské proměnné zápisem <code>:{?nazev}</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d57c7a7c506276597af619bdb8c62fa5b592745a p]</small></sup>. Nově také můžeme používat proměnné zobrazující status předchozího SQL příkazu: <code>ERROR</code>, <code>LAST_ERROR_MESSAGE</code>, <code>LAST_ERROR_STATE</code> or <code>SQLSTATE</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=69835bc8988812c960f4ed5aeee86b62ac73602a p]</small></sup>.
+
Time: 160101,032 ms (02:40,101)
 +
</pre>
 +
Možná jste někdy použili příkaz <code>\g</code> - je náhradou ke středníku a používá se pro spuštění příkazu. V posledních několika letech vzniklo několik odvozených příkazů - <code>\gset</code>, <code>\gexec</code>. Verze 10 přidává příkaz <code>\gx</code>, který spustí dotaz a výsledek zobrazí v expandovaném formátu (je to zkratka za <code>\x</code> příkaz <code>\g\x</code>). Ve verzi 11 bude k dispozici příkaz <code>\gdesc</code>, který zobrazí formát výsledku dotazu (sloupce a jejich datové typy).
  
V <code>psql</code> nikdy silný skriptovací jazyk nebude. Cokoliv složitějšího vždy bude nutné napsat v Perlu, Pythonu, ... . Je ale snahou, aby se v <code>psql</code> daly jednoduše řešit úlohy obvyklé při testování nebo nasazování.
+
<h2>Změny v SQL</h2>
  
Příkaz <code>\gdesc</code>] zobrazí popis výsledku dotazu (bez jeho provedení)<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=49ca462eb165dea297f1f110e8eac064308e9d51 p]</small></up>:
+
Novinek ohledně SQL je minimum a jsou to spíš takové vychytávky zpříjemňující život (PostgreSQL má téměř kompletní podporu ANSI/SQL, takže není moc co přidávat).
<pre>
+
postgres=# SELECT oid, relname, relkind FROM pg_class \gdesc
+
┌─────────┬────────┐
+
│ Column  │  Type  │
+
╞═════════╪════════╡
+
│ oid    │ oid    │
+
│ relname │ name  │
+
│ relkind │ "char" │
+
└─────────┴────────┘
+
(3 rows)
+
  
-- může být zajímavé pro výrazy
+
Zřejmě nejdůležitější novinkou je podpora tzv ANSI/SQL [https://blog.2ndquadrant.com/postgresql-10-identity-columns/ <i>identity sloupců</i>] - tyto sloupce obsahují unikátní hodnoty a jejich obsah a konzistenci řeší samotná databáze. Určitě znáte typ <code>serial</code> a jeho implementaci v Postgresu. Identity sloupce jsou svou funkcí (a vlastně i implementací) použití typu <code>serial</code> hodně podobné. Pro identity sloupce se používá ANSI/SQL zápis a vůči řešení s typem serial jsou téměř všechny hraniční případy dobře pokryty (pokusy o změny hodnoty, pokusy o přímé vložení hodnoty, vytváření klonu tabulky atd):
postgres=# SELECT 10 + 20, 10 + 20.1 \gdesc
+
<pre>
┌──────────┬─────────┐
+
postgres=# CREATE TABLE itest1 (a int GERERATED ALWAYS AS IDENTITY, b text);
Column  │  Type  │
+
CREATE TABLE
╞══════════╪═════════╡
+
postgres=# INSERT INTO itest1 VALUES(default,'aaa');
│ ?column? │ integer │
+
INSERT 0 1
│ ?column? │ numeric │
+
postgres=# INSERT INTO itest1 VALUES(2,'aaa');
└──────────┴─────────┘
+
ERROR:  cannot insert into column "a"
(2 rows)
+
DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS.
 +
HINT: Use OVERRIDING SYSTEM VALUE to override.
 +
postgres=# UPDATE itest1 SET a = a;
 +
ERROR:  column "a" can only be updated to DEFAULT
 +
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
 +
postgres=# INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz');
 +
INSERT 0 1
 +
</pre>
 +
Jelikož identity sloupce mohou být různých typů (int,bigint, smallint), bylo nutné rozšířit podporu sekvencí o tuto možnost. Výsledkem je příkaz <code>CREATE SEQUANCE AS type</code>:
 +
<pre>
 +
CREATE SEQUENCE sequence_test5 AS integer;
 +
CREATE SEQUENCE sequence_test6 AS smallint;
 +
CREATE SEQUENCE sequence_test7 AS bigint;
 
</pre>
 
</pre>
==Prostředí uložených procedur==
+
Poslední příjemnou novinkou je možnost neuvádět parametry funkce v DDL příkazech (<code>ALTER FUNCTION</code>, <code>DROP FUNCTION</code>) v případě, že funkce není přetížená (její název je unikátní). Odstraňování funkcí (příkaz <code>DROP FUNCTION</code>) s více parametry byl občas boj. Nyní to bude výrazně jednodušší (což u mazání zase nemusí být vždy výhoda).
===Procedury - příkaz <code>CREATE PROCEDURE</code>===
+
  
Ve většina databází, kde byly implementovány tzv uložené procedury, se rozlišuje mezi funkcemi a procedurami. Rozdíl není pouze v zápisu. Typicky, ne nutně, se funkce používají v příkazu <code>SELECT</code>, a nelze v nich modifikovat obsah databáze. Procedury jsou aktivované příkazem <code>CALL</code>, nelze je používat v SELECTech, a kód v nich může modifikovat databázi, případně explicitně řídit transakce. Implementace uložených procedur v PostgreSQL je tou výjimkou potvrzující pravidlo. Až do verze 11, programátoři v Postgresu mohli použít pouze funkce (ovšem bez většiny omezení funkcí v ostatních db). Pokud byl kód psán přímo pro Postgres, tak odlišný model (pouze funkce versus procedury a funkce) nepůsobil žádné problémy. Když se kód portoval z jiných databází, tak už s tím občas problémy byly, a občas se část kódu musela přestěhovat do aplikace. Počínaje verzí 11 bude možné i v PostgreSQL psát procedury<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e4128ee767df3c8c715eb08f8977647ae49dfb59 p]</small></sup> a v těchto procedurách explicitně volat příkazy <code>COMMIT</code> a <code>ROLLBACK</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8561e4840c81f7e345be2df170839846814fa004 p]</small></sup> (s několika omezeními - např. transakce je možné kontrolovat, pouze pokud prováděný kód byl aktivován pouze příkazem <code>CALL</code>). Procedury mohou mít <code>IN</code> a <code>INOUT</code> parametry<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=33803f67f1c4cb88733cce61207bbf2bd5b599cc p]</small></sup>. Koncepčně je návrh procedur v Postgresu podobný Oracle případně DB2.  
+
Pokud používáte editovatelné pohledy (updateable views) nebo pohledy s <code>INSTEAD OF</code> triggerem, tak pro Vás může být zajímavá informace, že pro import do těchto pohledů lze od této verze použít i příkaz <code>COPY</code>.
  
 +
<h2>Nové datové typy a funkce</h2>
 +
 +
Mám rád XML a rád jej v PostgreSQL používám. PostgreSQL má perfektní funkce pro [https://www.postgresql.org/docs/current/static/functions-xml.html generování XML] - <code>XMLELEMENT</code>, <code>XMLFOREST</code>, ... Horší to bylo s parsováním XML - k dispozici byla pouze funkce <code>XPATH</code>, která samozřejmě, že fungovala, ale používala se při trochu složitější práci krkolomně. Raději jsem si napsal tabulkovou funkci v <i>PLPerlu</i> nebo <i>PLPythonu</i>, použil XML reader API, a funkci <code>XPATH</code> jsem se vyhnul. To už je minulost. Verze PostgreSQL přináší podporu SQL/XML funkce <code>XMLTABLE</code>. S touto funkcí lze velkou většinu XML dokumentů převést na tabulku:
 
<pre>
 
<pre>
CREATE OR REPLACE PROCEDURE foo(a int, INOUT b int, INOUT c int)
+
CREATE TABLE xmldata AS SELECT
AS $$
+
xml $$
BEGIN
+
&lt;ROWS&gt;
   b := 10 * a;
+
   &lt;ROW id="1"&gt;
   c := 10 + a;
+
    &lt;COUNTRY_ID&gt;AU&lt;/COUNTRY_ID&gt;
END
+
    &lt;COUNTRY_NAME&gt;Australia&lt;/COUNTRY_NAME&gt;
$$ LANGUAGE plpgsql;
+
   &lt;/ROW&gt;
 +
  &lt;ROW id="5"&gt;
 +
    &lt;COUNTRY_ID&gt;JP&lt;/COUNTRY_ID&gt;
 +
    &lt;COUNTRY_NAME&gt;Japan&lt;/COUNTRY_NAME&gt;
 +
    &lt;PREMIER_NAME&gt;Shinzo Abe&lt;/PREMIER_NAME&gt;
 +
    &lt;SIZE unit="sq_mi"&gt;145935&lt;/SIZE&gt;
 +
  &lt;/ROW&gt;
 +
  &lt;ROW id="6"&gt;
 +
    &lt;COUNTRY_ID&gt;SG&lt;/COUNTRY_ID&gt;
 +
    &lt;COUNTRY_NAME&gt;Singapore&lt;/COUNTRY_NAME&gt;
 +
    &lt;SIZE unit="sq_km"&gt;697&lt;/SIZE&gt;
 +
  &lt;/ROW&gt;
 +
&lt;/ROWS&gt;
 +
$$ AS data;
  
-- neinteraktivní volání
+
SELECT xmltable.*
postgres=# DO $$
+
  FROM xmldata,
DECLARE b int; c int;
+
      XMLTABLE('//ROWS/ROW'
BEGIN
+
                PASSING data
  CALL foo(12, b, c);
+
                COLUMNS id int PATH '@id',
  RAISE NOTICE 'b: %, c: %', b, c;
+
                        ordinality FOR ORDINALITY,
END;
+
                        "COUNTRY_NAME" text,
$$;
+
                        country_id text PATH 'COUNTRY_ID',
NOTICE:  b: 120, c: 22
+
                        size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
DO
+
                        size_other text PATH
 +
                            'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
 +
                        premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;
  
-- interaktivní volání
+
id | ordinality | COUNTRY_NAME | country_id | size_sq_km |  size_other  | premier_name 
postgres=# CALL foo(13, NULL, NULL);
+
----+------------+--------------+------------+------------+--------------+---------------
┌─────┬────┐
+
  1 |          1 | Australia    | AU        |            |              | not specified
│  b
+
  5 |          2 | Japan        | JP        |            | 145935 sq_mi | Shinzo Abe
╞═════╪════╡
+
  6 |          3 | Singapore    | SG        |        697 |              | not specified
│ 130 │ 23 │
+
</pre>
└─────┴────┘
+
Pracuje se i na podpoře typů JSON, Jsonb. První novinkou je možnost vícenásobného mazání klíčů (nemusí se klíče odstraňovat v cyklu, a tudíž promazání může být výrazně rychlejší):
 +
<pre>
 +
select '{"a":1 , "b":2, "c":3}'::jsonb - '{c,b}'::text[];
 +
  ?column?
 +
----------
 +
{"a": 1}
 
(1 row)
 
(1 row)
 
</pre>
 
</pre>
 
+
Funkce <code>json_populate_record</code> a <code>json_to_record</code> dokaží pracovat i s dokumenty obsahující více úrovní nebo obsahující pole:
===Optimalizace práce s kompozitními typy v PL/pgSQL===
+
Po klidných letech jsme se v [https://www.postgresql.org/docs/11/static/plpgsql.html PL/pgSQL] dočkali výraznější revize kódu pro práci s kompozitními proměnnými. Do verze 11 v PL/pgSQL existovaly statické kompozitní proměnné (tzv ROW type) a dynamické kompozitní proměnné (tzv RECORD type). Důvod pro toto dělení byl historický - ROW typy byly implementovány jako první, a v Postgresu zůstávaly kvůli větší rychlosti práce s proměnnými tohoto typu. Většina ROW types vychází z tabulkek. Jejich metadata se uloží do cache při prvním spuštění funkce. Bohužel při změně tabulky se tato část cache neaktualizuje a nekonzistence cache může způsobit pád funkce v PL/pgSQL. To byl hlavní důvod proč se po změnách struktur tabulek doporučovalo zavřít všechny spojení do Postgresu. Od verze 11 se staré ROW typy nepoužívají. Poměrně velkým refaktoringem se pořešily výkonnostní problémy RECORD typů a tudíž není důvod proč používat ROW type proměnné<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=4b93f57999a2ca9b9c9e573ea32ab1aeaa8bf496 p]</small></sup>. Měly by se tím redukovat poinstalační provozní problémy navázané na změny struktury tabulek.
+
 
+
Díky nové implementaci kompozitních typů bylo možné jednoduše přidat možnost jednoduše definovat výchozí hodnoty i pro kompozitní proměnné (dále je možné nastavit zda-li má být proměnná konstantní, případně <code>NOT NULL</code>)<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f9263006d871d127794a402a7bef713fdd882156 p]</small></sup>.
+
 
+
==Partitioning==
+
Oblastí, kde je v PostgreSQL 11 nejviditelnější pokrok, je [https://cs.wikipedia.org/wiki/Partition_(datab%C3%A1ze) <i>partitioning</i>]. Ve verzi 11 se navazuje na nový partitioning z verze 10 a přidávají se nové funkce nebo odstraňují některé limity. Měl by se zrychlit <code>INSERT</code> do partition, a také se změnil algoritmus (<i>partition pruning</i>) pro identifikaci partitions se kterými se bude v SQL příkazu pracovat<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9fdb675fc5d2de825414e05939727de8b120ae81 p]</small></sup>.
+
 
+
Pro mne asi nejzajímavější vlastností je možnost definovat defaultní partition<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=6f6b99d1335be8ea1b74581fc489a97b109dd08a p]</small></sup> a automatický přesun záznamu mezi partitions<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=2f178441044be430f6b4d626e4dae68a9a6f6cec p]</small></sup>. Defaultní partition se použije, pokud neexistuje odpovídající cílová partition:
+
 
<pre>
 
<pre>
CREATE TABLE data(a text, vlozeno date) PARTITION BY RANGE(vlozeno);
+
select *
CREATE TABLE data_2016 PARTITION OF data FOR VALUES FROM ('2016-01-01') TO ('2016-12-31');
+
  from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)
CREATE TABLE data_2017 PARTITION OF data FOR VALUES FROM ('2017-01-01') TO ('2017-12-31');
+
CREATE TABLE data_other PARTITION OF DATA DEFAULT;
+
  
postgres=# INSERT INTO data VALUES('hello', '2015-06-08');
+
a |    b    |    c    | d |      r
INSERT 0 1
+
---+---------+---------+---+---------------
postgres=# SELECT * FROM data_other ;
+
1 | [1,2,3] | {1,2,3} |   | (123,"a b c")
┌───────┬────────────┐
+
</pre>
  a   │  vlozeno  │
+
Výraznější změny v podpoře JSONu, Jsonb bychom se měli dočkat ve verzi 11, která by měla obsahovat podporu standardu SQL/JSON.
╞═══════╪════════════╡
+
│ hello │ 2015-06-08 │
+
└───────┴────────────┘
+
(1 row)
+
  
postgres=# UPDATE data SET a = 'Hi' WHERE vlozeno = '2015-06-08';
+
Funkce <code>to_date</code>, <code>to_timestamp</code> byly tolerantní vůči nevalidnímu zápisu datumu (např. bylo možné zadat číslo dne 40). Předpokládalo se, že tato relativně stará implementace věrně emuluje Oracle a z důvody zachování kompatibility se na tyto funkce nesahalo. Poměrně nedávno se udělaly nové testy na Oracle, a zjistilo se, že nevalidní datum Oracle nedovolí, a že implementace v Postgresu je postavená na chybné tradici. Ve verzi 10 jsou tyto funkce restriktivní a nedovolí zadat na vstupu nevalidní datum. To u některých aplikací může způsobit problémy s kompatibilitou.
UPDATE 1
+
postgres=# SELECT * FROM data_other ;
+
┌────┬────────────┐
+
│ a  │  vlozeno  │
+
╞════╪════════════╡
+
│ Hi │ 2015-06-08 │
+
└────┴────────────┘
+
(1 row)
+
  
postgres=# UPDATE data SET a = 'Nazdar', vlozeno = '2017-08-23' WHERE vlozeno = '2015-06-08';
+
V extenzi <code>pgcrypto</code> je nová funkce <code>pg_strong_random()</code> určená pro generování silných náhodných čísel pro zabezpečení. K dispozici je nový datový typ <code>macaddr8</code>. Extenze <code>contrib/btree_gist</code>, která obsahuje gist indexy pro některé speciální datové typy nyní indexem podporuje i datový typ <code>uuid</code>.
UPDATE 1
+
postgres=# SELECT * FROM data_other ;
+
┌───┬─────────┐
+
│ a │ vlozeno │
+
╞═══╪═════════╡
+
└───┴─────────┘
+
(0 rows)
+
  
postgres=# SELECT * FROM data_2017;
+
<h2>Prostředí uložených procedur</h2>
┌────────┬────────────┐
+
│  a    │  vlozeno  │
+
╞════════╪════════════╡
+
│ Nazdar │ 2017-08-23 │
+
└────────┴────────────┘
+
(1 row)
+
</pre>
+
  
V <code>psql</code> bylo vylepšeno zobrazení struktury partitiovaných tabulek<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=05b6ec39d72f7065bb5ce770319e826f1da92441 p]</small></sup>:
+
Co se týče uložených procedur, tak ve verzi 10 je pouze jedna, ale zato docela významná nová funkce. V statement triggrech lze přistupovat k tzv změnovým tabulkám <code>NEW</code> a <code>OLD</code>, které obsahují řádky před a po provedení operace. Bez těchto tabulek byly statement triggery do jisté míry nepoužitelné. Existující workaroundy byly buďto nespolehlivé nebo pomalé, nebo jste byli nuceni napsat trigger v C. Osobně jsem statement trigger nikdy na nic nepotřeboval - lépe se mi pracuje s řádkovými (row) triggery. Neportoval jsem ale aplikace, kde by se tyto triggery používaly (např. v MSSQL výlučně). To dosud znamenalo téměř kompletní přepis odpovídající logiky. S novými změnovými tabulkami je portace takových triggerů výrazně jednodušší.
 
<pre>
 
<pre>
postgres=# \d data
+
CREATE FUNCTION transition_table_level1_ri_parent_del_func()
                Table "public.data"
+
  RETURNS TRIGGER
┌─────────┬──────┬───────────┬──────────┬─────────┐
+
  LANGUAGE plpgsql
│ Column  │ Type │ Collation │ Nullable │ Default │
+
AS $$
╞═════════╪══════╪═══════════╪══════════╪═════════╡
+
  DECLARE n bigint;
│ a       │ text │          │          │        │
+
  BEGIN
│ vlozeno │ date │          │          │        │
+
    PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no;
└─────────┴──────┴───────────┴──────────┴─────────┘
+
    IF FOUND THEN
Partition key: RANGE (vlozeno)
+
       RAISE EXCEPTION 'RI error';
Number of partitions: 3 (Use \d+ to list them.)
+
    END IF;
 +
    RETURN NULL;
 +
  END;
 +
$$;
  
postgres=# \d+ data
+
CREATE TRIGGER transition_table_level1_ri_parent_del_trigger
                                    Table "public.data"
+
  AFTER DELETE ON transition_table_level1
┌─────────┬──────┬───────────┬──────────┬─────────┬──────────┬──────────────┬─────────────┐
+
  REFERENCING OLD TABLE AS p
│ Column  │ Type │ Collation │ Nullable │ Default │ Storage  │ Stats target │ Description │
+
  FOR EACH STATEMENT EXECUTE PROCEDURE
╞═════════╪══════╪═══════════╪══════════╪═════════╪══════════╪══════════════╪═════════════╡
+
    transition_table_level1_ri_parent_del_func();
│ a      │ text │          │          │        │ extended │              │            │
+
│ vlozeno │ date │          │          │        │ plain    │              │            │
+
└─────────┴──────┴───────────┴──────────┴─────────┴──────────┴──────────────┴─────────────┘
+
Partition key: RANGE (vlozeno)
+
Partitions: data_2016 FOR VALUES FROM ('2016-01-01') TO ('2016-12-31'),
+
            data_2017 FOR VALUES FROM ('2017-01-01') TO ('2017-12-31'),
+
            data_other DEFAULT
+
 
</pre>
 
</pre>
S vyjímkou přesunu záznamu do jiné partition je podporovaný i merge - v PostgreSQL příkaz <code>INSERT ON CONFLICT</code>. Vždy by měla být funkční varianta <code>INSERT ON CONFLICT DO NOTHING</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=87c37e3291cb75273ccdf4645b9472dd805c4493 p]</small></sup>. Příkaz <code>INSERT ON CONFLICT DO UPDATE</code> by měl fungovat se zmíněným omezením<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=555ee77a9668e3f1b03307055b5027e13bf1a715 p]</small></sup>.
+
Teoreticky by se <i>statement triggery</i> daly použít pro kontrolu referenční integrity (PostgreSQL používá řádkové systémové triggery). Když o tom tak přemýšlím, tak si nejsem jistý jestli by to byla velká výhra. Nyní má kontrola RI dost velkou režii, ale případný problém se identifikuje rychle a operace, která by vedla k porušení RI se okamžitě přeruší. Se statement triggery by odpadla režie RI, ale import by mohl pokračovat až do konce a na závěr by spadnul na triggerech. Ledaže by se ještě implementovala proměnná udávající maximální velikost změnové tabulky, která by inicializovala předčasnou aktivaci statement triggerů. Pak by to mohlo fungovat. O ničem takovém ale nevím.
  
Novinkou je základní podpora hash partitioningu<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1aba8e651ac3e37e1d2d875842de1e0ed22a651e p]</small></sup>:
+
Přístup k těmto změnovým tabulkám je i v triggerech napsaných v Perlu nebo Pythonu. Z pohledu kódu jsou to tabulky jako všechny jiné:
 
<pre>
 
<pre>
CREATE TABLE pht1 (a int, b int, c text) PARTITION BY HASH(c);
+
CREATE FUNCTION transition_table_test_f() RETURNS trigger LANGUAGE plperl AS
CREATE TABLE pht1_p1 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
+
$$
CREATE TABLE pht1_p2 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
+
    my $cursor = spi_query("SELECT * FROM old_table");
CREATE TABLE pht1_p3 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
+
    my $row = spi_fetchrow($cursor);
 +
    defined($row) || die "expected a row";
 +
    elog(INFO, "old: " . $row->{id} . " -> " . $row->{name});
 +
    my $row = spi_fetchrow($cursor);
 +
    !defined($row) || die "expected no more rows";
 +
 
 +
    my $cursor = spi_query("SELECT * FROM new_table");
 +
    my $row = spi_fetchrow($cursor);
 +
    defined($row) || die "expected a row";
 +
    elog(INFO, "new: " . $row->{id} . " -> " . $row->{name});
 +
    my $row = spi_fetchrow($cursor);
 +
    !defined($row) || die "expected no more rows";
 +
 
 +
    return undef;
 +
$$;
 
</pre>
 
</pre>
 +
Popularita Pythonu roste a roste i počet uživatelů, kteří píší uložené procedury v Pythonu. Při volání kódu v Pythonu se musí parametry převést do formátu, který podporuje runtime Pythonu. Naopak výsledek je nutné konvertovat do formátů Postgresu. Předchozí verze přidaly podporu jedno dimenzionálních polí. Počínaje verzí 10 mohou být parametry funkce, případně výsledek funkce i více dimenzionální pole.
  
Pokud se shoduje partitioning dvou joinovaných tabulek, pak lze použít tzv <i>partition-wise join</i><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f49842d1ee31b976c681322f76025d7732e860f3 p]</small></sup>. Join na úrovni partition je úspornější. Na druhou stranu, detekce jestli je možné tento typ joinu použít, je výpočetně náročná úloha, a proto je defaultně vypnutá. Lze ji zapnout volbou <code>enable_partition_wise_join = on</code>. Podobný trik lze použít i pro agregaci, pokud agregujeme alespoň podle jednoho klíče použitého pro řízení partitioningu<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e2f1eb0ee30d144628ab523432320f174a2c8966 p]</small></sup>. Tím, že se agregace provede na úrovni partitions (a nikoliv tabulky) jakoby zmenšíme velikost vstupních dat, a tudíž je možné, že se lépe vejdeme do operační paměti a použije se rychlejší (paměťově náročnější) algoritmus. Také tato optimalizace je kvůli náročnosti na plánování defaultně vypnutá a pro její aktivaci je nutné ji zapnout konfigurací <code>enable_partitionwise_aggregate = on</code>.
+
<h2>Práce s řetězci </h2>
  
Nově také můžeme psát <code>FOR EACH ROW</code> after triggery i pro partitiované tabulky<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=86f575948c773b0ec5b0f27066e37dd93a7f0a96 p]</small></sup>.
+
Měly by být odstraněny snad už všechny problémy s regulárními výrazy obsahujícími UTF8 více bajtové znaky (PostgreSQL má vlastní implementaci regulárních výrazů).
  
Od PostgreSQL 11 můžeme vytvářet indexy nad partitiovanými tabulkami<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8b08f7d4820fd7a8ef6152a9dd8c6e3cb01e5f99 p]</small></sup>. S vytvořením indexu na partitiované tabulce se index automaticky vytvoří i na všech partitions (v Postgresu, rekurzivně na všech tabulkách, které se používají jako partition). Partitiované tabulky mohou obsahovat i unikátní index<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=eb7ed3f3063401496e4aa4bd68fa33f0be31a72f p]</small></sup>, případně na těchto tabulkách může být definovaný primární klíč. Platí ovšem podmínka, že unikátní index případně primární klíč musí obsahovat atribut(y), který(é) řídí partitioning:
+
Poměrně zásadní je integrace knihovny [https://cs.wikipedia.org/wiki/International_Components_for_Unicode ICU]. Tato multiplatformní knihovna pokrývá kompletní spektrum operací nad Unicode řetězci a je poměrně často používaná. Postgres dosud běžel pouze nad vestavěnou podporou locales z operačního systému, a ve valné většině případů s knihovnami z operačního systému nebyly žádné problémy. Na některých platformách, i na některých verzích nebo distribucích Linuxu, ale buďto chybí podpora pro Unicode nebo používané verze glibc mají některé funkce chybně implementované (a pak některé optimalizace pro zrychlení řazení musí být vypnuté). Náhradním řešením pro tyto případy je právě použití knihovny <i>ICU</i>.
<pre>
+
CREATE UNIQUE INDEX ON data(a, vlozeno);
+
ALTER TABLE data ADD PRIMARY KEY (a, vlozeno);
+
</pre>
+
  
Zatím nelze vytvořit cizí klíče vůči partitiované tabulce, nicméně alespoň již můžeme v partitiované tabulce používat cizí klíče<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3de241dba86f3dd000434f70aebba725fb928032 p]</small></sup>.
+
<h2>Administrace a monitoring</h2>
  
Ve starších verzích detekce použitých partitions proběhla pouze v čase plánování dotazu. Pokud při plánování dotazu nebyly k dispozici potřebné informace, tak planner použil všechny partitions. Od nové verze Postgres dokáže eliminovat partitions i v době zpracování dotazu <sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=499be013de65242235ebdde06adb08db887f0ea5 p]</small></sup>. Měly by být podporované operace <i>append</i>, <i>nested loop</i> a <i>init plans</i> (První poznámka: ještě to trochu kulhá, implementace si neporadí s funkcemi a pseudo konstantami (<code>CURRENT_DATE</code>, <code>CURRENT_TIMESTAMP</code>. Druhá poznámka: existuje patch, který tuto situaci opravuje. Není ale zřejmé, jestli bude aplikován v jedenáctce nebo až v další verzi.).
+
Prakticky všechny zálohovací aplikace se budou muset upravit pro PostgreSQL 10 a vyšší. Došlo k přejmenování několika kritických adresářů a funkcí. Tyto adresáře obsahovaly v názvu řetězec "log". Což, jak se několikrát ukázalo, není praktické (někteří uživatelé si při čištění logů smazali i tyto pro Postgres zásadní adresáře a soubory). To je důvod, proč se dotčené adresáře přejmenovaly - např. adresář "xlog" se přejmenoval na "wal" ("clog" se mění na "xact"). K podobné změně došlo v názvech funkcí. Např. funkce <code>pg_switch_xlog</code> byla přejmenována na <code>pg_switch_wal</code>. Běžných aplikací by se tyto změny vůbec neměly dotknout.
  
==Administrace==
+
Roky upozorňuji uživatele, že by v Postgresu neměli používat <i>hash index</i>. I přes některé své zajímavé vlastnosti byl pro produkční nasazení nepoužitelný - nebyl jištěný transakčním logem. To znamená, že v případě havárie některé změny v obsahu indexu nemusely být zapsány na disk a jen otázkou náhody, jak moc by, díky tomu, byl hash index rozbitý. Docela dlouho se uvažovalo i o tom, že se podpora hash indexu odstraní. Od minulé verze mají vývojáři Postgresu k dispozici nové API umožňující extenzím zapisovat do transakčního logu (indexy v Postgresu jsou vlastně taky "extenze"). Toto API např. používá extenze <code>bloom</code>. A díky tomuto API už nebylo tak pracné dopsat podporu transakčního logu pro hash indexy. Tudíž od verze 10 je možné používat hash indexy v produkčním prostředí.  
Již v předchozích verzích byl příkaz <code>ALTER TABLE ADD COLUMN</code> extrémně rychlý, a to díky tomu, že došlo pouze ke změně metadat. Na data se vůbec nesahalo. Počínaje verzí 11 to platí i pro příkaz <code>ALTER TABLE ADD COLUMN DEFAULt expr</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=16828d5c0273b4fe5f10f42588005f16b415b2d8 p]</small></sup>.
+
  
Nový příkaz [https://www.postgresql.org/docs/11/static/pgverifychecksums.html <code>pg_verify_checksums</code>] slouží k offline ověření kontrolních součtů datových stránek.
+
Politiky <i>RLS</i> (<i>Row Level Security Policy</i>) nejsou restriktivní v tom smyslu, že stačí, že je splněna jedna politika z několika a uživatel má přístup k datům. V 10 můžeme vytvářet tzv restriktivní politiky - ty musí být splněny vždy, bez ohledu na ostatní:
+
<pre>
Nově si [https://www.postgresql.org/docs/11/static/pgprewarm.html <code>pg_prewarm</code>] díky svému Background Work procesu periodicky ukládá stav shared buffers (cache datových stránek v PostgreSQL). Po restartu serveru druhým BG procesem na základě uloženého stavu obnoví obsah cache<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=79ccd7cbd5ca44bee0191d12e9e65abf702899e7 p]</small></sup>.
+
-- Pokud se admin připojí vzdáleně, pak tato politika
 +
-- zabrání přístupu k obsahu tabulky passwd
 +
CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
 +
    USING (pg_catalog.inet_client_addr() IS NULL);
 +
</pre>
 +
Upravily se některé výchozí hodnoty v konfiguraci, které odpovídají základnímu nastavení replikace, tak aby šlo bezproblémově provést zálohování příkazem <code>pg_basebackup</code>. V <code>pg_hba.conf</code> je defaultně povolená replikace z localhostu:
 +
<pre>
 +
wal_level = replica
 +
max_wal_senders = 10
 +
max_replication_slots = 10
 +
</pre>
 +
V předchozích verzích bylo možné používat tzv replikační sloty - jedná se o databázové objekty udržující stav replikace s konkrétním klientem a garantující možnost synchronizace s replikou navázanou na otevřený replikační slot. V 10ce jsou k dispozici dočasné replikační sloty - držené pouze po dobu připojení. Tuto funkcionalitu využívá <code>pg_basebackup</code> z 10ky - díky dočasnému replikačnímu slotu je garantováno, že nedojde k protočení transakčních logů, a že vytvořená záloha bude konzistentní (se všemi potřebnými segmenty transakčního logu).
  
Přepínačem <code>--create-slot</code> příkazu <code>pg_basebackup</code> lze vynutit vytvoření trvalého replikačního slotu, jehož název je určen přepínačem <code>--slot</code>.  
+
Příkaz <code>VACUUM</code> se skládá ze tří kroků. V posledním kroku je snaha redukovat velikost tabulky, a tabulka (resp. její datové soubory) se čtou pozpátku. Zpětné sekvenční čtení samozřejmě operační systémy nepodporují, a tak je tato operace poměrně pomalá. Přednačítáním (prefetch) je možné ji výrazně urychlit. V komentáři k patchi je zmíněno pětinásobné zrychlení. Při běžném nasazení asi změnu nepoznáte, protože poslední krok je krátký, a tato optimalizace tam nehraje roli. Něco jiného je, pokud byste použili <code>pg_repack</code>. Pak tuto optimalizaci určitě oceníte.  
  
Novým parametrem tabulky <code>toast_tuple_target</code> v rozmezí 128 bajtů a 8KB můžeme určit velikost hodnot, které se budou ukládat externě - v tzv TOAST tabulkách (výchozí hodnota odpovídá 2KB).
+
Nová extenze <code>amcheck</code> obsahuje funkce umožňujíc zkontrolovat konzistenci btree a hash indexu. Primárně je určena pro regresní testy postgresu (obnova po havárii, replikace) ale stejně tak dobře může posloužit i dalším uživatelům.
  
==Optimalizace výkonu==
+
Volbou <code>--no-blobs</code> můžeme blokovat zálohování blobů. V extenzi <code>pg_stat_statements</code> došlo k náhradě symbolu označující pozici parametru (dříve "?", nyní $1, $n).
===Použití více CPU pro příkaz===
+
[https://www.enterprisedb.com/blog/parallel-hash-postgresql Velké úsilí] stála implementace paralelního hash joinu<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1804284042e659e7d16904e7bbb0ad546394b6a3 p]</small></sup>. Pro uživatele s většími databázemi, a náročnějšími dotazy by zrychlení provádění dotazů mělo být výrazné. V nové verzi PostgreSQL může použít více CPU i pro dotazy obsahující poddotazy (což v předchozích verzích zpracování přes více CPU blokovalo)<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e89a71fb449af2ef74f47be1175f99956cf21524 p]</small></sup>. Paralelní zpracování dotazů je nyní možné i pro dotazy příkazem <code>CREATE TABLE AS SELECT</code>. Zparalelizována byla i klauzule <code>UNION ALL</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0927d2f46ddd4cf7d6bf2cc84b3be923e0aedc52 p]</small></sup>:
+
  
 +
Ve verzi 9.6 se zavedly tzv <i>defaultní role</i> - tehdy pouze role <code>pg_signal_backend</code>. Defaultní role umožňují získat některá jasně ohraničená práva uživatele <code>postgres</code>. V desítce jsou k dispozici další role určené k monitorování databáze: <code>pg_read_all_settings</code>, <code>pg_read_all_stats</code>, <code>pg_stat_scan_tables</code> a <code>pg_monitor</code>:
 
<pre>
 
<pre>
SET LOCAL parallel_setup_cost = 10;
+
GRANT pg_signal_backend TO admin_user;
EXPLAIN (COSTS OFF)
+
SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1
+
UNION ALL
+
SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1;
+
                    QUERY PLAN                   
+
----------------------------------------------------
+
Gather
+
  Workers Planned: 4
+
  ->  Parallel Append
+
        ->  Parallel Seq Scan on tenk1
+
              Filter: (fivethous = (tenthous + 1))
+
        ->  Parallel Seq Scan on tenk1 tenk1_1
+
              Filter: (fivethous = (tenthous + 1))
+
(7 rows)
+
 
</pre>
 
</pre>
 +
Pokud jsem potřeboval zjistit, kdo odkud má přístup k databázi, pak jsem se musel podívat do konfiguračního souboru <code>pg_hba.conf</code>. To už znamená minimálně nutnost připojení na server s databází. Od 10ky máme systémový pohled <code>pg_hba_file_rules</code>, kde je obsah <code>pg_hba.conf</code> zpřístupněn ve strukturované podobě:
 +
<pre>
 +
postgres=# SELECT * FROM pg_hba_file_rules;
 +
line_number | type  |  database    | user_name |  address  |                netmask                | auth_method | options | error
 +
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
 +
          84 | local | {all}        | {all}    |          |                                        | trust      |        |
 +
          86 | host  | {all}        | {all}    | 127.0.0.1 | 255.255.255.255                        | trust      |        |
 +
          88 | host  | {all}        | {all}    | ::1      | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust      |        |
 +
          91 | local | {replication} | {all}    |          |                                        | trust      |        |
 +
          92 | host  | {replication} | {all}    | 127.0.0.1 | 255.255.255.255                        | trust      |        |
 +
          93 | host  | {replication} | {all}    | ::1      | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust      |        |
 +
(6 rows)
 +
</pre>
 +
Nově také nebude nutné si vlastními silami počítat replikační lag - pohled <code>pg_stat_replication</code> obsahuje metriky: <code>write_lag</code>, <code>flush_lag</code> a <code>replay_lag</code>. V nové verzi také uvidíme v <code>pg_stat_activity</code> všechny pracovní procesy bez ohledu na to, jestli jsou nebo nejsou připojené do konkrétní databáze. I tyto procesy mohou čekat na zámky, a proto je praktické je v <code>pg_stat_activity</code> zobrazit. Typ procesu je popsán ve sloupci <code>backend_type</code>:
 +
<pre>
 +
  | wait_event_type |    wait_event      | state  | backend_xid | backend_xmin |              query              |    backend_type   
 +
--+-----------------+---------------------+--------+-------------+--------------+----------------------------------+---------------------
 +
  | Activity        | AutoVacuumMain      |        |            |              |                                  | autovacuum launcher
 +
  | Activity        | LogicalLauncherMain |        |            |              |                                  | background worker
 +
  |                |                    | active |            |          568 | select * from pg_stat_activity ; | client backend
 +
  | Activity        | BgWriterMain        |        |            |              |                                  | background writer
 +
  | Activity        | CheckpointerMain    |        |            |              |                                  | checkpointer
 +
  | Activity        | WalWriterMain      |        |            |              |                                  | walwriter
 +
</pre>
 +
V předchozí verzi došlo k rozšíření pohledu <code>pg_stat_activity</code>. Přibyly sloupce <code>wait_event</code> a <code>wait_event_type</code>. V desítce zde můžeme vidět i čekání na IO.
  
Paralelizovat lze i řazení<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9da0cc35284bdbe8d442d732963303ff0e0a40bc p]</small></sup>. Zrychlení by mělo být vidět při vytváření indexů <code>CREATE INDEX</code>.
+
<h2>Partitioning</h2>
  
===Statistiky, odhady, a optimalizace prováděcích plánů===
+
Někdy i dobrý nápad se může ukázat v dlouhodobém výhledu jako kontraproduktivní. To se dá, bohužel, napsat i o partitioningu v Postgresu. Partitioning v Postgresu je téměř obecný a jeho implementace založená na dědičnosti a na redukci prováděcího plánu byla rychlá a spolehlivá. Bohužel, právě kvůli své univerzálnosti, je partitioning omezen pouze na čtení, a větší počet partitions na jednu tabulku dost nepříjemně prodlužuje dobu plánování dotazu. Navíc je partitioning statický (přístup k partitions je zafixovaný v době plánování dotazu), a tudíž pro přístup k datům využívajícím partitioning je nutné používat dynamické SQL.  
Nově se při kalkulaci odhadů rozlišuje] mezi operátorem <code>&lt;</code> a operátorem <code>&lt;=</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7d08ce286cd5854d58152e428c28636a616bdc42 p]</small></sup>. Ve starších verzích se  <code>&lt;=</code> převádělo na <code>&lt;</code>. Totéž platí a platilo pro <code>&gt;</code> a <code>&gt;=</code>. Existovaly situace, kdy starší zjednodušená implementace dávala špatné odhady, např. když hodnoty <code>a</code> a <code>b</code> ve výrazu <code>x BETWEEN a AND b</code> byly blízko sebe.
+
  
V PostgreSQL se ke každému sloupci udržuje seznam nejčastějších hodnot (<i>Most Common Values</i>) včetně pravděpodobnosti výskytu těchto hodnot. Do verze 11 se do tohoto seznamu ukládaly hodnoty jejichž pravděpodobnost výskytu bylo o 25% vyšší než pravděpodobnost výskytu všech not null hodnot. Nově je seznam nejčastějších hodnot definován jako co největší množina hodnot, jejichž pravděpodobnost výskytu je větší než průměrná pravděpodobnost výskytu hodnot nezařazených do seznamu MCV. Nová kalkulace by měla se lépe chovat v případech, kdy několik málo hodnot má větší než průměrnou pravděpodobnost výskytu. Pro rovnoměrné rozdělení by měl být seznam MCV menší a pro nerovnoměrná rozdělení naopak větší<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b5db1d93d2a6e2d3186f8798a5d06e07b7536a1d p]</small></sup>.
+
To je dost důvodů, aby byl stávající partitioning výrazně přepracován. Práce na novém partitioningu začaly před několika roky. Ve verzi 10 můžeme vidět první výsledky. Ve verzi 11 se pracuje na optimalizaci planneru - mělo by dojít k výraznému zrychlení plánování dotazů nad partitiovanými tabulkami.
  
Změnil se i výpočet ceny za bitmap index scan<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5edc63bda68a77c4d38f0cbeae1c4271f9ef4100 p]</small></sup>. Nová kalkulace je bližší realitě, a měla by prodražit bitmap index scan v situacích, kdy se vytvořená bitmapa nevejde do <code>work_mem</code>, a místo s mapou řádek se pracuje s mapou stránek.
+
Ve verzi 10 Postgres umožňuje deklarativní zápis. Díky méně obecné implementaci už Postgres zvládne i write operace bez nutnosti psát triggery:
  
===Práce s indexy===
+
<ul>
Příkaz <code>VACUUM</code> vytváří (nastavuje) bitmapu stránek, které neobsahují smazané záznamy (<i>visibility map</i>). Pokud je tato mapa k dispozici, a pokud index obsahuje všechny potřebné sloupce k vykonání dotazu, pak optimalizátor může použít přístupovou metodu (k datům) <i>index only scan</i>. Tím se ušetří náhodné přístupy do tabulky. Od verze jedenáct se stejná optimalizace může použít i pro <i>bitmap scan</i> (sekvenční čtení souboru akcelerované indexem)<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7c70996ebf0949b142a99c9445061c3c83ce62b3 p]</small></sup>.
+
<li>Partitioning založený na rozsahu (range):
 +
<pre>
 +
CREATE TABLE data(a text, vlozeno date) PARTITION BY RANGE(vlozeno);
 +
CREATE TABLE data_2016 PARTITION OF data FOR VALUES FROM ('2016-01-01') TO ('2016-12-31');
 +
CREATE TABLE data_2017 PARTITION OF data FOR VALUES FROM ('2017-01-01') TO ('2017-12-31');
  
Index only scan nelze použít, pokud index neobsahuje všechny potřebné hodnoty použité v dotazu. Od verze 11 je možné index vytvořit včetně tzv neklíčových položek (nepoužijí se pro vyhledávání, ale jsou uložené v indexu)<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8224de4f42ccf98e08db07b43d52fed72f962ebb p]</small></sup>. Pro indexy obsahující neklíčové položky se také používá název <i>covering indexes</i>:
+
postgres=# INSERT INTO data VALUES('hello', '2016-06-08');
 +
INSERT 0 1
 +
postgres=# INSERT INTO data VALUES('hello', '2015-06-08');
 +
ERROR: no partition of relation "data" found for row
 +
DETAIL:  Partition key of the failing row contains (vlozeno) = (2015-06-08).
 +
</pre>
 +
Dropnutí hlavní tabulky odstraní i partition tabulky bez nutnosti použití <code>CASCADE</code>:
 
<pre>
 
<pre>
CREATE UNIQUE INDEX ON upsert(lower(key)) INCLUDE (payload);
+
postgres=# DROP TABLE data;
CREATE TABLE lcku_table (id INTEGER, value TEXT, PRIMARY KEY (id) INCLUDE (value));
+
DROP TABLE
 
</pre>
 
</pre>
 
+
<li>Partitioning založený na výčtu:
V případě, že se aktualizuje neoindexovaný sloupec, tak PostgreSQL použije tzv <i>hot-update</i> (Heap Only). Tento způsob aktualizace je výrazně rychlejší, jelikož nevyžaduje aktualizaci indexů nad tabulkou. V nové verzi PostgreSQL použije hot-update i po aktualizaci indexovaného sloupce funkcionálním indexem, pokud nedojde ke změně výsledku zaindexované vypočítané hodnoty<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c203d6cf81b4d7e43edb2b75ec1b741ba48e04e0 p]</small></sup>. Tato novinka by měla zrychlit aktualizace řádků s XML, případně JSON dokumenty, kde se používá funkcionální index pro přístup k vybraným klíčovým položkám (které jsou často neměnné).
+
 
+
Nový operátor <code>^@</code> je akcelerován SP-GiST indexem a může být použitý jako alternativa pro vyhledávání podle prefixu řetězce<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=710d90da1fd8c1d028215ecaf7402062079e99e9 p]</small></sup>:
+
 
<pre>
 
<pre>
-- stara klasika, nefunguje pro ^@
+
CREATE TABLE data(a text, vlozeno date) PARTITION BY LIST(EXTRACT(YEAR FROM vlozeno));
CREATE INDEX ON obce(nazev varchar_pattern_ops);
+
CREATE TABLE data_2016 PARTITION OF data FOR VALUES IN (2016);
postgres=# EXPLAIN SELECT * FROM obce WHERE nazev like 'Ben%';
+
CREATE TABLE data_2017 PARTITION OF data FOR VALUES IN (2017);
┌──────────────────────────────────────────────────────────────────────────────────────┐
+
│                                      QUERY PLAN                                      │
+
╞══════════════════════════════════════════════════════════════════════════════════════╡
+
│ Index Scan using obce_nazev_idx on obce  (cost=0.28..8.30 rows=1 width=41)          │
+
│  Index Cond: (((nazev)::text ~>=~ 'Ben'::text) AND ((nazev)::text ~<~ 'Beo'::text)) │
+
│  Filter: ((nazev)::text ~~ 'Ben%'::text)                                            │
+
└──────────────────────────────────────────────────────────────────────────────────────┘
+
(3 rows)
+
  
-- nova moznost
+
postgres=# INSERT INTO data VALUES('hello', '2015-06-08');
CREATE INDEX ON obce USING spgist(nazev);
+
ERROR:  no partition of relation "data" found for row
 +
DETAIL:  Partition key of the failing row contains (date_part('year'::text, vlozeno)) = (2015).
 +
postgres=# INSERT INTO data VALUES('hello', '2016-06-08');
 +
INSERT 0 1
  
postgres=# EXPLAIN SELECT * FROM obce WHERE nazev like 'Ben%';
+
postgres=# EXPLAIN SELECT * FROM data WHERE extract(year from vlozeno) = 2016;
┌──────────────────────────────────────────────────────────────────────────────────────┐
+
                                                  QUERY PLAN                                                
│                                      QUERY PLAN                                     │
+
--------------------------------------------------------------------------------------------------------------
╞══════════════════════════════════════════════════════════════════════════════════════╡
+
Append  (cost=0.00..32.23 rows=6 width=36)
│ Index Scan using obce_nazev_idx on obce (cost=0.15..8.17 rows=1 width=41)           │
+
  ->  Seq Scan on data_2016 (cost=0.00..32.23 rows=6 width=36)
│  Index Cond: (((nazev)::text ~>=~ 'Ben'::text) AND ((nazev)::text ~<~ 'Beo'::text)) │
+
        Filter: (date_part('year'::text, (vlozeno)::timestamp without time zone) = '2016'::double precision)
│  Filter: ((nazev)::text ~~ 'Ben%'::text)                                           │
+
└──────────────────────────────────────────────────────────────────────────────────────┘
+
 
(3 rows)
 
(3 rows)
 
-- novy operator
 
postgres=# EXPLAIN SELECT * FROM obce WHERE nazev ^@ 'Ben';
 
┌────────────────────────────────────────────────────────────────────────────┐
 
│                                QUERY PLAN                                │
 
╞════════════════════════════════════════════════════════════════════════════╡
 
│ Index Scan using obce_nazev_idx on obce  (cost=0.15..8.17 rows=1 width=41) │
 
│  Index Cond: ((nazev)::text ^@ 'Ben'::text)                              │
 
└────────────────────────────────────────────────────────────────────────────┘
 
(2 rows)
 
 
</pre>
 
</pre>
 +
</ul>
 +
Je zde ještě hodně omezení, ale ta by měla být během dvou následujících verzí postupně odstraněna.
  
==Ostatní==
+
<h2>Vestavěná logická replikace</h2>
Doménové typy jsou jakési aliasy pro ostatní datové typy. Na základě typu <code>bigint</code> si mohu vytvořit doménový typ pro identifikátory:
+
<pre>
+
CREATE DOMAIN id_type AS bigint NOT NULL;
+
</pre>
+
  
Pak kdekoliv mohu používat <code>id_type</code> ve smyslu názvu datového typu. Nově lze vytvářet domény z kompozitních typů<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=37a795a60b4f4b1def11c615525ec5e0e9449e05 p]</small></sup>, a také lze vytvářet pole doménového typu<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c12d570fa147d0ec273df53de3a2802925d551ba p]</small></sup>. Doménové typy jsou ANSI/SQL alternativou ku referencovaným typům používaným v Oracle.
+
Už je to určitě několik let, co Petr Jelínek poslal patch s prototypem do mailing listu. Roky trvalo, než se připravila potřebná infrastruktura (výrazně obecnější než co obsahoval původní patch) a než bylo možné čistě implementovat logickou replikaci. V desítce se základní balík patchů dostal do upstreamu a pro logickou master/slave replikaci už není potřeba instalace další extenze. Lze oprávněně předpokládat, že další integrace bude postupovat rychleji (cílem je master/master replikace, pro kterou je nyní nutná extenze [https://www.2ndquadrant.com/en/resources/bdr/ <i>BDR</i>]).
  
Nově jsou B-tree indexy optimalizovány pro vkládání zvyšujících se hodnot<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=2b27273435392d1606f0ffc95d73a439a457f08e p]</small></sup>. Pokud byla vložena aktuálně největší hodnota, pak se její pozice a hodnota uloží do cache. V případě, že vkládáme další ještě větší hodnotu, pak se neprohledává strom indexu, ale rovnou se použije pozice z cache. Na desítce je vložení zvyšujících se hodnot o něco málo rychlejší než vkládání náhodných dat. V PostgreSQL 11 je vložení cca 1M zvyšujících se hodnot o &#8531; rychlejší než vložení náhodných hodnot. Jaký bude výsledek v praxi jde těžko odhadnout - většinou je nutné obsluhovat více indexů, a zvyšující se posloupnost je většinou pouze u několika málo sloupců.
+
Fyzická replikace vyžaduje aby repliky byly identické klony mastera. Na slavea se z mastera přenáší informace o zápisech (prostřednictvím transakčního logu), a jejich aplikací se replika synchronizuje s masterem. Také u logické replikace se přenášejí informace o změnách dat (a také prostřednictvím transakčního logu) - není to statement replikace. Ale díky tomu, že se pracuje na logické úrovni, tak replika nemusí být identická s masterem a tudíž nemusíme replikovat celý server. Můžeme replikovat pouze vybrané tabulky (nebo také všechny - <code>CREATE PUBLICATION test_pub FOR ALL TABLES</code>).  
  
Přidáním podpory klauzule <code>RANGE offset PRECEDING/FOLLOWING</code> je v PostgreSQL kompletní podpora window funkcí, tak jak ji definuje ANSI/SQL<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0a459cec96d3856f476c2db298c6b52f592894e8 p]</small></sup>. Pro většinu uživatelů tato skutečnost bude téměř neviditelná.
+
Logická replikace probíhá na základě obvyklého modelu - na masteru máme balíky publikovaných tabulek. Na replikách se přihlásíme k odběru vybraných balíků.
  
Naopak, uživatelé, kteří používají logickou replikaci, a těch je docela hodně, uvítají, že v nové verzi logická replikace podporuje i příkaz <code>TRUNCATE</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=039eb6e92f20499ac36cc74f8a5cef7430b706f6 p]</small></sup>.
+
Logická replikace se používá docela snadno (slave u mne běží lokálně na portu 5433). Pro logickou replikaci není nutné modifikovat <code>pg_hba.conf</code>. V <code>postgresql.conf</code> je nutné nastavit <code>wal_level</code> na <code>logical</code>.
  
Pro uživatele fulltextu v Postgresu bude zajímavá funkce <code>websearch_to_tsquery</code> a možná i funkce <code>phraseto_tsquery</code><sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1664ae1978bf0f5ee940dc2fc8313e6400a7e7da p]</small></sup>. První umožňuje specifikovat výběr zápisem obvyklým pro webové aplikace - např. <code>"sad cat" or "fat rat"</code>. Tato funkce je také tolerantní k chybám zápisu hledaného výrazu. Druhá funkce vyhledává fráze - slova ve správném pořadí:
+
Na masteru vytvoříme tabulku a tu zveřejníme:
 
<pre>
 
<pre>
-- vyžaduje speciální syntax
+
CREATE TABLE foo(id int primary key, a int);
postgres=# SELECT * FROM obce WHERE to_tsvector(nazev) @@ to_tsquery('mladá boleslav');
+
CREATE PUBLICATION test_pub FOR TABLE foo;
ERROR:  syntax error in tsquery: "mladá boleslav"
+
INSERT INTO foo VALUES(1, 200);
 +
</pre>
 +
Pokud má tabulka primární klíč, tak rovnou na ní můžeme provádět i jiné operace než <code>INSERT</code>. Pokud nemá a chtěli bychom mazat, editovat, pak musíme nastavit <i>replikační identitu</i> (viz příkaz <code>ALTER TABLE REPLICA IDENTITY</code>).
  
-- nevyžaduje speciální syntax
+
Na slave musíme vytvořit také tabulku (DDL příkazy se zatím nereplikují) a přihlásit se k odběru:
postgres=# SELECT * FROM obce WHERE to_tsvector(nazev) @@ websearch_to_tsquery('mladá boleslav');
+
<pre>
┌─────┬──────────┬────────────────┬────────────┬───────────┬──────────┬─────────┐
+
CREATE TABLE foo(id int primary key, a int);
id  │ okres_id │    nazev      │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
+
CREATE SUBSCRIPTION test_sub CONNECTION 'port=5432' PUBLICATION test_pub;
╞═════╪══════════╪════════════════╪════════════╪═══════════╪══════════╪═════════╡
+
</pre>
│ 613 │ CZ0207   │ Mladá Boleslav │      22462 │    22288 │    38.9 │    42.1
+
Po dokončení příkazu je replikace aktivní a tabulka <code>foo</code> je naplněná:
└─────┴──────────┴────────────────┴────────────┴───────────┴──────────┴─────────┘
+
<pre>
 +
-- slave
 +
postgres=# SELECT * FROM foo;
 +
id |
 +
----+------
 +
   1 | 1000
 
(1 row)
 
(1 row)
 
postgres=# SELECT * FROM obce WHERE to_tsvector(nazev) @@ websearch_to_tsquery('mladá boleslav or mladá vožice');
 
┌──────┬──────────┬────────────────┬────────────┬───────────┬──────────┬─────────┐
 
│  id  │ okres_id │    nazev      │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
 
╞══════╪══════════╪════════════════╪════════════╪═══════════╪══════════╪═════════╡
 
│  613 │ CZ0207  │ Mladá Boleslav │      22462 │    22288 │    38.9 │    42.1 │
 
│ 1705 │ CZ0317  │ Mladá Vožice  │      1335 │      1392 │    39.2 │    41.4 │
 
└──────┴──────────┴────────────────┴────────────┴───────────┴──────────┴─────────┘
 
(2 rows)
 
 
postgres=# SELECT * FROM obce WHERE to_tsvector(nazev) @@ websearch_to_tsquery('mladá boleslav or vožice');
 
┌──────┬──────────┬─────────────────────────┬────────────┬───────────┬──────────┬─────────┐
 
│  id  │ okres_id │          nazev          │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
 
╞══════╪══════════╪═════════════════════════╪════════════╪═══════════╪══════════╪═════════╡
 
│  613 │ CZ0207  │ Mladá Boleslav          │      22462 │    22288 │    38.9 │    42.1 │
 
│ 1705 │ CZ0317  │ Mladá Vožice            │      1335 │      1392 │    39.2 │    41.4 │
 
│ 1712 │ CZ0317  │ Nová Ves u Mladé Vožice │        91 │        86 │    40.9 │    43.8 │
 
│ 1764 │ CZ0317  │ Zhoř u Mladé Vožice    │        44 │        51 │    40.1 │    38.2 │
 
└──────┴──────────┴─────────────────────────┴────────────┴───────────┴──────────┴─────────┘
 
(4 rows)
 
 
</pre>
 
</pre>
 +
Tabulka <code>foo</code> je i na slave read/write - takže mohu i zde vložit řádek do této tabulky. Pokud by na masteru došlo ke vložení řádku se stejným <code>id</code>, pak nedojde ke kolizi, ale data z masteru přepíšou data na replice (pokud bych chtěl jiné řešení kolizí, pak si musím nainstalovat extenzi [https://www.2ndquadrant.com/en/resources/pglogical/ <code>pgLogical</code>] nebo <code>BDR</code>). Jsem rád za takto jednoduché řešení. Většině lidí bude vyhovovat jednoduchý default, a pro ty, kdo potřebují něco jiného, je tu plán B. Trochu jsem se obával, že používání logické replikace bude komplikované (na základě zkušeností se <code>Slony</code>), je ale vidět, že moje obavy byly zbytečné.
  
Zajímavě vypadá funkce <code>strict_word_similarity</code>, která se podobá funkci <code>word_similarity</code>, ale na rozdíl od ní bere v potaz hranice slov<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=be8a7a6866276b228b4ffaa3003e1dc2dd1d140a p]</small></sup>.
+
V souvislosti s logickou replikací pozor na příkaz <code>TRUNCATE</code> - ten se zatím nezapisuje do transakčního logu, a tudíž se nepřenese na repliky.
  
FDW API umožňovalo úpravy dat cizích tabulek, jelikož se ale jelo po jednom záznamu nad primárním klíčem, tak tyto operace byly relativně náročné. Počínaje novou verzí je možné distribuovat příkazy <code>UPDATE</code>, <code>DELETE</code> na cizí server, a který je může provést efektivně jako hromadnou operaci<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1bc0100d270e5bcc980a0629b8726a32a497e788 p]</small></sup>.
+
<h2>Optimalizace a zpracování dotazů</h2>
  
I v minulých verzích bylo možné napsat extenze, které provedly určitou činnost při přihlášení a odhlášení do databáze. Jelikož zachycení těchto událostí nebylo explicitně podporováno, tak tyto extenze byly postavené tak trochu na vodě. V PostgreSQL 11 je možné využít hooky <code>session_start_hook</code> a <code>session_end_hook</code>, a extenzi s tímto chováním napsat čistě a jednoduše<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cd8ce3a22c0b48d32ffe6543837ba3bb647ac2b2 p]</small></sup>.
+
Nově <i>FDW</i> API umožňuje tzv aggregate push down. V tomto kontextu to znamená, že se agregace může provést na cizím serveru. Postgres dostane rovnou agregovaná data. Samozřejmě, že toto chování musí podporovat použitý FDW driver.
  
Ve verzi 11 je lze příkazem <code>LOCK</code> explicitně zamknout pohled podobně jako tabulku<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=34c20de4d0b0ea8f96d0c518724d876c7b984cf5 p]</small></sup>. Zamknutí pohledu vedek k rekurzivnímu zamknutí tabulek použitých v pohledu.
+
Zrychlit by se měly agregace nad numerickým sloupcem založené na sumě. Výrazně rychlejší by měla být operace hashagg v případě, že výsledný počet řádků (počet skupin) je větší než několik tisíc. To by se mělo projevit i ve výběru unikátních hodnot (DISTINCT). V 10ce se také objeví první patche, které optimalizují evaluaci výrazů. Dalším krokem by mělo být nasazení JIT na počítání výrazů (PostgreSQL 11 nebo 12). Zkoušel jsem maximální možné zrychlení pro jednotlivé optimalizace (best case) na tabulce s 10M řádků (cca 500MB).
 +
<ul>
 +
<li>Počítání agregační funkce sum nad typem numeric - cca 25%
 +
<li>Optimalizace hash agg - zrychlení o cca 20%
 +
<li>Optimalizace evaluace výrazů - zrychlení o cca 45%
 +
</ul>
 +
Jaké by bylo reálné zrychlení se vůbec nedá odhadnout - v praxi je výrazně více IO waitů - na druhou stranu jeden dotaz může obsahovat více agregačních funkcí i výrazně komplikovanější výrazy než jsem zkoušel. Pokud ale IO nebude výrazným hrdlem, tak si dovolím tipnout, že by 10ka měla být v řadě dotazů o nějakých 20% rychlejší (OLAP).
  
Pokračuje se v trendu separovat práva superuživatele a umožnit některá jeho práva nastavit běžnému uživateli. Role <code>pg_read_server_files</code>, <code>pg_write_server_files</code> a <code>pg_execute_server_program</code> umožní uživateli použít funkce pro čtení souborů serveru, případně pro zápis do souboru nebo spuštění programu ze serveru - příkazy <code>COPY</code> a <code>COPY TO/FROM PROGRAM</code> na straně serveru<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0fdc8495bff02684142a44ab3bc5b18a8ca1863a p]</small></sup>.
+
Použití <i>RLS</i> (Row Level Security) mohlo blokovat některé optimalizace plánovače dotazů (např. flattening). Výsledkem mohl být, samozřejmě, pomalý dotaz. V desítce se pracuje s RLS chytřeji a k blokování optimalizací by nemělo docházet.
  
<code>pgbench</code> je aplikace pro většinu uživatelů PostgreSQL absolutně nezajímavá. Každým rokem ovšem sdílí více a více kódu s SQL konzolí <code>psql</code>. A proto může být zajímavé, že od nové verze je k dispozici relativně široká nabídka matematických a logických operátorů<sup><small>[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bc7fa0c15c590ddf4872e426abd76c2634f22aca p]</small></sup>, které jak doufám, se dostanou i do <code>psql</code>:
+
V 9.5 se objevila možnost používat tzv <code>GROUPING SETs</code>. Algoritmus agregace byl omezen na klasický <i>sort aggregate</i>. Tento algoritmus vyžaduje seřazená vstupní data. V desítce může optimalizátor zvolit i <i>hash aggregate</i>. Ušetří se tím jeden sort (u větších dat potenciálně hodně drahá operace).
<pre>
+
\set c0 debug(1.0 = 0.0 and 1.0 != 0.0)
+
\set c1 debug(0 = 1 Or 1.0 = 1)
+
\set c4 debug(case when 0 < 1 then 32 else 0 end)
+
</pre>
+
  
==TimescaleDB & PipelineDB==
+
Nově lze použít index i nad výčtovými typy (díky extenzi <code>btree_gist</code>). Taktéž je možné použít fulltext (a fulltextový index) nad daty typu JSON, Jsonb. V indexu by se měl lépe recyklovat volný prostor - mělo by docházet k menšímu nafukování indexů.
Nad kódem PostgreSQL vznikla řada dalších databází optimalizovaných, rozšířených pro specifické úlohy. BSD licence, se kterou je licencován kód PostgreSQL, ničemu takovému nebrání, a stejně tak se těmto forkům (většinou komerčním) nebrání komunita. Je to způsob, jak se další a další vývojáři mohou seznámit s kódem Postgresu, a případně začít psát i pro upstream. Navíc složitější kód se nevyplácí držet mimo upstream - pozdější synchronizace je nepříjemná a náročná práce. Aktuálně asi nejpopulárnější forky PostgreSQL jsou [https://www.enterprisedb.com/ EnterpriseDB] (cílí na uživatele Oracle) a [https://www.citusdata.com/ Citus] (OLAP a MPP). Staršími forky jsou např. databáze [https://www.ibm.com/analytics/netezza Netezza], [https://greenplum.org/ Pivotal Greenplum database] nebo [https://en.wikipedia.org/wiki/Amazon_Redshift Amazon Redshift].
+
  
Pokud se jedná o více změn, tak pak údržba nebo vývoj vlastního forku PostgreSQL není levná záležitost (není problém mít několik i možná desítek kratších patchů). Starší forky jsou úplně odtržené od Postgresu a mnohokrát z hlediska SQL, případně uživatelského komfortu jsou zastaralé. Greenplum už cca 2 roky migruje z PostgreSQL 8.2, a aktuálně jsou na 9.0. V CitusData byli první, kdo svůj fork přepsali do formátu extenze PostgreSQL. Extenze v PostgreSQL mají svůj kód izolovaný od kódu PostgreSQL a tudíž údržba extenze je výrazně jednodušší a méně pracná než údržba forku. Další databází, která byla původně forkem a nyní se [https://www.pipelinedb.com/blog/pipelinedb-0-9-9-one-more-release-until-pipelinedb-is-a-postgresql-extension transformovala do extenze] je PipelineBD. Stejně tak, jak je údržba extenze jednodušší než údržba forku, tak je používání extenze jednodušší než používání forku.
+
Optimalizace se dočkala i operace bitmap heap scan. Opět by mělo dojít k výraznému zrychlení.
  
===TimescaleDB===
+
V 10 se zvětšuje množina operací podporující paralelní zpracování dotazu (použití více CPU pro jeden dotaz): <code>parallel index scan</code>, <code>parallel index only scan</code>, <code>paralel bitmap heap scan</code>, <code>gather merge</code> (redukuje zbytečné řazení).
[https://www.timescale.com/ <i>TimescaleDB</i>] je relativně nová komerční (s otevřeným kódem) extenze, která do PostgreSQL přidává některé vlastnosti tzv <i>timeseries</i> databází. Rozšiřuje partitioning v PostgreSQL o automaticky vytvářené partitions (definované časovým obdobím). Kromě jedné povinné dimenze pro partitioning (časové období) lze vytvářet partitions ještě podle jednoho vybraného atributu. Ve výsledku můžeme dostat 2D partitioning (např. podle času a kódu země). Jelikož je partitioning v TimescaleDB předepsán hodně natvrdo, tak může být a je lépe optimalizován než obecný partitioning v Postgresu (ten má problémy s větším počtem patitions (cca 100 je ještě ok)).  
+
  
Kromě toho umí v dotazu identifikovat některé vybrané funkce (např. <code>date_trunc</code>) a lépe optimalizovat dotazy, které tyto funkce používají. Pro Postgres jsou téměř všechny funkce black box, a tudíž optimalizátor nemůže být příliš agresivní (navíc ani správně nedokáže odhadnout výsledek).
+
<h2>Statistiky, více sloupcové statistiky</h2>
  
S touto extenzí Postgres automaticky vytváří množství menších partitions. Jelikož se indexy nad menšími tabulkami dobře vejdou do RAM, tak je aktualizace indexu rychlou operací a tudíž se TimescaleDB může pochlubit relativně stabilní rychlostí vkládání dat do db. Některé časté dotazy z oblasti timeseries databází jsou pak díky rozpoznání chování funkce a následné optimalizaci znatelně rychlejší. Na některých úlohách může být vidět, že TimescaleDB je hybridní databáze a nativní [https://en.wikipedia.org/wiki/Time_series_database timeseries databáze] budou výrazně rychlejší. Nicméně v mnoha případech díky TimescaleDB bude výkon Postgresu pro danou úlohu dostačující a naopak díky Postgresu uživatel získá komfort v podobě silného SQL (a v případě, že již zná Postgres, tak si nemusí svůj stack rozšiřovat o další technologii).
+
Optimalizace dotazů v Postgresu je založená na odhadech budoucího výsledku (resp. efektu použitých podmínek a operací) a odhad je vychází ze sloupcových statistik (viz příkaz <code>ANALYZE</code>). Použitý statistický model předpokládá nezávislost mezi uloženými daty. Často ale pracujeme s daty, kde nějaká závislost mezi atributy je, občas je závislost velice silná (obec, okres, kraj). Potom dochází k podstřelení odhadu, který se může velmi nepříjemně projevit např. nevhodně použitým nested loopem. Stávající statistiky můžeme rozšířit více sloupcovou statistikou vytvořenou příkazem <code>CREATE STATISTICS</code>:
 +
<pre>
 +
CREATE STATISTICS nazev_okres_id (dependencies) ON nazev, okres_id FROM obce;
 +
</pre>
 +
Volbou dependencies určuje, že se statistika váže k funkčním závislostem mezi sloupci. Pokud použijeme <code>ndistinct</code>, tak bude zpřesňovat odhady počtu unikátních hodnot (<code>ndistinct</code>), které se používají například při optimalizaci agregace.
  
===PipelineDB===
+
Bohužel nemám žádná data, na kterých by byl vidět pozitivní efekt těchto nových statistik, takže nic dalšího k těmto statistikám neukáži. Opět, implementace, která je v 10ce je spíš nastartování spíše dlouhodobého a komplikovaného projektu. Zatím se vícesloupcové statistiky počítají pouze v rámci jedné tabulky. Syntaxe je připravena i pro počítání statistik mezi tabulkami. Jde zejména o podchycení vazbu mezi PK a FK. Tam v některých aplikacích (zejména OLAP - kalkulace s časovou dimenzí) dochází k naprosto fatálním odhadům.
Existence proudových databází (<i>stream databases</i>) je drtivé většině uživatelů dobře utajena, ačkoliv by se řadě uživatelů perfektně hodily. O těchto databázích se hodně mluvilo na přelomu milénia, bohužel bez větší odezvy uživatelů. Dnes se k těmto databázím dostáváme oklikou přes architektury založené na zpracování proudů dat (např. Apache Kafka). [https://www.pipelinedb.com/ PipelineDB] je pokus přiblížit tyto databáze uživatelům - do klasického Postgresu zavádí nové objekty a operace: <i>stream buffer</i>, <i>continuous aggregation</i>, <i>sliding window queries</i>, atd.
+
  
 +
<h2><code>pgexportdoc</code>, <code>pgimportdoc</code>, <code>pspg</code></h2>
 +
 +
Hodně zajímavou vlastností je schopnost Postgresu pracovat s velkými dokumenty (texty, XMLka, JSONy) jako s hodnotami. Menší problémem je export a import těchto dokumentů z a do Postgresu bez vlastní aplikace. PostgreSQL nabízí několik způsobů a API, ale žádný nástroj, který by se dal jednoduše použít z příkazové řádky, případně se dal použít pro psaní skriptů. Po několika letech jsem doiteroval ke dvěma jednoduchým aplikacím, které jednoduše nahrají dokument ze souboru do postgresu (<code>pgimportdoc</code>) nebo naopak uloží dokument z Postgresu do souboru (<code>pgexportdoc</code>).
 
<pre>
 
<pre>
CREATE STREAM wiki_stream (hour timestamp, project text, title text, view_count bigint, size bigint);
+
[pavel@localhost ]$ pgimportdoc postgres -f ~/Stažené/enprimeur.xml -c 'insert into xmldata values($1)' -t XML
CREATE CONTINUOUS VIEW wiki_stats AS
+
[pavel@localhost ]$ cat ~/Stažené/enprimeur.xml | pgimportdoc postgres -c 'insert into xmldata values($1)' -t XML
SELECT hour, project,
+
[pavel@localhost ]$ cat ~/Stažené/enprimeur.xml | pgimportdoc postgres -E latin2 -c 'insert into doc values($1) returning id' -t TEXT
        count(*) AS total_pages,
+
[pavel@localhost ]$ pgexportdoc -c 'select x from xmldata where id = 1' -t XML -f myxmldoc.xml
        sum(view_count) AS total_views,
+
        min(view_count) AS min_views,
+
        max(view_count) AS max_views,
+
        avg(view_count) AS avg_views,
+
        percentile_cont(0.99) WITHIN GROUP (ORDER BY view_count) AS p99_views,
+
        sum(size) AS total_bytes_served
+
FROM wiki_stream
+
GROUP BY hour, project;"
+
 
</pre>
 
</pre>
 +
Rád pracuji v textovém režimu - pro mne je to příjemná úleva pro oči - a také mohu jednoduše pracovat vzdáleně na relativně pomalé lince a neřešit rychlost linky. Navíc v psql se mi dobře pracuje - v podstatě nikdy jsem nepotřeboval nic jiného. Základem pro ergonomickou práci v psql je přenastavení defaultního pageru <code>more</code> na <code>less</code>. Roky jsem s <code>less</code>em pracoval, ale říkal jsem si, že by to chtělo trochu vylepšit. <code>less</code> je generický pager - nemá žádnou speciální funkcionalitu pro prohlížení tabulek a samozřejmě, že občas je problém, že nějaká informace "ujede" mimo obrazovku. Výsledkem několika měsíčního si hraní je nový pager [https://github.com/okbob/pspg <code>pspg</code>] - napsaný speciálně na použití v psql. Základní vlastností je možnost zafixování prvních n řádků a prvních m sloupců plus inteligentní skrolování po sloupcích. Možnost vybrat si barevné schéma je spíš malá legrácka.
  
Kontinuální pohled, jestli je to možné tak přeložit, je jakási obdoba neustále aktualizovaného materializovaného pohledu. <code>INSERT</code> do proudu <code>wiki_stream</code> způsobí chytrou aktualizaci pohledu. Neprovádí se klasická agregace, ale pouze aktualizace čítačů. Díky tomu bez větších nároků na CPU a IO můžeme mít k dispozici on-line agregovaná data.
+
<h2><code>Ora2pg</code>, <code>plpgsql_check</code>, <code>Orafce</code></h2>
  
Letošní rok je pro PipelineDB přelomový. Z forku Postgresu se [https://www.pipelinedb.com/blog/pipelinedb-0-9-8-postgresql-extension-refactor-nearing-completion mění na extenzi] Postgresu. Po způsobu Citusu a TimescaleDB. Bohužel v extenzích PostgreSQL není možné rozšiřovat SQL - zatím nikdo nepřišel jak dynamicky rozšiřovat parser generovaný [https://www.gnu.org/software/bison/ bisonem]. Tudíž PipelineDB přijde o svá rozšíření SQL (která podle mne zjednodušují a zpřehledňují zápis), na oplátku se zbaví nutnosti udržovat své vlastní buildy PostgreSQL. Údržba extenze je výrazně omezenější, jednoduší. Naštěstí jazyk a prostředky v PostgreSQL jsou dostatečně silné, takže požadovanou funkcionalitu je možné implementovat:
+
[https://github.com/darold/ora2pg <code>Ora2pg</code>] je volně dostupná aplikace určená ke konverzi dat a struktur z Oracle, MySQL do PostgreSQL. Za poslední rok tato aplikace udělala neskutečný pokrok v podpoře migrace PL/SQL funkcí a procedůr. Pro někoho může být podstatné schopnost migrace SQL příkazů / pohledů z Oracle pluskové syntaxe outer joinů do ANSI SQL syntaxe. Už dříve (a k tomu bylo <code>Ora2pg</code> navrženo a používáno) se bezproblémově a poměrně jednoduše používalo k migraci schématu. Samotný export dat může být rychlejší díky podpoře více pracovních procesů (workerů).
 +
 
 +
Zatím to vypadá, že se [https://github.com/okbob/plpgsql_check <code>plpgsql_check</code>] součástí upstreamu nestane - ne v nejbližší době - jedná se o cca 5000 řádků kódu a protlačit je do upstreamu by zabralo hodně času a v tuhle chvíli to není pro nikoho priorita. Vývoj v GitHubu funguje na výbornou. Díky tomu, že je <code>plpgsql_check</code> v komunitním repozitáři, tak je pro většinu uživatelů snadno dostupný (problém je s buildem pro Windows - to je docela opruz). Nově <code>plpgsql_check</code> mnohem lépe detekuje nepoužívané proměnné, nenastavené výstupní parametry a nově obsahuje i detekci mrtvého kódu.
 
<pre>
 
<pre>
-- původní zápis
+
create or replace function fx(x int)
CREATE STREAM <stream>
+
returns int as $$
-- nový zápis
+
begin
CREATE FOREIGN TABLE s (x integer, ...) SERVER pipelinedb;
+
  begin
 +
    if (x &gt; 0) then
 +
      raise exception 'xxx' using errcode = 'XX888';
 +
    else
 +
      raise exception 'yyy' using errcode = 'YY888';
 +
    end if;
 +
    return -1; -- dead code;
 +
  end;
 +
  return -1;
 +
end;
 +
$$ language plpgsql;
  
-- původní zápis
+
select * from plpgsql_check_function_tb('fx(int)');
CREATE CONTINUOUS VIEW continuous_view AS SELECT count(*) FROM s;
+
 
-- nový zápis
+
functionid | lineno | statement | sqlstate |    message      | detail | hint |    level    | position | query | context
CREATE VIEW continuous_view WITH (action=materialize, ...) AS SELECT count(*) FROM s;
+
------------+--------+-----------+----------+------------------+--------+------+---------------+----------+-------+---------
 +
fx        |      9 | RETURN    | 00000    | unreachable code |        |      | warning extra |          |      |
 +
fx        |    11 | RETURN    | 00000    | unreachable code |        |      | warning extra |          |      |
 +
(2 rows)
 +
 
 +
create or replace function fx(x int)
 +
returns int as $$
 +
begin
 +
  begin
 +
    if (x > 0) then
 +
      raise exception 'xxx' using errcode = 'XX888';
 +
    else
 +
      raise exception 'yyy' using errcode = 'YY888';
 +
    end if;
 +
  exception
 +
    when sqlstate 'XX888' then
 +
      null;
 +
    when sqlstate 'YY888' then
 +
      null;
 +
  end;
 +
end; -- missing return;
 +
$$ language plpgsql;
 +
 
 +
select * from plpgsql_check_function_tb('fx(int)');
 +
 
 +
functionid | lineno | statement | sqlstate |                    message                    | detail | hint | level | position | query | context
 +
------------+--------+-----------+----------+------------------------------------------------+--------+------+-------+----------+-------+---------
 +
fx        |        |          | 2F005    | control reached end of function without RETURN |        |      | error |          |      |
 +
(1 row)
 
</pre>
 
</pre>
 +
Největší změnou v [https://github.com/orafce/orafce Orafce] je nová dokumentace jak samotné knihovny <code>Orafce</code>, tak procesu migrace z Oracle do Postgresu. Relativně malou změnou je přidání několika pohledů emulujících některé systémové pohledy v Oracle, které se používají v uložených procedurách.
  
Mám rád bohaté barokní SQL. Líbí se mi stará syntaxe, která je názorná, nekomplikovaná. Naprosto ale rozumím důvodům, proč PipelineDB přechází na jiný formát. Extenzi je nepoměrně jednodušší udržovat i doručovat uživatelům, případně budoucím zákazníkům. Nicméně, kdyby někdo dokázal dynamicky rozšiřovat parser generovaný z gramatiky bisonu, tak ať se mi ozve. Rád mu zaplatím pivko.
+
<h2>Závěr</h2>
  
==Závěr==
+
10ka je určitě dobrá verze a vzhledem k času na finalizaci by měla být rychlá a stabilní. To, co se ale připravuje do 11ky a do 12ky, tak to bude docela síla - ať už je to integrace JIT pro vyhodnocení výrazů, radikální zrychlení plánování nad partitiovanými tabulkami, implementace podpory JSONu podle standardu, možnost relativně jednoduše navrhovat další storage (existuje prototyp paměťového ACID storage). Je toho hodně. Je jasné, že od prototypů k zamergování kódu do upstreamu bývá hodně dlouhá cesta, ale určitě se bude na co v následujících dvou letech těšit.
PostgreSQL má za sebou dobré roky, kdy se začíná postupně a úspěšně nasazovat i v korporátním prostředí, ve kterém se o open source databáze ještě před několika lety nezavadilo, a stávající uživatelé jsou vesměs spokojeni. Veškerá energie, co jde do vývoje a práce vývojářů je vidět.
+
  
Co můžeme čekat od verze 12? Pravděpodobně podporu JSONu podle ANSI/SQL, nové techniky optimalizace dotazu (přepis podmínek <code>OR</code> na <code>UNION</code>, natlačení agregace před JOIN, nové typy úložišť (např. [https://github.com/EnterpriseDB/zheap <i>zheap</i>], které není nutné vacuuovat), možná session proměnné, skoro určitě <code>MERGE</code> podle ANSI/SQL, možná transparentní šifrování. Plánů, prototypů i téměř hotových patchů je hodně. Uvidíme, co bude příští rok.
 
 
----------
 
----------
 
<ncl>Category:Verze PostgreSQL</ncl>
 
<ncl>Category:Verze PostgreSQL</ncl>
 
[[Category:Články]]
 
[[Category:Články]]
 
[[Category:Verze PostgreSQL]]
 
[[Category:Verze PostgreSQL]]

Aktuální verze z 17. 6. 2018, 04:45

PostgreSQL 10

Po roce tu máme opět novou verzi PostgreSQL, a to verzi 10. Rok vývoje proběhl relativně v klidu - dodržel se časový plán, takže zůstalo dost času na stabilizaci a dokonce i na dovolené top vývojářů. Zatím skoro pokaždé jsem mohl napsat, že nová verze obsahuje více novinek, a pracovalo na ní více vývojářů než na předchozí verzi. Nic se nezměnilo. Na Postgres se nabalují další a další projekty, a celý projekt se dostává do, pro mně, neuvěřitelného tempa. Je vidět, že infrastruktura, komunita funguje velice dobře. Neřeší se nesmysly - vše je soustředěné na práci a na konečný výsledek. Jako každý rok mohu napsat, že práce na některých funkcích finišují a na jiných teprve začínají. Už je vidět hodně velký pokrok v podpoře použití více CPU pro jeden dotaz. Na hodně vysoké úrovni je také optimalizace přístupu k cizím zdrojům (FDW). Desítka přináší novou implementaci partitioningu a s desítkou je také k dispozici logická replikace.

Touto verzí se přechází na nový systém číslování. Následující verze bude 11, další 12, atd. Jedná se pouze o kosmetickou změnu - nový způsob verzování je jednoznačný a není k němu nutná diskuze, zda-li má nebo nemá smysl zvýšit číslo linie. Vzhledem k způsobu vývoje bylo první číslo nic neříkající - rozdíl mezi 9.5 a 9.6 je výrazně větší než např. rozdíl mezi 7.4 a 8.0.

Přihlašování do PostgreSQL

Nově lze do connection stringu zadat vícenásobné URI, případně více serverů (každý server může mít ještě specifikován port). V případě, že se nepovede připojení na první server, klient se zkusí připojit na druhý, atd . Do connection stringu lze ještě přidat požadavek na na připojení typu read/write klíčem target_session_attrs=read-write. Tím se zablokuje připojení na repliky, které jsou read only, a kde by aplikace mohla mít problémy.

Používání md5 k ověřování přístupu se dnes již nepovažuje za bezpečné a implementují se modernější metody. V 10ce je to implementace SCRAM-SHA-256:

-- v pg_hba.conf musí být pro uživatele foorole nastavena ověřovací metoda scram
SET password_encryption = 'scram';
CREATE ROLE foorole PASSWORD 'foo';

Změny v psql

Konečně je v psql možnost použít základní jednoduché skriptování - příkazy \if, \elif, \else a \endif. Tento programovací jazyk není určený k psaní aplikací. Je určený k psaní konfiguračních skriptů (např. uložených v .psqlrc), alter skriptů nebo k psaní jednodušších testů a testovacích scénářů.

SELECT pg_is_in_recovery() as is_slave \gset
\if :is_slave
    \set PROMPT1 '\nslave %x$ '
\else
    \set PROMPT1 '\nmaster %x$ '
\endif

A jelikož je k dispozici alespoň základní logika, tak se také hodí nové vestavěné psql proměnné popisující verzi klienta a verzi serveru:

SERVER_VERSION_NAME = '10rc1'
SERVER_VERSION_NUM = '100000'
VERSION = 'PostgreSQL 10rc1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.1 20170915 (Red Hat 7.2.1-2), 64-bit'
VERSION_NAME = '10rc1'
VERSION_NUM = '100000'

psql zatím nemá k dispozici vlastní interpret výrazů - veškeré kalkulace se musí řešit přes server a SQL nebo přes shell operačního systému. Text mezi zpětnými apostrofy se vyhodnocuje shellem - nově lze uvnitř shellového výrazu použít i psql proměnné:

postgres=# \set a 10
postgres=# \set b 20
postgres=# \set c `echo $((:a + :b))`
postgres=# \echo :c
30

Další poměrně viditelnou změnou je rozšíření výpisu doby běhu dotazu (původně pouze ms) o zobrazení ve formátu intervalu:

postgres=# SELECT pg_sleep(160);
 pg_sleep 
----------
 
(1 row)

Time: 160101,032 ms (02:40,101)
 

Možná už jste někdy použili příkaz \g - je náhradou ke středníku a používá se pro spuštění příkazu. V posledních několika letech vzniklo několik odvozených příkazů - \gset, \gexec. Verze 10 přidává příkaz \gx, který spustí dotaz a výsledek zobrazí v expandovaném formátu (je to zkratka za \x příkaz \g\x). Ve verzi 11 bude k dispozici příkaz \gdesc, který zobrazí formát výsledku dotazu (sloupce a jejich datové typy).

Změny v SQL

Novinek ohledně SQL je minimum a jsou to spíš takové vychytávky zpříjemňující život (PostgreSQL má téměř kompletní podporu ANSI/SQL, takže není moc co přidávat).

Zřejmě nejdůležitější novinkou je podpora tzv ANSI/SQL identity sloupců - tyto sloupce obsahují unikátní hodnoty a jejich obsah a konzistenci řeší samotná databáze. Určitě znáte typ serial a jeho implementaci v Postgresu. Identity sloupce jsou svou funkcí (a vlastně i implementací) použití typu serial hodně podobné. Pro identity sloupce se používá ANSI/SQL zápis a vůči řešení s typem serial jsou téměř všechny hraniční případy dobře pokryty (pokusy o změny hodnoty, pokusy o přímé vložení hodnoty, vytváření klonu tabulky atd):

postgres=# CREATE TABLE itest1 (a int GERERATED ALWAYS AS IDENTITY, b text);
CREATE TABLE
postgres=# INSERT INTO itest1 VALUES(default,'aaa');
INSERT 0 1
postgres=# INSERT INTO itest1 VALUES(2,'aaa');
ERROR:  cannot insert into column "a"
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
postgres=# UPDATE itest1 SET a = a;
ERROR:  column "a" can only be updated to DEFAULT
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
postgres=# INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz');
INSERT 0 1

Jelikož identity sloupce mohou být různých typů (int,bigint, smallint), bylo nutné rozšířit podporu sekvencí o tuto možnost. Výsledkem je příkaz CREATE SEQUANCE AS type:

CREATE SEQUENCE sequence_test5 AS integer;
CREATE SEQUENCE sequence_test6 AS smallint;
CREATE SEQUENCE sequence_test7 AS bigint;

Poslední příjemnou novinkou je možnost neuvádět parametry funkce v DDL příkazech (ALTER FUNCTION, DROP FUNCTION) v případě, že funkce není přetížená (její název je unikátní). Odstraňování funkcí (příkaz DROP FUNCTION) s více parametry byl občas boj. Nyní to bude výrazně jednodušší (což u mazání zase nemusí být vždy výhoda).

Pokud používáte editovatelné pohledy (updateable views) nebo pohledy s INSTEAD OF triggerem, tak pro Vás může být zajímavá informace, že pro import do těchto pohledů lze od této verze použít i příkaz COPY.

Nové datové typy a funkce

Mám rád XML a rád jej v PostgreSQL používám. PostgreSQL má perfektní funkce pro generování XML - XMLELEMENT, XMLFOREST, ... Horší to bylo s parsováním XML - k dispozici byla pouze funkce XPATH, která samozřejmě, že fungovala, ale používala se při trochu složitější práci krkolomně. Raději jsem si napsal tabulkovou funkci v PLPerlu nebo PLPythonu, použil XML reader API, a funkci XPATH jsem se vyhnul. To už je minulost. Verze PostgreSQL přináší podporu SQL/XML funkce XMLTABLE. S touto funkcí lze velkou většinu XML dokumentů převést na tabulku:

CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>
  <ROW id="1">
    <COUNTRY_ID>AU</COUNTRY_ID>
    <COUNTRY_NAME>Australia</COUNTRY_NAME>
  </ROW>
  <ROW id="5">
    <COUNTRY_ID>JP</COUNTRY_ID>
    <COUNTRY_NAME>Japan</COUNTRY_NAME>
    <PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
    <SIZE unit="sq_mi">145935</SIZE>
  </ROW>
  <ROW id="6">
    <COUNTRY_ID>SG</COUNTRY_ID>
    <COUNTRY_NAME>Singapore</COUNTRY_NAME>
    <SIZE unit="sq_km">697</SIZE>
  </ROW>
</ROWS>
$$ AS data;

SELECT xmltable.*
  FROM xmldata,
       XMLTABLE('//ROWS/ROW'
                PASSING data
                COLUMNS id int PATH '@id',
                        ordinality FOR ORDINALITY,
                        "COUNTRY_NAME" text,
                        country_id text PATH 'COUNTRY_ID',
                        size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
                        size_other text PATH
                             'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
                        premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;

 id | ordinality | COUNTRY_NAME | country_id | size_sq_km |  size_other  | premier_name  
----+------------+--------------+------------+------------+--------------+---------------
  1 |          1 | Australia    | AU         |            |              | not specified
  5 |          2 | Japan        | JP         |            | 145935 sq_mi | Shinzo Abe
  6 |          3 | Singapore    | SG         |        697 |              | not specified

Pracuje se i na podpoře typů JSON, Jsonb. První novinkou je možnost vícenásobného mazání klíčů (nemusí se klíče odstraňovat v cyklu, a tudíž promazání může být výrazně rychlejší):

select '{"a":1 , "b":2, "c":3}'::jsonb - '{c,b}'::text[];
 ?column? 
----------
 {"a": 1}
(1 row)

Funkce json_populate_record a json_to_record dokaží pracovat i s dokumenty obsahující více úrovní nebo obsahující pole:

select *
   from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)

 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

Výraznější změny v podpoře JSONu, Jsonb bychom se měli dočkat ve verzi 11, která by měla obsahovat podporu standardu SQL/JSON.

Funkce to_date, to_timestamp byly tolerantní vůči nevalidnímu zápisu datumu (např. bylo možné zadat číslo dne 40). Předpokládalo se, že tato relativně stará implementace věrně emuluje Oracle a z důvody zachování kompatibility se na tyto funkce nesahalo. Poměrně nedávno se udělaly nové testy na Oracle, a zjistilo se, že nevalidní datum Oracle nedovolí, a že implementace v Postgresu je postavená na chybné tradici. Ve verzi 10 jsou tyto funkce restriktivní a nedovolí zadat na vstupu nevalidní datum. To u některých aplikací může způsobit problémy s kompatibilitou.

V extenzi pgcrypto je nová funkce pg_strong_random() určená pro generování silných náhodných čísel pro zabezpečení. K dispozici je nový datový typ macaddr8. Extenze contrib/btree_gist, která obsahuje gist indexy pro některé speciální datové typy nyní indexem podporuje i datový typ uuid.

Prostředí uložených procedur

Co se týče uložených procedur, tak ve verzi 10 je pouze jedna, ale zato docela významná nová funkce. V statement triggrech lze přistupovat k tzv změnovým tabulkám NEW a OLD, které obsahují řádky před a po provedení operace. Bez těchto tabulek byly statement triggery do jisté míry nepoužitelné. Existující workaroundy byly buďto nespolehlivé nebo pomalé, nebo jste byli nuceni napsat trigger v C. Osobně jsem statement trigger nikdy na nic nepotřeboval - lépe se mi pracuje s řádkovými (row) triggery. Neportoval jsem ale aplikace, kde by se tyto triggery používaly (např. v MSSQL výlučně). To dosud znamenalo téměř kompletní přepis odpovídající logiky. S novými změnovými tabulkami je portace takových triggerů výrazně jednodušší.

CREATE FUNCTION transition_table_level1_ri_parent_del_func()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $$
  DECLARE n bigint;
  BEGIN
    PERFORM FROM p JOIN transition_table_level2 c ON c.parent_no = p.level1_no;
    IF FOUND THEN
      RAISE EXCEPTION 'RI error';
    END IF;
    RETURN NULL;
  END;
$$;

CREATE TRIGGER transition_table_level1_ri_parent_del_trigger
  AFTER DELETE ON transition_table_level1
  REFERENCING OLD TABLE AS p
  FOR EACH STATEMENT EXECUTE PROCEDURE
    transition_table_level1_ri_parent_del_func();

Teoreticky by se statement triggery daly použít pro kontrolu referenční integrity (PostgreSQL používá řádkové systémové triggery). Když o tom tak přemýšlím, tak si nejsem jistý jestli by to byla velká výhra. Nyní má kontrola RI dost velkou režii, ale případný problém se identifikuje rychle a operace, která by vedla k porušení RI se okamžitě přeruší. Se statement triggery by odpadla režie RI, ale import by mohl pokračovat až do konce a na závěr by spadnul na triggerech. Ledaže by se ještě implementovala proměnná udávající maximální velikost změnové tabulky, která by inicializovala předčasnou aktivaci statement triggerů. Pak by to mohlo fungovat. O ničem takovém ale nevím.

Přístup k těmto změnovým tabulkám je i v triggerech napsaných v Perlu nebo Pythonu. Z pohledu kódu jsou to tabulky jako všechny jiné:

CREATE FUNCTION transition_table_test_f() RETURNS trigger LANGUAGE plperl AS
$$
    my $cursor = spi_query("SELECT * FROM old_table");
    my $row = spi_fetchrow($cursor);
    defined($row) || die "expected a row";
    elog(INFO, "old: " . $row->{id} . " -> " . $row->{name});
    my $row = spi_fetchrow($cursor);
    !defined($row) || die "expected no more rows";

    my $cursor = spi_query("SELECT * FROM new_table");
    my $row = spi_fetchrow($cursor);
    defined($row) || die "expected a row";
    elog(INFO, "new: " . $row->{id} . " -> " . $row->{name});
    my $row = spi_fetchrow($cursor);
    !defined($row) || die "expected no more rows";

    return undef;
$$;

Popularita Pythonu roste a roste i počet uživatelů, kteří píší uložené procedury v Pythonu. Při volání kódu v Pythonu se musí parametry převést do formátu, který podporuje runtime Pythonu. Naopak výsledek je nutné konvertovat do formátů Postgresu. Předchozí verze přidaly podporu jedno dimenzionálních polí. Počínaje verzí 10 mohou být parametry funkce, případně výsledek funkce i více dimenzionální pole.

Práce s řetězci

Měly by být odstraněny snad už všechny problémy s regulárními výrazy obsahujícími UTF8 více bajtové znaky (PostgreSQL má vlastní implementaci regulárních výrazů).

Poměrně zásadní je integrace knihovny ICU. Tato multiplatformní knihovna pokrývá kompletní spektrum operací nad Unicode řetězci a je poměrně často používaná. Postgres dosud běžel pouze nad vestavěnou podporou locales z operačního systému, a ve valné většině případů s knihovnami z operačního systému nebyly žádné problémy. Na některých platformách, i na některých verzích nebo distribucích Linuxu, ale buďto chybí podpora pro Unicode nebo používané verze glibc mají některé funkce chybně implementované (a pak některé optimalizace pro zrychlení řazení musí být vypnuté). Náhradním řešením pro tyto případy je právě použití knihovny ICU.

Administrace a monitoring

Prakticky všechny zálohovací aplikace se budou muset upravit pro PostgreSQL 10 a vyšší. Došlo k přejmenování několika kritických adresářů a funkcí. Tyto adresáře obsahovaly v názvu řetězec "log". Což, jak se několikrát ukázalo, není praktické (někteří uživatelé si při čištění logů smazali i tyto pro Postgres zásadní adresáře a soubory). To je důvod, proč se dotčené adresáře přejmenovaly - např. adresář "xlog" se přejmenoval na "wal" ("clog" se mění na "xact"). K podobné změně došlo v názvech funkcí. Např. funkce pg_switch_xlog byla přejmenována na pg_switch_wal. Běžných aplikací by se tyto změny vůbec neměly dotknout.

Roky upozorňuji uživatele, že by v Postgresu neměli používat hash index. I přes některé své zajímavé vlastnosti byl pro produkční nasazení nepoužitelný - nebyl jištěný transakčním logem. To znamená, že v případě havárie některé změny v obsahu indexu nemusely být zapsány na disk a jen otázkou náhody, jak moc by, díky tomu, byl hash index rozbitý. Docela dlouho se uvažovalo i o tom, že se podpora hash indexu odstraní. Od minulé verze mají vývojáři Postgresu k dispozici nové API umožňující extenzím zapisovat do transakčního logu (indexy v Postgresu jsou vlastně taky "extenze"). Toto API např. používá extenze bloom. A díky tomuto API už nebylo tak pracné dopsat podporu transakčního logu pro hash indexy. Tudíž od verze 10 je možné používat hash indexy v produkčním prostředí.

Politiky RLS (Row Level Security Policy) nejsou restriktivní v tom smyslu, že stačí, že je splněna jedna politika z několika a uživatel má přístup k datům. V 10 můžeme vytvářet tzv restriktivní politiky - ty musí být splněny vždy, bez ohledu na ostatní:

-- Pokud se admin připojí vzdáleně, pak tato politika
-- zabrání přístupu k obsahu tabulky passwd
CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
    USING (pg_catalog.inet_client_addr() IS NULL); 

Upravily se některé výchozí hodnoty v konfiguraci, které odpovídají základnímu nastavení replikace, tak aby šlo bezproblémově provést zálohování příkazem pg_basebackup. V pg_hba.conf je defaultně povolená replikace z localhostu:

wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

V předchozích verzích bylo možné používat tzv replikační sloty - jedná se o databázové objekty udržující stav replikace s konkrétním klientem a garantující možnost synchronizace s replikou navázanou na otevřený replikační slot. V 10ce jsou k dispozici dočasné replikační sloty - držené pouze po dobu připojení. Tuto funkcionalitu využívá pg_basebackup z 10ky - díky dočasnému replikačnímu slotu je garantováno, že nedojde k protočení transakčních logů, a že vytvořená záloha bude konzistentní (se všemi potřebnými segmenty transakčního logu).

Příkaz VACUUM se skládá ze tří kroků. V posledním kroku je snaha redukovat velikost tabulky, a tabulka (resp. její datové soubory) se čtou pozpátku. Zpětné sekvenční čtení samozřejmě operační systémy nepodporují, a tak je tato operace poměrně pomalá. Přednačítáním (prefetch) je možné ji výrazně urychlit. V komentáři k patchi je zmíněno pětinásobné zrychlení. Při běžném nasazení asi změnu nepoznáte, protože poslední krok je krátký, a tato optimalizace tam nehraje roli. Něco jiného je, pokud byste použili pg_repack. Pak tuto optimalizaci určitě oceníte.

Nová extenze amcheck obsahuje funkce umožňujíc zkontrolovat konzistenci btree a hash indexu. Primárně je určena pro regresní testy postgresu (obnova po havárii, replikace) ale stejně tak dobře může posloužit i dalším uživatelům.

Volbou --no-blobs můžeme blokovat zálohování blobů. V extenzi pg_stat_statements došlo k náhradě symbolu označující pozici parametru (dříve "?", nyní $1, $n).

Ve verzi 9.6 se zavedly tzv defaultní role - tehdy pouze role pg_signal_backend. Defaultní role umožňují získat některá jasně ohraničená práva uživatele postgres. V desítce jsou k dispozici další role určené k monitorování databáze: pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables a pg_monitor:

GRANT pg_signal_backend TO admin_user;

Pokud jsem potřeboval zjistit, kdo odkud má přístup k databázi, pak jsem se musel podívat do konfiguračního souboru pg_hba.conf. To už znamená minimálně nutnost připojení na server s databází. Od 10ky máme systémový pohled pg_hba_file_rules, kde je obsah pg_hba.conf zpřístupněn ve strukturované podobě:

postgres=# SELECT * FROM pg_hba_file_rules;
 line_number | type  |   database    | user_name |  address  |                 netmask                 | auth_method | options | error 
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
          84 | local | {all}         | {all}     |           |                                         | trust       |         | 
          86 | host  | {all}         | {all}     | 127.0.0.1 | 255.255.255.255                         | trust       |         | 
          88 | host  | {all}         | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
          91 | local | {replication} | {all}     |           |                                         | trust       |         | 
          92 | host  | {replication} | {all}     | 127.0.0.1 | 255.255.255.255                         | trust       |         | 
          93 | host  | {replication} | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
(6 rows)

Nově také nebude nutné si vlastními silami počítat replikační lag - pohled pg_stat_replication obsahuje metriky: write_lag, flush_lag a replay_lag. V nové verzi také uvidíme v pg_stat_activity všechny pracovní procesy bez ohledu na to, jestli jsou nebo nejsou připojené do konkrétní databáze. I tyto procesy mohou čekat na zámky, a proto je praktické je v pg_stat_activity zobrazit. Typ procesu je popsán ve sloupci backend_type:

  | wait_event_type |     wait_event      | state  | backend_xid | backend_xmin |              query               |    backend_type     
--+-----------------+---------------------+--------+-------------+--------------+----------------------------------+---------------------
  | Activity        | AutoVacuumMain      |        |             |              |                                  | autovacuum launcher
  | Activity        | LogicalLauncherMain |        |             |              |                                  | background worker
  |                 |                     | active |             |          568 | select * from pg_stat_activity ; | client backend
  | Activity        | BgWriterMain        |        |             |              |                                  | background writer
  | Activity        | CheckpointerMain    |        |             |              |                                  | checkpointer
  | Activity        | WalWriterMain       |        |             |              |                                  | walwriter

V předchozí verzi došlo k rozšíření pohledu pg_stat_activity. Přibyly sloupce wait_event a wait_event_type. V desítce zde můžeme vidět i čekání na IO.

Partitioning

Někdy i dobrý nápad se může ukázat v dlouhodobém výhledu jako kontraproduktivní. To se dá, bohužel, napsat i o partitioningu v Postgresu. Partitioning v Postgresu je téměř obecný a jeho implementace založená na dědičnosti a na redukci prováděcího plánu byla rychlá a spolehlivá. Bohužel, právě kvůli své univerzálnosti, je partitioning omezen pouze na čtení, a větší počet partitions na jednu tabulku dost nepříjemně prodlužuje dobu plánování dotazu. Navíc je partitioning statický (přístup k partitions je zafixovaný v době plánování dotazu), a tudíž pro přístup k datům využívajícím partitioning je nutné používat dynamické SQL.

To je dost důvodů, aby byl stávající partitioning výrazně přepracován. Práce na novém partitioningu začaly před několika roky. Ve verzi 10 můžeme vidět první výsledky. Ve verzi 11 se pracuje na optimalizaci planneru - mělo by dojít k výraznému zrychlení plánování dotazů nad partitiovanými tabulkami.

Ve verzi 10 Postgres umožňuje deklarativní zápis. Díky méně obecné implementaci už Postgres zvládne i write operace bez nutnosti psát triggery:

  • Partitioning založený na rozsahu (range):
    CREATE TABLE data(a text, vlozeno date) PARTITION BY RANGE(vlozeno);
    CREATE TABLE data_2016 PARTITION OF data FOR VALUES FROM ('2016-01-01') TO ('2016-12-31');
    CREATE TABLE data_2017 PARTITION OF data FOR VALUES FROM ('2017-01-01') TO ('2017-12-31');
    
    postgres=# INSERT INTO data VALUES('hello', '2016-06-08');
    INSERT 0 1
    postgres=# INSERT INTO data VALUES('hello', '2015-06-08');
    ERROR:  no partition of relation "data" found for row
    DETAIL:  Partition key of the failing row contains (vlozeno) = (2015-06-08).
    

    Dropnutí hlavní tabulky odstraní i partition tabulky bez nutnosti použití CASCADE:

    postgres=# DROP TABLE data;
    DROP TABLE
    
  • Partitioning založený na výčtu:
    CREATE TABLE data(a text, vlozeno date) PARTITION BY LIST(EXTRACT(YEAR FROM vlozeno));
    CREATE TABLE data_2016 PARTITION OF data FOR VALUES IN (2016);
    CREATE TABLE data_2017 PARTITION OF data FOR VALUES IN (2017);
    
    postgres=# INSERT INTO data VALUES('hello', '2015-06-08');
    ERROR:  no partition of relation "data" found for row
    DETAIL:  Partition key of the failing row contains (date_part('year'::text, vlozeno)) = (2015).
    postgres=# INSERT INTO data VALUES('hello', '2016-06-08');
    INSERT 0 1
    
    postgres=# EXPLAIN SELECT * FROM data WHERE extract(year from vlozeno) = 2016;
                                                      QUERY PLAN                                                  
    --------------------------------------------------------------------------------------------------------------
     Append  (cost=0.00..32.23 rows=6 width=36)
       ->  Seq Scan on data_2016  (cost=0.00..32.23 rows=6 width=36)
             Filter: (date_part('year'::text, (vlozeno)::timestamp without time zone) = '2016'::double precision)
    (3 rows)
    

Je zde ještě hodně omezení, ale ta by měla být během dvou následujících verzí postupně odstraněna.

Vestavěná logická replikace

Už je to určitě několik let, co Petr Jelínek poslal patch s prototypem do mailing listu. Roky trvalo, než se připravila potřebná infrastruktura (výrazně obecnější než co obsahoval původní patch) a než bylo možné čistě implementovat logickou replikaci. V desítce se základní balík patchů dostal do upstreamu a pro logickou master/slave replikaci už není potřeba instalace další extenze. Lze oprávněně předpokládat, že další integrace bude postupovat rychleji (cílem je master/master replikace, pro kterou je nyní nutná extenze BDR).

Fyzická replikace vyžaduje aby repliky byly identické klony mastera. Na slavea se z mastera přenáší informace o zápisech (prostřednictvím transakčního logu), a jejich aplikací se replika synchronizuje s masterem. Také u logické replikace se přenášejí informace o změnách dat (a také prostřednictvím transakčního logu) - není to statement replikace. Ale díky tomu, že se pracuje na logické úrovni, tak replika nemusí být identická s masterem a tudíž nemusíme replikovat celý server. Můžeme replikovat pouze vybrané tabulky (nebo také všechny - CREATE PUBLICATION test_pub FOR ALL TABLES).

Logická replikace probíhá na základě obvyklého modelu - na masteru máme balíky publikovaných tabulek. Na replikách se přihlásíme k odběru vybraných balíků.

Logická replikace se používá docela snadno (slave u mne běží lokálně na portu 5433). Pro logickou replikaci není nutné modifikovat pg_hba.conf. V postgresql.conf je nutné nastavit wal_level na logical.

Na masteru vytvoříme tabulku a tu zveřejníme:

CREATE TABLE foo(id int primary key, a int);
CREATE PUBLICATION test_pub FOR TABLE foo;
INSERT INTO foo VALUES(1, 200);

Pokud má tabulka primární klíč, tak rovnou na ní můžeme provádět i jiné operace než INSERT. Pokud nemá a chtěli bychom mazat, editovat, pak musíme nastavit replikační identitu (viz příkaz ALTER TABLE REPLICA IDENTITY).

Na slave musíme vytvořit také tabulku (DDL příkazy se zatím nereplikují) a přihlásit se k odběru:

CREATE TABLE foo(id int primary key, a int);
CREATE SUBSCRIPTION test_sub CONNECTION 'port=5432' PUBLICATION test_pub;

Po dokončení příkazu je replikace aktivní a tabulka foo je naplněná:

-- slave
postgres=# SELECT * FROM foo;
 id |  a   
----+------
  1 | 1000
(1 row)

Tabulka foo je i na slave read/write - takže mohu i zde vložit řádek do této tabulky. Pokud by na masteru došlo ke vložení řádku se stejným id, pak nedojde ke kolizi, ale data z masteru přepíšou data na replice (pokud bych chtěl jiné řešení kolizí, pak si musím nainstalovat extenzi pgLogical nebo BDR). Jsem rád za takto jednoduché řešení. Většině lidí bude vyhovovat jednoduchý default, a pro ty, kdo potřebují něco jiného, je tu plán B. Trochu jsem se obával, že používání logické replikace bude komplikované (na základě zkušeností se Slony), je ale vidět, že moje obavy byly zbytečné.

V souvislosti s logickou replikací pozor na příkaz TRUNCATE - ten se zatím nezapisuje do transakčního logu, a tudíž se nepřenese na repliky.

Optimalizace a zpracování dotazů

Nově FDW API umožňuje tzv aggregate push down. V tomto kontextu to znamená, že se agregace může provést na cizím serveru. Postgres dostane rovnou agregovaná data. Samozřejmě, že toto chování musí podporovat použitý FDW driver.

Zrychlit by se měly agregace nad numerickým sloupcem založené na sumě. Výrazně rychlejší by měla být operace hashagg v případě, že výsledný počet řádků (počet skupin) je větší než několik tisíc. To by se mělo projevit i ve výběru unikátních hodnot (DISTINCT). V 10ce se také objeví první patche, které optimalizují evaluaci výrazů. Dalším krokem by mělo být nasazení JIT na počítání výrazů (PostgreSQL 11 nebo 12). Zkoušel jsem maximální možné zrychlení pro jednotlivé optimalizace (best case) na tabulce s 10M řádků (cca 500MB).

  • Počítání agregační funkce sum nad typem numeric - cca 25%
  • Optimalizace hash agg - zrychlení o cca 20%
  • Optimalizace evaluace výrazů - zrychlení o cca 45%

Jaké by bylo reálné zrychlení se vůbec nedá odhadnout - v praxi je výrazně více IO waitů - na druhou stranu jeden dotaz může obsahovat více agregačních funkcí i výrazně komplikovanější výrazy než jsem zkoušel. Pokud ale IO nebude výrazným hrdlem, tak si dovolím tipnout, že by 10ka měla být v řadě dotazů o nějakých 20% rychlejší (OLAP).

Použití RLS (Row Level Security) mohlo blokovat některé optimalizace plánovače dotazů (např. flattening). Výsledkem mohl být, samozřejmě, pomalý dotaz. V desítce se pracuje s RLS chytřeji a k blokování optimalizací by nemělo docházet.

V 9.5 se objevila možnost používat tzv GROUPING SETs. Algoritmus agregace byl omezen na klasický sort aggregate. Tento algoritmus vyžaduje seřazená vstupní data. V desítce může optimalizátor zvolit i hash aggregate. Ušetří se tím jeden sort (u větších dat potenciálně hodně drahá operace).

Nově lze použít index i nad výčtovými typy (díky extenzi btree_gist). Taktéž je možné použít fulltext (a fulltextový index) nad daty typu JSON, Jsonb. V indexu by se měl lépe recyklovat volný prostor - mělo by docházet k menšímu nafukování indexů.

Optimalizace se dočkala i operace bitmap heap scan. Opět by mělo dojít k výraznému zrychlení.

V 10 se zvětšuje množina operací podporující paralelní zpracování dotazu (použití více CPU pro jeden dotaz): parallel index scan, parallel index only scan, paralel bitmap heap scan, gather merge (redukuje zbytečné řazení).

Statistiky, více sloupcové statistiky

Optimalizace dotazů v Postgresu je založená na odhadech budoucího výsledku (resp. efektu použitých podmínek a operací) a odhad je vychází ze sloupcových statistik (viz příkaz ANALYZE). Použitý statistický model předpokládá nezávislost mezi uloženými daty. Často ale pracujeme s daty, kde nějaká závislost mezi atributy je, občas je závislost velice silná (obec, okres, kraj). Potom dochází k podstřelení odhadu, který se může velmi nepříjemně projevit např. nevhodně použitým nested loopem. Stávající statistiky můžeme rozšířit více sloupcovou statistikou vytvořenou příkazem CREATE STATISTICS:

CREATE STATISTICS nazev_okres_id (dependencies) ON nazev, okres_id FROM obce;

Volbou dependencies určuje, že se statistika váže k funkčním závislostem mezi sloupci. Pokud použijeme ndistinct, tak bude zpřesňovat odhady počtu unikátních hodnot (ndistinct), které se používají například při optimalizaci agregace.

Bohužel nemám žádná data, na kterých by byl vidět pozitivní efekt těchto nových statistik, takže nic dalšího k těmto statistikám neukáži. Opět, implementace, která je v 10ce je spíš nastartování spíše dlouhodobého a komplikovaného projektu. Zatím se vícesloupcové statistiky počítají pouze v rámci jedné tabulky. Syntaxe je připravena i pro počítání statistik mezi tabulkami. Jde zejména o podchycení vazbu mezi PK a FK. Tam v některých aplikacích (zejména OLAP - kalkulace s časovou dimenzí) dochází k naprosto fatálním odhadům.

pgexportdoc, pgimportdoc, pspg

Hodně zajímavou vlastností je schopnost Postgresu pracovat s velkými dokumenty (texty, XMLka, JSONy) jako s hodnotami. Menší problémem je export a import těchto dokumentů z a do Postgresu bez vlastní aplikace. PostgreSQL nabízí několik způsobů a API, ale žádný nástroj, který by se dal jednoduše použít z příkazové řádky, případně se dal použít pro psaní skriptů. Po několika letech jsem doiteroval ke dvěma jednoduchým aplikacím, které jednoduše nahrají dokument ze souboru do postgresu (pgimportdoc) nebo naopak uloží dokument z Postgresu do souboru (pgexportdoc).

[pavel@localhost ]$ pgimportdoc postgres -f ~/Stažené/enprimeur.xml -c 'insert into xmldata values($1)' -t XML
[pavel@localhost ]$ cat ~/Stažené/enprimeur.xml | pgimportdoc postgres -c 'insert into xmldata values($1)' -t XML
[pavel@localhost ]$ cat ~/Stažené/enprimeur.xml | pgimportdoc postgres -E latin2 -c 'insert into doc values($1) returning id' -t TEXT
[pavel@localhost ]$ pgexportdoc -c 'select x from xmldata where id = 1' -t XML -f myxmldoc.xml

Rád pracuji v textovém režimu - pro mne je to příjemná úleva pro oči - a také mohu jednoduše pracovat vzdáleně na relativně pomalé lince a neřešit rychlost linky. Navíc v psql se mi dobře pracuje - v podstatě nikdy jsem nepotřeboval nic jiného. Základem pro ergonomickou práci v psql je přenastavení defaultního pageru more na less. Roky jsem s lessem pracoval, ale říkal jsem si, že by to chtělo trochu vylepšit. less je generický pager - nemá žádnou speciální funkcionalitu pro prohlížení tabulek a samozřejmě, že občas je problém, že nějaká informace "ujede" mimo obrazovku. Výsledkem několika měsíčního si hraní je nový pager pspg - napsaný speciálně na použití v psql. Základní vlastností je možnost zafixování prvních n řádků a prvních m sloupců plus inteligentní skrolování po sloupcích. Možnost vybrat si barevné schéma je spíš malá legrácka.

Ora2pg, plpgsql_check, Orafce

Ora2pg je volně dostupná aplikace určená ke konverzi dat a struktur z Oracle, MySQL do PostgreSQL. Za poslední rok tato aplikace udělala neskutečný pokrok v podpoře migrace PL/SQL funkcí a procedůr. Pro někoho může být podstatné schopnost migrace SQL příkazů / pohledů z Oracle pluskové syntaxe outer joinů do ANSI SQL syntaxe. Už dříve (a k tomu bylo Ora2pg navrženo a používáno) se bezproblémově a poměrně jednoduše používalo k migraci schématu. Samotný export dat může být rychlejší díky podpoře více pracovních procesů (workerů).

Zatím to vypadá, že se plpgsql_check součástí upstreamu nestane - ne v nejbližší době - jedná se o cca 5000 řádků kódu a protlačit je do upstreamu by zabralo hodně času a v tuhle chvíli to není pro nikoho priorita. Vývoj v GitHubu funguje na výbornou. Díky tomu, že je plpgsql_check v komunitním repozitáři, tak je pro většinu uživatelů snadno dostupný (problém je s buildem pro Windows - to je docela opruz). Nově plpgsql_check mnohem lépe detekuje nepoužívané proměnné, nenastavené výstupní parametry a nově obsahuje i detekci mrtvého kódu.

create or replace function fx(x int)
returns int as $$
begin
  begin
    if (x > 0) then
      raise exception 'xxx' using errcode = 'XX888';
    else
      raise exception 'yyy' using errcode = 'YY888';
    end if;
    return -1; -- dead code;
  end;
  return -1;
end;
$$ language plpgsql;

select * from plpgsql_check_function_tb('fx(int)');

 functionid | lineno | statement | sqlstate |     message      | detail | hint |     level     | position | query | context
------------+--------+-----------+----------+------------------+--------+------+---------------+----------+-------+---------
 fx         |      9 | RETURN    | 00000    | unreachable code |        |      | warning extra |          |       |
 fx         |     11 | RETURN    | 00000    | unreachable code |        |      | warning extra |          |       |
(2 rows)

create or replace function fx(x int)
returns int as $$
begin
  begin
    if (x > 0) then
      raise exception 'xxx' using errcode = 'XX888';
    else
      raise exception 'yyy' using errcode = 'YY888';
    end if;
  exception
    when sqlstate 'XX888' then
      null;
    when sqlstate 'YY888' then
      null;
  end;
end; -- missing return;
$$ language plpgsql;

select * from plpgsql_check_function_tb('fx(int)');

 functionid | lineno | statement | sqlstate |                    message                     | detail | hint | level | position | query | context
------------+--------+-----------+----------+------------------------------------------------+--------+------+-------+----------+-------+---------
 fx         |        |           | 2F005    | control reached end of function without RETURN |        |      | error |          |       |
(1 row)

Největší změnou v Orafce je nová dokumentace jak samotné knihovny Orafce, tak procesu migrace z Oracle do Postgresu. Relativně malou změnou je přidání několika pohledů emulujících některé systémové pohledy v Oracle, které se používají v uložených procedurách.

Závěr

10ka je určitě dobrá verze a vzhledem k času na finalizaci by měla být rychlá a stabilní. To, co se ale připravuje do 11ky a do 12ky, tak to bude docela síla - ať už je to integrace JIT pro vyhodnocení výrazů, radikální zrychlení plánování nad partitiovanými tabulkami, implementace podpory JSONu podle standardu, možnost relativně jednoduše navrhovat další storage (existuje prototyp paměťového ACID storage). Je toho hodně. Je jasné, že od prototypů k zamergování kódu do upstreamu bývá hodně dlouhá cesta, ale určitě se bude na co v následujících dvou letech těšit.