PostgreSQL 9.6 new features

Published On - September 15, 2017

anjana

POSTGRESQL is an open-source object-relational database system. It is not controlled by any corporation or other private entity. The source code is available free of charge. PostgreSQL supports transactions, subselects, triggers, views, foreign key referential integrity, and sophisticated locking. New features in Postgres are:

  • Parallel execution of sequential scans, joins and aggregates.
  • Avoid scanning pages unnecessarily during vacuum freeze operations.
  • Synchronous replication now allows multiple standby servers for increased reliability.
  • Full-text search can now search for phrases (multiple adjacent words).
  • postgres_fdw now supports remote joins, sorts, UPDATEs, and DELETEs.
  • Substantial performance improvements, especially in the area of scalability on multi-CPU-socket servers.

Parallel execution of sequential scans, joins and aggregates

PostgreSQL can devise query plans which can leverage multiple CPUs in order to answer queries faster. This feature is known as the parallel query. Mostly, queries that touch a large amount of data but return only a few rows to the user will get benefit by using Parallel Query. It can now execute a full table scan in multiple parallel processes, up to the limits set by the user.

Avoid scanning pages unnecessarily during vacuum freeze operations

Freezing of table is sometime necessary to guarantee safe transaction id wraparound. Previously it scanned all heap pages but now it will scan the pages modified only from the last seen. It is very helpful in cases of rarely written tables.

Synchronous replication now allows multiple standby servers for increased reliability

Two new options have been added to PostgreSQL’s synchronous replication feature allows 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. These settings control the behavior of the built-in streaming replication feature. Servers will be either a Master or a Standby server. Masters can send data, while Standby(s) are always receivers of replicated data. When cascading replication is used, Standby server(s) can also be senders, optimized as well as receivers. parameters are mainly for sending and Standby servers, though some parameters have to mean only on the Master server. Settings may vary across the cluster without problems if that is required.

Full-text search can now search for phrases (multiple adjacent words)

Full Text Searching (or just text search) provides the capability to identify natural-language documents that satisfy a query, and optionally to sort them by relevance to the query. Improve full-text search to support searching for phrases, that is, lexemes appearing adjacent to each other in a specific order, or with a specified distance between them. A phrase-search query can be specified in tsquery input using the new operators <-> and <N>. The former means that the lexemes before and after it must appear adjacent to each other in that order. The latter means they must be exactly N lexemes apart.

postgres_fdw now supports remote joins, sorts, UPDATEs, and DELETEs

The PostgreSQL-to-PostgreSQL data federation river, 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. To prepare for remote access using postgres_fdw:
  1. Install the postgres_fdw extension using CREATE EXTENSION.
  2. Create a foreign server object, using CREATE SERVER, to represent each remote database you want to connect to. Specify connection information, except user and password, as options of the server object.
  3. Create a user mapping, using CREATE USER MAPPING, for each database user you want to allow to access each foreign server. Specify the remote username and password to use as user and password options of the user mapping.
  4. Create a foreign table, using CREATE FOREIGN TABLE, for each remote table you want to access. The columns of the foreign table must match the referenced remote table. You can, however, use table and/or column names different from the remote table’s, if you specify the correct remote names as options of the foreign table object.

 psql

It is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file.You can activate it by: 
psql mydb
where mydb is the database name.

Backups

Prior to PostgreSQL 9.6, the only way to perform concurrent physical backups was through pg_basebackup, via the streaming replication protocol. Low-level file system copy was only available in an exclusive mode, by calling pg_start_backup(), initiating the copy of data files, then finally calling pg_stop_backup().

References

]]>