what's new in postgres plus advanced server 9.3
DESCRIPTION
Learn more about EnterpriseDB's Postgres Plus Advanced Server 9.3! Highlights of Postgres Plus Advanced Server 9.3 include: Major Partitioning Enhancements Materialized Views New RPM packages New EDB Failover Manager New capabilities in Postgres Enterprise Manager 4.0TRANSCRIPT
© 2013 EDB All rights reserved 8.1. 1
What’s new in Postgres Plus Advanced Server 9.3?
Craig Silveira
Director, Product management
© 2013 EDB All rights reserved 8.1. 2
Synchronized with and contributing to the PostgreSQL community
Postgres Plus: A Super-Set of Features
© 2013 EDB All rights reserved 8.1. 3
Core PostgreSQL New Features/Improvements
© 2013 EDB All rights reserved 8.1. 4
PostgreSQL 9.3 Performance!• pg_upgrade parallel process enhancements
pg_upgrade performance�seconds vs number of tables�
© 2013 EDB All rights reserved 8.1. 5
PostgreSQL 9.3 Performance!• Optimize Referential Integrity Checks Involving NULLs
− #1 deadlock issue in Postgres − UPDATEs that do not affect columns in foreign key now take new
lock: NO KEY UPDATE lock so it doesn’t block − Foreign key checks use KEY SHARE lock which doesn’t block − Should greatly benefit systems with high concurrency
• New lock parameter − Lock_timeout specifies how long to wait to acquire a lock
© 2013 EDB All rights reserved 8.1. 6
PostgreSQL 9.3 Materialized Views!
• A stored query result
• Can be refreshed ad-hoc
• For results that don't need to be completely up-to-date
• Useful for reporting and warehousing data
• Can be indexed like a regular table
• Won’t thrash shared cache
• Can reduce memory usage of server
• #1 requested feature by Postgres Community
© 2013 EDB All rights reserved 8.1. 7
Materialized View Example!
!!SELECT survey_date, count(survey_result_count) total_surveys,! round(avg(survey_result_count)) average_result,! min(survey_result_count) smallest_sample,! max(survey_result_count) largest_sample!FROM survey_results!GROUP BY survey_date!ORDER BY survey_date ASC;!!
CREATE MATERIALIZED VIEW survey_summary AS!SELECT survey_date, count(survey_result_count) total_surveys,! round(avg(survey_result_count)) average_result,! min(survey_result_count) smallest_sample,! max(survey_result_count) largest_sample!FROM survey_results!GROUP BY survey_date!ORDER BY survey_date ASC;!!SELECT * FROM survey_summary;!!!
CREATE TABLE survey_results (id serial PRIMARY KEY, survey_date date, survey_id int, survey_result_count int);!
Takes 7.5 seconds to execute on test machine on table with 20 million rows: 7552.442 ms
Creating the view takes the same time to execute as the original query.
Executing survey_summary query takes 4.62ms
© 2013 EDB All rights reserved 8.1. 8
PostgreSQL 9.3 Additional Features!• User Defined Background Worker Processes
− Develop own processes using PostgreSQL as workload coordinator
• Writeable Foreign Tables
• Extend large object access to 4TB − The previous limit was 2GB
• Auto updateable views – instead of triggers no longer needed
• Event Triggers − Triggers fire on DDL actions
• Continued development of NoSQL-like functionality − Additional constructor and extractor methods − JSON parsing support − Hstore/JSON casting functions
• LATERAL Joins
© 2013 EDB All rights reserved 8.1. 9
PostgreSQL 9.3 Additional Features con’t!• Enhanced Reliability and Availability
− Fast failover − Ensures sub second failover thus providing ‘carrier grade’ availability
− Streaming re-mastering − Makes reconfiguration of replicas after failover easier
− Data page checksums − Helps identify bad disks that could lead to data corruption
− PG_ISREADY utility − A new CLI utility to test whether a server is ready to accept connections − Ex pg_isready –h localhost –p 5432
− Pg_basebackup generates basic recovery file − Simplifies setup of standby database
− Architecture independent streaming
© 2013 EDB All rights reserved 8.1. 10
PPAS New Features
© 2013 EDB All rights reserved 8.1. 11
Advanced Server 9.3 Oracle Compatibility!• Package Support for:
− DBMS_RANDOM - random number generator − DBMS_SCHEDULER – db job scheduler − DBMS_CRYPTO – data encryption − DBMS_LOCK.sleep − DBMS_MVIEW – materialized view management
• Package Support for: − UTL_HTTP – make url calls and return page contents
− UTL_URL
• Support for Functions: − REGEXP_INSTR − REGEXP_COUNT − REGEXP_SUBSTR
• Custom constructor methods for Objects
• Oracle compatible Materialized Views
© 2013 EDB All rights reserved 8.1. 12
Advanced Server 9.3!
• EDB Loader Enhancements − Perform updates as well as inserts − New bulk loading options – ROWS parameter − Environment variables for control
• RPM distributions for Linux − Easier and more consistent installations and maintenance
• Partitioning improvements − Fast pruning − Ability to address more partitions without performance degradation
© 2013 EDB All rights reserved 8.1. 13
Fast Pruning Enhancement!
© 2013 EDB All rights reserved 8.1. 14
Scale to Larger # of Partitions!
© 2013 EDB All rights reserved 8.1. 15
EDB Failover Manager
© 2013 EDB All rights reserved 8.1. 16
What is Failover Manager?
• A solution to aid in the creation of highly available configurations of Postgres
• Monitors the health of a Postgres HA configuration
• Automates the failover process in the even of a failure
• Used in conjunction with Streaming Replication
© 2013 EDB All rights reserved 8.1. 17
Failover Manager Architecture
Agent Agent
© 2013 EDB All rights reserved 8.1. 18
Failover Manager Features
• Automatic Failover from master to replica node
• Configurable fencing operation − By default uses VIP − Parameter to specify alternative operation
− Ex: reconfigure a load balancer
• Manual failover configuration possible
• Email notifications when cluster status changes • Witness node provides protection against ‘split brain’ scenarios
• User configurable wait times
• Built on PPCD/Jgroups technology − Proven functionality
© 2013 EDB All rights reserved 8.1. 19
Postgres Enterprise Manager 4.0
© 2013 EDB All rights reserved 8.1. 20
• Single management console allows easy visual control
• Works for both PostgreSQL and Postgres Plus
• Graphically start/stop, configure, define and manage storage, security, database objects
MONITOR MANAGE TUNE
Only solution available combining all three tasks into one tool
Postgres Enterprise Manager (PEM)
© 2013 EDB All rights reserved 8.1. 21
Postgres Enterprise Manager 4.0 • New Features
− Tuning Wizard − Logfile Alerting − Advanced Graphics − Auto-discovery of
manageable servers by Agent installer
− User Customizable Dashboards
− Bulk Probe Tuning − Many other usability and
performance enhancements
− SQL/Protect support PEM 4.0 Webinar December 17th 1pm EST Register Now!
© 2013 EDB All rights reserved 8.1. 22
Summary • 9.3 New Features include significant enhancements in:
− Performance − High Availability − Compatibility − Ease of Development
• Be sure to download Postgres Plus Advanced Server 9.3 and give it a try!
http://www.enterprisedb.com/downloads
© 2013 EDB All rights reserved 8.1. 23