Äîêóìåíò âçÿò èç êýøà ïîèñêîâîé ìàøèíû. Àäðåñ îðèãèíàëüíîãî äîêóìåíòà : http://www.sai.msu.su/~megera/postgres/talks/RIT-Bartunov-Korotkov-2014.pdf Äàòà èçìåíåíèÿ: Fri Apr 25 15:16:07 2014 Äàòà èíäåêñèðîâàíèÿ: Mon Apr 11 08:09:58 2016 Êîäèðîâêà: Ïîèñêîâûå ñëîâà: m 43

- PostgreSQL 9.4
, PostgreSQL major contributor , PostgreSQL major contributor


::Teodor Sigaev
· Locale support · Extendability (indexing)
· GiST, GIN, SP-GiST

· Extensions:
· · · · · intarray pg_trgm ltree hstore, hstore v2.0 jsonb plantuner
htps://www.facebook.com/oleg.bartunov obartunov@gmail.com

· Full Text Search (FTS)


Alexander Korotkov
· · · · · Indexed regexp search GIN compression & fast scan Fast GiST build Range types indexing Split for GiST

aekorotkov@gmail.com


Agenda
· - PostgreSQL
· · · · · · · · · - NoSQL vs Relatonal Hstore -- key-value model Json, Jsonb -- document-oriented model Parallelism (Background workers) View (Materialized) Logical replicaton Indexing

· PostgreSQL 9.4 ( , 2014)


-
· - :) · ( ) · - RDBMS
· ,

· json -- - · -- schema-less
· · Key-value (NoSQL)


NoSQL
· · · · ·

( )

· --
SQL -- , .
· , , SQL · SQL , ...


NoSQL
· · · · · · ·

( )

·
, SQL->HTTP key-value « », ACID BASE -- ,

· , , -
· - shared-nothing

· NoSQL --


NoSQL
· Key-value databases
· Ordered k-v

· Column family (column-oriented) stores · Big Table -- value :
· column families, columns, and tmestamped versions (maps-of maps-of maps)

· Document databases · Value - ,
· , FTS --

· Graph databases -- ordered-kv



!
· -
· ( key-value ) · ( ) · ( , )


Introducton to hstore
· Hstore -- key/value storage
(inspired by perl hash)

'a=>1, b=>2'::hstore
· Key, value -- strings · Get value for a key: hstore -> text · Operators with indexing support (GiST, GIN) Check for key: hstore ? text Contains: hstore @> hstore · check documentatons for more · Functons for hstore manipulatons (akeys, avals, skeys, svals, each,......)


History of hstore development
· May 16, 2003 -- frst version of hstore


Introducton to hstore
· Hstore benefts
· In provides a fexible model for storing a semi-structured data in relatonal database · hstore has binary storage

· Hstore drawbacks
· Too simple model ! Hstore key-value model doesn't supports tree-like structures as json (introduced in 2006, 3 years afer hstore)

· Json -- popular and standartzed (ECMA-404 The JSON Data Interchange Standard, JSON RFC-7159) · Json -- PostgreSQL 9.2, textual storage


Hstore vs Json
· hstore json
CREATE TABLE hstore_test AS (SELECT 'a=>1, b=>2, c=>3, d=>4, e=>5'::hstore AS v FROM generate_series(1,1000000)); CREATE TABLE json_test AS (SELECT '{"a":1, "b":2, "c":3, "d":4, "e":5}'::json AS v FROM generate_series(1,1000000));

SELECT sum((v->'a')::text::int) FROM json_test; 851.012 ms SELECT sum((v->'a')::int) FROM hstore_test; 330.027 ms


Hstore vs Json
· PostgreSQL already has json since 9.2, which supports documentbased model, but
· It's slow, since it has no binary representaton and needs to be parsed every tme · Hstore is fast, thanks to binary representaton and index support · It's possible to convert hstore to json and vice versa, but current hstore is limited to key-value

· Need hstore with document-based model. Share it's binary representation with json !


History of hstore development
· May 16, 7.3 · Dec, 05, (thanks, · May 23, · Sep, 20, 2003 - frst (unpublished) version of hstore for PostgreSQL 2006 Hube 2007 2010 - hstore is a part of PostgreSQL 8.2 rt Depesz Lubaczewski!) - GIN index for hstore, PostgreSQL 8.3 - Andrew Gierth improved hstore, PostgreSQL 9.0


