What's New in PostgreSQL 9.6

What's New in PostgreSQL 9.6

• 1.
  Major Features: Postgres9.6 BRUCE MOMJIAN POSTGRESQL is an open-source, full-featured relational database. This presentation gives an overview of the Postgres 9.6 release. Creative Commons Attribution License http://momjian.us/presentations Last updated: September, 2016 1 / 20
• 2.
  PostgreSQL the database… OpenSource Object Relational DBMS since 1996 Distributed under the PostgreSQL License Similar technical heritage as Oracle, SQL Server & DB2 However, a strong adherence to standards (ANSI-SQL 2008) Highly extensible and adaptable design Languages, indexing, data types, etc. E.g. PostGIS, JSONB, SQL/MED Extensive use throughout the world for applications and organizations of all types Bundled into Red Hat Enterprise Linux, Ubuntu, CentOS and Amazon Linux 2 / 20
• 3.
  PostgreSQL the community… Independentcommunity led by a Core Team of six Large, active and vibrant community www.postgresql.org Downloads, Mailing lists, Documentation Sponsors sampler: Google, Red Hat, VMWare, Skype, Salesforce, HP and EnterpriseDB http://www.postgresql.org/community/ 3 / 20
• 4.
  EnterpriseDB the company… Leadingworldwide provider of Postgres software and services More than 3,500 enterprises, governments, and other organizations worldwide EDB Postgres Platform with: PostgreSQL and EDB Postgres Advanced Server including additional enterprise functionaility Tool Suites for Management, Integration, and Migration, including High Availability and Disaster Recovery Professional Services, 24/7 global support, and Remote DBA Training and Certification Citizenship Contributor of key features: Materialized Views, JSON, & more Nine community members on staff 4 / 20
• 5.
  EnterpriseDB the company… 5/ 20
• 6.
  EnterpriseDB Is aLeader The Gartner report, Magic Quadrant for Operational Database Management Systems, by Donald Feinberg, Merv Adrian, Nick Heudecker, Adam Ronthal, and Terilyn Palanca was published October 12, 2015. 6 / 20
• 7.
  9.6 Feature Outline 1.Parallel execution of sequential scans, joins and aggregates 2. Avoid scanning pages unnecessarily during vacuum freeze operations 3. Synchronous replication now allows multiple standby servers for increased reliability 4. Full-text search can now search for phrases (multiple adjacent words) 5. postgres_fdw now supports remote joins, sorts, UPDATEs, and DELETEs 6. Substantial performance improvements, especially in the area of scalability on multi-CPU-socket servers 7. Allow limiting of snapshot age 8. New monitoring capabilities 9. Allow long-idle transactions to be cancelled Full item list at http://www.postgresql.org/docs/devel/static/ release-9-6.html 7 / 20
• 8.
  1. Parallel Executionof Sequential Scans, Joins and Aggregates CREATE TABLE partest (x INTEGER); INSERT INTO partest SELECT * FROM generate_series(1, 2000000); EXPLAIN SELECT count(*) FROM partest; QUERY PLAN ----------------------------------------------------------------------- Aggregate (cost=37059.38..37059.39 rows=1 width=8) -> Seq Scan on partest (cost=0.00..31417.50 rows=2256750 width=0) 8 / 20
• 9.
  Parallel Aggregate andSequential Scan SET max_parallel_workers_per_gather = 8; EXPLAIN SELECT count(*) FROM partest; QUERY PLAN ----------------------------------------------------------------- Finalize Aggregate (cost=21604.12..21604.13 rows=1 width=8) -> Gather (cost=21603.90..21604.11 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=20603.90..20603.91 row… -> Parallel Seq Scan on partest (cost=0.00..… 9 / 20
• 10.
  2. Avoid ScanningPages Unnecessarily During Vacuum Freeze Operations Freezing of tables is occasionally necessary to guarantee safe transaction id wraparound Usually performed by autovacuum Previously it scanned all heap pages Now, only pages modified since the last freeze are scanned Great benefit for rarely-written tables 10 / 20
• 11.
  3. Synchronous ReplicationNow Allows Multiple Standby Servers for Increased Reliability synchronous_standby_names controls which standby servers the primary waits for to confirm commit Previously, if multiple were specified, only the first connected standby was waited for Now, you can specify the number of connected standbys to wait for, e.g. 2 (standby1, standby2, standby3) 11 / 20
• 12.
  4. Full-text SearchCan Now Search for Phrases (Multiple Adjacent Words) You can now search for words positioned relative to other words ’ice <-> cream’ matches strings with ’ice’ and ’cream’ adjacent and in order ’mutually <2> destruction’ matches a word in between phraseto_tsquery() creates a tsquery with <-> between each supplied word 12 / 20
• 13.
  5. postgres_fdw NowSupports Remote Joins, Sorts, UPDATEs, and DELETEs SQL Queries Join, Sort PG FDW UPDATE, DELETE Foreign Server Foreign Server Foreign Server 13 / 20
• 14.
  6. Substantial PerformanceImprovements, Especially in the Area of Scalability on Multi-CPU-Socket Servers Sorting Locking, especially for shared buffers Checkpoints Aggregates File growth Process title updates on Windows 14 / 20
• 15.
  7. Allow Limitingof Snapshot Age Seession 1 Session 2 SHOW old_snapshot_threshold; 1min CREATE TABLE snaptest (x int); INSERT INTO snaptest VALUES (1); BEGIN WORK; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM snaptest; 1 UPDATE SNAPTEST SET x = 2; SELECT pg_sleep(300); VACUUM snaptest; SELECT * FROM snaptest; ERROR: snapshot too old 15 / 20
• 16.
  8. New MonitoringCapabilities pg_stat_activity wait-type reporting Vacuum progress reporting pg_config system view pg_control values exposed New system view to monitor WAL receiver status Notification queue monitoring 16 / 20
• 17.
  9. Allow Long-idleTransactions To Be Cancelled SET idle_in_transaction_session_timeout = ’2s’; BEGIN WORK; -- sit idle for 3 seconds SELECT 1; FATAL: terminating connection due to idle-in-transaction timeout server closed the connection unexpectedly 17 / 20
• 18.
  Possible 10 Features Additionalparallelism FDW enhancements for sharding Partitioning syntax Built-in logical replication HOT improvements Multivariate statistics Client-side failover 18 / 20
• 19.
  Additional Resources… Postgres Downloads: www.enterprisedb.com/downloads Productand Services information: info@enterprisedb.com 19 / 20
• 20.
  Conclusion http://momjian.us/presentations https://www.flickr.com/photos/thevlue/ 20 /20