Release date: 2016-01-07
Major enhancements in PostgreSQL 9.5 include:
INSERTs that would generate
constraint conflicts to be turned into
UPDATEs or ignored
Create mechanisms for tracking the progress of replication, including methods for identifying the origin of individual changes during logical replication
Add Block Range Indexes (BRIN)
Substantial performance improvements for sorting
Substantial performance improvements for multi-CPU machines
The above items are explained in more detail in the sections below.
Version 9.5 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:
Adjust operator precedence to match the SQL standard (Tom Lane)
The precedence of
<> has been reduced to match that
The precedence of
IS NULL) has been reduced to
be just below these six comparison operators. Also,
multi-keyword operators beginning with
NOT now have the precedence of their
base operator (for example,
BETWEEN now has the same precedence as
BETWEEN) whereas before they
had inconsistent precedence, behaving like
NOT with respect to their left operand
but like their base operator with respect to their right
operand. The new configuration parameter
operator_precedence_warning can be enabled to warn
about queries in which these precedence changes result in
different parsing choices.
Change pg_ctl's default shutdown
fast (Bruce Momjian)
This means the default behavior will be to forcibly cancel existing database sessions, not simply wait for them to exit.
Use assignment cast behavior for data type conversions in PL/pgSQL assignments, rather than converting to and from text (Tom Lane)
This change causes conversions of Booleans to strings
Other type conversions may succeed in more cases than
before; for example, assigning a numeric value
3.9 to an integer variable
will now assign 4 rather than failing. If no
assignment-grade cast is defined for the particular
source and destination types, PL/pgSQL will fall back to its old
I/O conversion behavior.
Allow characters in server command-line options to be escaped with a backslash (Andres Freund)
Formerly, spaces in the options string always
separated options, so there was no way to include a space
in an option value. Including a backslash in an option
value now requires writing
Change the default value of the GSSAPI
include_realm parameter to 1, so
that by default the realm is not removed from a
GSS or SSPI principal name (Stephen
If you previously adjusted
checkpoint_segments, the following
formula will give you an approximately equivalent
max_wal_size = (3 * checkpoint_segments) * 16MB
Note that the default setting for
max_wal_size is much higher than the
used to be, so adjusting it might no longer be
Decommission server configuration parameter
which was deprecated in earlier releases (Andres
While SSL renegotiation is a good idea in theory, it
has caused enough bugs to be considered a net negative in
practice, and it is due to be removed from future
versions of the relevant standards. We have therefore
removed support for it from PostgreSQL. The
ssl_renegotiation_limit parameter still
exists, but cannot be set to anything but zero
(disabled). It's not documented anymore, either.
Remove server configuration parameter
autocommit, which was already deprecated
and non-operational (Tom Lane)
rolcatupdate field, as it had no
usefulness (Adam Brightwell)
pg_stat_replication system view's
sent field is now NULL, not zero,
when it has no valid value (Magnus Hagander)
jsonb array extraction operators to accept
negative subscripts, which count from the end of JSON
arrays (Peter Geoghegan, Andrew Dunstan)
Previously, these operators returned
NULL for negative subscripts.
Below you will find a detailed account of the changes between PostgreSQL 9.5 and the previous major release.
Add Block Range Indexes (BRIN) (Álvaro Herrera)
BRIN indexes store only summary data (such as minimum and maximum values) for ranges of heap blocks. They are therefore very compact and cheap to update; but if the data is naturally clustered, they can still provide substantial speedup of searches.
Allow queries to perform accurate distance filtering of bounding-box-indexed objects (polygons, circles) using GiST indexes (Alexander Korotkov, Heikki Linnakangas)
Previously, to exploit such an index a subquery had to be used to select a large number of rows ordered by bounding-box distance, and the result then had to be filtered further with a more accurate distance calculation.
Allow GiST indexes to perform index-only scans (Anastasia Lubennikova, Heikki Linnakangas, Andreas Karlsson)
Add configuration parameter gin_pending_list_limit to control the size of GIN pending lists (Fujii Masao)
This value can also be set on a per-index basis as
an index storage parameter. Previously the
pending-list size was controlled by work_mem,
which was awkward because appropriate values for
work_mem are often much
too large for this purpose.
Issue a warning during the creation of hash indexes because they are not crash-safe (Bruce Momjian)
Improve the speed of sorting of
numeric fields via “abbreviated” keys (Peter
Geoghegan, Andrew Gierth, Robert Haas)
Extend the infrastructure that allows sorting to
be performed by inlined, non-SQL-callable comparison functions
Improve performance of hash joins (Tomas Vondra, Robert Haas)
Improve concurrency of shared buffer replacement (Robert Haas, Amit Kapila, Andres Freund)
Reduce the number of page locks and pins during index scans (Kevin Grittner)
The primary benefit of this is to allow index vacuums to be blocked less often.
Make per-backend tracking of buffer pins more memory-efficient (Andres Freund)
Improve lock scalability (Andres Freund)
This particularly addresses scalability problems when running on systems with multiple CPU sockets.
Allow the optimizer to remove unnecessary references to left-joined subqueries (David Rowley)
Allow pushdown of query restrictions into subqueries with window functions, where appropriate (David Rowley)
Allow a non-leakproof function to be pushed down into a security barrier view if the function does not receive any view output columns (Dean Rasheed)
Teach the planner to use statistics obtained from
an expression index on a boolean-returning function,
when a matching function call appears in
WHERE (Tom Lane)
basic statistics (null fraction and average column
width) even for columns whose data type lacks an
equality function (Oleksandr Shulgin)
Speed up CRC (cyclic redundancy check) computations and switch to CRC-32C (Abhijit Menon-Sen, Heikki Linnakangas)
Improve bitmap index scan performance (Teodor Sigaev, Tom Lane)
by avoiding unnecessary memory copies (Robert
Increase the number of buffer mapping partitions (Amit Kapila, Andres Freund, Robert Haas)
This improves performance for highly concurrent workloads.
Add per-table autovacuum logging control via new
parameter (Michael Paquier)
Add new configuration parameter cluster_name (Thomas Munro)
This string, typically set in
allows clients to identify the cluster. This name
also appears in the process title of all server
processes, allowing for easier identification of
processes belonging to the same cluster.
Prevent non-superusers from changing log_disconnections on connection startup (Fujii Masao)
Check “Subject Alternative Names” in SSL server certificates, if present (Alexey Klyukin)
When they are present, this replaces checks against the certificate's “Common Name”.
Add system view
pg_stat_ssl to report
information (Magnus Hagander)
Add libpq functions to return SSL information in an implementation-independent way (Heikki Linnakangas)
PQgetssl() can still be used to
functions, it is now considered deprecated because
future versions of libpq might support other
implementations. When possible, use the new functions
PQsslInUse() to obtain SSL information in an
Make libpq honor any OpenSSL thread callbacks (Jan Urbanski)
Previously they were overwritten.
This change allows the allocation of a large
number of WAL
files without keeping them after they are no longer
needed. Therefore the default for
max_wal_size has been set to
1GB, much larger than
the old default for
checkpoint_segments. Also note that
standby servers perform restartpoints to try to limit
their WAL space consumption to
max_wal_size; previously they did
not pay any attention to
The previous OOM control infrastructure
involved compile-time options
LINUX_OOM_ADJ, which are no longer
supported. The new behavior is available in all
Allow recording of transaction commit time stamps when configuration parameter track_commit_timestamp is enabled (Álvaro Herrera, Petr Jelínek)
Time stamp information can be accessed using
local_preload_libraries to be set by
ALTER ROLE SET (Peter Eisentraut,
Allow autovacuum workers to respond to configuration parameter changes during a run (Michael Paquier)
Make configuration parameter debug_assertions read-only (Andres Freund)
This means that assertions can no longer be turned
off if they were enabled at compile time, allowing
for more efficient code optimization. This change
also removes the postgres
Allow setting effective_io_concurrency on systems where it has no effect (Peter Eisentraut)
Add system view
pg_file_settings to show the
contents of the server's configuration files (Sawada
pending_restart to the system
pg_settings to indicate a
change has been made but will not take effect until a
database restart (Peter Eisentraut)
SYSTEM values to be reset with
ALTER SYSTEM RESET (Vik
This command removes the specified setting from
Create mechanisms for tracking the progress of replication, including methods for identifying the origin of individual changes during logical replication (Andres Freund)
This is helpful when implementing replication solutions.
Rework truncation of the multixact commit log to be properly WAL-logged (Andres Freund)
This makes things substantially simpler and more robust.
This replaces the old parameter
Add new archive_mode
always to allow
standbys to always archive received WAL files (Fujii Masao)
Add configuration parameter wal_retrieve_retry_interval to control WAL read retry after failure (Alexey Vasiliev, Michael Paquier)
This is particularly helpful for warm standbys.
Allow compression of full-page images stored in WAL (Rahila Syed, Michael Paquier)
This feature reduces WAL volume, at the cost of more CPU time spent on WAL logging and WAL replay. It is controlled by a new configuration parameter wal_compression, which currently is off by default.
Archive WAL files
during standby promotion (Heikki Linnakangas)
Add configuration parameter log_replication_commands to log replication commands (Fujii Masao)
Report the processes holding replication slots in
The new output column is
primary_conninfo setting to use
INSERTs that would generate
constraint conflicts to be turned into
UPDATEs or ignored (Peter Geoghegan,
Heikki Linnakangas, Andres Freund)
The syntax is
INSERT ... ON
CONFLICT DO NOTHING/UPDATE. This is the Postgres
implementation of the popular
Allow setting multiple target columns in an
the result of a single sub-SELECT (Tom Lane)
This is accomplished using the syntax
UPDATE tab SET (col1, col2, ...) = (SELECT
SKIP LOCKED to skip locked
rows (Thomas Munro)
This does not throw an error for locked rows like
TABLESAMPLE to return a
subset of a table (Petr Jelínek)
This feature supports the SQL-standard table sampling methods. In addition, there are provisions for user-defined table sampling methods.
Suggest possible matches for mistyped column names (Peter Geoghegan, Robert Haas)
Add more details about sort ordering in
output (Marius Timmer, Lukas Kreft, Arne Scheffer)
the number of pages skipped due to pins (Jim Nasby)
properly update the
pg_stat* tuple counters (Alexander
reindex an entire schema using the
SCHEMA option (Sawada Masahiko)
VERBOSE option to
SCHEMA from outputting object names,
VERBOSE is used
Add row-level security control (Craig Ringer, KaiGai Kohei, Adam Brightwell, Dean Rasheed, Stephen Frost)
This feature allows row-by-row control over which
users can add, modify, or even see rows in a table.
This is controlled by new commands
ALTER TABLE ... ENABLE/DISABLE ROW
Allow changing of the WAL logging status of a table after
ALTER TABLE ... SET LOGGED /
UNLOGGED (Fabrízio de Royes Mello)
Add support for
ALTER TABLE ... RENAME CONSTRAINT
Allow some DDL commands to accept
SESSION_USER, meaning the current user
or session user, in place of a specific user name
(Kyotaro Horiguchi, Álvaro Herrera)
Support comments on domain constraints (Álvaro Herrera)
Reduce lock levels of some create and alter trigger and foreign key commands (Simon Riggs, Andreas Karlsson)
LOCK TABLE ... ROW
EXCLUSIVE MODE for those with
INSERT privileges on the target table
Previously this command required
Apply table and domain
CHECK constraints in order by name
The previous ordering was indeterminate.
This allows these per-database settings to be
changed without manually modifying the
pg_database system catalog.
Add support for IMPORT FOREIGN SCHEMA (Ronan Dunklau, Michael Paquier, Tom Lane)
This command allows automatic creation of local foreign tables that match the structure of existing tables on a remote server.
constraints to be placed on foreign tables (Shigeru
Hanada, Etsuro Fujita)
Such constraints are assumed to be enforced on the remote server, and are not enforced locally. However, they are assumed to hold for purposes of query optimization, such as constraint exclusion.
Allow foreign tables to participate in inheritance (Shigeru Hanada, Etsuro Fujita)
To let this work naturally, foreign tables are now
allowed to have check constraints marked as not
valid, and to set storage and
OID characteristics, even though these
operations are effectively no-ops for a foreign
Allow foreign data wrappers and custom scans to implement join pushdown (KaiGai Kohei)
ddl_command_end event trigger is
installed, capture details of DDL activity for it to inspect
This information is available through a
pg_event_trigger_ddl_commands(), or by
inspection of C data structures if that function
doesn't provide enough detail.
Allow event triggers on table rewrites caused by
TABLE (Dimitri Fontaine)
Add columns to the output of
This allows simpler processing of delete operations.
xml data type to accept empty or
all-whitespace content values (Peter Eisentraut)
This is required by the SQL/XML specification.
macaddr input using the format
Disallow non-SQL-standard syntax for
interval with both precision and
field specifications (Bruce Momjian)
Per the standard, such type specifications should be
written as, for example,
MINUTE TO SECOND(2). PostgreSQL formerly allowed this
to be written as
MINUTE TO SECOND, but it must now be written in
the standard way.
Equivalent functions already existed for type
text array, and
integer values to be subtracted
(Dmitry Dolgov, Andrew Dunstan)
|| operator (Dmitry Dolgov, Andrew
numeric values (Plato
ARRAY() to take arrays as
inputs (Ali Akbar, Tom Lane)
distance operator <->
Previously, only a single-byte character was allowed as an escape.
width_bucket() variant that supports any
sortable data type and non-uniform bucket widths (Petr
Add an optional
missing_ok argument to
pg_read_file() and related functions
(Michael Paquier, Heikki Linnakangas)
=> to specify named parameters
in function calls (Pavel Stehule)
could be used. This requires removing the possibility
=> to be a
user-defined operator. Creation of user-defined
=> operators has been
issuing warnings since PostgreSQL 9.0.
Add POSIX-compliant rounding for platforms that use PostgreSQL-supplied rounding functions (Pedro Gimeno Fortea)
Previously, only the specific role owning the target session could perform these operations; now membership in that role is sufficient.
to output the time stamp of the statistics snapshot
This represents the last time the snapshot file was written to the file system.
mxid_age() to compute multi-xid age (Bruce
Improve support for composite types in PL/Python (Ed Behn, Ronan Dunklau)
This allows PL/Python functions to return arrays of composite types.
Reduce lossiness of PL/Python floating-point value conversions (Marko Kreen)
Allow specification of conversion routines between SQL data types and data types of procedural languages (Peter Eisentraut)
This should result in these programs being installed by default in most installations.
Add pg_rewind, which allows re-synchronizing a master server after failback (Heikki Linnakangas)
Allow pg_receivexlog to manage physical replication slots (Michael Paquier)
This is controlled via new
Allow pg_receivexlog to
synchronously flush WAL to storage using new
(Furuya Osamu, Fujii Masao)
Without this, WAL files are fsync'ed only on close.
Allow vacuumdb to vacuum in parallel
In vacuumdb, do not prompt for the same password repeatedly when multiple connections are necessary (Haribabu Kommi, Michael Paquier)
--verbose option to
Make pg_basebackup use a tablespace mapping file when using tar format, to support symbolic links and file paths of 100+ characters in length on MS Windows (Amit Kapila)
Add pg_xlogdump option
--stats to display summary
statistics (Abhijit Menon-Sen)
Allow psql to produce AsciiDoc output (Szymon Guz)
that displays only failed commands to psql's
ECHO variable (Pavel Stehule)
This behavior can also be selected with
Provide separate column, header, and border linestyle control in psql's unicode linestyle (Pavel Stehule)
Single or double lines are supported; the default
Add new option
variables to display the current multiline statement
line number (Sawada Masahiko)
pager_min_lines to control pager
invocation (Andrew Dunstan)
Improve psql line counting used when deciding to invoke the pager (Andrew Dunstan)
psql now fails if
the file specified by an
--log-file switch cannot be written
(Tom Lane, Daniel Vérité)
Previously, it effectively ignored the switch in such cases.
Add psql tab completion when setting the search_path variable (Jeff Janes)
Currently only the first schema can be tab-completed.
Improve psql's tab completion for triggers and rules (Andreas Karlsson)
\? help sections
\? variables shows
options shows the command-line options.
\? commands shows the
meta-commands, which is the traditional output and
remains the default. These help displays can also
be obtained with the command-line option
Show tablespace size in psql's
\db+ (Fabrízio de Royes Mello)
Show data type owners in psql's
\dT+ (Magnus Hagander)
\watch to output
--echo-hidden from echoing
\watch queries, since
that is generally unwanted.
\ef commands honor
ECHO_HIDDEN (Andrew Dunstan)
tab completion for
:variable names (Pavel
Allow tab completion of role names in
\c commands (Ian Barwick)
Allow pg_dump to
share a snapshot taken by another session using
--snapshot (Simon Riggs,
The remote snapshot must have been exported by
logical replication slot creation. This can be used
to share a consistent snapshot across multiple
Support table sizes exceeding 8GB in tar archive format (Tom Lane)
The POSIX standard for tar format does not allow elements of a tar archive to exceed 8GB, but most modern implementations of tar support an extension that does allow it. Use the extension format when necessary, rather than failing.
Make pg_dump always print the server and pg_dump versions (Jing Wang)
Previously, version information was only printed
Remove the long-ignored
--ignore-version option from
Support multiple pg_ctl
-o options, concatenating their
values (Bruce Momjian)
Allow control of pg_ctl's event source logging on MS Windows (MauMau)
This only controls pg_ctl, not the server, which
has separate settings in
If the server's listen address is set to a
wildcard value (
in IPv4 or
:: in IPv6),
connect via the loopback address rather than trying
to use the wildcard address literally (Kondo
This fix primarily affects Windows, since on other platforms pg_ctl will prefer to use a Unix-domain socket.
In connection with this change, the functionality previously provided by the pg_upgrade_support module has been moved into the core server.
Support multiple pg_upgrade
options, concatenating their values (Bruce
Improve database collation comparisons in pg_upgrade (Heikki Linnakangas)
Remove support for upgrading from 8.3 clusters (Bruce Momjian)
Move pgbench from
src/bin (Peter Eisentraut)
Fix calculation of TPS number “excluding connections establishing” (Tatsuo Ishii, Fabien Coelho)
The overhead for connection establishment was miscalculated whenever the number of pgbench threads was less than the number of client connections. Although this is clearly a bug, we won't back-patch it into pre-9.5 branches since it makes TPS numbers not comparable to previous results.
Allow counting of pgbench transactions that take over a specified amount of time (Fabien Coelho)
This is controlled by a new
Allow pgbench to generate Gaussian/exponential
\setrandom (Kondo Mitsumasa, Fabien
\set command to handle
arithmetic expressions containing more than one
operator, and add
(modulo) to the set of operators it supports (Robert
Haas, Fabien Coelho)
Simplify WAL record format (Heikki Linnakangas)
This allows external tools to more easily track what blocks are modified.
Improve the representation of transaction commit and abort WAL records (Andres Freund)
Add atomic memory operations API (Andres Freund)
Allow custom path and scan methods (KaiGai Kohei, Tom Lane)
This allows extensions greater control over the optimizer and executor.
Allow foreign data wrappers to do post-filter locking (Etsuro Fujita)
Foreign tables can now take part in
INSERT ... ON CONFLICT DO NOTHING
queries (Peter Geoghegan, Heikki Linnakangas, Andres
Foreign data wrappers must be modified to handle
INSERT ... ON CONFLICT DO
UPDATE is not supported on foreign tables.
hash_create()'s API for selecting
simple-binary-key hash functions (Teodor Sigaev, Tom
Improve parallel execution infrastructure (Robert Haas, Amit Kapila, Noah Misch, Rushabh Lathia, Jeevan Chalke)
Remove Alpha (CPU) and Tru64 (OS) ports (Andres Freund)
Remove swap-byte-based spinlock implementation for ARMv5 and earlier CPUs (Robert Haas)
ARMv5's weak memory ordering made this locking implementation unsafe. Spinlock support is still possible on newer gcc implementations with atomics support.
Generate an error when excessively long (100+ character) file paths are written to tar files (Peter Eisentraut)
Tar does not support such overly-long paths.
This avoids possible problems with these indexes when different databases of a cluster have different default collations.
Change the spinlock primitives to function as compiler barriers (Robert Haas)
Allow higher-precision time stamp resolution on Windows 8, Windows Server 2012, and later Windows systems (Craig Ringer)
Install shared libraries to
bin in MS
Windows (Peter Eisentraut, Michael
src/test/modules together with
contrib on MSVC builds (Michael
option to be honored by the MSVC build (Michael Paquier)
MSVC contrib builds
Add icons to all MSVC-built binaries and version information to all MS Windows binaries (Noah Misch)
MinGW already had such icons.
Add optional-argument support to the internal
implementation (Michael Paquier, Andres Freund)
This is used by the MSVC build.
Add statistics for minimum, maximum, mean, and standard deviation times to pg_stat_statements (Mitsumasa Kondo, Andrew Dunstan)
Add pgcrypto function
pgp_armor_headers() to extract
PGP armor headers
(Marko Tiikkaja, Heikki Linnakangas)
Allow empty replacement strings in unaccent (Mohammad Alhashash)
This is useful in languages where diacritic signs are represented as separate characters.
Allow multicharacter source strings in unaccent (Tom Lane)
This could be useful in languages where diacritic signs are represented as separate characters. It also allows more complex unaccent dictionaries.
Add information about buffer pins to pg_buffercache display (Andres Freund)
Allow pgstattuple to report
approximate answers with less overhead using
Move dummy_seclabel, test_shm_mq, test_parser, and worker_spi from
src/test/modules (Álvaro Herrera)
These modules are only meant for server testing, so they do not need to be built or installed when packaging PostgreSQL.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.