Nested hstore


Nested hstore & jsonb
· Nested hstore PGCon-2013, , ( 24 ) -- Engine Yard !
One step forward true json data type.Nested hstore with arrays support

· PGCon Europe -- 2013, , (29 )
Binary storage for nested data structuresand applicaton to hstore data type

·
· nested hstore jsonb --

· Andrew Dunstan jsonb
·


Nested hstore & jsonb
· - Peter Geoghegan, hstore , (Nested hstore patch for 9.3). · 23 Andrew Dunstan jsonb 9.4 !
pgsql: Introduce jsonb, a structured format for storing json.
Introduce jsonb, a structured format for storing json. The new format accepts exactly the same data as the json type. However, it is stored in a format that does not require reparsing the orgiginal text in order to process it, making it much more suitable for indexing and other operatons. Insignifcant whitespace is discarded, and the order of object keys is not preserved. Neither are duplicate object keys kept - the later value for a given key is the only one stored.


Jsonb vs Json
select '{"c":0, "a":2,"a":1}'::json, '{"c":0, json | jsonb -----------------------+-----------------{"c":0, "a":2,"a":1} | {"a": 1, "c": 0} (1 row) "a":2,"a":1}'::jsonb;

· · · ·

jso jso jso jso

n: nb: nb: nb:

«as is» (whitespace) ,


Jsonb vs Json
· Data · 1,252,973 bookmarks from Delicious in json format · The same bookmarks in jsonb format · The same bookmarks as text · Server · MBA, 8 GB RAM, 256 GB SSD · Test · Input performance - copy data to table · Access performance - get value by key · Search performance contains @> operator


Jsonb vs Json
select count(js->>'title') from js; count --------1252973 (1 row) Time: 9215.143 ms select count(jb->>'title') from jb; count --------1252973 (1 row) Time: 977.860 ms select count(js->'tags'->1->'term') from js; count -------796792 (1 row) Time: 12352.468 ms select count(jb->'tags'->1->'term') from jb; count -------796792 (1 row) Time: 1080.460 ms

Jsonb 10 Json !


Jsonb vs Json
· Data · 1,252,973 bookmarks from Delicious in json format · The same bookmarks in jsonb format · The same bookmarks as text
=# \dt+ Schema -------public public public | +| | | List of relations Name | Type | Owner | Size | Description -----+-------+----------+---------+------------jb | table | postgres | 1374 MB | js | table | postgres | 1322 MB | tx | table | postgres | 1322 MB |

< 4%


Jsonb vs Json
· Input performance ( )
· Copy data (1,252,973 rows) as text, json,jsonb
copy t from '/path/to/test.dump'

Text: 34 s Json: 37 s Jsonb: 43 s

- as is - json validaton - json validaton, binary storage


Jsonb vs Json
· Access performance -- get value by key
· · · · Base: select j from js; Jsonb: select j->>'updated' from jb; Json: select j->>'updated' from js; Regexp: select (regexp_matches( j,

'"updated":"([^"]*)"'))[1] from tx; Base: 0.6 s Jsonb: 1 s Jsonb 40X Json ! Json: 9.6 s , regexp: 12.8 s


Jsonb vs Json
explain analyze select count(*) from js where js #>>'{tags,0,term}' = 'NYC'; QUERY PLAN ---------------------------------------------------------------------------Aggregate (cost=187812.38..187812.39 rows=1 width=0) (actual time=10054.602..10054.602 rows=1 loops=1) -> Seq Scan on js (cost=0.00..187796.88 rows=6201 width=0) (actual time=0.030..10054.426 rows=123 loops=1) Filter: ((js #>> '{tags,0,term}'::text[]) = 'NYC'::text) Rows Removed by Filter: 1252850 Planning time: 0.078 ms Execution runtime: 10054.635 ms (6 rows)

Json: contains @>,


