What's new in PostgreSQL 9.4

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Other new features: Ordered-set aggregates)
Line 1: Line 1:
This page contains an overview of PostgreSQL Version 9.3's features, including descriptions, testing and usage information, and links to blog posts containing further information. See also [[PostgreSQL 9.4 Open Items]].
This page contains an overview of PostgreSQL Version 9.4's features, including descriptions, testing and usage information, and links to blog posts containing further information. See also [[PostgreSQL 9.4 Open Items]].
=Major new features=
=Major new features=

Revision as of 08:56, 6 March 2014

This page contains an overview of PostgreSQL Version 9.4's features, including descriptions, testing and usage information, and links to blog posts containing further information. See also PostgreSQL 9.4 Open Items.


Major new features

Replication improvements

Replication slots

TODO: Blog posts, redundancy of wal_keep_segments et al.

Replication slots allow standbys to provide information to the primary or upstream cascading standby as to the point they've reached in the write-ahead log. This information is available to the primary even when the standby is offline or disconnected. This eliminates the need for wal_keep_segments, which required the admin to estimate how many extra WAL files would be needed to be kept around in order to ensure supply to the standby in case of excessive replication lag.

Here is an example:

On the primary server, set max_replication_slots to allow the standby to register to a slot:

 max_replication_slots = 1

The wal_level will also need to be set to at least "archive", but as this example will be using a hot standby, we'll set it to "hot_standby":

 wal_level = hot_standby

Restart the primary for these settings to take effect, then connect to the primary and create a replication slot:

 SELECT * FROM pg_create_physical_replication_slot('standby_replication_slot');

A standby can then use this replication slot by specifying it as the primary_slotname parameter in its recovery.conf:

 primary_slotname = 'standby_replication_slot'

On the primary, you can see this being used:

 postgres=# SELECT * FROM pg_replication_slots;
        slot_name       | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn 
  standby_physical_slot |        | physical  |        |          | t      |      |              | 0/3000420
 (1 row)

Now the primary will keep hold of WAL for as along as it needs to, which is until all standbys have reported that they have progressed beyond that point. Note, however, that if a standby goes offline, and the replication slow isn't dropped, WAL will build up on the primary. So if a standby needs to be decommissioned, it's slot should then be dropped like so:

 # SELECT pg_drop_replication_slot('standby_replication_slot');
 (1 row)

Otherwise, ensure there is enough disk capacity available to keep WAL on the primary until the standby returns.

In future logical replication will also take advantage of replication slots.

Logical decoding

TODO: Blog posts, explanation of what it's the groundwork for

Performance improvements

GIN Index's now faster and smaller

TODO: Blog posts, benchmark results


When a database instance is restarted, its shared buffers are empty again, which means all queries will initially have to read data in direct from disk. pg_prewarm can load relation data back into the buffers to "warm" the buffers back up again. This will mean that queries, that would otherwise have to load parts of a table in bit by bit, can have the data available in shared buffers ready for them.

TODO: Example

Other new features


Normally, cluster-wide settings need to be edited in the postgresql.conf, but now changes can be made via the ALTER SYSTEM command.


 postgres=# ALTER SYSTEM SET log_min_duration_statement = '5s';

This doesn't actually change postgresql.conf. Instead it writes the setting to a file called postgresql.auto.conf. This file *always* gets read last, so it will always override any settings in postgresql.conf. Setting it to DEFAULT removes the line from postgresql.auto.conf:

 postgres=# ALTER SYSTEM SET log_min_duration_statement = DEFAULT;

An advantage of making changes this way is that the settings are more likely to be correct as they are validated before they are added:

 postgres=# ALTER SYSTEM SET wal_level = 'like the legend of the phoenix';
 ERROR:  invalid value for parameter "wal_level": "like the legend of the phoenix"
 HINT:  Available values: minimal, archive, hot_standby, logical.

If this had been set in postgresql.conf instead, and someone attempted to restart the cluster, it would fail. Naturally postgresql.auto.conf should never be edited manually.


Prior to PostgreSQL 9.4, refreshing a materialized view meant locking the entire table, and therefore preventing anything querying it, and if a refresh took a long time to acquire the exclusive lock (while it waits for queries using it to finish), it in turn is holding up subsequent queries. This can now been mitigated with the CONCURRENTLY keyword:


A unique index will need to exist on the materialized view though. Instead of locking the materialized view up, it instead creates a temporary updated version of it, compares the two versions, then applies INSERTs and DELETEs against the materialized view to apply the difference. This means queries can still use the materialized view while it's being updated.

Unlike its non-concurrent form, tuples aren't frozen, and it needs VACUUMing due to the aforementioned DELETEs that will leave dead tuples behind.


Set-returning functions are most frequently used in the FROM clause of a query, and now if it's suffixed with WITH ORDINALITY, a column containing an ordered sequence of numbers is added.

For example:

 postgres=# SELECT * FROM json_object_keys('{"mobile": 4234234232, "email": "x@me.com", "address": "1 Street Lane"}'::json) WITH ORDINALITY;
  json_object_keys | ordinality 
  mobile           |          1
  email            |          2
  address          |          3
 (3 rows)

Ordered-set aggregates

There is now support for ordered-set aggregates which provide order-sensitive information on columns. One example is the mode average:

 postgres=# SELECT mode() WITHIN GROUP (ORDER BY eye_colour) FROM population;
 (1 row)

Or get the value of a column the specified percentage in, such as 20%

 postgres=# SELECT percentile_disc(0.2) WITHIN GROUP (ORDER BY age) FROM population;
 (1 row)

Backwards compatibility

Personal tools