New Community Version of PostgreSQL 9.6 Boosts Performance with Parallel Query and Vertical and Horizontal Scalability Features

Date -2016-09-29 Location - BEDFORD, Mass. “Members of EDB’s staff worked as part of the PostgreSQL Community to develop performance advances for PostgreSQL 9.6 that will extract greater efficiency from today’s hardware for larger and more complex workloads,” said Marc Linster, Ph.D., Senior Vice President, Product Development. “Based on contributions from EDB and the community at large, PostgreSQL 9.6 will benefit EDB customers that require parallel processing.”

Advances in PostgreSQL 9.6 move the database deeper into analytics, addressing long-time bottlenecks that have made it difficult to run PostgreSQL on larger data sets. Enhancements to the freeze map, for example, allow PostgreSQL to scale up further than ever before, while new parallel query facilities make it possible to use multiple CPU cores to accelerate response times for queries that touch a lot of data. Expanded replication capabilities make it possible to build a truly reliable multi-node read scaling cluster based on PostgreSQL. Full-text searching enhancements help make PostgreSQL more effective with document stores and now supports the ability to search for words based on proximity, which helps lexical and forensic applications.

Scalability Enhancements

Scalability enhancements to PostgreSQL 9.6 that improve performance include:

• Parallel sequential scans, joins, and aggregates: These features improve query performance on today’s multi-core servers by enabling PostgreSQL to utilize multiple cores in a server. This improves query performance on today’s increasingly powerful hardware, particularly for CPU-intensive queries that benefit from using all cores on a server. These vertical scalability, or scale up, features are an important advance for PostgreSQL and will support the development of more advanced parallel processing capabilities in later releases.
• Enhancement to Freeze Map using autovacuum: PostgreSQL’s Freeze Map features track data blocks that do not require vacuuming, thereby avoiding unnecessary multiple scans of data. This scale up enhancement saves I/O overhead and results in more efficient use of hardware for greater overall performance and scalability.
• Support for Remote Joins, Sorts, and Updates in postgres_fdw: The Foreign Data Wrapper (FDW) feature in PostgreSQL that links the database to external data sources, has been enhanced to increase query performance when it is used to connect multiple Postgres databases. postgres_fdw can now “push down” some query logic to remote servers to better distribute workloads, and use the foreign database server to perform the query operations. With this predicate push down of sorts, joins, and batch data updates, users can distribute workloads across multiple servers, and expand the database’s horizontal, or scale out, capacity. 
• Synchronous replication enhancements: Synchronous replication now allows multiple synchronous standbys that have the option to wait for transactions to be applied (rather than received). This allows better consistency across database clusters and expands horizontal scalability for read-only transactions to identical replicas. This also improves disaster recovery architectures.  

DBA Features

PostgreSQL 9.6 also includes several enhancements that assist developers and database administrators:

• Full text search for phrases: This feature improves the text search capabilities of PostgreSQL and makes it a better alternative to dedicated text search technologies that some companies use to search the web and mine data.
• Exposed wait states: An improved pg_state_activity system view more effectively supports troubleshooting and helps DBAs diagnose problems with queries and resolve issues.

EDB Contributions

EDB engineers committed over 10,000 hours of development time to the complete rewrite of pgAdmin, the leading graphical open source management, development, and administration tool for PostgreSQL. Working under Dave Page, Vice President, Chief Architect, Tools and Installers at EDB, who is also head of the pgAdmin open source project, EDB engineers rewrote pgAdmin in Python and Javascript/jQuery. The tool can now be run as a standalone on the desktop for a single user or deployed on a web server to support multiple users. For details on changes in pgAdmin 4 and the team that was involved, please read the blog by Dave Page, The Elephant Nears the Finish Line. 

EDB is bundling pgAdmin 4 with PostgreSQL 9.6 in its one-click installers of the database, which can be found here.  

EDB is honored to have made code contributions to some of the major features in the PostgreSQL 9.6 release. EDB also partners with other companies that work with and use PostgreSQL to drive continual improvement of PostgreSQL, the world’s most advanced open source database.

Availability

PostgreSQL 9.6 is now available. To download the free version of PostgreSQL using an easy one-click installer, click here to download, or visit the PostgreSQL community download page here. To leverage the full value of PostgreSQL for enterprise deployments, the database also is available as part of the EDB Postgres Standard subscription.  To get started with the EDB Postgres™ Platform, download EDB Postgres Advanced Server here. For more information, contact sales@enterprisedb.com.

To learn more about Postgres, attend Postgres Vision 2016, the preeminent event for thought leadership, collaboration, and networking with the best and brightest companies defining the future of enterprise Postgres and open source data management. This unique event includes insights from technology and business luminaries, Fortune 500 use cases, a look at the Postgres ecosystem, and long-range outlooks from press and analysts such as Gartner and IDC. Featured sponsors include Amazon Web Services (AWS), IBM, Infor, Pivotal, Google, Red Hat, Carahsoft, Avnet, Ashnik, and Shadow-Soft.