Jsonb vs Json
explain analyze select count(*) from jb where jb @> '{"tags":[{"term":"NYC"}]}'::jsonb; QUERY PLAN --------------------------------------------------------------------------------------Aggregate (cost=191521.30..191521.31 rows=1 width=0) (actual time=1263.201..1263.201 rows=1 loops=1) -> Seq Scan on jb (cost=0.00..191518.16 rows=1253 width=0) (actual time=0.007..1263.065 rows=285 loops=1) Filter: (jb @> '{"tags": [{"term": "NYC"}]}'::jsonb) Rows Removed by Filter: 1252688 Planning time: 0.065 ms Execution runtime: 1263.225 ms (6 rows)

Jsonb: contains @> ! json ~ 10


Jsonb vs Json
create index gin_jb_idx on jb using gin(jb);
explain analyze select count(*) from jb where jb @> '{"tags":[{"term":"NYC"}]}'::jsonb; QUERY PLAN --------------------------------------------------------------------------------------Aggregate (cost=4772.72..4772.73 rows=1 width=0) (actual time=8.486..8.486 rows=1 loops=1) -> Bitmap Heap Scan on jb (cost=73.71..4769.59 rows=1253 width=0) (actual time=8.049..8.462 rows=285 loops=1) Recheck Cond: (jb @> '{"tags": [{"term": "NYC"}]}'::jsonb) Heap Blocks: exact=285 -> Bitmap Index Scan on gin_jb_idx (cost=0.00..73.40 rows=1253 width=0) (actual time=8.014..8.014 rows=285 loops=1) Index Cond: (jb @> '{"tags": [{"term": "NYC"}]}'::jsonb) Planning time: 0.115 ms Execution runtime: 8.515 ms (8 rows)

Jsonb: contains @> ! GIN : 150X !! - keys && values


GIN hash index
· Idea: index hash(full paths to elements and values) {a=>{b=>{c=>1}}, d=>{1,2,3}} path-keys: a.b.c.1, d..1, d..2,d..3 GIN: {hash(path-key)}


Jsonb vs Json
create index gin_jb_hash_idx on jb using gin(jb jsonb_hash_ops);
explain analyze select count(*) from jb where jb @> '{"tags":[{"term":"NYC"}]}'::jsonb; QUERY PLAN --------------------------------------------------------------------------------------Aggregate (cost=4732.72..4732.73 rows=1 width=0) (actual time=0.644..0.644 rows=1 loops=1) -> Bitmap Heap Scan on jb (cost=33.71..4729.59 rows=1253 width=0) (actual time=0.102..0.620 rows=285 loops=1) Recheck Cond: (jb @> '{"tags": [{"term": "NYC"}]}'::jsonb) Heap Blocks: exact=285 -> Bitmap Index Scan on gin_jb_hash_idx (cost=0.00..33.40 rows=1253 width=0) (actual time=0.062..0.062 rows=285 loops=1) Index Cond: (jb @> '{"tags": [{"term": "NYC"}]}'::jsonb) Planning time: 0.056 ms Execution runtime: 0.668 ms (8 rows)

Jsonb: contains @> ! GIN : 150X !! - keys && values GIN++ : 1800X !!! - hash key.value


MongoDB 2.6.0
· Load data - ~13 min SLOW ! Jsonb 43 s
mongoimport --host localhost -c js --type json < delicious-rss-1250k 2014-04-08T22:47:10.014+0400 3700 1233/second ... 2014-04-08T23:00:36.050+0400 1252000 1547/second 2014-04-08T23:00:36.565+0400 check 9 1252973 2014-04-08T23:00:36.566+0400 imported 1252973 objects

· Search - ~ 1s (seqscan) THE SAME
db.js.fnd({tags: {$elemMatch:{ term: "NYC"}}}).count() 285 -- 980 ms


