Uploaded bySatoshi Nagayasu
PostgreSQL 9.4
This document summarizes a presentation about new features in PostgreSQL 9.4. It discusses enhancements for NoSQL support with JSON and GIN indexes, analytics with new aggregate functions and materialized views, increased flexibility with logical replication, easier administration using ALTER SYSTEM, and improved infrastructure for parallelization through dynamic background workers and shared memory. The presentation provides an overview of the status of 9.4 and highlights some of the major new categories of features.
PostgreSQL 9.4
- 1. PostgreSQL 9.4 JSON,Analytics, and More Japan PostgreSQL Users Group Satoshi Nagayasu snaga@uptime.jp @pgcon china 2014
- 2. Satoshi Nagayasu •2004 – NTT DATA • 2005 – JPUG PR Director • 2009 – Uptime Technologies • 2010 – JPUG Chairperson • 2013 – Minacare
- 3. Satoshi Nagayasu •Database Engineer • Data Center Engineer • ITSM Specialist • Data Steward • System Architect • Co-founder • CTO
- 4. What I Didin PostgreSQL • pgstatindex • pageinspect • xlogdump – ... and lots of rejected patches! • Patch review • PostgresForest, Postgres-XC – at my prev jobs
- 5. Thanks to... •Magnus Hagander • Michael Paquier • Toshi Harada • Noriyoshi Shinoda • ... and many pg guys!
- 6. Agenda • 9.4Overview • NoSQL (JSON and GIN Index) • Analytics (Aggregation & Mat.View) • Replication and Beyond (Logical Decoding) • Administration (ALTER SYSTEM) • Infrastructure (For Parallelization)
- 7.
- 8. 9.4 Overview -Status • Current Status – RC1 released on 20th November • Officially announced 9.4 to be released on 18th December (the next Thursday)
- 9. 9.4 Overview -Statistics • As of beta2 (by Magnus Hagander) – 2222 files changed – 131,805 insertions (+) – 59,333 deletions(-) • As of RC1 (by Michael Paquier) – 2183 files changed – 374,421 insertions (+) – 209,439 deletions (-)
- 10. 9.4 Overview -Changes
- 11. 9.4 Overview -Changes Server Indexes General Performance Monitoring SSL Server Settings Replication and Recovery Logical Decoding Queries Utility Commands EXPLAIN Views Object Manipulation Data Types JSON Functions System Information Functions Aggregates Server‐Side Languages PL/pgSQL Server‐Side Language libpq Client Applications psql Backslash Commands pg_dump pg_basebackup Source Code Additional Modules pgbench pg_stat_statements
- 12. Categories of Enhancements • NoSQL (JSON and GIN Index) • Analytics (Aggregation & Mat.View) • Replication+ (Logical Decoding) • Administration (ALTER SYSTEM) • Basic Infrastructure (Parallelization)
- 13. NoSQL (JSON andGIN Index)
- 14. NoSQL - JSONB • JSON vs. JSONB
- 15. 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.
- 16. NoSQL - GINIndex • JSON+btree vs. JSONB+GIN – Btree indexes vs. GIN index Table Index Size Comparison http://www.slideshare.net/toshiharada/jpug-studyjsonbdatatype20141011-40103981
- 17. Analytics (Aggregation &Materialized View)
- 18. Analytics - Aggregation • FILTER replaces CASE WHEN.
- 19. Analytics - Aggregation • New Aggregate Functions – percentile_cont() – percentile_disc() – mode() – rank() – dense_rank() – percent_rank() – cume_dist()
- 20. Analytics - Aggregation • Ordered-set aggregates – mode(), most common value in a subset
- 21. Analytics - Aggregation • Ordered-set aggregates – rank(), rank of a value in a subset
- 22. Analytics – Materialized Views • REFRESH MATERIALIZED VIEW CONCURRENTLY myview • Allows refreshing a MV concurrently without taking exclusive lock. • Refreshing a large MV can benefit from CONCURRENTLY in terms of usability.
- 23. Replication and Beyond (Logical Decoding)
- 24. Replication and Beyond– Logical Decoding • “Logical” representation from replication stream – INSERT/UPDATE/DELETE operations – Can be replayed on different version/platform • pg_recvlogical command – It shows how it works • Replication can be more flexible – BDR (Bi-Directional Rep.), Slony, and more ... – Continuous Backup as well
- 25.
- 26.
- 27. 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.
- 28.
- 29. Dynamic Background Workers • In 9.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.
- 30. 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.
- 31. My Tiny Favorite (pl/pgsql stacktrace)
- 32.
- 33. Many other enhancements, so please try it asap.
- 34.
- 35.