PostgreSQL Novinky (a JSONB) Tomáš Vondra, GoodData

PostgreSQL 9.4 - Novinky (a JSONB) Tomáš Vondra, GoodData ([email protected]) http://blog.pgaddict.com ([email protected])

9.4 release notes http://www.postgresql.org/docs/9.4/static/release-9-4.html

článek od Pavla Stěhule (květen 2015) http://www.root.cz/clanky/postgresql-9-4-transakcni-sql-json-databaze/

What's new in PostgreSQL 9.4 (Magnus Hagander) http://www.hagander.net/talks/postgresql94_2.pdf

PostgreSQL Conference Europe 2014 (říjen, Madrid) https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2014

Postup vývoje 9.4 2013

červen

9.3 branch

2013

červen

commitfest #1

2013

září

commitfest #2

2013

listopad

commitfest #3

2014

leden

commitfest #4

2014

květen

beta 1

2014

červenec

beta 2

2014

říjen

beta 3

2014

listopad (?)

PostgreSQL 9.4.0

Aktuální stav ●

●

●

testuje se beta3 –

Pomozte s testováním!

–

Aplikační testy nade vše (zkuste svoji aplikaci na 9.4).

trochu statistiky –

2222 souborů změněno

–

131805 nových řádek (+)

–

59333 smazaných řádek (-)

méně než 9.3 ... –

ale všichni víme že LOC je výborná metrika ;-)

https://www.openhub.net/p/postgres

Takže co je vlastně nového? ●

vývojářské / SQL vlastnosti

●

DBA a administrace

●

replikace a recovery

●

infrastruktura

Vývojářské / SQL vlastnosti ●

agregační funkce –

FILTER aggregates

–

ordered-set aggregates

–

další menší vylepšení

●

vylepšení aktualizovatelných pohledů

●

UNNEST (WITH ORDINALITY)

●

pl/pgsql stacktrace

●

JSONB (nejlepší na závěr)

agregační výrazy (FILTER) SELECT a, SUM(CASE WHEN b < 10 THEN c ELSE NULL END) AS pod_10, SUM(CASE WHEN b >= 10 THEN c ELSE NULL END) AS nad_10 FROM tabulka GROUP BY a; SELECT a, SUM(c) FILTER (WHERE b < 10) AS pod_10, SUM(c) FILTER (WHERE b >= 10) AS nad_10 FROM tabulka GROUP BY a;

ordered-set aggregates ●

pořadí vstupních hodnot není definováno –

často nepodstatné (MIN, MAX, SUM, …)

–

někdy na něm ale záleží (array_agg, string_agg, …)

–

lze ho určit pomocí ORDER BY ve volání funkce

SELECT a, SUM(b ORDER BY c) AS suma_b_serazene_dle_c, ARRAY_AGG(b ORDER BY c) AS pole_b_serazene_dle_c FROM tabulka GROUP BY a; ( Toto není ordered-set aggregate! )

ordered-set aggregates ●

některé agregační funkce pořadí vyžadují –

z definice (jinak to prostě nedává smysl)

–

rank, percentil, ...

–

direct / aggregate argumenty (rozdíl)

SELECT a, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY b) AS median_b FROM tabulka GROUP BY a;

ordered-set aggregates ●

některé agregační funkce pořadí vyžadují –

z definice (jinak to prostě nedává smysl)

–

rank, percentil, ...

–

direct / aggregate argumenty (rozdíl)

SELECT a, PERCENTILE_DISC(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY b) AS median_b FROM tabulka GROUP BY a; ( proprietární rozšíření )

hypotetické agregační funkce ●

Kam by se zařadila daná hodnota? –

pořadí – rank(..), dense_rank(..)

–

relativní (0, 1) – percent_rank(..), cume_dist(..)

SELECT a, rank('xyz') WITHIN GROUP (ORDER BY b), dense_rank('xyz') WITHIN GROUP (ORDER BY b), percent_rank('xyz') WITHIN GROUP (ORDER BY b) FROM tabulka GROUP BY a;

agregační funkce / další vylepšení ●

