![Administration pg_stat_archiver statistics view $ select * from pg_stat_archiver ; -[ RECORD 1 ]------+------------------------------ archived_count | 21 last_archived_wal | 00000001000000000000000E last_archived_time | 2014-04-29 11:37:45.337841+01 failed_count | 12 last_failed_wal | 00000001000000000000000F last_failed_time | 2014-04-29 11:43:24.732469+01 stats_reset | 2014-04-29 11:37:16.995943+01 Thursday, May 22, 14](https://image.slidesharecdn.com/postgres94-firstlook-140522105245-phpapp01/75/Postgres-9-4-First-Look-23-2048.jpg)
![SQL ordered-set aggregates pagila=# SELECT count(*), min(amount), max(amount), percentile_cont(array[0.5,0.9]) WITHIN GROUP (ORDER BY amount) FROM payment; count | min | max | percentile_disc -------+------+-------+----------------- 16049 | 0.00 | 11.99 | {3.99,6.99} (1 row) Thursday, May 22, 14](https://image.slidesharecdn.com/postgres94-firstlook-140522105245-phpapp01/75/Postgres-9-4-First-Look-33-2048.jpg)
Uploaded byRobert Treat
Postgres 9.4 First Look
The document discusses upcoming features in Postgres 9.4 including: - Performance improvements such as reduced WAL size for updates, improved GIN index performance, and ability to separate query planning and execution times. - Administration enhancements like ALTER SYSTEM for configuration changes, improved EXPLAIN output, and a new pg_stat_archiver view. - New SQL functions like MAKE_TIMESTAMP and ordered-set aggregates. - Backend changes like PL/pgSQL stack traces, concurrent REFRESH for materialized views, and the new JSONB binary format. - Future directions include additional performance work and SQL features.
Postgres 9.4 First Look
- 1. / Robert Treat 9.4On The Floor A Talk About Upcoming Features in Postgres 9.4 Thursday, May 22, 14
- 2.
- 3. Hello Postgres 6.x Major Contributor advocacy,tools, web Thursday, May 22, 14
- 4. Hello Postgres 6.x Major Contributor advocacy,tools, web root beer : crocs : shawarma Thursday, May 22, 14
- 5. Hello “for companies whoneed to build scalable and efficient web services, OmniTI is the leading cross-disciplinary provider of consulting, development, and operational resources to help your company grow” 15% of Alexa Top 100 postgres consulting | web development Thursday, May 22, 14
- 6. The Development Cycle Postgres Thursday,May 22, 14
- 7. The Development Cycle •June14th,2013-Branch9.4-devel • June2013-CF1 • September2013-CF2 • November2013-CF3 • January2014-CF4 Thursday, May 22, 14
- 8. The Development Cycle •June14th,2013-Branch9.4-devel • June2013-CF1 • September2013-CF2 • November2013-CF3 • January2014-CF4 • May15th,2014-Postgres9.4Beta1 Thursday, May 22, 14
- 9. The Development Cycle https://wiki.postgresql.org/wiki/HowToBetaTest BetaRelease == Test Please! Thursday, May 22, 14
- 10.
- 11. Performance reduce WAL forupdates Reduce the size of WAL records generated for UPDATE operations Less WAL volume should mean less I/O & resource usage Thursday, May 22, 14
- 12. Performance xlog insert scaling Reducelock contention for WAL insert operations, improving scalability of WAL system Thursday, May 22, 14
- 13. Performance pg_prewarm Contrib module forloading relation data into memory. Can choose from OS cache (async or sync) or shared_buffers. SELECT pg_prewarm('actor'); Thursday, May 22, 14
- 14. Performance GIN Index improvements friday@ 4:15 | GIN stronger than ever compact page format 2x - 10x reduced index size fast scan improvements queries w/ some freq. some rare items Thursday, May 22, 14
- 15. Performance separate planning /exec time in EXPLAIN ANALYZE pagila=#explain analyze select count(*) from payment where payment_date<='2007-02-01'::date; QUERY PLAN -------------------------------------------------------------------------------------------- Aggregate (cost=72.26..72.27 rows=1 width=0) (actual time=11.712..11.712 rows=1 loops=1) -> Append (cost=0.00..69.36 rows=1159 width=0) (actual time=2.256..11.416 rows=1157 loops=1) -> Seq Scan on payment (cost=0.00..0.00 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1) Filter: (payment_date <= '2007-02-01'::date) -> Seq Scan on payment_p2007_01 (cost=0.00..23.46 rows=1157 width=0) (actual time=2.253..5.728 rows=1157 loops=1) Filter: (payment_date <= '2007-02-01'::date) -> Seq Scan on payment_p2007_02 (cost=0.00..45.90 rows=1 width=0) (actual time=5.380..5.380 rows=0 loops=1) Filter: (payment_date <= '2007-02-01'::date) Rows Removed by Filter: 2312 Planning time: 140.555 ms Execution time: 12.562 ms (11 rows) Thursday, May 22, 14
- 16. Performance add “grouping columns”to EXPLAIN output pagila=# explain select postal_code, count(*) pagila-# from address group by postal_code; QUERY PLAN ---------------------------------------------------------------- HashAggregate (cost=17.05..23.02 rows=597 width=5) Group Key: postal_code -> Seq Scan on address (cost=0.00..14.03 rows=603 width=5) Thursday, May 22, 14
- 17. Performance lock wait context LOG:process 11367 still waiting for ShareLock on transaction 717 after 1000.108 ms DETAIL: Process holding the lock: 11366. Wait queue: 11367. CONTEXT: while updating tuple (0,2) in relation "foo" STATEMENT: UPDATE foo SET value = 3; Thursday, May 22, 14
- 18.
- 19. Administration ALTER SYSTEM provide amechanism to alter system level configuration from SQL ALTER SYSTEM SET work_mem='42MB'; SELECT pg_reload_conf(); Thursday, May 22, 14
- 20. Administration pset improvements pagila=# pset Borderstyle (border) is 1. Target width (columns) unset. Expanded display (expanded) is off. Field separator (fieldsep) is "|". Default footer (footer) is on. Output format (format) is aligned. Line style (linestyle) is ascii. Null display (null) is "". Locale-adjusted numeric output (numericlocale) is off. Pager (pager) is used for long output. Record separator (recordsep) is <newline>. Table attributes (tableattr) unset. Title (title) unset. Tuples only (tuples_only) is off. also now works with tab completion Thursday, May 22, 14
- 21. Administration set tablespace optionsat create time CREATE TABLESPACE rmd LOCATION '/ramdisk' WITH (random_page_cost = 0.9); use to require create + alter Thursday, May 22, 14
- 22. Administration TABLESPACE SHUFFLING move objectsfrom one tablespace to another {tables|indexes|all} ALTER TABLESPACE pg_default MOVE INDEXES TO rmd; Thursday, May 22, 14
- 23. Administration pg_stat_archiver statistics view $select * from pg_stat_archiver ; -[ RECORD 1 ]------+------------------------------ archived_count | 21 last_archived_wal | 00000001000000000000000E last_archived_time | 2014-04-29 11:37:45.337841+01 failed_count | 12 last_failed_wal | 00000001000000000000000F last_failed_time | 2014-04-29 11:43:24.732469+01 stats_reset | 2014-04-29 11:37:16.995943+01 Thursday, May 22, 14
- 24. Administration remove kerberos 5authentication deprecated since 8.3 use GSSAPI instead Thursday, May 22, 14
- 25. Administration SSL / TLSimprovements ‣ add TLS v1.1/1.2 support ‣ remove SSL v3 support ‣ change default cipher suite to HIGH:!aNULL ‣ HIGH: Contains only secure and well-research algorithms ‣ !aNULL: Needed to disable suites that do not authenticate server Thursday, May 22, 14
- 26.
- 27.
- 28. SQL make time • make_interval(years,months, weeks, days, hours, mins, secs) • make_timestamp(year, month, day, hour, min, sec) • make_timestamptz(year, month, day, hour, min, sec, timezone) pagila=# select make_interval(hours := 2, mins := 1, secs := 1.2); make_interval --------------- 02:01:01.2 (1 row) Thursday, May 22, 14
- 29. SQL “moving aggregates” ‣ optimizationfor aggregates called within moving window of window function call ‣ initial support for count(), sum(), avg(), stddev(), variance(), and boolean aggregates Thursday, May 22, 14
- 30. SQL ordered-set aggregates ‣ newclass of aggregates ‣“offset in group” ‣WITHIN GROUP ‣SQL 2008 Thursday, May 22, 14
- 31. SQL ordered-set aggregates pagila=# selectamount, count(*) from payment group by amount order by 1; amount | count --------+------- 0.00 | 24 0.99 | 2979 1.98 | 1 1.99 | 640 2.99 | 3542 3.98 | 8 3.99 | 1109 4.99 | 3789 5.98 | 7 5.99 | 1299 6.99 | 1119 7.98 | 5 7.99 | 670 8.97 | 1 8.99 | 485 9.98 | 1 9.99 | 256 10.99 | 104 11.99 | 10 (19 rows) Thursday, May 22, 14
- 32. SQL ordered-set aggregates pagila=# SELECT count(*),min(amount), max(amount), percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) FROM payment; count | min | max | percentile_disc -------+------+-------+----------------- 16049 | 0.00 | 11.99 | 3.99 (1 row) median amount Thursday, May 22, 14
- 33. SQL ordered-set aggregates pagila=# SELECT count(*),min(amount), max(amount), percentile_cont(array[0.5,0.9]) WITHIN GROUP (ORDER BY amount) FROM payment; count | min | max | percentile_disc -------+------+-------+----------------- 16049 | 0.00 | 11.99 | {3.99,6.99} (1 row) Thursday, May 22, 14
- 34. SQL ordered-set aggregates pagila=# SELECT count(*),min(amount),max(amount), mode() within group (order by amount) FROM payment; count | min | max | mode -------+------+-------+------ 16049 | 0.00 | 11.99 | 4.99 (1 row) Thursday, May 22, 14
- 35.
- 36. SQL FILTER aggregates pagila=# SELECTcustomer_id, count(*), count(*) FILTER (WHERE amount > 5) FROM payment WHERE customer_id = 148 GROUP BY customer_id; customer_id | count | count -------------+-------+------- 148 | 46 | 13 (1 row) ‣ add support for FILTER clause in aggregates ‣ no more case-then-null / case Thursday, May 22, 14
- 37.
- 38. Backend plpgsql stack traces CREATEOR REPLACE FUNCTION public.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; Thursday, May 22, 14
- 39. Backend refresh materialized viewsconcurrently REFRESH MATERIALIZED VIEW CONCURRENTLY <view> * requires unique index Thursday, May 22, 14
- 40. Backend “with check” optionfor auto-updateable views ‣ given an updateable view ‣ only allow visible rows within the view ‣ LOCAL ‣ checking conditions only on the view itself ‣ CASCADE ‣ recursively check on parents (default) Thursday, May 22, 14
- 41. Backend jsonb new storage formatfor json text data “binary json” Thursday, May 22, 14
- 42. Backend jsonb new storage formatfor json text data “binary json” pros: ✓no need to re-parse data ✓improved indexing support ✓allows for equality checking Thursday, May 22, 14
- 43. Backend jsonb new storage formatfor json text data “binary json” pros: ✓no need to re-parse data ✓improved indexing support ✓allows for equality checking cons: - whitespace munging - json/jsonb quirkiness - more disk space on small json objects tomorrow afternoon - morissette 256 Thursday, May 22, 14
- 44. Backend jsonb tomorrow afternoon -morissette 256 json jsonb mongodb table size 1322MB 1375MB 1666MB index size 630MB 283MB N/A single key index 79MB 58MB 96MB speed 18.759ms 1.111ms 2ms https://plus.google.com/+ThomBrownUK/posts/1JizRBGPYBq Thursday, May 22, 14
- 45. Backend recovery target “immediate” recoverytarget tells postgres when to launch - target time, target xid, target name, all immediate allows start up as soon as consistent state reached Thursday, May 22, 14
- 46.
- 47. Future dynamic background workers 9.3added background workers available only at startup 9.4 gives you the ability to start background workers dynamically, from SQL Thursday, May 22, 14
- 48. Future dynamic shared memory notdynamic shared_buffers! :-( Thursday, May 22, 14
- 49. Future dynamic shared memory allowsthe server to create new shared memory segments on the fly, for example when requested by a background worker. also adds some message passing infrastructure. not dynamic shared_buffers! :-( Thursday, May 22, 14
- 50. Future extensible TOAST support addedinfrastructure to support external, alternative compression schemes Thursday, May 22, 14
- 51. Future replication improvements wal_level =logical replica identity replication slots logical decoding greatly expands capabilities of 3rd party replication tools with an eye on “logical replication” Friday @ 4:15 | Andres Freund | Changeset Extraction Thursday, May 22, 14
- 52.
- 53. Thanks! Postgres Services Health Audits 24x7Monitoring RemoteDBA Services Postgres Migrations more: @robtreat2 http://xzilla.net @omniti http://omniti.com Thursday, May 22, 14