Zobrazení zdroje stránky PostgreSQL 9.4 (2014): transakční sql json databáze

<p>Postgres má ve svém genetickém kódu tři základní vlastnosti ACID, SQL a rozšiřitelnost. To umožňuje jednoduše použít Postgres pro správu dat v extrémně různých oborech - pro zpracování socioekonomických dat, pro zpracování vědeckých dat, pro řízení procesů. S kvalitní podporou formátu JSON (ve verzi 9.4) může Postgres konkurovat etablovaným databázím určeným pro správu dokumentů (Document Management Systems).</p>
<p>Přemýšlím, co mám o 9.4 a o vývoji 9.4 napsat. Je to verze, na které asi zatím pracovalo nejvíce vývojářů v historii Postgresu, a je to znát na počtu a složitosti nově implementovaných funkcí. Dokončila se implementace některých funkcí (např. editovatelné pohledy), jiné funkce konečně dostaly smysl (např. díky snížení úrovně zámku možnost odložené validace podmínek), a na jiné se opět nedostalo (Row-level security nebo moje implementace vylepšené kontroly SQL příkazů v PLpgSQL). Konečně, s vestavěnou implementací nerelačního typu jsonb se otevřel nový prostor pro nasazení PostgreSQL. Background workery (jak to přeložit?) jsou už ve stavu, kdy lze nad nimi psát smysluplné extenze a ještě v 9.4 se stihl implementovat základ pro logickou replikaci (zatím s omezenou funkcionalitou, která je zatím zajímavá jen pro autory extenzí). Udělalo se toho docela dost a nebojím se tvrdit, že proces založený na commitfestech funguje a rozumně vytyčuje mantinely pro komunitní vývoj tak, aby nedocházelo k větším konfliktům a k rozhádání komunity. To je dáno i tím, že relativně hodně vývojářů pracuje s/na Postgresu už skoro 10 let (a někteří už téměř 20 let), znají své hranice a vědí, že jiný než praktický a pragmatický přístup k vývoji a k diskuzím v mailing listu nemá smysl.</p>
<p>Na vývoji 9.4 se podepsala (pozdržením možná i o dva měsíce) kritická chyba (vlastně několik souvisejících chyb) v 9.3. Pro mne jednou z nejzajímavějších funkcí v 9.3 (a pro uživatele Postgresu v ČR se kterými jsem v kontaktu) byla optimalizace zamykání spojeného s implementací referenční integrity (což jsou takové ty zámky, o kterých člověk netuší, kde se mu v aplikaci berou). V důsledku této chyby mohl transakční log obsahovat některé informace ve špatném pořadí. Chyba se projevila jen při vyšší zátěži a určité souhře okolností. V každém případě ale mohla vést k poškození dat na masteru (po obnově) nebo k poškození repliky (což několik uživatelů zahlásilo). To by už samo o sobě bylo dost nepříjemné, ale přišel ještě větší problém. Opravou této chyby se zanesla nová kritická chyba do nepostižené 9.2 (opět s impaktem na uživatele vestavěné binární replikace). V krátké době se muselo vydat několik kritických oprav a teprve verze 9.3.4 je bezpečná.</p>
<p>Kdybych si měl vybrat svého favorita, tak ať přemýšlím jak přemýšlím, myslím si, že nejvíce uživatelů v tuto chvílí ocení podporu tzv ordered aggregates - agregačních funkcí definovaných nad seřazenými daty (určitě znáte percentily - medián je 50% percentil). Nerelační datové typy (HStore, JSON a XML) zas až tak tady rozšířené nejsou, jakkoliv jsou užitečné (např. dobře mohou nahradit EAV) a adaptace na jsonb bude trvat pár let. A pokud by pro uživatele nebyla možnost kalkulace percentilů zajímavá, pak si možná vybere z následujícího seznamu:</p>
==Rozšíření SQL==
===Podmíněné agregační funkce===
<p>Nově můžeme u agregační funkce použít klauzuli <code>FILTER (WHERE )</code>. Podobné chování jsem už dříve mohl dosáhnout pomocí vloženého výrazu <code>CASE</code>. Nový způsob je o něco expresivnější, přímočařejší.</p>
<pre>postgres=# SELECT count(*), count(*) FILTER (WHERE i &gt; 5) FROM generate_series(1,10) g(i);
 count | count
-------+-------
    10 |     5
(1 row)</pre>
===Agregační funkce na seřazených datech===
<p>Některé agregační funkce má smysl počítat pouze na seřazených datech. Speciální syntaxe těchto agregačních funkcí tuto vlastnost zdůrazňuje. Funkce generate_series vrací posloupnost od 1 do 10. 50% percentil - medián je hodnota 5.5:</p>
<pre>postgres=# SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY i) FROM generate_series(1,10) g(i);
 percentile_cont
-----------------
             5.5
(1 row)</pre>
<p>Proprietární rozšíření funkce <em>percentile_cont</em> umožňuje jednorázově zadat (v poli) a naráz spočítat více percentilů (výsledkem je pole percentilů):</p>
<pre>postgres=# SELECT percentile_cont(ARRAY[0.1,0.5,0.9]) WITHIN GROUP (ORDER BY i) FROM generate_series(1,10) g(i);
 percentile_cont
-----------------
 {1.9,5.5,9.1}
(1 row)</pre>
<p>Agregační funkce rank (existuje také mírně odlišná analytická funkce rank) vrací hypotetické pořadí prvku v množině:</p>
<pre>postgres=# SELECT rank(1.75) WITHIN GROUP (ORDER BY v) FROM unnest(ARRAY[2,1.3,1.4,1.6,1.8]) g(v);
 rank
------
    4
(1 row)</pre>
===Slučování výsledků funkcí vracejících tabulku na základě pořadí a klauzule WITH ORDINALITY===
<p>Pomocí konstruktoru tabulky &quot;rows from&quot; můžeme spojovat tabulky získané jako výsledek volání SRF funkcí (Set Returning Functions) a to na základě pořadí řádků. Speciální klauzulí <code>WITH ORDINALITY</code> připojíme k výsledné tabulce sloupec s čísly řádků.</p>
<pre>postgres=# SELECT * FROM ROWS FROM (generate_series(1,2), generate_series(1,3)) g(a1, a2);
 a1 | a2
----+----
  1 |  1
  2 |  2
    |  3
(3 rows)</pre>
<p>Víceparametrický unnest je speciálním případem použití konstruktoru &quot;rows from&quot;.</p>
<pre>postgres=# SELECT * FROM unnest(array[1,3], array[3,4,5,6], array[0,1]) g(a,b,c);
 a | b | c
---+---+---
 1 | 3 | 0
 3 | 4 | 1
   | 5 | 
   | 6 | 
(4 rows)

postgres=# SELECT * FROM unnest(array[1,3], array[3,4,5,6], array[0,1]) WITH ORDINALITY g(a,b,c);
 a | b | c | ordinality
---+---+---+------------
 1 | 3 | 0 |          1
 3 | 4 | 1 |          2
   | 5 |   |          3
   | 6 |   |          4
(4 rows)</pre>
<p>Klauzuli <code>WITH ORDINALITY</code> lze použít i pro stávající SRF funkce (a explicitně joinovat):</p>
<pre>postgres=# SELECT * FROM generate_series(1,10,2) WITH ORDINALITY g(i, ord);
 i | ord
---+-----
 1 |   1
 3 |   2
 5 |   3
 7 |   4
 9 |   5