group keys v EXPLAIN EXPLAIN SELECT COUNT(a) FROM tabulka GROUP BY b; QUERY PLAN -------------------------------------------------------------HashAggregate (cost=195.00..195.11 rows=11 width=8) Group Key: b -> Seq Scan on tabulka (cost=0.00..145.00 rows=100 width=8) (3 rows)

automaticky updatovatelné pohledy ●

●

pro jednoduché pohledy lze „překládat“ DML příkazy –

jedna tabulka ve FROM

–

bez agregací / množinových operací, apod.

–

pouze jednoduché odkazy na sloupce tabulky

–

nesmí být označený pomocí „security_barrier“

od 9.4 lze používat výrazy, konstanty, volání funkcí –

samozřejmě tyto sloupce nelze měnit

CREATE VIEW zamestnanci_view AS SELECT emp_id, dept_id, (salary*0.6) AS cista_mzda FROM zamestnanci_tabulka WHERE dept_id IN (10, 20);

automaticky updatovatelné pohledy ●

řádky odfiltrované přes WHERE nelze updatovat

●

řádek ale může „vypadnout“ –

WITH CHECK OPTION tomu zabrání

–

další krok na cestě k „Row Level Security“ (řízení přístupu k řádkům)

CREATE VIEW zamestnanci_view AS SELECT id_zamestnance, id_oddeleni, (plat*0.6) AS cista_mzda FROM zamestnanci_tabulka WHERE id_oddeleni IN (10, 20) WITH CHECK OPTION; UPDATE zamestnanci_view SET id_oddeleni = 30;

unnest SELECT unnest(ARRAY[1,2,3]) AS a;

a --1 2 3 (3 rows)

unnest SELECT unnest(ARRAY[1,2,3]) AS a, unnest(ARRAY[4,5,6]) AS b;

unnest SELECT unnest(ARRAY[1,2,3]) AS a, unnest(ARRAY[4,5,6]) AS b; a | b ---+--1 | 4 2 | 5 3 | 6 (3 rows)

unnest SELECT unnest(ARRAY[1,2,3]) AS a, unnest(ARRAY[4,5])

AS b;

unnest SELECT unnest(ARRAY[1,2,3]) AS a, unnest(ARRAY[4,5]) a | b ---+--1 | 4 2 | 5 3 | 4 1 | 5 2 | 4 3 | 5 (6 rows)

AS b;

unnest SELECT * FROM unnest(ARRAY[1,2,3], ARRAY[4,5]) AS t(a,b); a | b ---+--1 | 4 2 | 5 3 | (3 rows)

unnest SELECT * FROM unnest(ARRAY[1,2,3], ARRAY[4,5]) WITH ORDINALITY AS t(a,b); a | b | ordinality ---+---+-----------1 | 4 | 1 2 | 5 | 2 3 | | 3 (3 rows)

unnest / ROWS FROM SELECT a, b, ordinality FROM ROWS FROM (unnest(ARRAY[1,2,3]), unnest(ARRAY[4,5])) WITH ORDINALITY AS t(a,b); SELECT a, b, ordinality FROM ROWS FROM (unnest(ARRAY[1,2,3]), generate_series(1,10)) WITH ORDINALITY AS t(a,b);

PL/pgSQL / call stack CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$ DECLARE stack text; BEGIN GET DIAGNOSTICS stack = PG_CONTEXT; RAISE NOTICE E'--- Call Stack ---\n%', stack; RETURN 1; END; $$ LANGUAGE plpgsql; SELECT outer_func(); NOTICE: --- Call Stack --PL/pgSQL function inner_func() line 4 at GET DIAGNOSTICS PL/pgSQL function outer_func() line 3 at RETURN ...

PL/pgSQL / call stack CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$ DECLARE stack text; BEGIN GET DIAGNOSTICS stack = PG_CONTEXT; RAISE NOTICE E'--- Call Stack ---\n%', stack; RETURN 1; EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS stack = PG_ERROR_CONTEXT; RAISE NOTICE E'--- Exception Call Stack ---\n%', stack; END; $$ LANGUAGE plpgsql; (od 9.2, oboje Pavel Stěhule)

DBA a administrace ●

MATERIALIZED VIEWS

●

přesun objektů mezi tablespacy

●

vylepšení GIN (komprese, fast scan)

●

ALTER SYSTEM / pg_reload_conf

●

