Download as PDF, PPTX
Uploaded bySatoshi Nagayasu
PostgreSQL 9.4 and Beyond @ FOSSASIA 2015 Singapore
This document summarizes Satoshi Nagayasu's presentation on PostgreSQL 9.4 and beyond. Key highlights include: improvements to JSON support with the new JSONB data type and GIN indexes; new aggregation functions and materialized views for analytics; logical decoding for more flexible replication; and infrastructure changes like dynamic background workers and shared memory to support parallelization. Upcoming features like BRIN indexes are also mentioned.
PostgreSQL 9.4 and Beyond @ FOSSASIA 2015 Singapore
- 1. PostgreSQL 9.4 andBeyond JSON, Analytics, and More Uptime Technologies Satoshi Nagayasu @snaga FOSSASIA 2015
- 2. Satoshi Nagayasu • SatoshiNagayasu – Database enthusiast. DBA and Data Steward. – Traveling Asia: Hong Kong, Shenzhen, Beijing, Singapore • Uptime Technologies – Co-founder – Providing consulting services around Database and Platform Technologies. • PostgreSQL – pgstatindex, pageinspect, xlogdump – PostgresForest, Postgres-XC (clusters) – Organizing Japanese Users Group.
- 3. What Iʼm doingon PostgreSQL • Postgres Toolkit – Brand new PostgreSQL DBA tool – Stay informed at uptime.jp/go/pt • Postgres Add-on for Hinemos – One of the most popular system management tools in Japan. – Monitoring, Alerting, Job Management, etc.
- 4. PostgreSQL and Hinemos Numberof sessions Database Size Cache Hit Ratio Number of Written Blocks
- 5. Hacking Hardware RaspberryPi 2 & DE0(FPGA) ZigBee (wireless)
- 6. Thanks to... • MagnusHagander • Michael Paquier • Toshi Harada • Noriyoshi Shinoda • ... and many pg guys!
- 7. Agenda • 9.4 Overview •NoSQL (JSON and GIN Index) • Analytics (Aggregation & Mat.View) • Replication and Beyond (Logical Decoding) • Administration (ALTER SYSTEM) • Infrastructure (For Parallelization) • Beyond 9.4
- 8.
- 9. 9.4 Overview -Status • The first official release. – 9.4 released on 18th December. • The latest stable release – 9.4.1 released on 5th February.
- 10. 9.4 Overview -Statistics • 9.4.0 - compared to 9.3.5 – 3,750 files changed. – 62,960 insertions (+) – 15,935 deletions (-)
- 11. 9.4 Overview -Changes
- 12.
- 13. Categories of Enhancements •NoSQL (JSON and GIN Index) • Analytics (Aggregation & Mat.View) • Replication+ (Logical Decoding) • Administration (ALTER SYSTEM) • Basic Infrastructure (Parallelization)
- 14. NoSQL (JSON and GINIndex)
- 15. NoSQL - JSONB •JSON vs. JSONB
- 16. NoSQL - JSONB •“Binary JSON” – Different from JSON, a text representation – Faster for searching • With JSONB... – No duplicated keys allowed. Last wins. – Key order not preserved. – Can take advantages of GIN Index.
- 17. NoSQL - GINIndex • JSON+btree vs. JSONB+GIN – Btree indexes vs. GIN index http://www.slideshare.net/toshiharada/jpug-studyjsonbdatatype20141011-40103981 Table Index Size Comparison
- 18.
- 19. Analytics - Aggregation •FILTER replaces CASE WHEN.
- 20. Analytics - Aggregation •New Aggregate Functions – percentile_cont() – percentile_disc() – mode() – rank() – dense_rank() – percent_rank() – cume_dist()
- 21. Analytics - Aggregation •Ordered-set aggregates – mode(), most common value in a subset
- 22. Analytics - Aggregation •Ordered-set aggregates – rank(), rank of a value in a subset
- 23. Analytics – Materialized Views •REFRESH MATERIALIZED VIEW CONCURRENTLY myview • Refreshing a MV concurrently (in background) without exclusive lock. • Usability and availability improved.
- 24. Replication and Beyond (LogicalDecoding)
- 25. Replication and Beyond– Logical Decoding • “Logical” representation from replication stream – INSERT/UPDATE/DELETE operations – Can be replayed on different version/platform • pg_recvlogical command – Shows how it works • Replication can be more flexible – BDR (Bi-Directional Rep.), Slony, and more ... – Continuous Backup as well
- 26.
- 27.
- 28. Administration - ALTER SYSTEM •ALTER SYSTEM SET – puts new value in postgresql.auto.conf – pg_reload_conf() reloads them. – postgresql.auto.conf takes priority over postgresql.conf. • ALTER SYSTEM RESET – Remove values from postgresql.auto.conf.
- 29.
- 30. Dynamic Background Workers • In9.3, background workers must start at the postmaster startup. • After 9.4, they can be launched “on-demand” basis. • From parallelization point of view... – It allows to launch multiple background processes to execute child queries in parallel.
- 31. Dynamic Shared Memory •Shared memory can be allocated “on-demand” basis – Cf.) by background workers • Main segment (ex. shared_buffers) still fixed at startup • Also supports lightweight message queue • From parallelization point of view... – It allows to share data and communicate with several bgworker processes.
- 32. My Tiny Favorite (pl/pgsqlstacktrace)
- 33.
- 34. There are manyother enhancements, so please try it asap.
- 35.
- 36. BRIN Index • BlockRange INdex – Holds "summary“ data, instead of raw data. – Reduces index size tremendously. – Also reduces creation/maintenance cost. – Needs extra tuple fetch to get the exact record. 0 50,000 100,000 150,000 200,000 250,000 300,000 Btree BRIN Elapsed time (ms) Index Creation 0 50,000 100,000 150,000 200,000 250,000 300,000 Btree BRIN Number of Blocks Index Size 0 2 4 6 8 10 12 14 16 18 Btree BRIN Elapsed time (ms) Select 1 record https://gist.github.com/snaga/82173bd49749ccf0fa6c
- 38. Commitfest 2015-2 CommitFest isa process to review, fix and commit the submitted patches. • Parallel Seq Scan • INSERT ... ON CONFLICT {UPDATE | IGNORE} • File level incremental backup • and others.. Still work in progress... commitfest.postgresql.org
- 39. Wrap-up • One ofthe most developer-friendly RDBMSes in the world. • Analytics features and the performance are improving. • Things are going to parallel.
- 40.
- 41.
- 42. Thank you! • E-mail:snaga@uptime.jp • Twitter, Github: @snaga • WeChat: satoshinagayasu