PostgreSQL 14 (2021): Porovnání verzí

Verze z 9. 8. 2021, 05:21

Autor: Pavel Stěhule, 2020

Když jsem před necelými 6 roky nazval článek k PostgreSQL 9.5 "Držte si klobouky, zrychlujeme", tak jsem vůbec netušil, jak rychlý vývoj může být. Dnes se už Postgres dostal do stavu, že asi jen málokdo stíhá sledovat kompletní vývoj. Díky tomu, že nad Postgresem je několik úspěšných komerčních projektů, tak Postgres netrpí nedostatkem vývojářů. Pozitivně se projevuje stabilita vývoje, dobře a rozumně nastavená pravidla, která vývojářům umožňují soustředit se na svojí práci. Výhodou už od samého začátku projektu je maximální důraz na kvalitu.

To, že stejná měřítka se aplikují na všechny všechny vývojáře, tak neskutečně zklidňuje atmosféru v komunitě. Postgres je skutečně stále komunitní projekt, i když už většina vývojářů na Postgresu jsou fulltime zaměstnanci. Co je důležité, nejsou to zaměstnanci jedné firmy. Největší váhu má EDB, která spolkla Second Quadrant. Stále ovšem dostatečnou protiváhou jsou společnosti Postgres Pro, CYBERTEC, Timescale, vlastně i Microsoft (Citus). Tyto firmy si hodně žárlivě hlídají kvalitu Postgresu, ale zároveň umí spolupracovat a těžit z této spolupráce. Bude to možná kacířská myšlenka. V určitých ohledech si myslím, že se Postgres stává "Linuxem" databázového světa. Platformou, kde velké množství různých hráčů spolupracuje. Samozřejmě, že tu jsou úplně jiná měřítka - Postgres je velikostí kódu i počtem vývojářů proti Linux prcek. Z této spolupráce profitují zmíněné firmy, jejich zákazníci, tak i ostatní uživatelé Postgresu.

V letošní verzi (PostgreSQL 14), asi není funkce kolem které by se dal postavit hlasitý marketing, ale je tam pár nových drobností, které když potřebujete, tak v produkci hodně pomohou (například nově možnost rychlé detekce zavření spojení během výpočtu dlouhých pomalých dotazů nebo třeba správné odhady na prázdných tabulkách).

Cizí tabulky (FDW)

Nově lze použí FDW API i pro příkaz TRUNCATE.

Interní operace Append (používá se pro čtení dat z partitions) nově podporuje asynchronní režim. Dříve se data z podřízených uzlů četla lineárně. V asynchronním režimu se data čtou, jak přicházejí od podřízených uzlů. Smysl to má hlavně pro analytiku, pro větší data, kde partišny pomocí FDW mapujete na samostatné servery. V případě Postgresu se tento režim musí explicitně zapnout na cizím serveru nastavením async_capable 'true':

ALTER SERVER loopback2 OPTIONS (ADD async_capable 'true');

Batch `INSERT`

V Postgresu je možné provádět DML (INSERT, UPDATE, DELETE) příkazy i nad cizími tabulkami. Funguje to docela dobře a spolehlivě (pozor, není tam použitý 2PC commit), bohužel hromadné operace jsou transformovány na řádkové operace. Od verze 14 je možné pro operace nad cizí tabulkou provádět i hromadně (zatím pouze pro INSERT). Jinak omezující podmínky pro UPDATE a DELETE umí postgres poslat cizímu serveru už dnes.

CREATE DATABASE omega;
\c omega

CREATE TABLE tabulka(a int, b varchar);

\c postgres

CREATE EXTENSION postgres_fdw;

CREATE SERVER omega_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'omega');

CREATE USER MAPPING FOR "pavel.stehule"
  SERVER omega_db OPTIONS (user 'pavel.stehule');

CREATE FOREIGN TABLE vzdalena_tabulka(a int, b varchar)
  SERVER omega_db
  OPTIONS (table_name 'tabulka');

CREATE FOREIGN TABLE vzdalena_tabulka2(a int, b varchar)
  SERVER omega_db
  OPTIONS (table_name 'tabulka', batch_size '1000');

postgres=# EXPLAIN ANALYZE VERBOSE INSERT INTO vzdalena_tabulka SELECT i, 'AHOJ' || i FROM generate_series(1,10000) g(i);
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Insert on public.vzdalena_tabulka  (cost=0.00..175.00 rows=0 width=0) (actual time=455.501..455.502 rows=0 loops=1)
   Remote SQL: INSERT INTO public.tabulka(a, b) VALUES ($1, $2)
   Batch Size: 1
   ->  Function Scan on pg_catalog.generate_series g  (cost=0.00..175.00 rows=10000 width=36) (actual time=0.444..6.218 rows=10000 loops=1)
         Output: g.i, ('AHOJ'::text || (g.i)::text)
         Function Call: generate_series(1, 10000)
 Planning Time: 0.082 ms
 Execution Time: 456.018 ms
(8 rows)

postgres=# EXPLAIN ANALYZE VERBOSE <code>INSERT</code> INTO vzdalena_tabulka SELECT i, 'AHOJ' || i FROM generate_series(1,10000) g(i);
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Insert on public.vzdalena_tabulka  (cost=0.00..175.00 rows=0 width=0) (actual time=455.501..455.502 rows=0 loops=1)
   Remote SQL: INSERT INTO public.tabulka(a, b) VALUES ($1, $2)
   Batch Size: 1000
   ->  Function Scan on pg_catalog.generate_series g  (cost=0.00..175.00 rows=10000 width=36) (actual time=0.459..2.007 rows=10000 loops=1)
         Output: g.i, ('AHOJ'::text || (g.i)::text)
         Function Call: generate_series(1, 10000)
 Planning Time: 0.082 ms
 Execution Time: 30.566 ms
(8 rows)

Na ukázce vidíte řádové zrychlení.

správa cache spojení na cizí servery

Dalším příjemným benefitem nové verze je automatický reconnect na cizí server po fatální chybě (po restartu vzdáleného (cizího) serveru). Voláním funkce postgres_fdw_disconnect(nazev) si lze vynutit zavření spojení vůči specifikovanému cizímu serveru (typu postgres_fdw). Funkce postgres_fdw_disconnect_all() zavře všechna spojení na vzdálené postgresové servery. Volbou keep_connections 'off' u cizího serveru si vynutit jednorázové připojení na cizí server.

SQL

Klauzuli OR REPLACE můžeme od 14ky používat i v příkazu CREATE TRIGGER. Klauzule INTO v příkazu SELECT (pozor nejedná se o PL/pgSQL!), byla označena za zastaralou, a místo ní by se měl používat příkaz CREATE TABLE AS SELECT.

Další novinkou je rozšíření klauzule GROUP BY o klauzuli DISTINCT pro klauzule CUBE nebo ROLLUP. Klauzule CUBE a ROLLUP generují kombinace sloupců (výrazů), které se postupně použijí v klauzuli GROUP BY (tzv grouping set). Tyto kombinace mohou být duplicitní. Klauzule DISTINCT duplicitní výrazy odstraní. Ačkoliv mne grouping sets docela fascinovaly, tak jsem zatím neměl příležitost je použít, takže ani netuším nakolik je klauzule GROUP BY DISTINCT užitečná. V každém případě je definovaná ve standardu, takže podpora této klauzule zvyšuje shodu Postgresu se standardem.

Klauzule SEARCH FIRST a CYCLE pro CTE

Roky Postgres podporuje rekurzivní dotazy (někdy se také používá termín Common Table Expression). Ale až v letošní verzi se dá považovat implementace rekurzivních dotazů za kompletní. Nyní již můžeme použít klauzule SEARCH (DEPTH|BREADTH) FIRST a CYCLE. První ze zmíněných klauzulí umožňuje specifikovat generovaný sloupec, který můžeme použít ve finální klauzuli ORDER BY a prostřednictví řazením výsledku rekurzivního dotazu tak můžeme simulovat řazení do hloubky nebo řazení do šířky. Druhá klauzule CYCLE slouží k detekci cyklů a k zastavení rekurze:

CREATE TABLE public.flights (
    departure character(20),
    arrival character(20),
    carrier character(15),
    flight_number character(5),
    price integer
);

INSERT INTO public.flights VALUES ('New York', 'Paris', 'Atlantic', '234', 400);
INSERT INTO public.flights VALUES ('Chicago', 'Miami', 'NA Air', '2334', 300);
INSERT INTO public.flights VALUES ('New York', 'London', 'Atlantic', '5473', 350);
INSERT INTO public.flights VALUES ('London', 'Athens', 'Mediterranean', '247', 340);
INSERT INTO public.flights VALUES ('Athens', 'Nicosia', 'Mediterranean', '2356', 280);
INSERT INTO public.flights VALUES ('Paris', 'Madrid', 'Euro Air', '3256', 380);
INSERT INTO public.flights VALUES ('Paris', 'Cairo', 'Euro Air', '63', 480);
INSERT INTO public.flights VALUES ('Chicago', 'Frankfurt', 'Atlantic', '37', 480);
INSERT INTO public.flights VALUES ('Frankfurt', 'Moscow', 'Asia Air', '2337', 580);
INSERT INTO public.flights VALUES ('Frankfurt', 'Beijing', 'Asia Air', '77', 480);
INSERT INTO public.flights VALUES ('Moscow', 'Tokyo', 'Asia Air', '437', 680);
INSERT INTO public.flights VALUES ('Frankfurt', 'Vienna', 'Euro Air', '59', 200);
INSERT INTO public.flights VALUES ('Paris', 'Rome', 'Euro Air', '534', 340);
INSERT INTO public.flights VALUES ('Miami', 'Lima', 'SA Air', '5234', 530);
INSERT INTO public.flights VALUES ('New York', 'Los Angeles', 'NA Air', '84', 330);
INSERT INTO public.flights VALUES ('Los Angeles', 'Tokyo', 'Pacific Air', '824', 530);
INSERT INTO public.flights VALUES ('Tokyo', 'Hawaii', 'Asia Air', '94', 330);
INSERT INTO public.flights VALUES ('Washington', 'Toronto', 'NA Air', '104', 250);
INSERT INTO public.flights VALUES ('Cairo', 'Paris', 'Euro Air', '1134', 440);
INSERT INTO public.flights VALUES ('Cairo', 'Paris', 'Atlantic', '1134', 440);

Pokud bych chtěl napsat dotaz, který mi dohledá destinace, kam lze dolétnout např. z New Yorku (s přestupy), tak na těchto datech s obyčejným CTE pohořím. V datech mám totiž cyklus - z Paříže mohu letět do Káhiry, a z Káhiry mohu letět do Paříže. Ve svém dotazu musím ošetřit, aby mi rekurze neuvázla v tomto cyklu.

WITH RECURSIVE destinations (departure, arrival, connections, cost)
  AS (SELECT f.departure, f.arrival, 0, price
        FROM flights f
       WHERE f.departure = 'New York'
      UNION ALL
      SELECT r.departure, b.arrival, r.connections + 1,
             r.cost + b.price
        FROM destinations r, flights b
       WHERE r.arrival = b.departure)
  CYCLE departure, arrival SET is_cycle USING path
SELECT departure, arrival, cost, is_cycle, path
  FROM destinations ;