(5 rows)</pre>
===Refaktoring implementace klauzule IF EXISTS v příkazu DROP===
<p>Všechny příkazy typu <code>DROP</code> nyní podporují klauzuli <code>IF EXISTS</code>, přičemž implementace této klauzule je vždy tolerantní vůči chybám (např. použití neexistujícího schématu, tabulky). V předchozích verzích někde klauzule <code>IF EXISTS</code> chyběla úplně, někde byla implementovaná, ale vyhodila vyjímku pokud obsahovalo odkaz na neexistující schéma (např. při dropnutí tabulky). Počínaje 9.4 je ve všech příkazech DROP a všude se chová stejně.</p>
<p>Této vlastnosti využívá implementace přepínače <code>--if-exists</code> příkazu pg_dump. Ten zajistí vložení klauzule <code>IF EXISTS</code> do příkazů <code>DROP</code>, které jsou vložené před příkazy <code>CREATE TABLE</code> (což aktivuje přepínač <code>--clean</code>). Motivací je eliminovat zavádějící (falešné) chyby typu &quot;object doesn't exists&quot;, které by se zobrazily při obnově databáze.</p>
==Materializovavané a aktualizovatelné pohledy==
<p>Materializované pohledy se v PostgreSQL aktualizují explicitně příkazem <code>REFRESH</code>. Tento příkaz vyžaduje exkluzivní zámek i pro čtení a blokuje čtení pohledu. Příkaz <code>REFRESH MATERIALIZED VIEW CONCURRENTLY</code> umožní aktualizaci pohledu bez zamknutí proti čtení. Nic ale není zadarmo - aktualizace může běžet déle a vyžaduje alespoň jeden unikátní index nad materializovaným pohledem</p>
<pre>postgres=# CREATE MATERIALIZED VIEW xxx AS SELECT current_timestamp AS refreshed FROM pg_sleep(20) x;
SELECT 1
postgres=# CREATE UNIQUE INDEX ON xxx(refreshed);
CREATE INDEX
postgres=# REFRESH MATERIALIZED VIEW xxx; -- blokuje čtení
REFRESH MATERIALIZED VIEW
postgres=# REFRESH MATERIALIZED VIEW  CONCURRENTLY xxx ; -- neblokuje čtení
REFRESH MATERIALIZED VIEW</pre>
<p>Další změny se týkají aktualizovatelných pohledů (updateable views). V 9.4 byla dokončena jejich implementace - implementací klauzule <code>CHECK OPTION</code>. Skrze tuto klauzuli si vynucujeme test viditelnosti záznamu po provedení DML příkazu (<code>INSERT</code>, <code>UPDATE</code>). Pokud pohled má klauzuli <code>CHECK OPTION</code>, tak každý přidaný/upravený záznam musí být vždy viděn v pohledu (pohled se nesmí chovat jako černá díra). Predikáty, které se použijí v pohledu, který má klauzuli <code>CHECK OPTION</code> se kontrolují vždy a jsou analogií tabulkových omezení (table constraints).</p>
<pre>postgres=# CREATE TABLE data(a int);
CREATE TABLE
postgres=# CREATE VIEW less20 AS SELECT * FROM data WHERE a &lt; 20;
CREATE VIEW
postgres=# CREATE VIEW great0_less20 AS SELECT * FROM less20 WHERE a &gt; 0 WITH LOCAL CHECK OPTION;
CREATE VIEW
postgres=# CREATE VIEW _great0_less20 AS SELECT * FROM less20 WHERE a &gt; 0 WITH CASCADED CHECK OPTION;
CREATE VIEW
postgres=# INSERT INTO great0_less20 VALUES(10);
INSERT 0 1
postgres=# INSERT INTO great0_less20 VALUES(20); -- efekt LOCAL CHECK OPTION
INSERT 0 1
postgres=# INSERT INTO _great0_less20 VALUES(20);
ERROR:  new row violates WITH CHECK OPTION for view &quot;less20&quot;
DETAIL:  Failing row contains (20).</pre>
<p><code>CASCADED CHECK OPTION</code> zajistí, že se kontrolují i &quot;zděděné&quot; predikáty z všech pohledů (i těch, které nemají klauzuli <code>CHECK OPTION</code>). Toto chování je implicitní pro klauzuli <code>CHECK OPTION</code> (tj pokud neuvedete klíčové slovo <code>CASCADED</code>). <code>LOCAL CHECK OPTION</code> vynutí kontrolu predikátů pouze u pohledů, které mají klauzuli <code>CHECK OPTION</code>. <code>LOCAL CHECK OPTION</code> nepřepíše zděděné <code>CASCADED CHECK OPTION</code>:</p>
<pre>postgres=# CREATE VIEW yyy AS SELECT * FROM _great0_less20 WITH LOCAL CHECK OPTION;
CREATE VIEW
postgres=# INSERT INTO yyy VALUES(20);
ERROR:  new row violates WITH CHECK OPTION for view &quot;less20&quot;
DETAIL:  Failing row contains (20).</pre>
<p>a z definice <code>CASCADED CHECK OPTION</code> přepíše <code>LOCAL CHECK OPTION</code>:</p>
<pre>postgres=# CREATE VIEW zzz AS SELECT * FROM great0_less20 WITH CASCADED CHECK OPTION;
CREATE VIEW
postgres=# INSERT INTO zzz VALUES(20);
ERROR:  new row violates WITH CHECK OPTION for view &quot;less20&quot;
DETAIL:  Failing row contains (20).</pre>
<p>Více lidí si při testování této funkce stěžovalo na neintuitivnost návrhu této funkce - je to ale naprogramované, tak jak vyžaduje standard a ve shodě s implementací v Oracle a DB2.</p>
<p>Aktualizovatelné pohledy nyní mohou obsahovat výrazy.</p>
<pre>postgres=# CREATE VIEW test1 AS SELECT a, sin(a) FROM data;
CREATE VIEW
postgres=# INSERT INTO test1 VALUES(10,0); -- neprojde (výraz nelze přepsat)
ERROR:  cannot insert into column &quot;sin&quot; of view &quot;test1&quot;
DETAIL:  View columns that are not columns of their base relation are not updatable.
postgres=# INSERT INTO test1(a) VALUES(10) RETURNING *;
+----+-------------------+
| a  |        sin        |
+----+-------------------+
| 10 | -0.54402111088937 |
+----+-------------------+
(1 row)

INSERT 0 1</pre>
<p>V předešlých verzích pohledy, které měli flag security barrier (sloužící k zajištění bezpečného omezení přístupu k datům) nebyly aktualizovatelné. Opět počínaje 9.4 se toto omezení ruší.</p>
==Nové funkce a datové typy==
<p>Datové typy pro uložení síťových adres inet a cidr mají nyní vestavěnou podpporu GiST indexu:</p>
<pre>CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops);
SET enable_seqscan TO off;

SELECT * FROM inet_tbl WHERE i &gt;&gt;= &#39;192.168.1.0/24&#39;::cidr ORDER BY i;
       c        |        i        
----------------+------------------
 192.168.1.0/24 | 192.168.1.0/24
 192.168.1.0/24 | 192.168.1.226/24
 192.168.1.0/24 | 192.168.1.255/24
(3 rows)</pre>
===Nerelační datový typ jsonb===
<p>Bezpochyby největším marketingovým trhákem 9.4 bude vestavěný datový typ <em>jsonb</em>. V Postgresu navazuje na typ <em>HStore</em> s několika rozdíly. Je integrován do jádra, podporuje rekurzi, umožňuje indexaci a vstup/výstup je ve formátu JSON (technologicky je blíže typu HStore než typu JSON z 9.3). Vyzkoušel jsem si načíst, fragmentovat a oindexovat jeden větší JSON:</p>
<pre>[pavel@localhost ~]$ ls -lh citylots.json 
-rw-rw-r--. 1 pavel pavel 181M May 20 19:35 citylots.json

