PostgreSQL 10 (2017) - drsně rozběhnutý slon

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

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.

Citováno z „http://postgres.cz/index.php?title=PostgreSQL_10_(2017)_-_drsně_rozběhnutý_slon&oldid=602“