┌───────────┬─────────────┬──────┬──────────┬───────────────────────────────────────────────────────────────────────────────────┐
│ departure │   arrival   │ cost │ is_cycle │                                       path                                        │
╞═══════════╪═════════════╪══════╪══════════╪═══════════════════════════════════════════════════════════════════════════════════╡
│ New York  │ Paris       │  400 │ f        │ {"(\"New York\",Paris)"}                                                          │
│ New York  │ London      │  350 │ f        │ {"(\"New York\",London)"}                                                         │
│ New York  │ Los Angeles │  330 │ f        │ {"(\"New York\",\"Los Angeles\")"}                                                │
│ New York  │ Athens      │  690 │ f        │ {"(\"New York\",London)","(\"New York\",Athens)"}                                 │
│ New York  │ Madrid      │  780 │ f        │ {"(\"New York\",Paris)","(\"New York\",Madrid)"}                                  │
│ New York  │ Cairo       │  880 │ f        │ {"(\"New York\",Paris)","(\"New York\",Cairo)"}                                   │
│ New York  │ Rome        │  740 │ f        │ {"(\"New York\",Paris)","(\"New York\",Rome)"}                                    │
│ New York  │ Tokyo       │  860 │ f        │ {"(\"New York\",\"Los Angeles\")","(\"New York\",Tokyo)"}                         │
│ New York  │ Nicosia     │  970 │ f        │ {"(\"New York\",London)","(\"New York\",Athens)","(\"New York\",Nicosia)"}        │
│ New York  │ Hawaii      │ 1190 │ f        │ {"(\"New York\",\"Los Angeles\")","(\"New York\",Tokyo)","(\"New York\",Hawaii)"} │
│ New York  │ Paris       │ 1320 │ t        │ {"(\"New York\",Paris)","(\"New York\",Cairo)","(\"New York\",Paris)"}            │
│ New York  │ Paris       │ 1320 │ t        │ {"(\"New York\",Paris)","(\"New York\",Cairo)","(\"New York\",Paris)"}            │
└───────────┴─────────────┴──────┴──────────┴───────────────────────────────────────────────────────────────────────────────────┘
(12 rows)

Podpora detekce cyklů v rekurzivních dotazech je asi největší změna v SQL v Postgresu za posledních několik let. Není to nic světoborného, ale potěší to, a ušetří práci. V tomto případě klauzule CYCLE zajistí vytvoření generovaného sloupce Path se polem dvojic departure, arrival. Pokud toto pole bude obsahovat duplicitní hodnotu, tak se nastaví další generovaný sloupec is_cycle na hodnotu true, a rekurze touto cestou dál nepokračuje. Snad souhlasíte se mnou, že se klauzule CYCLE používá docela jednoduše.

Klauzule SEARCH v CTE funguje podobně v tom smyslu, že opět se pracuje s generovaným sloupcem, na který se pak budeme odkazovat v klauzuli ORDER BY:

-- prohledávání do hloubky
WITH RECURSIVE destinations (departure, arrival, connections, cost)
  AS (SELECT f.departure, f.arrival, 0, price
        FROM flights f
       WHERE f.departure = 'New York'
      UNION ALL
      SELECT r.departure, b.arrival, r.connections + 1,
             r.cost + b.price
        FROM destinations r, flights b
       WHERE r.arrival = b.departure)
  SEARCH DEPTH FIRST BY departure, arrival SET ordercol
  CYCLE departure, arrival SET is_cycle USING path
SELECT departure, arrival, cost, is_cycle, ordercol
  FROM destinations
  ORDER BY ordercol;
┌───────────┬─────────────┬──────┬──────────┬───────────────────────────────────────────────────────────────────────────────────┐
│ departure │   arrival   │ cost │ is_cycle │                                     ordercol                                      │
╞═══════════╪═════════════╪══════╪══════════╪═══════════════════════════════════════════════════════════════════════════════════╡
│ New York  │ London      │  350 │ f        │ {"(\"New York\",London)"}                                                         │
│ New York  │ Athens      │  690 │ f        │ {"(\"New York\",London)","(\"New York\",Athens)"}                                 │
│ New York  │ Nicosia     │  970 │ f        │ {"(\"New York\",London)","(\"New York\",Athens)","(\"New York\",Nicosia)"}        │
│ New York  │ Los Angeles │  330 │ f        │ {"(\"New York\",\"Los Angeles\")"}                                                │
│ New York  │ Tokyo       │  860 │ f        │ {"(\"New York\",\"Los Angeles\")","(\"New York\",Tokyo)"}                         │
│ New York  │ Hawaii      │ 1190 │ f        │ {"(\"New York\",\"Los Angeles\")","(\"New York\",Tokyo)","(\"New York\",Hawaii)"} │
│ New York  │ Paris       │  400 │ f        │ {"(\"New York\",Paris)"}                                                          │
│ New York  │ Cairo       │  880 │ f        │ {"(\"New York\",Paris)","(\"New York\",Cairo)"}                                   │
│ New York  │ Paris       │ 1320 │ t        │ {"(\"New York\",Paris)","(\"New York\",Cairo)","(\"New York\",Paris)"}            │
│ New York  │ Paris       │ 1320 │ t        │ {"(\"New York\",Paris)","(\"New York\",Cairo)","(\"New York\",Paris)"}            │
│ New York  │ Madrid      │  780 │ f        │ {"(\"New York\",Paris)","(\"New York\",Madrid)"}                                  │
│ New York  │ Rome        │  740 │ f        │ {"(\"New York\",Paris)","(\"New York\",Rome)"}                                    │
└───────────┴─────────────┴──────┴──────────┴───────────────────────────────────────────────────────────────────────────────────┘
(12 rows)

-- prohledávání do šířky
WITH RECURSIVE destinations (departure, arrival, connections, cost)
  AS (SELECT f.departure, f.arrival, 0, price
        FROM flights f
       WHERE f.departure = 'New York'
      UNION ALL
      SELECT r.departure, b.arrival, r.connections + 1,
             r.cost + b.price
        FROM destinations r, flights b
       WHERE r.arrival = b.departure)
  SEARCH BREADTH FIRST BY departure, arrival SET ordercol
  CYCLE departure, arrival SET is_cycle USING path
SELECT departure, arrival, cost, is_cycle, ordercol
  FROM destinations
  ORDER BY ordercol;
┌───────────┬─────────────┬──────┬──────────┬──────────────────────────────┐
│ departure │   arrival   │ cost │ is_cycle │           ordercol           │
╞═══════════╪═════════════╪══════╪══════════╪══════════════════════════════╡
│ New York  │ London      │  350 │ f        │ (0,"New York",London)        │
│ New York  │ Los Angeles │  330 │ f        │ (0,"New York","Los Angeles") │
│ New York  │ Paris       │  400 │ f        │ (0,"New York",Paris)         │
│ New York  │ Athens      │  690 │ f        │ (1,"New York",Athens)        │
│ New York  │ Cairo       │  880 │ f        │ (1,"New York",Cairo)         │
│ New York  │ Madrid      │  780 │ f        │ (1,"New York",Madrid)        │
│ New York  │ Rome        │  740 │ f        │ (1,"New York",Rome)          │
│ New York  │ Tokyo       │  860 │ f        │ (1,"New York",Tokyo)         │
│ New York  │ Hawaii      │ 1190 │ f        │ (2,"New York",Hawaii)        │
│ New York  │ Nicosia     │  970 │ f        │ (2,"New York",Nicosia)       │
│ New York  │ Paris       │ 1320 │ t        │ (2,"New York",Paris)         │
│ New York  │ Paris       │ 1320 │ t        │ (2,"New York",Paris)         │
└───────────┴─────────────┴──────┴──────────┴──────────────────────────────┘
(12 rows)

Redukce omezení použití klíčových slov v názvů (labels) sloupců příkazu `SELECT`

Nově Postgres umožní používat většinu (bez 39 z 450) klíčových slov jako aliasy aniž by bylo nutné používat klíčové slovo AS. Stejně je ale dobrým zvykem klíčové slovo AS používat (hlavní efekt je v případě portace aplikací z jiných databází do Postgresu).

-- PostgreSQL 14
postgres=# SELECT 1 user;
┌──────┐
│ user │
╞══════╡
│    1 │
└──────┘
(1 row)

-- PostgreSQL 13
postgres=# SELECT 1 user;
ERROR:  syntax error at or near "user"
LINE 1: SELECT 1 user;
                 ^
postgres=# SELECT 1 AS user;
┌──────┐
│ user │
╞══════╡
│    1 │
└──────┘
(1 row)

ANSI/SQL Syntax pro funkce a procedury

Další novinkou je podpora ANSI/SQL syntaxe pro vlastní SQL funkce a procedury. Obvykle se v Postgresu používá univerzální (ve smyslu podpory všech jazyků pro uložené procedury) zápis těla funkce jako řetězce. Nově lze funkce a procedury v jazyku SQL zapsat vnořené SQL příkazy (případně použít blok BEGIN ATOMIC ... END):

-- ANSI/SQL syntax
CREATE OR REPLACE FUNCTION fx(a int)
RETURNS integer
LANGUAGE sql 
RETURN a + 10;

-- Postgres proprietární syntax
CREATE OR REPLACE FUNCTION fx(a int)
RETURNS integer AS $$
  SELECT a + 10;
$$ LANGUAGE sql;

Možná jednou doiterujeme k podpoře SQL/PSM. Pokud použijete nový zápis, tak se tělo funkce uloží jako AST (a nikoliv jako text). Podobné je to u pohledů. Výhodou by měla být vyšší bezpečnost (sémantika je definována v době registrace funkce, nikoliv v době exekuce). Další výhodou (možná nevýhodou) je automatické sledování závislostí. Teď ještě nedokážu docenit možné benefity, a i autor patche argumentoval vyšší shodou se standardem. Mohla by to být cesta k implementaci standardních triggerů.

Administrace

Příčiny některých produkčních problémů mohou být dost bizarní. Vzpomínám si na situaci, kdy uživatel skrz svoji aplikaci startoval náročný dotaz běžící několik desítek minut. Po pár minutách nečinnosti zkusil zavřít aplikaci, a znovu ji nastartoval a znovu nastartoval pomalý dotaz, a totéž udělal ještě několikrát. Nakonec na serveru běžela necelá desítka stejných extrémně náročných dotazů, a server začal kolabovat v důsledku přetížení IO. Jádro pudla bylo ve způsobu, jakým byla aplikace ukončena. Nedošlo k ukončení běhu dotazu (cancel), nedošlo k odhlášení. Normálně Postgres detekuje takovou situaci až ve chvíli, kdy zhavaruje komunikace. U náročných dotazů může trvat docela dlouho než se spočítá první řádek. Dnes by řešením mohlo být nastavení konfigurační proměnné client_connection_check_interval. Poté server pravidelně v určeném intervalu kontroluje, jestli je spojení s klientem stále aktivní. Zatím by to mělo fungovat pouze na Linuxu (případně na systémech, které implementují Linuxové rozšíření POLLRDHUP).

Příkaz COPY patří mezi příkazy, které často běží delší dobu (při importech stovek miliónů nebo miliard řádků). Od nové verze se můžeme podívat na statistiku tohoto příkazu dotazem SELECT * FROM pg_stat_progress_copy.

Konfigurace Postgresu nikdy nebyla složitá (v drtivé většině případů). Základem je správné nastavení shared_buffers, work_mem, maintainance_work_mem a effective_cache_size v závislosti na dostupné paměti serveru. Více méně mechanicky se nastavovala hodnota checkpoint_completation_target na 0.9. Poslední krok si od 14ky ušetříme. Hodnota 0.9 bude nastavena jako default.