MongoDB 2.6.0
· Search -- 1ms (index)
db.js.ensureIndex( {"tags.term" : 1} ) db.js.fnd({tags: {$elemMatch:{ term: "NYC"}}}).explain() { "cursor" : "BtreeCursor tags.term_1", "isMultKey" : true, "n" : 285, "nscannedObjects" : 285, "nscanned" : 285, "nscannedObjectsAllPlans" : 285, ................................ "millis" : 1, "indexBounds" : { "tags.term" : [ [ "NYC", "NYC" ] ]


Summary: PostgreSQL 9.4 vs Mongo 2.6.0
· contains @>
· · · · · · · · · json jsonb jsonb mongo : : : : 10 8.5 0.7 1.0 s ms ms ms seqscan GIN GIN HASH opclass btree index 690 295 44 387 100

·Table size

·Input performance:
· · · · Text Json Jsonb mongo : : : : 34 37 43 13 s s s m

·postgres : 1.3Gb ·mongo : 1.8Gb

·
jsonb_ops (?) jsonb_hash_ops jsonb_hash_ops (tags) mongo (tags) mongo (tags.term)

Mb Mb Mb (using gin((jb->'tags') jsonb_hash_ops) Mb Mb


Jsonb functons
Name | Result data type | Argument data types ----------------------------+------------------+-------------------------------------------------------------------------jsonb_array_element | jsonb | from_json jsonb, element_index integer jsonb_array_element_text | text | from_json jsonb, element_index integer jsonb_array_elements | SETOF jsonb | from_json jsonb, OUT value jsonb jsonb_array_elements_text | SETOF text | from_json jsonb, OUT value text jsonb_array_length | integer | jsonb jsonb_cmp | integer | jsonb, jsonb jsonb_contained | boolean | jsonb, jsonb jsonb_contains | boolean | jsonb, jsonb jsonb_each | SETOF record | from_json jsonb, OUT key text, OUT value jsonb jsonb_each_text | SETOF record | from_json jsonb, OUT key text, OUT value text jsonb_eq | boolean | jsonb, jsonb jsonb_exists | boolean | jsonb, text jsonb_exists_all | boolean | jsonb, text[] jsonb_exists_any | boolean | jsonb, text[] jsonb_extract_path | jsonb | from_json jsonb, VARIADIC path_elems text[] jsonb_extract_path_op | jsonb | from_json jsonb, path_elems text[] jsonb_extract_path_text | text | from_json jsonb, VARIADIC path_elems text[] jsonb_extract_path_text_op | text | from_json jsonb, path_elems text[] jsonb_nested_contains | boolean | jsonb, jsonb jsonb_object_field | jsonb | from_json jsonb, field_name text jsonb_object_field_text | text | from_json jsonb, field_name text jsonb_object_keys | SETOF text | jsonb jsonb_out | cstring | jsonb jsonb_populate_record | anyelement | base anyelement, from_json jsonb, use_json_as_text boolean DEFAULT false jsonb_populate_recordset | SETOF anyelement | base anyelement, from_json jsonb, use_json_as_text boolean DEFAULT false jsonb_recv | jsonb | internal jsonb_send | bytea | jsonb jsonb_to_record | record | from_json jsonb, nested_as_text boolean DEFAULT false jsonb_to_recordset | SETOF record | from_json jsonb, nested_as_text boolean DEFAULT false jsonb_typeof | text | jsonb


Jsonb
·
· JSON Types, JSON Functons and Operators

· nested hstore
·

· ·
· « - » - women oriented query (+) · VODKA -- GIN CREATE INDEX ... USING VODKA !

· <,>,&& ...


NoSQL vs Relatonal
· PostgreSQL 9.4+ -- -
· · · · · json MongoDB NoSQL !?
· 0.1% NoSQL · NoSQL -- cache



NoSQL vs Relatonal

4-core HT+ server, 1 client with 1..64 python scripts async synchronous_commit = of, json documents data fts in memory


PostgreSQL popularity - 2014

htp://www.databasefriends.co/2014/03/favorite-relatonal-database.html


PostgreSQL popularity - 2014
htp://db-engines.com/en/ranking


PostgreSQL 9.4


9.4: Development schedule
· · · · · June 14, 2013 - branch 9.3 June 2013 - CF1 September 2013 - CF2 November 2013 - CF3 January 2014 -- CF4 14 ----> htps://commitest.postgresql.org /


9.4: Parallelism in PostgreSQL
· «Implementng Parallelism in PostgreSQL. Where We Are Today, and What's On The Horizon».
htps://www.pgcon.org /2014/schedule/events/693.en.html
PostgreSQL's architecture is based heavily on the idea that each connecton is served by a single backend process, but CPU core counts are rising much f aster than CPU speeds, and large data sets can't be efciently processed serially. Adding parallelism to PostgreSQL requires signifcant architectural changes to many areas of the system, including background workers, shared memory, memory allocaton, locking, GUC, transactons, snapshots, and more.


9.4: Dynamic background workers


9.4: Dynamic background workers
· 9.3:
· , , «» . ·

· , , ORDER BY, , . , , . , ( shared memory ).


9.4: Dynamic background workers
· 9.4: Allow background workers to be started dynamically.
· · · : contrib/worker_spi

· 9.4: Allow dynamic allocaton of shared memory segments.
· . Shared memory .

· 9.4: Shared Memory Message Queues
· shared memory ( ) .


9.4: 256 Gb
· Allow using huge TLB pages on Linux (MAP_HUGETLB)
· , 1Tb . , «In-Memory Columnar Store extension for PostgreSQL»,
htps://www.pgcon.org /2014/schedule/events/643.en.html «IMCS is In-Memory Columnar Store for PostgreSQL. Vertcal data model is more efcient for analytc queries performing operatons on entre column. IMCS provides 10-100 tmes improvement in performance comparing with standard SQL queries «

· Linux 4 Kb 256 Gb · "Transacton Lookaside Bufers" (TLB), Huge Page Size -- 2048 Kb ! Linux.
Cat /proc/meminfo | grep Hugepagesize


9.4: View (1)
· 9.4: CREATE VIEW .... WITH [CASCADED | LOCAL) CHECK OPTION
· ( auto-updatable VIEW), VIEW
CREATE TABLE INSERT INTO CREATE VIEW INSERT INTO =# SELECT * i ---1 -1 5 -5 (4 rows) =# SELECT * i --1 5 (2 rows) t1 (i integer); t1 VALUES(1),(-1); vt1 AS SELECT * FROM t1 WHERE i > 0; vt1 VALUES(5),(-5); FROM t1; CREATE TABLE t1 (i integer); INSERT INTO t1 VALUES(1),(-1); CREATE VIEW vt1 AS SELECT * FROM t1 WHERE i > 0 WITH CHECK OPTION; INSERT INTO vt1 VALUES (5); INSERT INTO vt1 VALUES (-5); ERROR: new row violates WITH CHECK OPTION for view "vt1" DETAIL: Failing row contains (-5). =# SELECT * FROM t1; i ---1 -1 5 (3 rows)

FROM vt1;


9.4: View (2)
· WITH CHECK OPTION · LOCAL
· VIEW

· CASCADE
· VIEW · -


9.4: View (3)
· Allow only some columns of a view to be auto-updateable · 9.3: view auto-updateable, ,
create view vvt1 as select t1.*, (select avg(i) from t1) from t1; SELECT column_name, is_updatable FROM information_schema.columns WHERE table_name = 'vvt1'; column_name | is_updatable -------------+-------------i | NO avg | NO

· 9.4: :)

SELECT column_name, is_updatable FROM information_schema.columns WHERE table_name = 'vvt1'; column_name | is_updatable -------------+-------------i | YES avg | NO


9.4: View (4)
· Make security barrier views automatcally updatable
Views which are marked as security_barrier must have their quals applied before any user-defined quals are called, to prevent user-defined functions from being able to see rows which the security barrier view is intended to prevent them from seeing. Remove the restriction on security barrier views being automatically updatable by adding a new securityQuals list to the RTE structure which keeps track of the quals from security barrier views at each level, independently of the user-supplied quals. When RTEs are later discovered which have securityQuals populated, they are turned into subquery RTEs which are marked as security_barrier to prevent any user-supplied quals being pushed down (modulo LEAKPROOF quals).


9.4: View (5)
· Security barrier view 9.2
CREATE CREATE SELECT SELECT SELECT TABLE t AS SELECT n, VIEW t_odd AS SELECT * FROM t_odd WHERE n * FROM (SELECT * FROM * FROM t t_odd WHERE 'secret'||n AS n, secret FROM < 4 => t WHERE n % 2 (n % 2 = 1) AND secret FROM generate_series(1,20) n; t WHERE n % 2 = 1; = 1) t_odd WHERE n < 4 => (n < 4)

EXPLAIN SELECT * FROM t_odd WHERE n < 4; QUERY PLAN --------------------------------------------------Seq Scan on t (cost=0.00..31.53 rows=2 width=36) Filter: ((n < 4) AND ((n % 2) = 1)) (2 rows)


9.4: View (6)
· Security barrier view 9.2
CREATE OR REPLACE FUNCTION f_leak(text) RETURNS boolean AS $$ BEGIN RAISE NOTICE 'Secret is: %',$1; RETURN true; END; $$ COST 1 LANGUAGE plpgsql; EXPLAIN SELECT * FROM t_odd WHERE f_leak(secret) AND n < 4; QUERY PLAN ---------------------------------------------------------Seq Scan on t (cost=0.00..34.60 rows=1 width=36) Filter: (f_leak(secret) AND (n < 4) AND ((n % 2) = 1)) (2 rows)


9.4: View (7)
· Security barrier view 9.2
SELECT * FROM t_odd WHERE f_leak(secret) AND n < 4; NOTICE: Secret is: secret1 NOTICE: Secret is: secret2 ............................. NOTICE: Secret is: secret15 NOTICE: Secret is: secret16 NOTICE: Secret is: secret17 NOTICE: Secret is: secret18 NOTICE: Secret is: secret19 NOTICE: Secret is: secret20 n | secret ---+--------1 | secret1 3 | secret3 (2 rows)


9.4: View (8)
· 9.2: View security_barrier ( autoupdatable)
· «view» ( security_barrier) · < LEAKPROOF, «» security_barrier
CREATE VIEW t_odd_sb WITH (security_barrier) AS SELECT n, secret FROM t WHERE n % 2 = 1; EXPLAIN SELECT * FROM t_odd_sb WHERE f_leak(secret) AND n < 4; QUERY PLAN --------------------------------------------------------------Subquery Scan on t_odd_sb (cost=0.00..31.55 rows=1 width=36) Filter: f_leak(t_odd_sb.secret) -> Seq Scan on t (cost=0.00..31.53 rows=2 width=36) Filter: ((n < 4) AND ((n % 2) = 1)) (4 rows)


9.4: View (4)
· 9.4: Make security barrier views automatcally updatable
CREATE VIEW rw_view AS SELECT person FROM tbl WHERE visibility = 'public'; EXPLAIN (costs off) UPDATE rw_view SET person=person WHERE leak(person); QUERY PLAN ----------------------------------------------------Update on base_tbl base_tbl_1 -> Subquery Scan on base_tbl Filter: leak(base_tbl.person) -> Seq Scan on base_tbl base_tbl_2 Filter: (visibility = 'public'::text)


9.4: Materialized View
· 9.3: CREATE (ALTER, DROP, REFRESH) MATERIALIZED VIEW
· · · · ( simple view) (AccessExclusiveLock lock) «not quite usable»

· 9.4: REFRESH MATERIALIZED VIEW CONCURRENTLY
· ( ) · ( , full outer join , ).


9.4: Logical Replicaton (1)
· PostgreSQL WAL
· · · · -- «», WAL recovery master-slave ,

·

htp://wiki.postgresql.org /wiki/Replicaton,_Clustering,_and_Connecton_Pooling · statement based: Slony, Bucardo, Pgpool, londiste


9.4: Logical Replicaton (2)
· (Robert Haas):
ht · · · · · · · p://rhaas.blogspot.ru/2011/02/case-for-logical-replicaton.html You can't replicaton to a diferent major version of PostgreSQL. You certainly can't replicate to a database other than PostgreSQL. You can't replicate part of the database. You can't write any data at all on the standby. You certainly can't do mult-master replicaton. MVCC bloat on the master propagates to the standby. Anything that would bloat the standby causes query cancellatons instead, or delays in recovery (or in 9.1, you'll be able to avoid the query cancellaton by bloatng the master).




9.4: Logical Replicaton (3)
· (tuples, ?) (inserted, updated, deleted)
-- , · Changeset extracton -- 9.4+ · WAL decoding -- 9.4+ SQL , · postgresql.conf:
wal_level = logical, max_replicaton_slots =

·
· Core


9.4: Logical Replicaton (4)
· , · API , . SLONY htp://www.postgresql.org /message-id/BLU0-SMTP30D7892E90D1FF9E4EA77EDC570@phx.gbl


9.4: GIN (Generalized Inverted Index)

QUERY: compensaton accelerometers INDEX: accelerometers compensaton 5,10,25,28,30,36,58,59,61,73,74 30,68 RESULT:

30


9.4: GIN
· Postng list compression
· 9.3 format: (4 bytes blockNumber , 2 bytes ofset): 90 bytes
(0,8) (0,14) (0,17) (0,22) (0,26) (0,33) (0,34) (0,35) (0,45) (0,47) (0,48) (1,3) (1,4) (1,6) (1,8)

· 9.4 format: compressed format, diference from previous item: 21 bytes
(0,8) +6 +3 +5 +4 +7 +1 +1 +10 +2 +1 +2051 +1+2 +2

· 10,000,000 -- 11 Mb vs 58 Mb ! SELECT g % 10 FROM generate_series(1,10000000) g;


9.4: GIN
· FAST SCAN rare & frequent ( ) · 9.3: Time (frequent & rare) ~ Time(frequent) · 9.4: Time(frequent & rare) ~ Time(rare) !!! · 9.4: GIN jsonb: keys values
Key 'tags' - 1138532, value '{{term=>NYC}}' - 285 10X !


9.4:
· Add recovery_target='immediate' opton
· recovery consistent ( xlogs). · standalone ( )

· Add a pg_lsn data type, to represent an LSN
· LSN - Log Sequence Number, 64-bit int, WAL · (=, !=, <, >, <=, >=, -)
SELECT (pg_current_xlog_location())::pg_lsn; pg_current_xlog_location -------------------------4/44123DE0 (1 row)


9.4:
· Add pg_stat_archiver statstcs view
· WAL
\d pg_stat_archiver View "pg_catalog.pg_stat_archiver" Column | Type | Modifiers --------------------+--------------------------+----------archived_count | bigint | last_archived_wal | text | last_archived_time | timestamp with time zone | failed_count | bigint | last_failed_wal | text | last_failed_time | timestamp with time zone | stats_reset | timestamp with time zone |


9.4:
· Multple-argument UNNEST ( WITH ORDINALITY)
=# SELECT * FROM unnest( array['a', 'b', 'c'], array['d', 'e', 'f'] ) as t(a,b); a|b ---+--a|d b|e c|f (3 rows) =# SELECT * FROM array['a', array['d', ) WITH ORDINALITY a|b|i ---+---+--a|d|1 b|e|2 c|f|3 (3 rows) unnest( 'b', 'c'], 'e', 'f'] as t(a,b,i);


9.4:
· FILTER aggregates ( case-then-null :)
SELECT i, count(*), count(*) FILTER (WHERE i>0) FROM t1 GROUP BY i; i | count | count ----+-------+------1| 1| 1 -1 | 1| 0 5| 1| 1 (3 rows)


9.4:
· Improved EXPLAIN
· Group Key Agg Group nodes · ( )
EXPLAIN ANALYZE SELECT * FROM t1 GROUP BY i; QUERY PLAN -----------------------------------------------------------HashAggregate (cost=40.00..42.00 rows=200 width=4) (actual time=0.013..0.013 rows=3 loops=1) Group Key: i -> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4) (actual time=0.005..0.007 rows=3 loops=1) Planning time: 0.023 ms ­ prepared queries Execution runtime: 0.042 ms (5 rows)


9.4:
· Show exact/lossy pages in EXPLAIN ANALYZE for a bitmap heap scan

SET work_mem = '64kB'; SET Time: 0.123 ms postgres=# EXPLAIN ANALYZE SELECT * FROM aa WHERE a BETWEEN 100000 AND 200000; QUERY PLAN -------------------------------------------------------------------------------------------------Bitmap Heap Scan on aa (cost=1064.93..48166.70 rows=50000 width=4) (actual time=17.281..1087.696 Recheck Cond: ((a >= 100000) AND (a <= 200000)) Rows Removed by Index Recheck: 8797633 Heap Blocks: exact=404 lossy=39367 -> Bitmap Index Scan on aai (cost=0.00..1052.43 rows=50000 width=0) (actual time=17.147..17.1 Index Cond: ((a >= 100000) AND (a <= 200000)) Planning time: 0.178 ms Execution runtime: 1093.238 ms (8 rows)


9.4:
· Show exact/lossy pages in EXPLAIN ANALYZE for a bitmap heap scan

SET work_mem = '4MB'; SET Time: 0.133 ms postgres=# EXPLAIN ANALYZE SELECT * FROM aa WHERE a BETWEEN 100000 AND 200000; QUERY PLAN -------------------------------------------------------------------------------------------------Bitmap Heap Scan on aa (cost=2009.76..47676.28 rows=94568 width=4) (actual time=46.602..330.244 Recheck Cond: ((a >= 100000) AND (a <= 200000)) Heap Blocks: exact=39771 -> Bitmap Index Scan on aai (cost=0.00..1986.12 rows=94568 width=0) (actual time=29.071..29.0 Index Cond: ((a >= 100000) AND (a <= 200000)) Planning time: 0.236 ms Execution runtime: 344.044 ms (7 rows)


9.4:
· Tablespace ( CREATE, ALTER)
CREATE TABLESPACE ssd LOCATION '/ssd' WITH (random_page_cost = 1.1);

· Tablespace
ALTER MOVE ALTER MOVE TABLESPACE pg_default INDEXES TO ssd; TABLESPACE ssd ALL TO pg_default;


9.4:
· « » - contrib/pg_prewarm
· () · : fnd $PGDATA/base -type f -exec cat {} + · SQL,
pg_prewarm(regclass, mode text default 'buffer', fork text default 'main', first_block int8 default null, last_block int8 default null) RETURNS int8 select pg_prewarm('jb'); pg_prewarm -----------175856 (1 row) 'prefetch'- async., OS cache 'read' - sync., OS cache 'buffer' - shared buffers


9.4:
· ALTER SYSTEM SET - «» postgresql.conf SQL
· postgresql.auto.conf · select pg_reload_conf(); · : -- SQL

· autovacuum_work_mem
· maintenance_work_mem · -1 ( maintenance_work_mem) · maintenance_work_mem , Autovacuum ( )


9.4:
· wal_log_hints for consistent relaton page tracking in WAL
· hint bits , pg_clog (pg_subtrans). , , pg_rewind ( · 9.3: checksums, overhead initdb. · 9.4: wal_log_hints = on

· Triggers on foreign tables
· , , ( ).


9.4:
· in-core GiST index opclass for cidr/inet data types
· This operator class can accelerate subnet/supernet tests as well as btree-equivalent ordered comparisons. It also handles a new network operator inet && inet (overlaps, a/k/a "is supernet or subnet of"), which is expected to be useful in exclusion constraints
CREATE SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT INDEX inet_idx2 * FROM inet_tbl * FROM inet_tbl * FROM inet_tbl * FROM inet_tbl * FROM inet_tbl * FROM inet_tbl * FROM inet_tbl * FROM inet_tbl * FROM inet_tbl * FROM inet_tbl * FROM inet_tbl ON inet_tbl WHERE i << WHERE i <<= WHERE i && WHERE i >>= WHERE i >> WHERE i < WHERE i <= WHERE i = WHERE i >= WHERE i > WHERE i <> using gist (i inet_ops); '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER '192.168.1.0/24'::cidr ORDER BY BY BY BY BY BY BY BY BY BY BY i; i; i; i; i; i; i; i; i; i; i;


9.4:
· Moving-aggregate support for a bunch of numerical aggregates
· First installment of the promised moving-aggregate support in built-in aggregates: count(), sum(), avg(), stddev() and variance() for assorted datatypes, though not for foat4/foat8. Depesz
=# SELECT SUM(n::int) OVER (ORDER BY n ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM generate_series(1,20000) g(n); COUNT(*) sum 1,2,3,4,5,6,7,8,9,10 10 ----------------------------| 200010000 1,2,3,4,5,6,7,8,9 9 : 9.3 ............. ----------------| 39999 10-1 | 9 : 9.4 20000 (20000 rows) . CREATE AGGREGATE Time: 22185.316 ms 9.4: Time: 21.975 ms (1000X !)


9.4: UPSERT ?
· Peter Geoghegan, Heroku. «UPSERT Why UPSERT is weird Counterintuitve lessons learned from the implementaton efort»,
htp://www.pgcon.org /2014/schedule/events/661.en.html Talk that examines implementaton process on the INSERT...ON DUPLICATE KEY LOCK FOR UPDATE feature proposed for PostgreSQL. · , 9.5 (2015)


9.4 !
Waitng for 9.4 by Hubert Depesz Postgres 9.4 feature highlight by Michael Paquier Postgres Weekly Planet PostgreSQL pgsql-commiters mailing list pgsql-hackers mailing list


! !