nové konfigurační parametry

●

pg_prewarm

●

pg_stat_statements (query ID)

MATERIALIZED VIEWS CREATE MATERIALIZED VIEW my_view AS SELECT …; CREATE UNIQUE INDEX my_index ON my_view (…); REFRESH MATERIALIED VIEW my_view; REFRESH MATERIALIED VIEW CONCURRENTLY my_view;

SET TABLESPACE ... ALTER TABLE ALL IN TABLESPACE tablespace1 OWNED BY uzivatel SET TABLESPACE tablespace2 [NOWAIT]; ALTER INDEX ... ALTER VIEW ... ALTER MATERIALIZED VIEW … (rozdíl oproti Pavlově článku / Magnusově prezentaci)

Vylepšení GIN indexů ●

pro hodnoty složené z „částí“ (pole, slova, …)

●

index se skládá z položek key1 => [rowid1, rowid2, rowid3, …] key2 => [rowid10, rowid20, rowid30, …] …

●

v podstatě bitmapové indexy (zvláštně kódované) –

●

jde použít na skalární typy (extenze btree_gin)

dvě významná vylepšení v 9.4 –

komprese posting listů (seznam odkazů na řádky)

–

fast scan (dotazy typu „častý & vzácný“ výrazně rychlejší)

ALTER SYSTEM ●

dosud bylo nutné přímo editovat postgresql.conf $ vim /var/lib/pgsql/9.1/data/postgresql.conf

●

nově je možné toho dosáhnout přímo z databáze ALTER SYSTEM SET work_mem = '128MB';

●

vytvoří se nový soubor s konfigurací (include) postgresql.auto.conf

●

stále nutný explicitní reload / restart :-( SELECT pg_reload_conf();

Konfigurační parametry ●

autovacuum_work_mem odděleno z maintenance_work_mem huge_pages – Linuxové systémy s velkým objemem RAM session_preload_libraries – načtení sdílených knihoven – na rozdíl od local_preload_libraries libovolných wal_log_hints – standardně se nelogují – ale občas jsou užitečné - replikace, rewind (maintenance_)work_mem / effective_cache_size – navýšení default hodnot (4x) –

●

●

●

●

pg_prewarm ●

triviální způsob jak „zahřát cache“ –

page cache (kernel) i shared buffers (DB)

–

statistiky shared_buffers lze získat přes pg_buffercache

pg_prewarm(regclass, mode text default 'buffer', fork text default 'main', first_block int8 default null, last_block int8 default null) RETURNS int8 (možná kombinace s pgfincore)

pg_stat_statements ●

●

●

texty dotazů se ukládají do souboru –

šetří sdílenou paměť

–

odstraňuje limit na délku dotazu

doplnění „query ID“ (interní hash dotazu) –

identifikace dotazu (monitorovací nástroje)

–

nestabilní mezi major verzemi / platformami

možnost získat statistiky bez textů dotazů SELECT * FROM pg_stat_statements(false); –

úspornější fungování monitorovacích nástrojů

Replikace a recovery ●

přesun tablespaces v pg_basebackup CREATE TABLESPACE ... LOCATION '...'; –

jiné rozložení disku, stejná mašina => :-( pg_basebackup -T olddir=newdir

●

time-delayed standby (recovery.conf) recovery_min_apply_delay=3600000

●

pg_stat_archiver –

čas/počet archivovaných WAL segmentů (atd.)

–

úspěšné i neúspěšné pokusy

Infrastruktura ●

●

základ logické replikace –

zpětná extrakce změn z transakčního logu

–

základy v 9.4, další patche (9.5)

–

alternativa k Slony, londiste, ...

replikační sloty –

flexibilní zachovávání WAL segmentů

–

alternativa wal_keep_segments / archive_command

–

alternativa hot_standby_feedback / vacuum_defer_cleanup_age

Infrastruktura ●

background workers –

uživatelské procesy spravované databází (extensions)

–

dynamická registrace, spouštění, ukončování

–

max_worker_processes

–

...

–

vnímáno jako základ pro „Parallel Query“

Spousta dalšího ... http://www.postgresql.org/docs/9.4/static/release-9-4.html

●

spousta malých změn a vylepšení –

výkonnostní vylepšení

–

zjednodušení práce

–

atd. atd.

JSONB Dokumentace http://www.postgresql.org/docs/9.4/static/datatype-json.html

NoSQL on ACID https://wiki.postgresql.org/images/d/de/NoSQL_training_-_pgconf.eu.pdf

KV a dokumenty v PostgreSQL HSTORE ●

kolekce key-value –

pouze řetězce, jedna úroveň (bez vnoření)

●

jednoduchá definice, rychlý, snadná práce

●

PostgreSQL 8.2 (2006) –

●

predatuje mnohé NoSQL řešení

ideální pro „řídké“ kolekce hodnot –

spousta sloupců, neznámé sloupce, ...

KV a dokumenty v PostgreSQL JSON ●

hierarchický model dokumentů

●

9.2 – samostatný datový typ

●

–

víceméně jenom validace vstupu, minimum funkcí

–

možnost psát funkce v PL/V8, PL/Perl, …

9.3 – doplněny operátory / funkce pro manipulaci, ...

JSONB ●

binární reprezentace JSON (neplést s BSON)

●

rychlejší, širší paleta operátorů, robustnější

●

PostgreSQL 9.4 (namísto vyvíjeného HSTORE2)

JSONB příklad CREATE TABLE json_data (data JSONB);

INSERT INTO json_data (data) VALUES ('{"name": "Apple Phone", "type": "phone", "brand": "ACME", "price": 200, "available": true, "warranty_years": 1}');

JSONB příklad CREATE TABLE json_data (data JSONB);

INSERT INTO json_data (data) VALUES ('{"full name": "John Joseph Carl Salinger", "names": [ {"type": "firstname",

"value": "John"},

{"type": "middlename", "value": "Joseph"}, {"type": "middlename", "value": "Carl"}, {"type": "lastname", ]}');

"value": "Salinger"}