pg_dump má nový přepínač -e případně --extension, kterým lze exportovat pouze vybranou extenzi. Od nové verze lze reindexaci změnit tablespace (online můžeme přestěhovat index). A další příjemná novinka - příkaz REINDEX lze použít i na partišnované tabulky (nemusíme jako dříve reindexovat každou partišnu zvlášť). V systémové tabulce pg_locks ve sloupci waitstart můžeme vidět u déle aktivních zámků (v defaultu nad 1sec) čas odkdy se na zámek čeká. Další sympatická maličkost, v rámci zotavení databáze po havárii se automaticky vymažou dříve postgresem vytvořené dočasné soubory. Nově můžeme nastavit cestu k adresáři s SSL certifikáty.

Vzpomínám si na mé rozhovory s autory databází (PCFAND a 602SQLServer), kteří shodně tvrdili, že jeden z nejkomplikovanějších problémů při realizaci databáze je správa paměti. V Postgresu, si myslím, že je tato otázka vyřešena docela dobře, ale musel se udělat hodně velký úkrok stranou. Tím úkrokem je vlastní hierarchicky organizovaná správa paměti. Základní prvkem správy paměti je tzv paměťový kontext (MemoryContext). Většinou se paměť alokuje v tzv aktuálním kontextu. Před startem nějaké operace vývojář vytvoří nový kontext, nastaví jej jako aktuální, pak zavolá požadovanou operaci, a zruší vytvořený kontext, a jako aktuální kontext nastaví původní kontext. Funguje to nad očekávání dobře, a dobře se to programuje (a je to docela jednoduché, když člověk pochopí, jak to funguje). Stejně se ale někdy mohou objevit problémy. A to když si nějaká operace vezme výrazně více paměti než se očekávalo, nebo když se paměťový kontext používá déle než se čekalo. Doposud se alokace paměti dala debugovat pouze skrze připojený debugger. Ve 14tce máme dvě nové možnosti. Můžeme udělat SELECT do pohledu pg_backend_memory_contexts. Jelikož se můžete dívat jen na paměť vlastního procesu, tak je tato možnost zajímavá jen na sledování bloatingu cache. Druhou možností je volání funkce pg_log_backend_memory_contexts. Parametrem této funkce je proces id (pid). Výsledek se uloží do logu Postgresu.

LOG:  level: 0; TopMemoryContext: 80800 total in 6 blocks; 14432 free (5 chunks); 66368 used
LOG:  level: 1; pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 1408 free (0 chunks); 6784 used
LOG:  level: 1; TopTransactionContext: 8192 total in 1 blocks; 7720 free (1 chunks); 472 used
LOG:  level: 1; RowDescriptionContext: 8192 total in 1 blocks; 6880 free (0 chunks); 1312 used
LOG:  level: 1; MessageContext: 16384 total in 2 blocks; 5152 free (0 chunks); 11232 used
LOG:  level: 1; Operator class cache: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
LOG:  level: 1; smgr relation table: 16384 total in 2 blocks; 4544 free (3 chunks); 11840 used
LOG:  level: 1; TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
...
LOG:  level: 1; ErrorContext: 8192 total in 1 blocks; 7928 free (3 chunks); 264 used
LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560 used

Pokud pro připojení k Postgresu používáte knihovnu libpq, tak můžete používat multi-host connection string. Connection string může obsahovat přihlašovací údaje k více serverům. V případě neúspěšného pokusu o připojení se postupně zkoušejí zadané přihlašovací údaje. Jedním z údajů connection stringu target_session_attrs můžeme upřesnit požadované vlastnosti spojení. Doposud bylo možné si vynutit pouze jednu vlastnost, a to read-write. Nově je možné použít any, read-write, read-only, primary, standby, prefer-standby. prefer-standby znamená, že se zkusí dohledat spojení na server, který je v standby módu, a pokud takový není, tak se vezme zavděk i primárním serverem.

Čekání vynucené recovery konfliktem (na standby serveru) lze nyní logovat po aktivaci volby log_recovery_conflict_waits.

Příkaz VACUUM má dvě nové funkce. Klauzulí PROCESS_TOAST FALSE můžeme vynutit ignorování TOAST tabulek (lze použít pouze s VACUUM FULL). To má smysl v případě velkých tabulek, kdy bloating vidíme na hlavní tabulce, a nikoliv na TOST tabulce (v případech, že nedochází k UPDATE, DELETE dat v TOAST tabulce). Pomocí nových konfiguračních proměnných vacuum_failsafe_age a vacuum_multixact_failsafe_age můžeme ovlivnit způsob běhu příkazu VACUUM. Pokud stáří nejstarší transakce přesáhne zadaný limit a uživatel nebo autovacuum spustí příkaz VACUUM, tak tento příkaz poběží v režimu failsafe. Což zde znamená, že VACUUM poběží co nejrychleji (bez omezení cost-delay), a bude provádět pouze nezbytně nutné operace proto, aby co nejdříve vyřešil problém s přetečením identifikátorů transakcí. Je to další chytrá pojistka proti "nečekaným" problémům souvisejících s přetečením id transakcí (toto riziko lze dobře monitorovat, ale napřed o něm musíte vědět, abyste chtěli použít nějaký monitoring).

Postupně v Postgresu se objevují nové vestavěné role, skrze které se mohou dedikovat některá práva super uživatele běžnému uživateli. Nově mohou být používané role pg_read_all_data a pg_write_all_data;

postgres=# CREATE TABLE boo(a int);
CREATE TABLE
postgres=# <code>INSERT</code> INTO boo VALUES(100);
<code>INSERT</code> 0 1
postgres=# GRANT pg_read_all_data TO tom;
GRANT ROLE
postgres=# SET role TO tom;
SET
postgres=> SELECT * FROM boo;
┌─────┐
│  a  │
╞═════╡
│ 100 │
└─────┘
(1 row)

postgres=> <code>INSERT</code> INTO boo VALUES(100);
ERROR:  permission denied for table boo

Trochu jinou vestavěnou rolí je role pg_database_owner, kterou lze použít pro vytváření šablon. Práva na objekty, která má pg_database_owner, automaticky získává vlastník databáze.

Vestavěná logická replikace nyní podporuje streamování transakcí, což znamená, že změny dat jsou zveřejňovány průběžně (nikoliv až po ukončení transakce). Streaming replikace se povolí parametrem streaming = on v příkazu CREATE SUBSCRIPTION. Příkazem ALTER SUBSCRIPTION můžeme nyní přidávat (ADD) a odebírat (DROP) publikace.

Zápisy do transakčního logu teď bude možné snadno monitorovat díky pohledu pg_stat_wal:

postgres=> SELECT * FROM pg_stat_wal;
┌─[ RECORD 1 ]─────┬───────────────────────────────┐
│ wal_records      │ 100453618                     │
│ wal_fpi          │ 103                           │
│ wal_bytes        │ 5928240553                    │
│ wal_buffers_full │ 588860                        │
│ wal_write        │ 590202                        │
│ wal_sync         │ 1607                          │
│ wal_write_time   │ 0                             │
│ wal_sync_time    │ 0                             │
│ stats_reset      │ 2021-05-09 23:12:59.695499+02 │
└──────────────────┴───────────────────────────────┘

V extenzi amcheck je nová funkce verify_heapam, která zkontroluje strukturu tabulky, a v případě problémů vypíše číslo bloku a offset (což dohromady dává tid) plus popis chyby. Poté poškozené řádky můžeme odstranit funkcí heap_force_kill nebo zafixovat příkazem heap_force_freeze (samozřejmě v závislosti na chybě). Nad extenzí amcheck je postavená nová utilita pg_amcheck (kontrolovaná databáze musí mít nainstalovanou extenzi amcheck):

[pavel@localhost contrib]$ /usr/local/pgsql/master/bin/pg_amcheck -P -d postgres
276/276 relations (100%) 3022/3022 pages (100%)

Nová konfigurační proměnná idle_session_timeout umožňuje si vynutit disconnect neaktivních spojení.

postgres=# SET idle_session_timeout TO '20s';
SET
postgres=# SELECT 1;
FATAL:  terminating connection due to idle-session timeout
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Novým způsobem zjištění, jestli je session otevřená vůči primárnímu serveru nebo vůči hot-standby serveru je nová session proměnná na straně serveru in_hot_standby:

postgres=# SHOW in_hot_standby ;
┌────────────────┐
│ in_hot_standby │
╞════════════════╡
│ off            │
└────────────────┘
(1 row)

pg_surgery

Nově je také v Postgresu nástroj, který umožňuje pracovat (a částečně opravovat) s poškozenou databází. Tím nástrojem je extenze pg_surgery, která obsahuje funkci heap_force_kill, umožňující odstranění řádků určených polem tuple identifikátorů tid. Druhou funkcí heap_force_free lze "zamrazit" (freeze) řádky, a tím si je zpřístupnit (například pokud není dostupný stav transakce, která tyto řádky (verze) vytvořila). Pozor, není to undelete (dokud neproběhne VACUUM, tak máte naději)!

postgres=# CREATE EXTENSION pg_surgery ;
CREATE EXTENSION
postgres=# CREATE TABLE foo(a int, b int);
CREATE TABLE
postgres=# <code>INSERT</code> INTO foo VALUES(1,2);
<code>INSERT</code> 0 1
postgres=# <code>INSERT</code> INTO foo VALUES(3,4);
<code>INSERT</code> 0 1
postgres=# SELECT ctid, * FROM foo;
┌───────┬───┬───┐
│ ctid  │ a │ b │
╞═══════╪═══╪═══╡
│ (0,1) │ 1 │ 2 │
│ (0,2) │ 3 │ 4 │
└───────┴───┴───┘
(2 rows)

postgres=# SELECT heap_force_kill('foo', ARRAY['(0,1)']::tid[]);
┌─────────────────┐
│ heap_force_kill │
╞═════════════════╡
│                 │
└─────────────────┘
(1 row)

postgres=# SELECT ctid, * FROM foo;
┌───────┬───┬───┐
│ ctid  │ a │ b │
╞═══════╪═══╪═══╡
│ (0,2) │ 3 │ 4 │
└───────┴───┴───┘
(1 row)

postgres=# <code>DELETE</code> FROM foo;
<code>DELETE</code> 1
postgres=# SELECT heap_force_freeze('foo', ARRAY['(0,2)']::tid[]);
┌───────────────────┐
│ heap_force_freeze │
╞═══════════════════╡
│                   │
└───────────────────┘
(1 row)

postgres=# SELECT ctid, * FROM foo;
┌───────┬───┬───┐
│ ctid  │ a │ b │
╞═══════╪═══╪═══╡
│ (0,2) │ 3 │ 4 │
└───────┴───┴───┘
(1 row)

Tato extenze může hodně pomoct, pokud dojde k poškození commit logu.

pg_stat_statements

Běžně na svých školeních říkám, že Postgres nelze provozovat bez extenzí. Základním minimem jsou extenze pg_stat_statements a auto_explain. První ze zmíněných extenzí byla rozšířená o zobrazení query_id. Pozor, aby Vám extenze pg_stat_statements v nové verzi fungovala, tak je nutné zapnout konfigurační volbu compute_query_id. query_id je hash generovaný z dotazu sloužící pro jeho identifikace. V nové verzi můžete na query_id narazit v příkazu EXPLAIN, v pohledu pg_stat_activity, a query_id může být také zalogováno skrze nastavení log_line_prefix:

SELECT * FROM obce WHERE nazev = 'Brno';
SELECT * FROM obce WHERE nazev = 'Praha';

