E.24. Postgres Pro 9.5.0.1
Release date: 2016-02-01
E.24.1. Overview
Major enhancements in Postgres Pro 9.5 over PostgreSQL 9.5 include:
Scalability improvements for multicore/multiprocessor systems.
Covering indexes.
Use portable implementation of the internationalization functions (libicu) instead of system specific.
Speedup of two-phase commit.
Contrib module pg_trgm supports substring searches.
Save-restore execution plan extension.
Dump/restore database statistics.
Some hunspell dictionaries for full-text search are bundled with distribution.
Phrase search support in full text search.
The above items are explained in more detail in the sections below.
E.24.2. Migration to Postgres Pro 9.5
A dump/restore using pg_dumpall, or use of pg_upgrade, is required for those wishing to migrate data from any previous release.
Dump/restore is not necessary when migrating from PostgreSQL 9.5 to Postgres Pro 9.5.
E.24.3. Changes
Below you will find a detailed account of the changes between Postgres Pro 9.5 and PostgreSQL 9.5.
E.24.3.1. Server
E.24.3.1.1. Indexes
Covering indexes. Now CREATE INDEX supports optional INCLUDING clause, which allows to include additional columns, which would not affect sort order or uniqueness, but allow speed up some queries using index only scans.
E.24.3.1.2. General Performance
Scalability improvements for multicore/multiprocessor systems:
Resource owner optimization for complicated queries: now resource lists has constant access time, which improves performance for heavily partitioned tables and queries which use hundreds of tables.
Fix lock contention for HASHHDR.mutex. (Alexander Alekseev). This prevents Postgres from waiting for lock up to several milliseconds when modifying state, shared between processes.
Improve buffer manager concurrency for multicore architectures.
Improve LWLock concurrency for Power8 architecture.
Speedup two phase commit by skipping two phase state files in normal path 2PC state info is written only to WAL at PREPARE, then read back from WAL at COMMIT PREPARED/ABORT PREPARED. Prepared transactions that live past one bufmgr checkpoint cycle will be written to disk in the same form as previously. Crash recovery path is not altered. Measured performance gains of 50-100% for short 2PC transactions by completely avoiding writing files and fsyncing. Other optimizations still available, further patches in related areas expected.
Stas Kelvich and heavily edited by Simon Riggs.
Based upon earlier ideas and patches by Michael Paquier and Heikki Linnakangas, a concrete example of how Postgres-XC has fed back ideas into PostgreSQL.
Reviewed by Michael Paquier, Jeff Janes and Andres Freund Performance testing by Jesper Pedersen.
E.24.3.1.3. Full text search
Phrase search - it is now possible to specify order of words in the text search query.
Improved hunspell dictionaries support. Now it is possible to use hunspell dictionaries with complex affix files, which were not supported in earlier versions.
Some hunspell dictionaries are included in the distribution. See Section E.24.3.3 below.
E.24.3.2. Portability improvements
Use libicu instead of system specific locale handling functions. This ensures same collating order on all platforms Based on patch by Palle Girgensohn http://people.freebsd.org/~girgen/postgresql-icu/README.html, ported to 9.5 and abbreviated sort key support added by Teodor Sigaev.
E.24.3.3. Additional Modules
pageinspect extension now can extract tuple data, not only meta information.
pg_trgm contrib module, which allows to do fuzzy string comparison, now supports substring searches.
sr_plan contrib module, which allows to save execution plan and force use of saved, known-good plan instead of computing new one each time.
dump_stat contrib module. This module allows to dump table statistics and restore it, thus eliminate need to run VACUUM ANALYZE after restoration of base or server upgrade
Several hunspell dictionaries for full-text search are now included as contrib modules and can be easily installed into the database using CREATE EXTENSION command.