funkce a operátory ●

http://www.postgresql.org/docs/9.4/static/functions-json.html

●

extrakce hodnot z JSON dokumentů

●

->

jako JSON dokument

#>

jako JSON dokument

->>

jako text

#>>

jako text

containment a existence @>

containment (sub-dokument)

?

existence klíče

?|

existence (alespoň jeden klíč)

?&

existence (všechny klíče)

extrakce hodnot (jako JSON) SELECT '{"a" : {"b" : "c"}}'::jsonb -> 'a'; {"b": "c"}

SELECT '{"a" : {"b" : "c"}}'::jsonb -> 'a' -> 'b'; "c"

SELECT '{"a" : {"b" : "c"}}'::jsonb #> '{a, b}'; SELECT '{"a" : {"b" : "c"}}'::jsonb #> ARRAY['a', 'b']; "c"

containment & existence SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb; true SELECT '{"a":1, "b":2}'::jsonb @> '{"b":3}'::jsonb; false SELECT '{"a":1, "b":2}'::jsonb ? 'a'; true SELECT '{"a":1, "b":2}'::jsonb ?| ARRAY['a', 'c']; true SELECT '{"a":1, "b":2}'::jsonb ?& ARRAY['a', 'c']; false

processing functions ●

to_json

●

json_extract_path

●

array_to_json

●

json_extract_path_text

●

row_to_json

●

json_object_keys

●

json_build_array

●

json_populate_record

●

json_build_object

●

json_populate_recordset

●

json_object

●

json_array_elements

●

json_array_elements_text

●

json_array_length

●

json_typeof

●

json_each

●

json_to_record

●

json_each_text

●

json_to_recordset

http://www.postgresql.org/docs/9.4/static/functions-json.html

processing functions ●

to_json

●

jsonb_extract_path

●

array_to_json

●

jsonb_extract_path_text

●

row_to_json

●

jsonb_object_keys

●

json_build_array

●

jsonb_populate_record

●

json_build_object

●

jsonb_populate_recordset

●

json_object

●

jsonb_array_elements

●

jsonb_array_elements_text

●

jsonb_array_length

●

jsonb_typeof

●

jsonb_each

●

jsonb_to_record

●

jsonb_each_text

●

jsonb_to_recordset

http://www.postgresql.org/docs/9.4/static/functions-json.html

Mailing list archive CREATE TABLE messages id parent_id thread_id list message_id ... sent author subject headers body_plain subject_tsvector body_tsvector );