postgres=# explain (analyze, verbose)  SELECT * from obce WHERE nazev = 'Praha';
┌────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                               QUERY PLAN                                               │
╞════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on public.obce  (cost=0.00..137.12 rows=1 width=41) (actual time=0.011..0.885 rows=1 loops=1) │
│   Output: id, okres_id, nazev, pocet_muzu, pocet_zen, vek_muzu, vek_zen                                │
│   Filter: ((obce.nazev)::text = 'Praha'::text)                                                         │
│   Rows Removed by Filter: 6249                                                                         │
│ Query Identifier: 959398426696428580                                                                  │
│ Planning Time: 0.077 ms                                                                                │
│ Execution Time: 0.905 ms                                                                               │
└────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

postgres=# SELECT * FROM pg_stat_statements WHERE queryid = 959398426696428580;
┌─[ RECORD 1 ]────────┬─────────────────────────────────────┐
│ userid              │ 16384                               │
│ dbid                │ 14981                               │
│ toplevel            │ t                                   │
│ queryid             │ 959398426696428580                  │
│ query               │ select * from obce where nazev = $1 │
│ plans               │ 0                                   │
│ total_plan_time     │ 0                                   │
│ min_plan_time       │ 0                                   │
│ max_plan_time       │ 0                                   │
│ mean_plan_time      │ 0                                   │
│ stddev_plan_time    │ 0                                   │
│ calls               │ 2                                   │
│ total_exec_time     │ 4.6674240000000005                  │
│ min_exec_time       │ 2.316639                            │
│ max_exec_time       │ 2.350785                            │
│ mean_exec_time      │ 2.3337120000000002                  │
│ stddev_exec_time    │ 0.017073000000000227                │
│ rows                │ 2                                   │
│ shared_blks_hit     │ 118                                 │
│ shared_blks_read    │ 0                                   │
│ shared_blks_dirtied │ 0                                   │
│ shared_blks_written │ 0                                   │
│ local_blks_hit      │ 0                                   │
│ local_blks_read     │ 0                                   │
│ local_blks_dirtied  │ 0                                   │
│ local_blks_written  │ 0                                   │
│ temp_blks_read      │ 0                                   │
│ temp_blks_written   │ 0                                   │
│ blk_read_time       │ 0                                   │
│ blk_write_time      │ 0                                   │
│ wal_records         │ 0                                   │
│ wal_fpi             │ 0                                   │
│ wal_bytes           │ 0                                   │
└─────────────────────┴─────────────────────────────────────┘

Pohled pg_stat_statements má dva nové sloupce. Hodnotou v sloupci toplevel můžeme rozlišovat jestli byl dotaz spuštěn aplikací nebo byl spuštěn z uložených procedur (vyžaduje povolené sledování všech příkazů včetně zanořených - pg_stat_statements.track = all). Druhým novým sloupcem je sloupec plans, který udává, kolikrát byl příkaz naplánován (porovnáním s počtem volání můžeme poznat, jestli se pro daný dotaz opakovaně používají prováděcí plány či nikoliv (vyžaduje zapnuté sledování planneru pg_stat_statements.track_planning).

psql

Letos v psql žádné velké změny nebudou. Snad se povede příští rok podpora pageru pro příkaz \watch.

V nové verzi \d zobrazí i rozšířené statistiky tabulky:

postgres=# \d obce
                                      Table "public.obce"
┌────────────┬───────────────────────┬───────────┬──────────┬──────────────────────────────────┐
│   Column   │         Type          │ Collation │ Nullable │             Default              │
╞════════════╪═══════════════════════╪═══════════╪══════════╪══════════════════════════════════╡
│ id         │ integer               │           │ not null │ nextval('obce_id_seq'::regclass) │
│ okres_id   │ character varying(6)  │           │          │                                  │
│ nazev      │ character varying(40) │           │          │                                  │
│ pocet_muzu │ integer               │           │          │                                  │
│ pocet_zen  │ integer               │           │          │                                  │
│ vek_muzu   │ numeric(3,1)          │           │          │                                  │
│ vek_zen    │ numeric(3,1)          │           │          │                                  │
└────────────┴───────────────────────┴───────────┴──────────┴──────────────────────────────────┘
Indexes:
    "_obce_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "obce_okres_id_fk" FOREIGN KEY (okres_id) REFERENCES okresy(id)
Statistics objects:
    "public"."s1" ON ((pocet_muzu + pocet_zen)) FROM obce
    "public"."s2" ON (((pocet_muzu + pocet_zen) / 10000)) FROM obce
    "public"."s3" ON pocet_muzu, pocet_zen FROM obce

Novým příkazem \dX se můžeme podívat na všechny rozšířené statistiky:

postgres=# \dX
                                      List of extended statistics
┌────────┬──────┬────────────────────────────────────────────────┬───────────┬──────────────┬─────────┐
│ Schema │ Name │                   Definition                   │ Ndistinct │ Dependencies │   MCV   │
╞════════╪══════╪════════════════════════════════════════════════╪═══════════╪══════════════╪═════════╡
│ public │ s1   │ ((pocet_muzu + pocet_zen)) FROM obce           │           │              │         │
│ public │ s2   │ (((pocet_muzu + pocet_zen) / 10000)) FROM obce │           │              │         │
│ public │ s3   │ pocet_muzu, pocet_zen FROM obce                │ defined   │ defined      │ defined │
└────────┴──────┴────────────────────────────────────────────────┴───────────┴──────────────┴─────────┘
(3 rows)

Příkazy \do a \df (výpis funkcí a operátorů) umožňují jako svůj další parametr zadat typ argumentu:

postgres=# \df ab*
                           List of functions
┌────────────┬────────┬──────────────────┬─────────────────────┬──────┐
│   Schema   │  Name  │ Result data type │ Argument data types │ Type │
╞════════════╪════════╪══════════════════╪═════════════════════╪══════╡
│ pg_catalog │ abbrev │ text             │ cidr                │ func │
│ pg_catalog │ abbrev │ text             │ inet                │ func │
│ pg_catalog │ abs    │ bigint           │ bigint              │ func │
│ pg_catalog │ abs    │ double precision │ double precision    │ func │
│ pg_catalog │ abs    │ integer          │ integer             │ func │
│ pg_catalog │ abs    │ numeric          │ numeric             │ func │
│ pg_catalog │ abs    │ real             │ real                │ func │
│ pg_catalog │ abs    │ smallint         │ smallint            │ func │
└────────────┴────────┴──────────────────┴─────────────────────┴──────┘
(8 rows)

postgres=# \df ab* numeric
                          List of functions
┌────────────┬──────┬──────────────────┬─────────────────────┬──────┐
│   Schema   │ Name │ Result data type │ Argument data types │ Type │
╞════════════╪══════╪══════════════════╪═════════════════════╪══════╡
│ pg_catalog │ abs  │ numeric          │ numeric             │ func │
└────────────┴──────┴──────────────────┴─────────────────────┴──────┘
(1 row)

Statistiky

Základem dobré optimalizace dotazů u databází jako je Postgres, Oracle, MSSQL včetně MySQL je práce se statistikami dat. Jak budou kvalitní odhady výsledku dotazů, tak budou kvalitní plány. Co se týče práce se statistikami, tak se Postgres každým rokem zlepšuje, a to i díky práci Tomáše Vondry (aktuálně i předsedy CSPUGu.

V nové verze se rozšiřující statistiky (extended statistics) použijí i pro podmínky (predikáty) používající operátor OR.

Funkcionální statistiky (statistiky nad výrazy)

Pro analytické aplikace mohou být funkcionální statistiky neskutečná bomba. Pro optimalizátor je drtivá většina funkcí blackbox. Optimalizátor rozpozná několik málo pseudo funkcí (vypadají jako funkce, ale jsou implementovány jinak než běžné funkce) jako je funkce coalesce. U běžných funkcí vůbec netuší jak se funkce chová. Když se optimalizátor nemá čeho chytit, tak použije odhad procentem. Pak je to o náhodě. Někdy se můžete trefit, dost často ne. Chybné odhady na velkých datech působí problémy. Například špatný odhad kardinality může způsobit chybnou optimalizaci agregační funkce.

-- odhad procentem
postgres=# EXPLAIN SELECT * FROM obce WHERE pocet_muzu + pocet_zen > 10000;
┌──────────────────────────────────────────────────────────┐
│                        QUERY PLAN                        │
╞══════════════════════════════════════════════════════════╡
│ Seq Scan on obce  (cost=0.00..152.75 rows=2083 width=41) │
│   Filter: ((pocet_muzu + pocet_zen) > 10000)             │
└──────────────────────────────────────────────────────────┘
(2 rows)

postgres=# EXPLAIN SELECT * FROM obce WHERE pocet_muzu + pocet_zen > 20000;
┌──────────────────────────────────────────────────────────┐
│                        QUERY PLAN                        │
╞══════════════════════════════════════════════════════════╡
│ Seq Scan on obce  (cost=0.00..152.75 rows=2083 width=41) │
│   Filter: ((pocet_muzu + pocet_zen) > 20000)             │
└──────────────────────────────────────────────────────────┘
(2 rows)

postgres=# EXPLAIN SELECT * FROM obce WHERE pocet_muzu + pocet_zen > 100000;
┌──────────────────────────────────────────────────────────┐
│                        QUERY PLAN                        │
╞══════════════════════════════════════════════════════════╡
│ Seq Scan on obce  (cost=0.00..152.75 rows=2083 width=41) │
│   Filter: ((pocet_muzu + pocet_zen) > 100000)            │
└──────────────────────────────────────────────────────────┘
(2 rows)

postgres=# EXPLAIN ANALYZE SELECT ((pocet_muzu + pocet_zen) / 10000)::int , count(*) FROM obce GROUP BY 1;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                 QUERY PLAN                                                 │
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ HashAggregate  (cost=184.00..205.96 rows=1464 width=12) (actual time=7.051..7.081 rows=15 loops=1)         │
│   Group Key: ((pocet_muzu + pocet_zen) / 10000)                                                            │
│   Batches: 1  Memory Usage: 73kB                                                                           │
│   ->  Seq Scan on obce  (cost=0.00..152.75 rows=6250 width=4) (actual time=0.034..3.541 rows=6250 loops=1) │
│ Planning Time: 0.192 ms                                                                                    │
│ Execution Time: 7.232 ms                                                                                   │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)

Můžeme si pomoct funkcionálním indexem. Pro funkcionální indexy se vytvářejí statistiky podobně jako pro sloupce:

CREATE INDEX ON obce((pocet_muzu + pocet_zen));
ANALYZE obce;

postgres=# EXPLAIN SELECT * FROM obce WHERE pocet_muzu + pocet_zen > 100000;
┌─────────────────────────────────────────────────────────────────────────────┐
│                                 QUERY PLAN                                  │
╞═════════════════════════════════════════════════════════════════════════════╡
│ Bitmap Heap Scan on obce  (cost=4.65..63.92 rows=47 width=41)               │
│   Recheck Cond: ((pocet_muzu + pocet_zen) > 100000)                         │
│   ->  Bitmap Index Scan on obce_expr_idx  (cost=0.00..4.63 rows=47 width=0) │
│         Index Cond: ((pocet_muzu + pocet_zen) > 100000)                     │
└─────────────────────────────────────────────────────────────────────────────┘
(4 rows)

CREATE INDEX ON obce((((pocet_muzu + pocet_zen) / 10000)::int));
ANALYZE obce;

postgres=# EXPLAIN ANALYZE SELECT ((pocet_muzu + pocet_zen) / 10000)::int , count(*) FROM obce GROUP BY 1;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                 QUERY PLAN                                                 │
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ HashAggregate  (cost=184.00..184.22 rows=15 width=12) (actual time=4.041..4.047 rows=15 loops=1)           │
│   Group Key: ((pocet_muzu + pocet_zen) / 10000)                                                            │
│   Batches: 1  Memory Usage: 24kB                                                                           │
│   ->  Seq Scan on obce  (cost=0.00..152.75 rows=6250 width=4) (actual time=0.017..2.039 rows=6250 loops=1) │
│ Planning Time: 0.725 ms                                                                                    │
│ Execution Time: 4.105 ms                                                                                   │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)

