[ANNOUNCE] PostgreSQL 9.6 Released!

Questions and answers regarding general SQL and backend databases

[ANNOUNCE] PostgreSQL 9.6 Released!

Postby ROCLASI » Fri Sep 30, 2016 5:36 am

PostgreSQL 9.6 Released!

PostgreSQL 9.6, the latest version of the world's leading open source database, was released today by the PostgreSQL Global Development Group. This release will allow users to both scale up and scale out high performance database workloads. New features include parallel query, synchronous replication improvements, phrase search, and improvements to performance and usability, as well as many more features.

Scale Up with Parallel Query

Version 9.6 adds support for parallelizing some query operations, enabling utilization of several or all of the cores on a server to return query results faster. This release includes parallel sequential (table) scan, aggregation, and joins. Depending on details and available cores, parallelism can speed up big data queries by as much as 32 times faster.

"I migrated our entire genomics data platform - all 25 billion legacy MySQL rows of it - to a single Postgres database, leveraging the row compression abilities of the JSONB datatype, and the excellent GIN, BRIN, and B-tree indexing modes. Now with version 9.6, I expect to harness the parallel query functionality to allow even greater scalability for queries against our rather large tables," said Mike Sofen, Chief Database Architect, Synthetic Genomics.

Scale Out with Synchronous Replication and postgres_fdw

Two new options have been added to PostgreSQL's synchronous replication feature which allow it to be used to maintain consistent reads across database clusters. First, it now allows configuring groups of synchronous replicas. Second, the "remote_apply" mode creates a more consistent view of data across multiple nodes. These features support using built-in replication to maintain a set of "identical" nodes for load-balancing read workloads.

The PostgreSQL-to-PostgreSQL data federation driver, postgres_fdw, has new capabilities to execute work on remote servers. By "pushing down" sorts, joins, and batch data updates, users can distribute workload across multiple PostgreSQL servers. These features should soon be added to other FDW drivers.

"With the capabilities of remote JOIN, UPDATE and DELETE, Foreign Data Wrappers are now a complete solution for sharing data between other databases and PostgreSQL. For example, PostgreSQL can be used to handle data input going to two or more different kinds of databases," said Julyanto Sutandang, Director of Business Solutions at Equnix.

Better Text Search with Phrases

PostgreSQL's full text search feature now supports "phrase search." This lets users search for exact phrases, or for words within a specified proximity to each other, using fast GIN indexes. Combined with new features for fine-tuning text search options, PostgreSQL is the superior option for "hybrid search" which puts together relational, JSON, and full text searching.

Smoother, Faster, and Easier to Use

Thanks to feedback and testing by PostgreSQL users with high-volume production databases, the project has been able to improve many aspects of performance and usability in this release. Replication, aggregation, indexing, sorting, and stored procedures have all been made more efficient, and PostgreSQL now makes better use of resources with recent Linux kernels. Administration overhead for large tables and complex workloads was also reduced, especially through improvements to VACUUM.

Other Features

Version 9.6 has many other features added over the last year of development, among them:

New system views and functions: pg_stat_wal_receiver, pg_visibility, pg_config, pg_blocking_pids, pg_notification_queue_usage
  • Command progress reporting support
  • Cascade support for installing extensions
  • pg_basebackup concurrency and replication slot support
  • Wait Event support
  • View editing and crosstabs in psql
  • User-defined expiration for old snapshots
  • Index-only scans for partial indexes
Additionally, the project has changed and improved the API for binary hot backups. As such, developers of custom backup software for PostgreSQL should do additional testing around the new version. See the Release Notes for more detail.

Links

Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Restoring problems between 9.4 and 9.6

Postby Bernd.N » Sun Oct 23, 2016 7:47 am

I had problems after installing a new pc with Win10 and switching to Postgres 9.6 there.
On my old machine I had Postgres 9.4.1.
So I created a backup of my DBs and tried to restore them on the new machine.
When the restore needed longer than some seconds I already knew something is not working.

The new pgAdmin looks fancy, but it did not stop the restore although there occured an error, and it was even hard to make it tell about the problem that occured:
'utf8' codec can't decode byte 0xbb in position 50: invalid start byte
So I had to kill pg_restore in the task manager.

I tried different backup formats with same result, all with UTF8 coding. I used the normal backup/restore command in pgAdmin in the context menu of the DB.
As I could not google the problem at once in relation to 9.6, I decided to stick with an older postgres version 9.4.9 and there everything was fine again.
One disadvantage of the new pgAdmin in 9.6 is also the very uncomfortable file picker they used, instead of deciding to just stick with a normal system filepicker from the system it works on.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: [ANNOUNCE] PostgreSQL 9.6 Released!

Postby ROCLASI » Mon Oct 24, 2016 2:10 pm

Hi Bernd,

When moving between major versions of PostgreSQL it is only recommended to use a PLAIN TEXT based backup. Which means you get a SQL script instead of a special data backup format.
This SQL script can't be restored using pg_restore however but needs to be applied using a sql editor like the command line psql.

More information you can find here.
https://www.postgresql.org/docs/current ... mpall.html

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: [ANNOUNCE] PostgreSQL 9.6 Released!

Postby Bernd.N » Mon Oct 24, 2016 2:31 pm

Thanks, Robert, I will do so next time. So learnt something new again. :)
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 8 guests