( integer PRIMARY KEY, integer, integer, varchar(32) NOT NULL, varchar(200), timestamp, text, text, jsonb, text, tsvector, tsvector

JSONB / diskový prostor List of relations Schema | Name | Size --------+----------------------+--------public | headers_jsonb | 1244 MB public | headers_jsonb_beta_2 | 1330 MB public | headers_json | 1517 MB public | headers_text | 1517 MB dump 1567 MB

Dotazování SELECT COUNT(*) FROM messages WHERE headers ? 'bcc'; SELECT (headers->>'message-id') AS mid FROM messages WHERE headers ? 'bcc'; SELECT COUNT(*) FROM messages WHERE headers @> '{"from" : "[email protected]"}'; QUERY PLAN -----------------------------------------------------------------Aggregate (cost=177501.99..177502.00 rows=1 width=0) -> Seq Scan on messages (cost=0.00..177499.70 rows=917 width=0) Filter: (headers @> '{"from": "[email protected]"}'::jsonb) Planning time: 0.178 ms (4 rows)

Indexování / btree ●

nesmysl indexovat celé JSON dokumenty – ale indexy nad výrazy lze použít

CREATE INDEX messages_cc_idx ON messages ((headers->>'cc')); SELECT * FROM messages WHERE headers->>'cc' = '[email protected]'; QUERY PLAN ------------------------------------------------------------------------Bitmap Heap Scan on messages (cost=200.09..16121.34 rows=4585 width=1529) Recheck Cond: ((headers ->> 'cc'::text) = '[email protected]'::text) -> Bitmap Index Scan on ttt (cost=0.00..198.94 rows=4585 width=0) Index Cond: ((headers ->> 'cc'::text) = '[email protected]'::text) Planning time: 1.044 ms (5 rows)

Indexování / GIN CREATE INDEX messages_gin_idx ON messages USING gin(headers); SELECT * FROM messages WHERE headers @> '{"cc" : "[email protected]"}'; QUERY PLAN -------------------------------------------------------------------------Bitmap Heap Scan on messages (cost=51.11..3518.80 rows=917 width=1529) Recheck Cond: (headers @> '{"cc": "[email protected]"}'::jsonb) -> Bitmap Index Scan on messages_gin_idx (cost=0.00..50.88 rows=917 width=0) Index Cond: (headers @> '{"cc": "[email protected]"}'::jsonb) Planning time: 0.135 ms (5 rows)

Indexování / GIN ●

jsonb_ops – –

●

výchozí operator class všechny základní operátory @> ? ?| ?&

jsonb_path_ops – –

menší, rychlejší indexy pouze @> operátor CREATE INDEX headers_path_idx ON messages USING gin(headers jsonb_path_ops);

●

možnost subdocument indexů CREATE INDEX messages_cc_idx ON messages USING gin((headers->'cc'));

Indexování / GIN List of relations Schema | Name | Size --------+---------------------------+-------public | messages_btree_idx | 64 MB public | messages_gin_idx | 503 MB public | messages_gin_path_idx | 270 MB public | messages_hash_id_key | 67 MB public | messages_pkey | 36 MB public | messages_cc_idx | 25 MB (4 rows)

PostgrteSQL NoSQL benchmark https://github.com/EnterpriseDB/pg_nosql_benchmark http://bit.ly/1rXinmp / http://bit.ly/1t2jG1r

(nižší hodnoty jsou lepší)

Fulltext benchmark ●

fulltextové vyhledávání v e-mailovém archivu

●

kombinace slov s různou frekvencí

●

33k reálných dotazů z postgresql.org

SELECT id FROM messages WHERE body_fts @@ ('high & performance')::tsquery ORDER BY ts_rank(body_fts, ('high & performance')::tsquery) DESC LIMIT 100;

Fulltext benchmark / 9.3 vs. 9.4 (GIN fastscan) 9.4 durations, divided by 9.3 durations (e.g. 0.1 means 10x speedup)

9.4 duration (relative to 9.3)

1.8 1.6 1.4 1.2 1 0.8 0.6 0.4 0.2 0 0.1

1

10

100

duration on 9.3 [miliseconds, log scale]

1000