Jenomže indexy u větších tabulek, kde dochází k častým úpravám, jsou to, co chcete omezit na minimum. Každý index vám jistým způsobem zpomaluje INSERT, UPDATE, DELETE (samozřejmě, že ty správné indexy redukují fullscan, a tyto operace dramaticky zrychlují). Každý index vám zpomaluje VACUUM, VACUUM FULL. Indexy je potřeba reindexovat. Prostě indexy nechcete dávat na tabulku jen tak z plezíru. Zvlášť kvůli výrazům, které mají nízkou kardinalitu.

Nicméně od statistik na funkcionálních indexech je krátká cesta k funkcionálním statistikám. Funkcionální statistika mi pomůže s odhady, a nemá režii indexů.

CREATE STATISTICS s1 ON (pocet_muzu + pocet_zen) FROM obce;
ANALYZE obce;
postgres=# EXPLAIN ANALYZE SELECT * FROM obce WHERE pocet_muzu + pocet_zen > 100000;
┌──────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                            QUERY PLAN                                            │
╞══════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce  (cost=0.00..152.75 rows=47 width=41) (actual time=0.031..1.815 rows=6 loops=1) │
│   Filter: ((pocet_muzu + pocet_zen) > 100000)                                                    │
│   Rows Removed by Filter: 6244                                                                   │
│ Planning Time: 0.202 ms                                                                          │
│ Execution Time: 1.855 ms                                                                         │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

CREATE STATISTICS s2 ON ((((pocet_muzu + pocet_zen) / 10000)::int)) FROM obce;
ANALYZE obce;
postgres=# EXPLAIN ANALYZE SELECT ((pocet_muzu + pocet_zen) / 10000)::int , count(*) FROM obce GROUP BY 1;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                 QUERY PLAN                                                 │
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ HashAggregate  (cost=184.00..184.22 rows=15 width=12) (actual time=4.389..4.396 rows=15 loops=1)           │
│   Group Key: ((pocet_muzu + pocet_zen) / 10000)                                                            │
│   Batches: 1  Memory Usage: 24kB                                                                           │
│   ->  Seq Scan on obce  (cost=0.00..152.75 rows=6250 width=4) (actual time=0.018..2.232 rows=6250 loops=1) │
│ Planning Time: 0.382 ms                                                                                    │
│ Execution Time: 4.455 ms                                                                                   │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)

Je jasné, že i funkcionální statistiky přidávají nějakou režii (ale ne vůči funkcionálním indexům). Každá statistika zpomaluje ANALYZE a zvyšuje náročnost plánování dotazu.

Změna chování statistik na prázdné tabulce

Jednou z nejpekelnějších vlastností Postgresu byly implicitní statistiky pro prázdné tabulky. V případě, že tabulka měla nula řádek, tak optimalizátor vždy vycházel z implicitní statistiky, která předpokládá, že je zaplněná jedna datová stránka tabulky, a pokud aktuálně v tabulce nejsou žádné řádky, tak je to něco speciálního, dočasného, co se za pár milisekund změní.

-- Postgres 9.6
postgres=# CREATE TABLE xx(a int);                         
CREATE TABLE                                               
postgres=# ANALYZE xx;                                     
ANALYZE                                                    
postgres=# EXPLAIN SELECT * FROM xx;                       
                      QUERY PLAN                           
------------------------------------------------------     
 Seq Scan on xx  (cost=0.00..35.50 rows=2550 width=4)      
(1 row)

Ve většině aplikací je tento předpoklad pravdivý. Také pokud uživatel ještě nepoužil příkaz ANALYZE, tak jsou lepší implicitní statistiky než žádné statistiky. U některých (většinou analytických aplikací) se dost často pracuje s tabulkami, kde se materializuje výsledek části výpočtu, a kde tyto tabulky často mají jeden nebo nula řádků. U takových aplikací působily implicitní statistiky na prázdných tabulkách velké problémy. Znám firmu, která do takových tabulek přidávala fake řádek (proto aby tabulka nebyla prázdná). V <a href="https://www.gooddata.com/">GoodData</a> toto chování bylo jedním z hlavních důvodů pro vlastní build Postgresu, a používání vlastních patchů.

Letos se už povedlo přesvědčit Toma Lanea, že implicitní statistiky na prázdných tabulkách nejsou vždy ten nejlepší nápad. Pokud tabulka nebyla zanalyzovaná, tak se použijí implicitní statistiky stejně jako dříve. Pokud tabulka zanalyzována byla, a je prázdná, tak se pro odhady použije odhad s jedním řádkem (při odhadech se počítá vždy s minimálně jedním řádkem):

-- Postgres 14
postgres=# CREATE TABLE xx(a int);
CREATE TABLE
postgres=# ANALYZE xx;
ANALYZE
postgres=# EXPLAIN SELECT * FROM xx;  
┌──────────────────────────────────────────────────┐
│                    QUERY PLAN                    │
╞══════════════════════════════════════════════════╡
│ Seq Scan on xx  (cost=0.00..0.00 rows=1 width=4) │
└──────────────────────────────────────────────────┘
(1 row)

Interní optimalizace

Ve starších verzích bylo možné použít trigramový index pouze pro operátor LIKE. Pro operátor "rovná se" bylo nutné mít klasický btree index (který je na tuto úlohu vhodnější). To ale znamenalo mít dva indexy nad jedním sloupcem. Od nové verze bude trigramový index podporovat i vyhledávání na základě rovnosti:

CREATE EXTENSION pg_trgm ;
CREATE INDEX ON obce USING gin (nazev gin_trgm_ops);
ANALYZE obce;

EXPLAIN ANALYZE SELECT * FROM obce WHERE nazev = 'Skalice';
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                       QUERY PLAN                                                       │
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Bitmap Heap Scan on obce  (cost=68.01..72.02 rows=1 width=41) (actual time=0.240..0.259 rows=3 loops=1)                │
│   Recheck Cond: ((nazev)::text = 'Skalice'::text)                                                                      │
│   Rows Removed by Index Recheck: 5                                                                                     │
│   Heap Blocks: exact=8                                                                                                 │
│   ->  Bitmap Index Scan on obce_nazev_idx  (cost=0.00..68.01 rows=1 width=0) (actual time=0.209..0.209 rows=8 loops=1) │
│         Index Cond: ((nazev)::text = 'Skalice'::text)                                                                  │
│ Planning Time: 0.844 ms                                                                                                │
│ Execution Time: 0.328 ms                                                                                               │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)

postgres=# \d obce
                                      Table "public.obce"
┌────────────┬───────────────────────┬───────────┬──────────┬──────────────────────────────────┐
│   Column   │         Type          │ Collation │ Nullable │             Default              │
╞════════════╪═══════════════════════╪═══════════╪══════════╪══════════════════════════════════╡
│ id         │ integer               │           │ not null │ nextval('obce_id_seq'::regclass) │
│ okres_id   │ character varying(6)  │           │          │                                  │
│ nazev      │ character varying(40) │           │          │                                  │
│ pocet_muzu │ integer               │           │          │                                  │
│ pocet_zen  │ integer               │           │          │                                  │
│ vek_muzu   │ numeric(3,1)          │           │          │                                  │
│ vek_zen    │ numeric(3,1)          │           │          │                                  │
└────────────┴───────────────────────┴───────────┴──────────┴──────────────────────────────────┘
Indexes:
    "_obce_pkey" PRIMARY KEY, btree (id)
    "obce_nazev_idx" gin (nazev gin_trgm_ops)
    "obce_okres_id_idx" btree (okres_id)
Foreign-key constraints:
    "obce_okres_id_fk" FOREIGN KEY (okres_id) REFERENCES okresy(id)

Postgres má vlastní implementaci regulárních výrazů, která vychází z implementace v Perlu. Letošní úpravy odstranily některé rozdíly v chování regexpů v Postgresu a v ostatních implementacích (testování probíhalo vůči JavaScriptu). Reimplementací některých částí došlo i k opravdu masivnímu zrychlení. Regulární výrazy ale většinou nejsou úzkým hrdlem relačních databází, takže pravděpodobně pouze minimum aplikací bude z této optimalizace těžit (i když jsou dotazy, kde vidím fullscan s regexpem).

Optimalizace se dočkala i v loni implementovaná podpora normalizace unicode znaků. Místo binárního hledání se používají dokonalé hašovací funkce (perfect hash function). Mělo by zrychlit i načítání dat příkazem COPY BINARY, čehož si ale asi opět všimne málo kdo, jelikož tato varianta příkazu COPY se používá spíše výjimečně.

Od předchozí verze Postgres podporuje tzv incremental sort (čtení dat v určitém pořadí z indexu a jejich následné seřazení podle dalších sloupců). V nové verzi bude možné použít incremental sort i pro analytické (window) funkce.

Ve verzi 9.6 bylo poprvé možné použít více CPU pro jeden dotaz. To už bude pomalu pět let. Nyní mnohem víc operací a příkazů podporuje tzv paralelismus. Od letoška je možné použit paralelní operace v INSERT INTO SELECT, CREATE MATERIALIZED VIEW a REFRESH MATERIALIZED VIEW (a v PL/pgSQL RETURN QUERY).

Provádění dotazů, které obsahují náročnější korelované poddotazy může urychlit nový uzel (nový příkaz) executoru result cache. Tento uzel zajistí uložení mezivýsledku do interní hashovací tabulky.

postgres=# EXPLAIN ANALYZE SELECT * FROM t1, LATERAL(SELECT count(*) FROM t2 WHERE t1.t2_id = t2.id) s ;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..476273.82 rows=1000000 width=16) (actual time=0.038..988.406 rows=1000000 loops=1)
   ->  Seq Scan on t1  (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.010..54.512 rows=1000000 loops=1)
   ->  <b>Result Cache</b>  (cost=4.65..4.67 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1000000)
         Cache Key: t1.t2_id
         Hits: 900002  Misses: 99998  Evictions: 0  Overflows: 0  Memory Usage: 10547kB
         ->  Aggregate  (cost=4.64..4.65 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=99998)
               ->  Index Only Scan using t2_id_idx on t2  (cost=0.42..4.62 rows=11 width=0) (actual time=0.001..0.002 rows=10 loops=99998)
                     Index Cond: (id = t1.t2_id)
                     Heap Fetches: 0
 Planning Time: 0.079 ms
 Execution Time: 1017.894 ms

postgres=# SET enable_resultcache TO off;  
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM t1, LATERAL(SELECT count(*) FROM t2 WHERE t1.t2_id = t2.id) s ;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.64..4689425.00 rows=1000000 width=16) (actual time=0.027..2731.491 rows=1000000 loops=1)
   ->  Seq Scan on t1  (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.007..56.764 rows=1000000 loops=1)
   ->  Aggregate  (cost=4.64..4.65 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1000000)
         ->  Index Only Scan using t2_id_idx on t2  (cost=0.42..4.62 rows=11 width=0) (actual time=0.001..0.002 rows=10 loops=1000000)
               Index Cond: (id = t1.t2_id)
               Heap Fetches: 0
 Planning Time: 0.077 ms
 Execution Time: 2760.573 ms