[pavel@localhost ~]$ head citylots.json 
{
&quot;type&quot;: &quot;FeatureCollection&quot;,
&quot;features&quot;: [
{ &quot;type&quot;: &quot;Feature&quot;, &quot;properties&quot;: { &quot;MAPBLKLOT&quot;: &quot;0001001&quot;, &quot;BLKLOT&quot;: &quot;0001001&quot;, &quot;BLOCK_NUM&quot;: &quot;0001&quot;, &quot;LOT_NUM&quot;: &quot;001&quot;, &quot;FROM_ST&quot;: &quot;0&quot;, &quot;TO_ST&quot;: &quot;0&quot;, &quot;STREET&quot;: &quot;UNKNOWN&quot;, &quot;ST_TYPE&quot;: null, &quot;ODD_EVEN&quot;: &quot;E&quot; }, &quot;geometry&quot;: { &quot;type&quot;: &quot;Polygon&quot;, &quot;coordinates&quot;: [ [ [ -122.422003528252475, 37.808480096967251, 0.0 ], [ -122.422076013325281, 37.808835019815085, 0.0 ], [ -122.421102174348633, 37.808803534992904, 0.0 ], [ -122.421062569067274, 37.808601056818148, 0.0 ], [ -122.422003528252475, 37.808480096967251, 0.0 ] ] ] } }
,
{ &quot;type&quot;: &quot;Feature&quot;, &quot;properties&quot;: { &quot;MAPBLKLOT&quot;: &quot;0002001&quot;, &quot;BLKLOT&quot;: &quot;0002001&quot;, &quot;BLOCK_NUM&quot;: &quot;0002&quot;, &quot;LOT_NUM&quot;: &quot;001&quot;, &quot;FROM_ST&quot;: &quot;0&quot;, &quot;TO_ST&quot;: &quot;0&quot;, &quot;STREET&quot;: &quot;UNKNOWN&quot;, &quot;ST_TYPE&quot;: null, &quot;ODD_EVEN&quot;: &quot;E&quot; }, &quot;geometry&quot;: { &quot;type&quot;: &quot;Polygon&quot;, &quot;coordinates&quot;: [ [ [ -122.42082593937107, 37.808631474146033, 0.0 ], [ -122.420858049679694, 37.808795641369592, 0.0 ], [ -122.419811958704301, 37.808761809714007, 0.0 ], [ -122.42082593937107, 37.808631474146033, 0.0 ] ] ] } }
,
{ &quot;type&quot;: &quot;Feature&quot;, &quot;properties&quot;: { &quot;MAPBLKLOT&quot;: &quot;0004002&quot;, &quot;BLKLOT&quot;: &quot;0004002&quot;, &quot;BLOCK_NUM&quot;: &quot;0004&quot;, &quot;LOT_NUM&quot;: &quot;002&quot;, &quot;FROM_ST&quot;: &quot;0&quot;, &quot;TO_ST&quot;: &quot;0&quot;, &quot;STREET&quot;: &quot;UNKNOWN&quot;, &quot;ST_TYPE&quot;: null, &quot;ODD_EVEN&quot;: &quot;E&quot; }, &quot;geometry&quot;: { &quot;type&quot;: &quot;Polygon&quot;, &quot;coordinates&quot;: [ [ [ -122.415701204606876, 37.808327252671461, 0.0 ], [ -122.415760743593196, 37.808630700240904, 0.0 ], [ -122.413787891332404, 37.808566801319841, 0.0 ], [ -122.415701204606876, 37.808327252671461, 0.0 ] ] ] } }
,
{ &quot;type&quot;: &quot;Feature&quot;, &quot;properties&quot;: { &quot;MAPBLKLOT&quot;: &quot;0005001&quot;, &quot;BLKLOT&quot;: &quot;0005001&quot;, &quot;BLOCK_NUM&quot;: &quot;0005&quot;, &quot;LOT_NUM&quot;: &quot;001&quot;, &quot;FROM_ST&quot;: &quot;206&quot;, &quot;TO_ST&quot;: &quot;286&quot;, &quot;STREET&quot;: &quot;JEFFERSON&quot;, &quot;ST_TYPE&quot;: &quot;ST&quot;, &quot;ODD_EVEN&quot;: &quot;E&quot; }, &quot;geometry&quot;: { &quot;type&quot;: &quot;Polygon&quot;, &quot;coordinates&quot;: [ [ [ -122.417346670944355, 37.808121127609709, 0.0 ], [ -122.417457443198046, 37.808685627252729, 0.0 ], [ -122.416003128921787, 37.808638547938997, 0.0 ], [ -122.41593547450509, 37.808293744156337, 0.0 ], [ -122.417346670944355, 37.808121127609709, 0.0 ] ] ] } }
[pavel@localhost ~]$ 
</pre>
<p>Pro import do Postgresu použiji LO API:</p>
<pre>postgres=# \lo_import ~/citylots.json 
lo_import 16510</pre>
<p>A dále jej převedu do jednořádkových tabulek</p>
<pre>CREATE OR REPLACE FUNCTION public.bytea_to_text(bytea)
 RETURNS text
 LANGUAGE sql
AS $function$
SELECT convert_from($1, current_setting(&#39;server_encoding&#39;))
$function$

CREATE TABLE city_text(data text);
INSERT INTO city_text SELECT bytea_to_text(lo_get(16510));
CREATE TABLE city_json(data json);
CREATE TABLE city_jsonb(data jsonb);
INSERT INTO city_json SELECT data::json FROM city_text;
INSERT INTO city_jsonb SELECT data::jsonb FROM city_text;</pre>
<p>Můžete si všimnout, že velikost tabulek je cca 1/4 vstupního souboru (díky transparentní komprimaci dat v Postgresu) a že jsonb má větší režii (ale nikterak velkou) než typ JSON a typ text (ve skutečnosti je JSON ukládán úplně stejně jako text).</p>
<pre>postgres=# \dt+ city*
                     List of relations
 Schema |    Name    | Type  | Owner | Size  | Description 
--------+------------+-------+-------+-------+-------------
 public | city_json  | table | pavel | 48 MB | 
 public | city_jsonb | table | pavel | 51 MB | 
 public | city_text  | table | pavel | 48 MB | 

-- Aktualizace pro finální verzi Postgresu (po redesignu formátu jsonb)
                     List of relations
 Schema |    Name    | Type  | Owner | Size  | Description 
--------+------------+-------+-------+-------+-------------
 public | city_json  | table | pavel | 48 MB | 
 public | city_jsonb | table | pavel | 42 MB | 
 public | city_text  | table | pavel | 48 MB | 
</pre>
<p>V dalším kroku se pokusím rozložit hlavní pole v JSONu na jednotlivé prvky a uložit je jako samostatné řádky:</p>
<pre>postgres=# INSERT INTO city_json_lines SELECT json_array_elements(json_object_field(data, &#39;features&#39;)) FROM city_json;
INSERT 0 206560
Time: 14778.181 ms
postgres=# INSERT INTO city_jsonb_lines SELECT jsonb_array_elements(jsonb_object_field(data, &#39;features&#39;)) FROM city_jsonb;
INSERT 0 206560
Time: 13085.024 ms</pre>
<p>Zde je rychlost operací nad typy JSON a jsonb zhruba stejná a opět je vidět mírně větší režie (objemově) typu jsonb:</p>
<pre>postgres=# \dt+ *_lines
                        List of relations
 Schema |       Name       | Type  | Owner |  Size  | Description 
--------+------------------+-------+-------+--------+-------------
 public | city_json_lines  | table | pavel | 155 MB | 
 public | city_jsonb_lines | table | pavel | 175 MB | 

-- Aktualizace pro finální verzi
                        List of relations
 Schema |       Name       | Type  | Owner |  Size  | Description 
--------+------------------+-------+-------+--------+-------------
 public | city_json_lines  | table | pavel | 160 MB | 
 public | city_jsonb_lines | table | pavel | 170 MB | 
</pre>
<p>Z dat ve formátu JSON, jsonb mohu extrahovat data:</p>
<pre>postgres=# SELECT DISTINCT json_extract_path_text(data, &#39;properties&#39;, &#39;STREET&#39;) FROM city_json_lines LIMIT 5;
 json_extract_path_text 
------------------------
 
 HUBBELL
 FLOOD
 BADGER
 BALDWIN
(5 rows)</pre>
<p>Při vyhledávání v neoindexovavých datech se už pak ukazují rozdíly mezi těmito typy (jsonb je cca 2-5x rychlejší):</p>
<pre>postgres=# SELECT count(DISTINCT json_extract_path_text(data, &#39;properties&#39;, &#39;STREET&#39;)) FROM city_json_lines;
 count 
-------
  1716
(1 row)

Time: 3016.364 ms
postgres=# SELECT count(DISTINCT jsonb_extract_path_text(data, &#39;properties&#39;, &#39;STREET&#39;)) FROM city_jsonb_lines;
 count 
-------
  1716
(1 row)

Time: 1685.399 ms

postgres=# SELECT count(*) FROM city_json_lines WHERE json_extract_path_text(data, &#39;properties&#39;, &#39;STREET&#39;) = &#39;FRANCE&#39;;
 count 
-------
    37
(1 row)

Time: 1597.302 ms

postgres=# SELECT count(*) FROM city_jsonb_lines WHERE jsonb_extract_path_text(data, &#39;properties&#39;, &#39;STREET&#39;) = &#39;FRANCE&#39;;
 count 
-------
    37
(1 row)

Time: 322.104 ms

-- další možný zápis
postgres=# SELECT count(*) FROM city_jsonb_lines WHERE data-&gt;&#39;properties&#39;-&gt;&gt;&#39;STREET&#39; = &#39;FRANCE&#39;;
 count 
-------
    37
(1 row)

Time: 357.258 ms</pre>
<p>Typ jsonb umožňuje vytvořit univerzální (nemusím dopředu vyjmenovat klíče) GIN index:</p>
<pre>CREATE INDEX ON city_jsonb_lines USING gin (data);
CREATE INDEX
Time: 44938.092 ms
postgres=# CREATE INDEX ON city_jsonb_lines ((data-&gt;&#39;properties&#39;-&gt;&gt;&#39;STREET&#39;));
CREATE INDEX
Time: 2217.319 ms

postgres=# \di+ city_jsonb_*
                                       List of relations
 Schema |           Name            | Type  | Owner |      Table       |  Size   | Description 
--------+---------------------------+-------+-------+------------------+---------+-------------
 public | city_jsonb_lines_data_idx | index | pavel | city_jsonb_lines | 137 MB  | 
 public | city_jsonb_lines_expr_idx | index | pavel | city_jsonb_lines | 5008 kB | 
(2 rows)</pre>
<p>Vytvořený index je docela velký a tak pokud nepotřebujeme univerzální řešení, může být praktičtější používat funkcionální index:</p>
<pre>-- použití univerzálního indexu
postgres=# EXPLAIN ANALYZE SELECT count(*) FROM city_jsonb_lines WHERE data @&gt; &#39;{&quot;properties&quot;:{&quot;STREET&quot;:&quot;FRANCE&quot;}}&#39;;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=815.29..815.30 rows=1 width=0) (actual time=0.819..0.820 rows=1 loops=1)
   -&gt;  Bitmap Heap Scan on city_jsonb_lines  (cost=53.60..814.78 rows=207 width=0) (actual time=0.693..0.800 rows=37 loops=1)
         Recheck Cond: (data @&gt; &#39;{&quot;properties&quot;: {&quot;STREET&quot;: &quot;FRANCE&quot;}}&#39;::jsonb)
         Heap Blocks: exact=14
         -&gt;  Bitmap Index Scan on city_jsonb_lines_data_idx  (cost=0.00..53.55 rows=207 width=0) (actual time=0.659..0.659 rows=37 loops=1)
               Index Cond: (data @&gt; &#39;{&quot;properties&quot;: {&quot;STREET&quot;: &quot;FRANCE&quot;}}&#39;::jsonb)
 Planning time: 0.209 ms
 Execution time: 0.941 ms
(8 rows)

--použití funkcionálního indexu
postgres=# EXPLAIN ANALYZE SELECT count(*) FROM city_jsonb_lines WHERE data-&gt;&#39;properties&#39;-&gt;&gt;&#39;STREET&#39; = &#39;FRANCE&#39;;
                                                                 QUERY PLAN                                             
---------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3360.23..3360.24 rows=1 width=0) (actual time=0.203..0.203 rows=1 loops=1)
   -&gt;  Bitmap Heap Scan on city_jsonb_lines  (cost=24.43..3357.65 rows=1033 width=0) (actual time=0.130..0.187 rows=37 loops=1)
         Recheck Cond: (((data -&gt; &#39;properties&#39;::text) -&gt;&gt; &#39;STREET&#39;::text) = &#39;FRANCE&#39;::text)
         Heap Blocks: exact=14
         -&gt;  Bitmap Index Scan on city_jsonb_lines_expr_idx  (cost=0.00..24.17 rows=1033 width=0) (actual time=0.085..0.085 rows=37 l oops=1)
               Index Cond: (((data -&gt; &#39;properties&#39;::text) -&gt;&gt; &#39;STREET&#39;::text) = &#39;FRANCE&#39;::text)
 Planning time: 0.185 ms
 Execution time: 0.298 ms
(8 rows)</pre>
<p>Podpora JSONu a jsonb je v začátcích a rozhodně je to jeden ze směrů, který by měl být v dalších verzích rozvíjen a podporován.</p>
===Nové funkce pro typ JSON===
<p>V 9.4 se rozšiřuje podpora typu JSON o několik funkcí. Funkce <code>json_typeof(json)</code> zobrazí typ honoty typu JSON. Funkce <code>json_build_array</code> vytvoří pole ve formátu JSON. <code>json_build_object</code> vytvoří objekt ve formátu JSON.</p>
<pre>postgres=# SELECT json_typeof(json &#39;123&#39;), json_typeof(json &#39;&quot;ahoj&quot;&#39;), json_typeof(json &#39;true&#39;);
 json_typeof | json_typeof | json_typeof
-------------+-------------+-------------
 number      | string      | boolean
(1 row)

postgres=# SELECT json_build_array(1,2,3);
 json_build_array
------------------
 [1, 2, 3]
(1 row)

Time: 0.594 ms
postgres=# SELECT json_build_array(&#39;Hi&#39;,&#39;Hello&#39;);
 json_build_array
------------------
 [&quot;Hi&quot;, &quot;Hello&quot;]
(1 row)

Time: 0.871 ms
postgres=# SELECT json_build_object(&#39;name&#39;,&#39;Tom&#39;,&#39;surname&#39;, &#39;Lane&#39;);
          json_build_object          
--------------------------------------
 {&quot;name&quot; : &quot;Tom&quot;, &quot;surname&quot; : &quot;Lane&quot;}
(1 row)

Time: 1.091 ms
postgres=# SELECT json_build_object(&#39;name&#39;,&#39;Tom&#39;,&#39;surname&#39;, &#39;Lane&#39;, &#39;Age&#39;,48);
                json_build_object                
--------------------------------------------------
 {&quot;name&quot; : &quot;Tom&quot;, &quot;surname&quot; : &quot;Lane&quot;, &quot;Age&quot; : 48}
(1 row)</pre>
<p>Nově máme k dispozici funkce sloužící k parsování JSONu - <code>json_to_record</code> a <code>json_to_recordset</code>:</p>
<pre>postgres=# SELECT * FROM json_to_record(&#39;{&quot;name&quot; : &quot;t15&quot;, &quot;type&quot; : &quot;GE1043&quot;}&#39;, true) AS x(name text, type text);
 name |  type 
------+--------
 t15  | GE1043
(1 row)

postgres=# SELECT * FROM json_to_recordset(&#39;[{&quot;a&quot;:10, &quot;b&quot;:10},{&quot;a&quot;:30}]&#39;, true) AS x(a int, b int);
 a  |   b   
----+--------
 10 |     10
 30 | [null]
(2 rows)</pre>
<p>Funkce <code>json_array_elements</code> a <code>json_array_elements_text</code> rozvine JSON pole na tabulku v Postgresu:</p>
<pre>postgres=# SELECT * FROM json_array_elements_text(&#39;[{&quot;a&quot;:10, &quot;b&quot;:10},{&quot;a&quot;:30}, &quot;hi&quot;]&#39;);
      value      
------------------
 {&quot;a&quot;:10, &quot;b&quot;:10}
 {&quot;a&quot;:30}
 hi
(3 rows)

Time: 1.041 ms
postgres=# SELECT * FROM json_array_elements(&#39;[{&quot;a&quot;:10, &quot;b&quot;:10},{&quot;a&quot;:30}, &quot;hi&quot;]&#39;);
      value      
------------------
 {&quot;a&quot;:10, &quot;b&quot;:10}
 {&quot;a&quot;:30}
 &quot;hi&quot;
(3 rows)</pre>
===Číselné konstruktory pro typ date, timestamp, interval===
<p>Implementací těchto funkcí jsem si splnil jeden svůj starý sen. Hrozně mi vadilo, že k vytvoření datumu z číselných poležek jsem musel použít formátované řetězce (a poměrně složité operace s řetězci), přičemž ale ty potřebné funkce v kódu Postgresu jsou, nejsou ovšem zpropagované do SQL.</p>
<pre>-- nesprávně - náchylné být nefunkční při změně konfigurace Postgresu
CREATE OR REPLACE FUNCTION create_date1(y int, m int, d int)
RETURNS date LANGUAGE sql AS
$$ SELECT format(&#39;%s-%s-%s&#39;, $1, $2, $3)::date $$;

-- správně, ale vyžaduje manipulaci s řetězci a parsování řetězce
CREATE OR REPLACE FUNCTION create_date2(y int, m int, d int)
RETURNS date LANGUAGE sql AS
$$ SELECT to_date(format(&#39;%s-%s-%s&#39;, $1, $2, $3), &#39;YYYY-MM-DD&#39;) $$;
SELECT create_date2(2014,5,8);

-- řešení v 9.4, bez jakékoliv manipulace s řetězci
postgres=# SELECT make_date(2014,5,8);
 make_date
────────────
 2014-05-08
(1 row)</pre>
<p>Ne, že by na soudobých CPU představovala manipulace s řetězci v těchto funkcích významnější úzké hrdlo (při běžném použití databáze), ale jistým malým způsobem mi to vždy trhalo žíly. V každém případě nová sada funkcí je efektivně implementovaná a navíc přináší určitý komfort:</p>
<pre>postgres=# SELECT make_timestamp(2014,5,8,9,37,0);
   make_timestamp
─────────────────────
 2014-05-08 09:37:00
(1 row)

postgres=# SELECT make_interval(0,0,2,1);
 make_interval
───────────────
 15 days
(1 row)

postgres=# SELECT make_interval(weeks := 2, days := 1);
 make_interval
───────────────
 15 days
(1 row)

Time: 0.820 ms
postgres=# SELECT make_interval(months := 1);
 make_interval
───────────────
 1 mon
(1 row)</pre>
===Funkce pro konverzi mezi LO a Bytea===
<p>I když si nemyslím, že BLOBy (Large Objects) v databázi mají smysl, někdy se můžou hodit a ušetřit dost práce. V Postgresu existuje docela šikovné API, které umožní jednoduše přesouvat obsah LO mezi klientem a serverem. I starší verze umožňovaly další manipulaci s LO (na straně serveru), např. konverzi do typu Bytea - a následnou konverzi do řetězce v kódování Base64. Muselo se ale trochu hackovat. Tento nedostatek je v 9.4 odstraněn díky funkcím <code>lo_create</code>, <code>lo_get</code> a <code>lo_put</code>:</p>
<pre>postgres=# \lo_import ~/avatar.png
lo_import 16395
postgres=# SELECT encode(lo_get(16395), &#39;base64&#39;);
                                    encode                                   
──────────────────────────────────────────────────────────────────────────────
 iVBORw0KGgoAAAANSUhEUgAAACsAAAAyCAIAAABK5rXEAAAACXBIWXMAAAsTAAALEwEAmpwYAAAA↵
 B3RJTUUH1woNFgE2tT89UQAAEu9JREFUWMNVeFmQnNd13jnn3n/rdbqnZ8FsxDILVoEkSJiSKIqk↵
 bFEORcf0VnaYKJW4Uik/ZHlybD/k3alKKlV5SrkqlUSplEzLkexyKZIIElwAEiIJEAABYsBZMIPZ↵
 p5fp/d/uPScPPYDi+9D/7eque879v7N838Hn/vRzRCQieLyQmEVBDGAMKBHtoVYmVJKA0kZAlGJE↵
 ...</pre>
===Ostatní nové funkce===
<p>Funkce <code>pg_sleep</code> dostala dva sourozence - <code>pg_sleep_for</code> (s parametrem interval) a <code>pg_sleep_until</code>. Jsou to v podstatě jen SQL makra, jejichž smyslem je zjednodušit vývojářům život. S těmito funkcemi ale opatrně! Pokud by se použily uvnitř déle trvající transakce, kde se navíc přistupuje k tabulkám, tak se efektivně vyblokuje VACUUM (jinak, volání funkce <em>sleep</em> uvnitř transakce většinou není dobrý nápad). A když už jsem nakousl toto téma. Hlídejte si delší otevřené transakce - v pohledu <code>pg_stat_activity</code> vidíte záznam ve stavu &quot;idle in transaction&quot;. Jedná se o aplikační chybu, která vede ke zpomalení postgresu (díky vyblokovanému VACUUMu), druhak k vyčerpání spojení do databáze (bo otevřené transakce blokují znovupoužití spojení).</p>
<pre>SELECT pg_sleep(1.5);
SELECT pg_sleep_for(&#39;5 minutes&#39;);
SELECT pg_sleep_until(&#39;tomorrow 03:00&#39;);</pre>
<p>V předchozích verzích nebylo úplně triviální otestovat, zda je nějaké pole prázdné (funkce <code>array_upper</code>, <code>array_lower</code>, <code>array_dims</code> vrací NULL) . S funkcí <code>cardinality(pole)</code> to už triviální je. Tato funkce vrací počet prvků v poli, a pro prázdné pole vrací nulu.</p>
<pre>postgres=# SELECT array_dims(&#39;{}&#39;::int[]);
 array_dims
────────────
 [null]
(1 row)

postgres=# SELECT cardinality(&#39;{}&#39;::int[]);
 cardinality
─────────────
           0
(1 row)</pre>
==Prostředí uložených procedur==
<p>Nově lze event triggery (DDL) psát i v Perlu:</p>
<pre>CREATE OR REPLACE FUNCTION perlsnitch() RETURNS event_trigger AS $$
  elog(NOTICE, &quot;perlsnitch: &quot; . $_TD-&gt;{event} . &quot; &quot; . $_TD-&gt;{tag} . &quot; &quot;);
$$ LANGUAGE plperl;

CREATE EVENT TRIGGER perl_a_snitch
    ON ddl_command_start
    EXECUTE PROCEDURE perlsnitch();</pre>
<p>Pro PL/pgSQL je tu nově možnost aktivovat extra kontroly kódu, přičemž si lze vybrat, jestli tyto kontroly mají vyhazovat varování nebo chyby. Zatím se jedná spíše o koncept - k dispozici je pouze jedna kontrola na zastíněné proměnné.</p>
<pre>SET plpgsql.extra_warnings TO &#39;shadowed_variables&#39;;

CREATE FUNCTION foo(f1 int) RETURNS int AS $$
DECLARE
f1 int;
BEGIN
RETURN f1;
END
$$ LANGUAGE plpgsql;
WARNING:  variable &quot;f1&quot; shadows a previously defined variable
LINE 3: f1 int;

SET plpgsql.extra_errors to &#39;shadowed_variables&#39;;
create or replace function shadowtest(f1 int)
   returns boolean as $$
declare f1 int; begin return 1; end $$ language plpgsql;
ERROR:  variable &quot;f1&quot; shadows a previously defined variable
LINE 3: declare f1 int; begin return 1; end $$ language plpgsql;</pre>
<p>Krůčkem vpřed je možnost získat obsah zásobníku volání (call stack) příkazem <code>GET DIAGNOSTICS PG_CONTEXT</code>:</p>
<pre>CREATE OR REPLACE FUNCTION public.outer_func() RETURNS integer AS $$
BEGIN
  RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.inner_func() RETURNS integer AS  $$
DECLARE
  stack text;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  RAISE NOTICE e&#39;--- Call Stack ---\n%&#39;, stack;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE:  --- Call Stack ---
PL/pgSQL function inner_func() line 4 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
 outer_func
------------
          1
(1 row)</pre>
<p>S debugováním plpgsql kódu by mohl pomoci přepínač <code>#print_strict_params</code>, který zajistí zobrazení parametrů dotazů označených jako <code>STRICT</code> (chceme aby výsledek byl přesně jeden řádek), pokud vrátí počet řádků různý od jedné. Mimo kód si lze vynutit toto chování nastavením <code>plpgsql.print_strict_params</code> na true v konfigu Postgreu (výchozí hodnota je false):</p>
<pre>-- test printing parameters after failure due to STRICT
CREATE TABLE foo AS SELECT 2 f1, &#39;foo&#39;::text f2;

SET plpgsql.print_strict_params TO true;

CREATE OR REPLACE FUNCTION footest() RETURNS void AS $$
DECLARE
  x record;
  p1 int := 2;
  p3 text := &#39;foo1&#39;;
BEGIN
  -- no rows
  SELECT * FROM foo WHERE f1 = p1 AND f1::text = p3 INTO STRICT x;
END$$ LANGUAGE plpgsql;

postgres=# SELECT footest();
ERROR:  query returned no rows
DETAIL:  parameters: p1 = &#39;2&#39;, p3 = &#39;foo1&#39;
CONTEXT:  PL/pgSQL function footest() line 8 at SQL statement
Time: 43.162 ms</pre>
<p>Pokud nemáte kód, který je založen na vyhazování a obsluhování velkého počtu vyjímek, tak tato funkce bude mít zanedbatelnou režii a není důvod, proč ji nezapnout (pokud používáte plpgsql a klauzuli <code>STRICT</code>).</p>
<p>Pro zobrazení delších textů se může hodit možnost zalamování textů v extended režimu (upozornění: tato funkce bude dostupná až v následující verzi)</p>
<p>[[Soubor:psql-9.1.png|extended mode 9.1]], [[Soubor:psql-9.4.png|extended wrapped mode 9.4]]</p>
==Správa a monitoring databáze==
<p>Malou, ale zásadní změnou je možnost konfigurace serveru čistě pomocí SQL resp. příkazu <code>ALTER SYSTEM SET</code>. Reload konfigurace se vynutí voláním funkce <code>pg_reload_conf()</code>.</p>
<p>S instalací PostgreSQL získáte desítky extenzí - některé z nich je nutné explicitně aktivovat příkazem <code>LOAD</code>. Pro opakované použití můžeme extenzi načíst při startu Postgresu - v konfiguraci nastavíme seznam <code>shared_preload_libraries</code> nebo (nově) <code>session_preload_libraries</code>. Změna první hodnoty vyžaduje restart Postgresu. <code>session_preload_libraries</code> restart nevyžaduje (vztahuje se na nově vytvořené spojení) a může se specificky upravit pro uživatele, pro databázi. Extenze, které pracují se sdílenou pamětí, zatím vyžadují aktivaci pomocí <code>shared_preload_libraries</code>. V budoucnu by ale tento požadavek mohl zmizet.</p>
<p>V 9.4 můžeme omezit rychlost klonování serveru (throttling) nastavením <code>--max-rate</code> (v kB za sec) příkazu <code>pg_basebackup</code>. Nově také můžeme v <code>pg_basebackup</code> realokovat tablespaces (volba <code>-T olddir=newdir</code>).</p>
<p>Ve specifických případech se může hodit volba <code>--analyze-in-stages</code> příkazu vacuumdb. Ta způsobí opakované volání přikazu ANALYZE s parametry statistic target: 1, 10, 100. Při první iteraci můžeme mít k dispozici hrubé statistiky velice rychle, každá následující iterace statistiky zpřesňuje. Při binárním upgrade nedochází k přenosu statistik a možnost rychle získat alespoň hrubé statistiky snižuje dobu nezbytného výpadku.</p>
<pre>/usr/local/pgsql/bin/vacuumdb -v  --analyze-in-stages postgres &amp;&gt; ~/log

INFO:  analyzing &quot;public.pgbench_accounts&quot;
INFO:  &quot;pgbench_accounts&quot;: scanned 300 of 634921 pages, containing 18900 live rows and 0 dead rows; 300 rows in sample, 40000024 estimated total rows
...
INFO:  analyzing &quot;public.pgbench_accounts&quot;
INFO:  &quot;pgbench_accounts&quot;: scanned 3000 of 634921 pages, containing 189000 live rows and 0 dead rows; 3000 rows in sample, 40000024 estimated total rows
...
INFO:  analyzing &quot;public.pgbench_accounts&quot;
INFO:  &quot;pgbench_accounts&quot;: scanned 30000 of 634921 pages, containing 1890000 live rows and 0 dead rows; 30000 rows in sample, 40000024 estimated total rows</pre>
<p>Zásadní úpravy se dočkala extenze [http://www.postgresql.org/docs/9.4/static/pgstatstatements.html <code>pg_stat_statements</code>]. Tato extenze zajišťuje statisku použití SQL příkazů včetně redukce konstant (nahrazení konstant parametry, tj <code>SELECT * FROM t1 WHERE c1='Pavel'</code> a <code>SELECT * FROM t1 WHERE c1='Petr'</code> se po normalizaci stane jedním SQL příkazem <code>SELECT * FROM t1 WHERE c1 = ?</code>). Ke každému SQL příkazu se eviduje počet exekucí, celková doba provádění, přístupy do cache, použití IO a použití dočasných souborů. Dříve tato data byla kompletně uložena v sdílené paměti. V 9.4 je text SQL příkazu uložen v souboru. Tím se výrazně redukuje alokace paměti a díky tomu se výchozí limit maximálního počtu příkazů mohl změnit z 1000 na 5000.</p>
<p>V 9.4 se také zaloguje držitel zámku (číslo procesu) v záznamu logu o čekání na zámek delším než 1sec (ve výchozím nastavení). Později můžeme zkusit dohledat tento proces v zalogovaných pomalých dotazech a potvrdit si tak (nebo vyvrátit) hypotézu, že zámky jsou způsobeny pomalými dotazy.</p>
<pre>-- 9.3
LOG:  process 26258 still waiting for ShareLock on transaction 3143 after 1000.180 ms
STATEMENT:  update t set a = 59;
LOG:  process 26258 acquired ShareLock on transaction 3143 after 13065.146 ms
STATEMENT:  update t set a = 59;

-- 9.4
LOG:  process 20863 still waiting for ShareLock on transaction 2029 after 1000.179 ms
DETAIL:  Process holding the lock: 20861. Wait queue: 20863.
CONTEXT:  while updating tuple (0,2) in relation &quot;t&quot;
STATEMENT:  update t set a = 59;
LOG:  process 20863 acquired ShareLock on transaction 2029 after 14124.213 ms
CONTEXT:  while updating tuple (0,2) in relation &quot;t&quot;
STATEMENT:  update t set a = 59;</pre>
===Replikace===
<p>Déle než rok se pracuje na implementaci vestavěné logické replikace. Možná i více než polovina patchů je již integrovaná do jádra. Nicméně vyjma několika experimentálních extenzí (jedna z extenzi implementuje [http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-output-plugin-logical-replication/ auditing]), není tento nový kód zpřístupněn uživatelům. Na produkčně použitelné extenze si budeme muset ještě jeden rok počkat (věřím, že v 9.5 logická replikace bude).</p>
<p>Pracovalo se i na vestavěné binární replikaci - nastavením <code>recovery_min_apply_delay</code> (např na hodnotu 5min) lze pozdržet aplikaci segmentů transakčních logů a vytvořit tak repliku, která je vůči masteru explicitně posunutá v čase (delayed replication).</p>
===Snížení úrovně zámků pro vybrané příkazy ALTER TABLE===
<p>Ve starších verzích všechny příkazy ALTER TABLE vynucovaly exkluzivní přístup (zámek) k tabulce. Což samozřejmě způsobovalo problémy u větších tabulek a pomalejších operací. Počínaje 9.4 si varianty příkazu <code>ALTER</code>: <code>VALIDATE CONSTRAINT</code>, <code>CLUSTER ON</code>, <code>SET WITHOUT CLUSTER</code>, <code>ALTER COLUMN SET STATISTICS</code>, <code>ALTER COLUMN SET ()</code>, <code>ALTER COLUMN RESET ()</code> vystačí s méně agresivním zámkem (<code>VALIDATE CONSTRAINT</code> vyžaduje ShareUpdateExclusiveLock - blokuje pouze další ALTER nebo VACUUM).</p>
<pre>-- vyžaduje exkluzivní zámek, ale stávající data nekontroluje
postgres=# ALTER TABLE omega ADD CONSTRAINT slow CHECK(slow_constraint(a)) NOT VALID;
ALTER TABLE
-- kontroluje stávající data, ale neblokuje DML a SELECT
postgres=# ALTER TABLE omega VALIDATE CONSTRAINT slow;
ALTER TABLE</pre>
<p>Na toto chování se čekalo dlouho - teprve nyní má <code>ALTER TABLE ADD CONSTRAINT NOT VALID</code> smysl.</p>
===Příkaz EXPLAIN===
<p>U komplikovanějších dotazů s větším počtem JOINů (větší &gt; 16) už i plánování dotazu může mít nezanedbatelnou režii (vteřiny, případně desítky vteřin). Této režie si nemusí být uživatel vědom, a při optimalizaci dotazů se může vydat po falešné stopě. Aby se tak nestalo, tak nyní <code>EXPLAIN</code> zobrazuje čas nezbytny pro naplánování dotazu:</p>
<pre>                                                                  QUERY PLAN                                                                 
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Sort  (cost=20.74..20.77 rows=10 width=133) (actual rows=1 loops=1)
   Sort Key: n.nspname, c.relname
   Sort Method: quicksort  Memory: 17kB
   -&gt;  Hash Join  (cost=1.14..20.58 rows=10 width=133) (actual rows=1 loops=1)
         Hash Cond: (c.relnamespace = n.oid)
         -&gt;  Seq Scan on pg_class c  (cost=0.00..19.06 rows=19 width=73) (actual rows=52 loops=1)
               Filter: ((relkind = ANY (&#39;{r,&quot;&quot;}&#39;::&quot;char&quot;[])) AND pg_table_is_visible(oid))
               Rows Removed by Filter: 246
         -&gt;  Hash  (cost=1.10..1.10 rows=3 width=68) (actual rows=2 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               -&gt;  Seq Scan on pg_namespace n  (cost=0.00..1.10 rows=3 width=68) (actual rows=2 loops=1)
                     Filter: ((nspname &lt;&gt; &#39;pg_catalog&#39;::name) AND (nspname &lt;&gt; &#39;information_schema&#39;::name) AND (nspname !~ &#39;^pg_toast&#39;::text))
                     Rows Removed by Filter: 4
 Planning time: 0.741 ms
 Execution time: 0.668 ms
(15 rows)

Time: 2.977 ms</pre>
<p>Data mám v cache (a jsou malá), takže u tohoto dotazu plánování zabere o něco více než samotná exekuce dotazu. Dalším vylepšením příkazu <code>EXPLAIN</code> je zobrazení klíčů pro agregaci (což může pomoci se orientovat v komplexnějším plánu):</p>
<pre>postgres=# EXPLAIN select count(*), relnamespace from pg_class group by relnamespace;
                           QUERY PLAN                           
─────────────────────────────────────────────────────────────────
 HashAggregate  (cost=12.42..12.46 rows=3 width=4)
   Group Key: relnamespace
   -&gt;  Seq Scan on pg_class  (cost=0.00..10.95 rows=295 width=4)
 Planning time: 0.182 ms
(4 rows)</pre>
<p>Posledním vylepšením příkazu <code>EXPLAIN</code> je zobrazení počtu ztrátových nebo úplných stránek k bitmap heap scanu. Bitmap heap scan je řízen bitmapou o velikosti, která by měla být menší než je <code>work_mem.</code> Při dostatku paměti tato bitmapa obsahuje id řádků, při nedostatku id datových stránek - případně část jsou id řádků, část id stránek. Jelikož se pro id řádků nemusí provádět recheck, tak jsou operace s řádkovými id rychlejší (tuple id) za cenu větší spotřeby paměti:</p>
<pre>postgres=# CREATE TABLE aa AS SELECT * FROM generate_series(1, 1000000) AS a ORDER BY random();
SELECT 1000000
postgres=# CREATE INDEX aai ON aa(a);
CREATE INDEX
postgres=# SET enable_indexscan=false;
SET
postgres=# SET enable_seqscan=false;
SET
postgres=# SET work_mem = &#39;64kB&#39;;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM aa WHERE a BETWEEN 100000 AND 200000;
                                                        QUERY PLAN                                                       
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Bitmap Heap Scan on aa  (cost=1853.03..7233.48 rows=97230 width=4) (actual time=21.461..345.237 rows=100001 loops=1)
   Recheck Cond: ((a &gt;= 100000) AND (a &lt;= 200000))
   Rows Removed by Index Recheck: 712693
   Heap Blocks: exact=814 lossy=3108
   -&gt;  Bitmap Index Scan on aai  (cost=0.00..1828.72 rows=97230 width=0) (actual time=21.288..21.288 rows=100001 loops=1)
         Index Cond: ((a &gt;= 100000) AND (a &lt;= 200000))
 Planning time: 0.291 ms
 Execution time: 474.558 ms
(8 rows)

postgres=# SET work_mem = &#39;10MB&#39;;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM aa WHERE a BETWEEN 100000 AND 200000;
                                                        QUERY PLAN                                                       
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Bitmap Heap Scan on aa  (cost=1853.03..7233.48 rows=97230 width=4) (actual time=20.858..173.533 rows=100001 loops=1)
   Recheck Cond: ((a &gt;= 100000) AND (a &lt;= 200000))
   Heap Blocks: exact=3922
   -&gt;  Bitmap Index Scan on aai  (cost=0.00..1828.72 rows=97230 width=0) (actual time=20.073..20.073 rows=100001 loops=1)
         Index Cond: ((a &gt;= 100000) AND (a &lt;= 200000))
 Planning time: 0.183 ms
 Execution time: 300.147 ms
(7 rows)</pre>
===ALTER TABLESPACE MOVE===
<p>Příkaz <code>ALTER TABLESPACE MOVE</code> nám umožňuje přesunout určitý typ objektů (vše, tabulky, indexy, materializované pohledy) do jiného tablespace. Objekty ještě můžeme filtrovat podle vlastníka:</p>
<pre>ALTER TABLESPACE testspace MOVE ALL TO pg_default;
ALTER TABLESPACE testspace2 MOVE TABLES OWNED BY test_owner TO pg_default;</pre>
==Ostatní vylepšení a změny==
<p>Background worker procesy (zákaznické procesy mající přistup k sdílené paměti) lze dynamicky startovat a zastavovat a uvnitř těchto procesů lze dynamicky alokovat a dealokovat sdílenou paměť (dříve bylo nutné sdílenou paměť alokovat při startu a nebylo možné ji uvolnit). Je hotové API pro zajištění komunikace mezi background worker procesem a procesem obsluhujícím přihlášeného uživatele (shared memory message queue). Polymorfní agregační funkce mohou mít nepolymorfní stavovou proměnnou. Zavedením tzv inverzní přechodové funkce (inversion transition function) se zásadně zrychluje výpočet klouzavých analytických agregačních funkcí. Další optimalizací se dosáhlo cca 20% zrychlení výpočtu agregačních funkcí sum a avg pro typ numeric. Optimalizovala se manipulace s obsahem transakčního logu včetně redukce objemu zapsaných dat při update řádku (pokud data zůstávají na téže stránce, tak se do logu zapisují změněné hodnoty (dříve celé řádky)). <code>work_mem</code> a <code>maintenance_work_mem</code> lze bezpečně nastavit na terabajty (za předpokladu, že máte k dispozici dostatek RAM). Výchozí hodnota pro <code>work_mem</code> je nově 4MB a pro <code>maintenance_work_mem</code> je 64MB. Výchozí hodnota <code>effective_cache_size</code> 4GB. Revitalizoval se datový typ line. Nově i agregační funkce mohou být variadické (s variabilním počtem parametrů) - např. variadický sum přes více sloupců. Defaultní parametry a pojmenované parametry lze použít i v případě analytických window funkcí. Tam, kde to je možné a povolené (a zatím pouze na Linuxu) se pro sdílenou paměť použijí velké datové stránky (nastavení <code>huge_tlb_pages</code> v postgresql.conf) - pozor na THP defrag (doporučuje se vypnout). Došlo k vylepšení komprimace v implementaci GIN indexů - ty by měly být menší a tedy budou vyžadovat méně RAM, také se budou rychlejší načítat z disku. Také by se měly zrychlit dotazy vůči GIN indexu s podmínkou typu &quot;sporadický &amp; častý&quot;. Nově lze vytvářet triggery i pro fdw (cizí/externí) tabulky. Extenze autoexplain (s nastavením analyze) zaloguje i režii triggerů. Seznam contrib modulů rozšířil modul <em>pg_prewarm</em>, který umožňuje přednačtení zadané tabulky do bufferů Postgresu. Volba <code>FORCE NULL</code> příkazu <code>COPY</code> vynutí test na NULL i pro řetězce zadené v uvozovkách.</p>
<p>Drobného vylepšení se dočkala i funkce PITR (Point-In-Time Recovery). Při obnově databáze a přehrávání transakčních logů PostgreSQL přehrává všechny dostupné segmenty transakčního logu. Přehrávání bylo možné pozastavit (případně ukončit) v zadaném čase nebo zadané transakci. Nově je možné pozastavit přehrávání při dosažení prvního konzistentního stavu databáze (<code>recovery_target=immediate</code>). Monitoring exportu segmentů transakčního logu umožňuje pohled pg_stat_archiver.</p>
<pre>postgres=# select * from pg_stat_archiver ;
-[ RECORD 1 ]------+------------------------------
archived_count     | 0                            
last_archived_wal  |                              
last_archived_time |                              
failed_count       | 0                            
last_failed_wal    |                              
last_failed_time   |                              
stats_reset        | 2014-05-20 22:12:13.888164+02</pre>
<p>V Makefile je podpora pro překlad a instalaci klientských aplikací a nezbytných knihoven - což by mělo zjednodušit vytváření instalačních balíčků obsahujcích pouze klienta (pokud vím, tak takový instalační balíček pro MS Windows chybí). Pro některé aplikace dodávané s Postgresem (createuser, dropuser, pg_isready, ..) jsou nově k dispozici TAP testy. Konečně lze jednoduše samostatně startovat vybrané regresní testy (<code>test make check-tests TESTS=&quot;json jsonb&quot;</code>). V pgBenchi lze omezit počet transakcí za sekundu (a lze tak sledovat o něco reálnější vliv PostgreSQL na CPU, IO, ..).</p>
<p>RPM balíčky pro Fedoru s 9.2kou jsou v repozitáři http://yum.postgresql.org/repopackages.php#pg94. Samotná instalace je triviální: <code>yum install postgresql94-server postgresql94-contrib</code>.</p>

<p>Ve své únorové přednášce na P2D2 nastínil Simon Rigs [http://www.postgresql.org/message-id/CA+U5nM+AFftDf-8UaMoe7Z8W6Sx-2EjFvEHephKQ=doEQ2Y1nQ@mail.gmail.com svou vizi] implementace podpory OLAPu v Postgresu v následujících letech. Simon je hodně velký vizionář a byznysman, takže jeho vize nemusí vypadat úplně realisticky (ačkoliv mnoho z toho, co v roce 2008 sliboval ohledně vestavěné replikace se splnilo). V každém případě lze čekat, že jeho tým bude generovat patche pro zrychlení dotazů typických pro OLAP. Myslím si, že je realistické čekat první produkční extenze postavené nad background worker API (scheduler, killer nečinných spojení, multi CPU sort ..) a samozřejmě základní dopracování logické replikace a extenze spojené s touto funkcionalitou (audit). Existují prototypy implementace autonomních transakcí, UPSERT (INSERT or UPDATE), SKIP LOCKED DATA, implementace jsquery, import FDW schématu. Co z toho bude hotové a co budou moci využívat uživatelé, uvidíme ale až za rok.</p>
----------
<DPL>category=Verze PostgreSQL</DPL>
[[Category:Články]]
[[Category:Verze PostgreSQL]]