(8 rows)

Zrychlení paralelního sekvenčního čtení

Mělo by také dojít k zrychlení paralelního sekvenčního čtení v případě použití rychlých SSD disků. Zkoušel jsem spočítat počet řádek tabulky s jednou miliardou řádků (tabulka 49GB, shared buffers 10GB, RAM 40GB, 5 paralelních procesů - 1 hlavní + 4 pomocné). V testu vidím cca 5-10% zrychlení (to není moc, ale vytváří to určitý polštář, aby i s novými funkcemi, s novým monitoringem databáze nezpomalovala):

-- PostgreSQL 14
postgres=# EXPLAIN ANALYZE SELECT count(*) FROM big_table;
                                                                       QUERY PLAN                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=9495427.42..9495427.43 rows=1 width=8) (actual time=45965.644..46027.930 rows=1 loops=1)
   ->  Gather  (cost=9495427.00..9495427.41 rows=4 width=8) (actual time=45965.089..46027.921 rows=5 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Partial Aggregate  (cost=9494427.00..9494427.01 rows=1 width=8) (actual time=45941.511..45941.515 rows=1 loops=5)
               ->  Parallel Seq Scan on big_table  (cost=0.00..8869427.00 rows=250000000 width=0) (actual time=0.486..30973.184 rows=200000000 loops=5)
 Planning Time: 0.038 ms
 Execution Time: 46027.961 ms
(8 rows)

-- PostgreSQL 13
postgres=# EXPLAIN ANALYZE SELECT count(*) FROM big_table;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=9495427.62..9495427.63 rows=1 width=8) (actual time=51722.896..51929.309 rows=1 loops=1)
   ->  Gather  (cost=9495427.20..9495427.61 rows=4 width=8) (actual time=51722.695..51929.301 rows=5 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Partial Aggregate  (cost=9494427.20..9494427.21 rows=1 width=8) (actual time=51716.645..51716.646 rows=1 loops=5)
               ->  Parallel Seq Scan on big_table  (cost=0.00..8869427.16 rows=250000016 width=0) (actual time=26.591..38344.396 rows=200000000 loops=5)
 Planning Time: 0.051 ms
 Execution Time: 51929.367 ms
(8 rows)

Zrychlení vyhledávání v seznamu hodnot

Docela hezky pomáhá optimalizace vyhledávání v seznamu hodnot. Pokud použijete dotaz s podmínkou ve tvaru WHERE id IN (1,2,3), tak Postgres až doposud prohledával seznam hodnot lineárně. Pro větší počet položek v seznamu to určitě není optimální (některé ORM se v generování dlouhých seznamů předhánějí - viděl jsem časté pomalé dotazy, kde seznamy měly i vyšší tisíce hodnot). V letošní verzi jsou interně seznamy nad 9 prvků převedeny do hash tabulky, a místo lineárního prohledávání se hledá v hašovací tabulce (pokud pro daný typ existuje hašovací funkce). V syntetickém testu jsem viděl opravdu výrazné zrychlení (a snížení zátěže CPU) už pro seznam se sto prvky.

-- PostgreSQL 14 (tabulka big_table má cca 4GB a vejde se do shared buffers)
SET max_parallel_workers_per_gather TO 0;
SELECT count(*) FROM big_table WHERE e IN (0,1,2,3,4,5,6,7, ... 103)

 count 
-------
  1049
(1 row)

Time: 1016.741 ms (00:01.017)

-- PostgreSQL 13 (data se mohou mírně lišit, vstupem jsou náhodně generovaná data)
 count 
-------
  1055
(1 row)

Time: 4829.417 ms (00:04.829)

-- Možná optimalizace - převedení na JOIN (PostgreSQL13 731ms, PostgreSQL14 731 ms)
SELECT count(*)
  FROM big_table 
       JOIN unnest(ARRAY[0,1,2,3 ... 103]) v(e)
       ON big_table.e = v.e;

Nižší pokles výkonu při velkém počtu neaktivních spojení

Zkoušel jsem výkon Postgresu v pgbenchi, což je syntetický benchmark založený na intenzivních updatech, a na testovací mašině měla verze 14 podobný výkon (možná o chlup lepší) jako 12 nebo 13. Jinak v pgbenchi je hezky vidět výrazný nárůst výkonu mezi 9.5 a 9.6, a potom mezi 11 a 12 (v pgbench testu je na tom 13, 14 velice podobně jako 12).

Když už jsem se pustil do testování, tak jsem si chtěl ověřit chování Postgresu v situaci, kdy uživatelé nastaví naprosto nerealisticky max_connection, a kdy je v Postgresu velké množství neaktivních session. Jednak uživatelé netuší, co dělají, a k tomu ještě netuší, jak Postgres funguje. Na rozdíl od ostatních databází (Oracle, MySQL) Postgres nemá integrovaný connection pooler. V každém otevřeném sezení je možné spustit dotaz, a Postgres jej začne okamžitě provádět. Pokud se rozjede velký počet výpočtů dotazů, tak pak na každý dotaz připadne méně CPU, a dotazy poběží déle. Jsme ale v databázi. Pokud dojde k souběhu většího množství dotazů, tak CPU začnou mnohem více spotřebovávat zámky (ať už ve formě spinlocků nebo uspáváním a probouzením procesů). Proto se důrazně doporučuje, aby max_connection byl cca 10-20 násobek počtu core, a pokud potřebujete více spojení (nebo pokud často vytváříte extrémně krátká spojení), tak použít externí pooler. Postgres se pak bude chovat výrazně lépe ve špičkách. Naštěstí pro uživatele, špičkové zátěže, kde by dnešní hw nestíhal, je relativně málo. Chtěl jsem si ověřit, jak a jestli se na výkonu podepíše velký počet neaktivních spojení. Testoval jsem 600 neaktivních spojení (vůči 4 CPU core, které jsem měl, jsem byl totálně mimo bezpečnou zónu). Na read/write testu jsem viděl cca 5% pokles výkonu (14ka bez ztráty výkonu). Výsledky z read only testů naznačovaly cca 10% výkonu (což je méně než jsem čekal). Na 14tce jsem viděl cca 5% pokles výkonu v této situaci. Pro představu režie aktivních spojení byla na 4core serveru cca 10% na každých 70 spojení. Na 140 aktivních spojeních byla ztráta výkonu cca 20-25% z cca 8000 tps (rw) a 50000 tps (ro) (pokud stejně jako já nedosáhnete ještě na limity IO). pgbench lze ale považovat za vytůněnou aplikaci. Pokud aplikace pálí CPU na zbytečných sekvenčních čteních velkých tabulek, tak bude pokles výkonu podstatně razantnější (dříve fullscan vytěžoval IO, dnes s dostatkem RAM a SSD fullscan vytěžuje CPU).

Redukce nárůstu velikosti indexů z důvodu implementace MVCC

Opět o něco chytřeji se pracuje s indexy, které mohou obsahovat duplicitní klíče z důvodu implementace multigenerační architektury. Snížením bloatingu indexů by mělo dojít ke zrychlení operace UPDATE. V Postgresu každý index zpomaluje UPDATE, a to i v případě, že se hodnota v indexovaném sloupci zůstala stejná. UPDATE tabulky, která má hodně indexů, je v Postgresu pomalý.

CREATE TABLE test(id integer primary key, v1 int, v2 int, v3 int);

<code>INSERT</code> INTO test SELECT i, random()*10000, random()*10000, random()*1000 FROM generate_series(1,1000000) g(i);

CREATE INDEX ON test(v1);
CREATE INDEX ON test(v2);
CREATE INDEX ON test(v3);

-- počáteční velikost indexů (Postgres 14)
postgres=# \di+ test*
                                          List of relations
┌────────┬─────────────┬───────┬───────┬───────┬─────────────┬───────────────┬─────────┬─────────────┐
│ Schema │    Name     │ Type  │ Owner │ Table │ Persistence │ Access method │  Size   │ Description │
╞════════╪═════════════╪═══════╪═══════╪═══════╪═════════════╪═══════════════╪═════════╪═════════════╡
│ public │ test_pkey   │ index │ pavel │ test  │ permanent   │ btree         │ 21 MB   │             │
│ public │ test_v1_idx │ index │ pavel │ test  │ permanent   │ btree         │ 6960 kB │             │
│ public │ test_v2_idx │ index │ pavel │ test  │ permanent   │ btree         │ 6960 kB │             │
│ public │ test_v3_idx │ index │ pavel │ test  │ permanent   │ btree         │ 7088 kB │             │
└────────┴─────────────┴───────┴───────┴───────┴─────────────┴───────────────┴─────────┴─────────────┘
(4 rows)

CREATE OR REPLACE PROCEDURE public.make_updates()
 LANGUAGE plpgsql
AS $procedure$
DECLARE _id integer;
BEGIN
  FOR j IN 1..100
  LOOP
    _id = (random()*1000000)::int;
    <code>UPDATE</code> test SET v1 = random() * 10000 WHERE id = _id;
  END LOOP;
END;
$procedure$

time for i in {1..10000}; do echo $i; psql -X -c "call make_updates()"; done
real	5m42,087s
user	0m12,108s
sys	0m24,588s

-- po 10K transakcí (1M update)
postgres=# \di+ test*
                                         List of relations
┌────────┬─────────────┬───────┬───────┬───────┬─────────────┬───────────────┬───────┬─────────────┐
│ Schema │    Name     │ Type  │ Owner │ Table │ Persistence │ Access method │ Size  │ Description │
╞════════╪═════════════╪═══════╪═══════╪═══════╪═════════════╪═══════════════╪═══════╪═════════════╡
│ public │ test_pkey   │ index │ pavel │ test  │ permanent   │ btree         │ 27 MB │             │
│ public │ test_v1_idx │ index │ pavel │ test  │ permanent   │ btree         │ 14 MB │             │
│ public │ test_v2_idx │ index │ pavel │ test  │ permanent   │ btree         │ 14 MB │             │
│ public │ test_v3_idx │ index │ pavel │ test  │ permanent   │ btree         │ 19 MB │             │
└────────┴─────────────┴───────┴───────┴───────┴─────────────┴───────────────┴───────┴─────────────┘
(4 rows)

-- PostgreSQL 13 na počátku
postgres=# \di+ test*
                                  List of relations
┌────────┬─────────────┬───────┬───────┬───────┬─────────────┬─────────┬─────────────┐
│ Schema │    Name     │ Type  │ Owner │ Table │ Persistence │  Size   │ Description │
╞════════╪═════════════╪═══════╪═══════╪═══════╪═════════════╪═════════╪═════════════╡
│ public │ test_pkey   │ index │ pavel │ test  │ permanent   │ 21 MB   │             │
│ public │ test_v1_idx │ index │ pavel │ test  │ permanent   │ 6952 kB │             │
│ public │ test_v2_idx │ index │ pavel │ test  │ permanent   │ 6960 kB │             │
│ public │ test_v3_idx │ index │ pavel │ test  │ permanent   │ 7112 kB │             │
└────────┴─────────────┴───────┴───────┴───────┴─────────────┴─────────┴─────────────┘
(4 rows)

real	10m39,457s
user	0m12,526s
sys	0m25,441s

-- PostgreSQL 13 po 10K transakcí (1M update) 
postgres=# \di+ test*
                                 List of relations
┌────────┬─────────────┬───────┬───────┬───────┬─────────────┬───────┬─────────────┐
│ Schema │    Name     │ Type  │ Owner │ Table │ Persistence │ Size  │ Description │
╞════════╪═════════════╪═══════╪═══════╪═══════╪═════════════╪═══════╪═════════════╡
│ public │ test_pkey   │ index │ pavel │ test  │ permanent   │ 43 MB │             │
│ public │ test_v1_idx │ index │ pavel │ test  │ permanent   │ 14 MB │             │
│ public │ test_v2_idx │ index │ pavel │ test  │ permanent   │ 14 MB │             │
│ public │ test_v3_idx │ index │ pavel │ test  │ permanent   │ 18 MB │             │
└────────┴─────────────┴───────┴───────┴───────┴─────────────┴───────┴─────────────┘
(4 rows)

Běh testu je na 14tce cca poloviční než na 13tce, a nárůst velikosti indexu je v 14tce výrazně menší než v 13tce (6MB x 22MB). Tohle je docela zásadní optimalizace. Bloating (nafukování) indexů primárních klíčů je docela častý problém, a ve 14tce může být 3x menší. Samozřejmě, že pořád Postgres bude preferovat normalizované (a tedy i rozumně široké) tabulky. Přestože tato optimalizace redukuje bloating, stále dochází k aktualizaci indexů i nemodifikovaných sloupců). Z testu je vidět, že k bloatingu stále dochází.

Redukce alokované paměti pro kontrolní dotazy RI na partišnách

Referenční integrita je v Postgresu implementovaná skrze systémové triggery, které volají kontrolní dotazy. Ačkoliv tyto kontrolní dotazy (a jejich prováděcí plány) jsou pro všechny partišny stejné, tak Postgres nesdílel jejich prováděcí plány. To se od 14ky vyřešilo, prováděcí plány kontrolních dotazů referenční integrity se sdílejí napříč partišnami.

Funkce

Jednoduchou funkcí string_to_table můžeme převést řetězec na tabulku.

postgres=# SELECT trim(string_to_table('Praha, Brno',','));
┌───────┐
│ btrim │
╞═══════╡
│ Praha │
│ Brno  │
└───────┘
(2 rows)

postgres=# SELECT string_to_table('ahoj',null);
┌─────────────────┐
│ string_to_table │
╞═════════════════╡
│ a               │
│ h               │
│ o               │
│ j               │
└─────────────────┘
(4 rows)

Funkce split_part může obsahovat záporný argument:

postgres=# SELECT split_part('a,h,o,j', ',', 2);
┌────────────┐
│ split_part │
╞════════════╡
│ h          │
└────────────┘
(1 row)

postgres=# SELECT split_part('a,h,o,j', ',', -2);
┌────────────┐
│ split_part │
╞════════════╡
│ o          │
└────────────┘
(1 row)

Přiznám se, že mne nenapadá rozumný příklad pro použití funkce trim pro datový typ bytea. Ale proč ne. Někdy se do bytea ukládají i řetězce v jiném kódování než je kódování databáze, a pak by trim pro bytea mohl mít smysl.

Novou funkcí (syntax je zkopírovaná z Oracle) je funkce unistr, která provádí dekódování escape n bajtových sekvencí unicode znaků (podporovány jsou následující formáty \XXXX, \+XXXXXX, \uXXXX, \UXXXXXXXX):

postgres=# SELECT
'  Arabic     : ' || unistr( '\0627\0644\0639\0631\0628\064A\0629' )      || '
  Chinese    : ' || unistr( '\4E2D\6587' )                               || '
  English    : ' || unistr( 'English' )                                  || '
  French     : ' || unistr( 'Fran\00E7ais' )                             || '
  German     : ' || unistr( 'Deutsch' )                                  || '
  Greek      : ' || unistr( '\0395\03BB\03BB\03B7\03BD\03B9\03BA\03AC' ) || '
  Hebrew     : ' || unistr( '\05E2\05D1\05E8\05D9\05EA' )                || '
  Japanese   : ' || unistr( '\65E5\672C\8A9E' )                          || '
  Korean     : ' || unistr( '\D55C\AD6D\C5B4' )                          || '
  Portuguese : ' || unistr( 'Portugu\00EAs' )                            || '
  Russian    : ' || unistr( '\0420\0443\0441\0441\043A\0438\0439' )      || '
  Spanish    : ' || unistr( 'Espa\00F1ol' )                              || '
  Thai       : ' || unistr( '\0E44\0E17\0E22' )
  as unicode_test_string;
┌──────────────────────────┐
│   unicode_test_string    │
╞══════════════════════════╡
│   Arabic     : العربية  ↵│
│   Chinese    : 中文     ↵│
│   English    : English  ↵│
│   French     : Français ↵│
│   German     : Deutsch  ↵│
│   Greek      : Ελληνικά ↵│
│   Hebrew     : עברית    ↵│
│   Japanese   : 日本語   ↵│
│   Korean     : 한국어   ↵│
│   Portuguese : Português↵│
│   Russian    : Русский  ↵│
│   Spanish    : Español  ↵│
│   Thai       : ไทย       │
└──────────────────────────┘
(1 row)

Nechybí podpora pro emoji (pozor, v případě emoji psql špatně nastaví šířku znaku a mrví rámečky).

postgres=# SELECT unistr('I \U0001f60d PostgreSQL\u203C');
     unistr      
═════════════════
 I 😍 PostgreSQL‼
(1 row)

Pro vlastní implementaci fronty se může hodit funkce trim_array, která vrátí vstupní pole bez posledních n prvků:

postgres=# SELECT trim_array(ARRAY[1,2,3,4,5,6], 3);
┌────────────┐
│ trim_array │
╞════════════╡
│ {1,2,3}    │
└────────────┘
(1 row)

Naopak, kdybyste chtěli vynechat prvních n prvků, tak lze použít zápis pro výběr z pole od m tého prvku:

postgres=# SELECT (ARRAY[1,2,3,4,5,6])[2+1:];
┌───────────┐
│   array   │
╞═══════════╡
│ {3,4,5,6} │
└───────────┘
(1 row)

Pro většinu z nás neviditelnou změnou bude změna datového typu výsledku funkce EXTRACT z double precision na numeric.

Nejsem si úplně jistý, proč se funkce date_bin jmenuje date_bin, její použití je ale jednoduché. Ořezává časovou hodnotu na násobek intervalu vztaženému k určenému časovému počátku. Ještě mne nenapadá žádná situace, kdy by tím počátkem nebyla nějaká nulová hodnota, ale třeba se něco časem ukáže:

--current_timestamp: 2021-05-07 17:18:56.627396+02
postgres=# SELECT date_bin('10min', current_timestamp, '2021-05-07 7:<b>35</b>:00');
┌────────────────────────┐
│        date_bin        │
╞════════════════════════╡
│ 2021-05-07 17:<b>15</b>:00+02 │
└────────────────────────┘
(1 row)

Datové typy

V nové verzi Postgresu bude k dispozici datový typ multirange. Je to kontejner pro hodnoty typu range. Oproti polím (které také mohou sloužit jako kontejner pro typ range) multirange zajistí, že hodnoty, které jsou zde uložené, nemají průnik a jsou seřazené:

postgres=# SELECT multirange('[1, 20]'::int4range) - multirange('[11,12]'::int4range);
┌──────────────────┐
│     ?column?     │
╞══════════════════╡
│ {[1,11),[13,21)} │
└──────────────────┘
(1 row)

postgres=# SELECT multirange('[1, 10]'::int4range) + multirange('[12,20]'::int4range);
┌──────────────────┐
│     ?column?     │
╞══════════════════╡
│ {[1,11),[12,21)} │
└──────────────────┘
(1 row)

postgres=# SELECT multirange('[1, 10]'::int4range) + multirange('[11,20]'::int4range);
┌──────────┐
│ ?column? │
╞══════════╡
│ {[1,21)} │
└──────────┘
(1 row)

Seznam datových typů schopných uložit hodnotu infinity se rozšiřuje o typ Numeric.

postgres=# SELECT <b>'infinity'</b>::numeric + 10.3;
┌──────────┐
│ ?column? │
╞══════════╡
│ Infinity │
└──────────┘
(1 row)

PL/pgSQL

přímá možnost zápisu do atributu v poli kompozitních hodnot

Mám rád uložené procedury. Myslím si, že je to nedoceněná technologie (ale také často špatně uchopená). Takže není překvapení, že detailně sleduji vývoj PL/pgSQL. V minulé verzi se razantně zrychlilo vyhodnocování výrazů (teď už mezi PL/pgSQL a interprety obecných programovacích jazyků (Perl, Python, Lua) není takový odstup. V letošní verzi se podařilo vyřešit další nedodělek PL/pgSQL, kterým byla chybějící podpora zápisu do atributu položky kompozitního pole. V podstatě šlo o to použít dříve implementovanou syntax (použitou například SQL příkazem UPDATE) v PL/pgSQL.

CREATE TYPE t2 AS (a int, b int);

DO $$
DECLARE
  a t2[] = ARRAY[NULL, NULL];
BEGIN
  /* Nový zápis - od verze 14 */
  a[1].b := 20;
  RAISE NOTICE '%', a;
END;
$$;
NOTICE:  {"(,20)",NULL}
DO

Ve starších verzích jsem pro zápis atributu potřeboval pomocnou kompozitní proměnnou (což bylo pro začátečníky matoucí, a obecně pomalejší):

DO $$
DECLARE
  a t2[] = ARRAY[NULL, NULL];
  t t2;
BEGIN
  /* Starý zápis */
  t := a[1];
  t.b := 20;
  a[1] := t;
  RAISE NOTICE '%', a;
END;
$$;
NOTICE:  {"(,20)",NULL}
DO

Tohle byl jeden z posledních artefaktů z raných dob PL/pgSQL (sedmičková řada Postgresu), kdy PL/pgSQL byl opravdu jednoduchý interpret procedurálního jazyka nad SQL (ale bez jakékoliv větší integrace s SQL prostředím).

Nejsem si úplně jistý, co přinese možnost paralelizace dotazů v příkazu RETURN QUERY. Minimálně, benefitem bude jednotný způsob zpracování dotazů (takže uživatel pak nemusí řešit proč někde dotaz běží rychleji než jinde).

Podpora indexů (subscripts) pro datové typy jsonb a hstore

Doposud v PL/pgSQL chyběly kolekce s indexem typu text (obdoba hashů v Perlu nebo slovníků (dictionary v Pythonu). Určitou náhradou se daly použít datové typy hstore a jsonb. Změna hodnoty se musela řešit voláním příslušné funkce, což vedlo k relativně nečitelnému zápisu. Od verze 14 existuje existuje nové API, které umožňuje implementaci indexu k atributům nebo položkám hodnoty (pozor, jde o něco jiného než indexy na tabulkách). Nechybí ani podpora indexace na levé straně přiřazovacího příkazu. Interně se nic zatím nemění - vyjma polí a kompozitních hodnot jsou všechny typy neměnné (immutable), a při změně atributu se vždy generuje nová kompletní hodnota se změněným atributem (na větších hodnotách je update atributu náročnou operací).

DO $$
DECLARE v jsonb;
BEGIN
  -- hodnota musi byt validní json hodnotou
  v['a'] = '"Ahoj"';
  -- na výstupu dostaneme opět json
  RAISE NOTICE '%', v['a'];
  -- přetypováním můžeme získat text nicméně syntaxe
  -- je ještě nedomyšlená a je nutné použít staré API
  -- (operátor pro získání hodnoty atributu)
  RAISE NOTICE '%', v['a'] #>> '{}';
END;
$$;
NOTICE:  "Ahoj"
NOTICE:  Ahoj
DO

Hodnota typu jsonb může mít libovolnou strukturu, kterou pomocí nové syntaxe můžeme upravovat (a samozřejmě i číst):

DO $$
DECLARE v jsonb;
BEGIN
  -- hodnota musí být validní json
  -- pole v jsonu se indexují od 0
  v['a'][1]['x'] = '"Ahoj"';
  RAISE NOTICE '%', v['a'][1]['x'];
  RAISE NOTICE '%', v;
END;
$$;
NOTICE:  "Ahoj"
NOTICE:  {"a": [null, {"x": "Ahoj"}]}
DO

Funguje to parádně, ale pozor. Vůči polím a kompozitům, které se mohou v rámci jednoho volání funkce v PL/pgSQL chovat jako mutable (modifikovatelné) objekty, tak jsonb i hstore jsou vždy (zatím) immutable (nemodifikovatelné - tj změna způsobí vždy vytvoření upravené nové kopie), a změny větších hodnot (mám na mysli velikost v paměti) mohou být pomalé (pokud by např. modifikovaný json měl víc než 1kB - samozřejmě, že záleží i na frekvenci úprav (pokud by se jednalo několik málo změn na jedno volání funkce, tak je to ok, a rychlostní rozdíl vůči mutable typům nezaznamenáte). Pozor, mimo PL/pgSQL jsou v Postgresu všechny hodnoty immutable.

INOUT parametry procedur

V nové verzi mohou mít procedury INOUT parametry. Na rozdíl od funkcí se v procedurách INOUT parametry používají podobně jako v ostatních databázích (minimálně v PL/pgSQL):

CREATE OR REPLACE PROCEDURE foo(a int, INOUT b int, c int, INOUT d int)
AS $$
BEGIN
  b := a + c;
  d := a * c;
END;
$$ LANGUAGE plpgsql;

-- použití z SQL
postgres=# CALL foo(10,NULL,20, NULL);
┌────┬─────┐
│ b  │  d  │
╞════╪═════╡
│ 30 │ 200 │
└────┴─────┘
(1 row)

-- použití z PL/pgSQL
postgres=# DO $$
DECLARE b int; c int;
BEGIN
  CALL foo(10, b, 20, c);
  RAISE NOTICE 'b = %, c = %', b, c;
END;
$$;
NOTICE:  b = 30, c = 200
DO

Ostatní

Opravdu se to nestává často, že by uživatel Postgresu potřeboval pracovat s fyzickou reprezentací řádku určenou tzv tuple ID (TID). TID specifikuje číslo datové stránky a pozici řádku na stránce. Nově Postgres může efektivně provést tzv range scan, kde se filtruje podle TID. Např. podmínkou WHERE ctid >= '(10,0)' určuji, že se má číst vše od 10 datové stránky. Dovedu si představit použití při pokusech o záchranu dat z poškozené databáze.

Stále platí, že kovářova kobyla chodí bosa. Systémový katalog Postgresu obsahuje desítky tabulek, které mají nějaké vztahy mezi sebou. U uživatelských tabulek tyto vztahy reprezentuje a zajišťuje referenční integrita. U systémových tabulek referenční integrita nebyla definovaná - konzistenci zajišťovaly interní procedury (což se bude dít i dále). Nově je ale referenční integrita definována i pro systémové tabulky. Implementačně se nic nemění. Ale definovanou referenční integritu si mohou načíst různé vizualizační nástroje nebo nástroje pro podporu psaní SQL dotazů.

Jelikož nikde nemám žádná data jako jsou uuid nebo MAC adresy, tak neumím posoudit užitečnost podpory bloom filtru pro BRIN index. S bloom filtrem by mělo být možné použít BRIN index i pro data, kde není vztah mezi hodnotou a pozicí v datovém souboru. Všechno kolem bloom filtrů je pro mne magie vyššího řádu, a asi by mne vůbec netrklo je použít.

Nově je také možné vytvořit minmax multi BRIN index. Jestli tomu správně rozumím, tak výchozí BRIN index obsahuje pro každou oblast jednu dvojici hodnot minimum, maximum. multi minmax může obsahovat více dvojic, díky čemuž může být efektivnější v případech, kdy hodnoty nejsou v korelaci s pozicí v datovém souboru.

Výhodou BRIN indexu je jeho malá velikost. Pro náhodná data jsem zkoušel BRIN index s bloom filtrem, přičemž pro cca 4GB tabulku měl index 400kB, a přístup byl cca 3x rychlejší než fullscan. Samozřejmě, že btree index rychlostí někde úplně jinde, ale jeho vytvoření bylo výrazně pomalejší a btree index má 2GB.

 CREATE TABLE t (a int);
 CREATE INDEX ON t 
   USING brin (a int4_minmax_multi_ops(values_per_range=16));

minmax multi index jsem zkoušel na náhodných datech, a fungoval překvapivě dobře. Index měl opět 400kB, ale na rozdíl od brin indexu s bloom filtrem trvalo nalezení hodnoty jen pár milisekund (s btree to bylo hodně pod 1ms, ale btree index byl 4x větší). Vzhledem k tomu, že jsem testoval nad daty, pro které není BRIN index vhodný, tak výsledek je super. S parametrem values_per_range=8 se velikost indexu snížila na 288kB a výběr hodnoty byl stále pod 10ms.

Když u těch indexů. Nově můžeme použít klauzuli INCLUDE i pro SP-GiST indexy. Pokud Postgres zjistí, že index obsahuje vsechny potřebné sloupce (a pokud je tabulka zvacuována a existuje mapa viditelnosti (visibility map), tak může data číst pouze z indexu (index only scan). Pro indexy, které obsahují neklíčové položky, se používá název covering indexes (v Postgresu od verze 11). SP-GiST (partišnovaný GiST) indexy se používají pro velká geo data, která mají rozdílnou hustotu (pevnina x oceány, města a jejich okolí x odlehlé oblasti).

Pokud budete mít Postgres přeložený s podporou lz4 komprese (--with-lz4), tak můžete přepnout interní komprimaci dat z pglz na lz4. Změna se provádí nastavením konfigurační proměnné default_toast_compression (a má vliv pak na nově vytvořené tabulky a sloupce) nebo ALTER COLUMN SET COMPRESSION:

ALTER TABLE cmpart2 ALTER COLUMN f1 SET <b>COMPRESSION lz4</b>;

V testu nad importovanou dokumentací Postgresu v rychlosti vyhrává lz4 (o 50%), naopak ve velikosti dat vyhrává pglz (o 10%). Na syntetických datech byla metoda komprimace lz4 6x rychlejší a data byla o polovičku menší.

Jednou z profláknutých magických konstant v Postgresu je pětka. Může se vám stát, že po pěti zavolání před připraveného dotazu (prepared statement) nebo po pěti zavolání funkce nebo procedury obsahující SQL příkazy se chování SQL příkazů dramaticky změní. Důvodem je heuristika,která určuje jestli se dotaz bude vykonávat obecným plánem (generic plan) nebo jednorázovým plánem (custom plan). Pro prvních pět iterací se použijí vždy jednorázové plány, a pak dále podle poměru ceny jednorázových a generických plánů se budou používat jenom generické plány nebo jednorázové plány. Pokud používáte prepared statements, tak se můžete používat na seznam před připravených příkazů (plánů) pro aktuálním připojení do databáze dotazem do pohledu pg_prepared_statements. Nově tento pohled obsahuje sloupce generic_plans a custom_plans, kde uvidíte, kolikrát se použil generický plán a kolikrát jednorázový plán.

Komunikační protokol mezi serverem a klientem je navržen, tak aby komunikace byla v prvé řadě robustní. Po každém příkazu dochází k synchronizaci spojení. Pro některé úlohy (velké množství rychlých příkazů) nebo některá prostředí s vyššími latencemi na spojení představuje tato synchronizace významnou režii. Řešením by měl být nový režim zřetězeného vykonávání příkazů (pipeline mode). Zřetězeným vykonáváním příkazů můžeme dosáhnout značného zrychlení, na druhou stranu napsat aplikaci, která by používala tento režim dá mnohem víc práce. Podpora pro pipeline režim existuje pouze v Cčkové knihovně libpq. Neslyšel jsem o dalších driverech s podporou tohoto režimu.

Závěr

Loňský rok za moc nestál (a ten letošní není o moc lepší) ale IT se nezastavilo, a docela mi udělalo radost, že PostgreSQL byla úspěšně použitá na několika větších (kritičtějších) projektech v ČR, kde by se dříve uvažovalo pouze o Oracle nebo MS SQL.

Vývoj 14ky prakticky skončil (teď už se bude jen testovat a opravovat chyby). Od července se rozjede vývoj 15ky. Už nyní je připravených patchů víc, než se stihne za rok integrovat. A to letos má vyjít nové rozšíření ANSI/SQL 2021. Práce jak na kostele. Tak uvidíme co bude hotové příští rok.

PostgreSQL 14 (2021): Porovnání verzí

Verze z 9. 8. 2021, 05:21

Obsah

Cizí tabulky (FDW)

Batch `INSERT`

správa cache spojení na cizí servery

SQL

Klauzule SEARCH FIRST a CYCLE pro CTE

Redukce omezení použití klíčových slov v názvů (labels) sloupců příkazu `SELECT`

ANSI/SQL Syntax pro funkce a procedury

Administrace

pg_surgery

pg_stat_statements

psql

Statistiky

Funkcionální statistiky (statistiky nad výrazy)

Změna chování statistik na prázdné tabulce

Interní optimalizace

Zrychlení paralelního sekvenčního čtení

Zrychlení vyhledávání v seznamu hodnot

Nižší pokles výkonu při velkém počtu neaktivních spojení

Redukce nárůstu velikosti indexů z důvodu implementace MVCC

Redukce alokované paměti pro kontrolní dotazy RI na partišnách

Funkce

Datové typy

PL/pgSQL

přímá možnost zápisu do atributu v poli kompozitních hodnot

Podpora indexů (subscripts) pro datové typy jsonb a hstore

INOUT parametry procedur

Ostatní

Závěr

Navigační menu

PostgreSQL 14 (2021): Porovnání verzí

Verze z 9. 8. 2021, 05:21

Cizí tabulky (FDW)

Batch INSERT

správa cache spojení na cizí servery

SQL

Klauzule SEARCH FIRST a CYCLE pro CTE

Redukce omezení použití klíčových slov v názvů (labels) sloupců příkazu SELECT

ANSI/SQL Syntax pro funkce a procedury

Administrace

pg_surgery

pg_stat_statements

psql

Statistiky

Funkcionální statistiky (statistiky nad výrazy)

Změna chování statistik na prázdné tabulce

Interní optimalizace

Zrychlení paralelního sekvenčního čtení

Zrychlení vyhledávání v seznamu hodnot

Nižší pokles výkonu při velkém počtu neaktivních spojení

Redukce nárůstu velikosti indexů z důvodu implementace MVCC

Redukce alokované paměti pro kontrolní dotazy RI na partišnách

Funkce

Datové typy

PL/pgSQL

přímá možnost zápisu do atributu v poli kompozitních hodnot

Podpora indexů (subscripts) pro datové typy jsonb a hstore

INOUT parametry procedur

Ostatní

Závěr

Navigační menu

Hledat

Batch `INSERT`

Redukce omezení použití klíčových slov v názvů (labels) sloupců příkazu `SELECT`