What's new in PostgreSQL 9.3

From PostgreSQL wiki

Revision as of 20:56, 8 May 2013 by Kgrittn (Talk | contribs)

Jump to: navigation, search

This page contains additional information about PostgreSQL Version 9.3's features, including descriptions, testing information, and usage information. See also the Release Notes and this page for a list of blog posts explaining some of the new features.


New features

... in no particular order ...

Items marked as "(DONE)" have a basic description + links, but can of course be improved / expanded.

  • Writeable Foreign Tables: write to external databases as well as read from them
  • pgsql_fdw driver for federation of PostgreSQL databases
  • VIEW features
    • Automatically updatable VIEWs (DONE)
    • MATERIALIZED VIEWs declaration (DONE)
    • Recursive view declaration (DONE)
  • Additional JSON constructor and extractor functions
  • Switch to Posix shared memory and mmap(). (DONE)
  • Indexed regular expression search
  • Disk page checksums to detect filesystem failures
  • Replication improvements
    • Streaming-only remastering of replicas
    • Streaming replication protocol is now architecture-independent.
    • Faster promotion of a streaming standby to primary ("Standby promotion is almost instant, allowing 99.999% availability for a replicated cluster.")
  • Performance and locking improvements for Foreign Key locks
  • Parallel pg_dump for faster backups (DONE)
  • Directories for configuration files (DONE)
  • pg_isready database connection checker (DONE)
  • 64-bit Large Object API
  • COPY FREEZE for reduced IO bulk loading
  • User-defined background workers for automating database tasks (DONE)

Configuration directive 'include_dir'

In addition to including separate configuration files via the 'include' directive, postgresql.conf now also provides the 'include_dir' directive which reads all files ending in ".conf" in the specified directory or directories.

Directories can be specified either as an absolute path or relative from the location of the main configuration file. Directories will be read in the order they occur, while files will be read sorted by C locale rules. It is possible for included files to contain their own 'include_dir' directives.


Custom Background Workers

This functionality enables modules to register themselves as "background worker processes", effectively operating as customised server processes. This is a powerful new feature with a wide variety of possible use cases, such as monitoring server activity, performing tasks at pre-defined intervals, customised logging etc.

Background worker processes can attach to PostgreSQL's shared memory area and to connect to databases internally; by linking to libpq they can also connect to the server in the same way as a regular client application. Background worker processes are written in C, and as server processes they have unrestricted access to all data and can potentially impact other server processes, meaning so they represent a potential security / stability risk. Consequently background worker processes should be developed and deployed with appropriate caution.

Providing an example would go beyond the scope of this article; please refer to the blogs linked below, which provide annotated sample code. The PostgreSQL source also contains a sample background worker process in contrib/worker_spi.


Parallel pg_dump for faster backups

The new -j njobs (--jobs='njobs') option enables pg_dump to dump njobs tables simultaneously, reducing the time it takes to dump a database. Example:

 pg_dump -U postgres -j4  -Fd -f /tmp/mydb-dump mydb

This dumps the contents of database "mydb" to the directory "/tmp/mydb-dump" using four simultaneous connections.


  • Parallel dumps can only be in directory format
  • Parellel dumps will place more load on the database, although total dump time should be shorter
  • pg_dump will open njobs + 1 connections to the database, so max_connections should be set appropriately
  • Requesting exclusive locks on database objects while running a parallel dump could cause the dump to fail
  • Parallel dumps from pre-9.2 servers need special attention

An ad-hoc test of this feature on a 4.5GB database (which compresses to around 370MB as a dump) with different values of -j produced following timings:

  • (no -j): 1m3s
  • -j2: 0m28s
  • -j3: 0m24s
  • -j4: 0m24s
  • -j5: 0m25s


'pg_isready' server monitoring tool

pg_isready is a wrapper for PQping created as a standard client application. It accepts a libpq-style connection string and returns one of four exit statuses:

  • 0: server is accepting connections normally
  • 1: server is rejecting connections (for example during startup)
  • 2: server did not response to the connection attempt
  • 3: no connection attempt was made (e.g. due to invalid connection parameters)

Example usage:

barwick@localhost:~$ pg_isready
/tmp:5432 - accepting connections
barwick@localhost:~$ pg_isready --quiet && echo "OK"
barwick@localhost:~$ pg_isready -p5431 -h localhost
localhost:5431 - accepting connections
barwick@localhost:~$ pg_isready -h example.com
example.com:5432 - no response


pgsql_fdw driver for federation of PostgreSQL databases

New PostgreSQL-to-PostgreSQL foreign data wrapper, which allows writes and "pushdown" of some query clauses to the external server.

Switch to Posix shared memory and mmap()

In 9.3, PostgreSQL has switched from using SysV shared memory to using Posix shared memory and mmap for memory management. This allows easier installation and configuration of PostgreSQL, and means that except in usual cases, system parameters such as SHMMAX and SHMALL no longer need to be adjusted. We need users to rigorously test and ensure that no memory management issues have been introduced by the change.


VIEW Features

Materialized Views

Materialized views are a special kind of view which cache the view's output as a physical table, rather than executing the underlying query on every access. Conceptually they are similar to "CREATE TABLE AS", but store the view definition so it can be easily refreshed.

Note that materialized views cannot be auto-refreshed; refreshes are not incremental; and the base table cannot be manipulated. It is currently uncertain whether they will be automatically populated by pg_restore (??? need to confirm this)

Contrived example

Create and populate a table with some arbitrary data:

CREATE TABLE matview_test_table (
INSERT INTO matview_test_table VALUES (
 ((NOW() - '2 days'::INTERVAL) + (generate_series(1,1000) || ' seconds')::INTERVAL)::TIMESTAMPTZ

Create a materialized view which lists the 5 most recent entries:

  SELECT id, ts
    FROM matview_test_table
   LIMIT 5
postgres=# SELECT * from matview_test_view ;
  id  |              ts               
 1000 | 2013-05-06 12:02:10.974711+09
  999 | 2013-05-06 12:02:09.974711+09
  998 | 2013-05-06 12:02:08.974711+09
  997 | 2013-05-06 12:02:07.974711+09
  996 | 2013-05-06 12:02:06.974711+09
(5 rows)

Add more data to the table:

INSERT INTO matview_test_table VALUES (
 ((NOW() - '1 days'::INTERVAL) + (generate_series(1,1000) || ' seconds')::INTERVAL)::TIMESTAMPTZ

View output does not change:

postgres=# SELECT * from matview_test_view ;
  id  |              ts               
 1000 | 2013-05-06 12:02:10.974711+09
  999 | 2013-05-06 12:02:09.974711+09
  998 | 2013-05-06 12:02:08.974711+09
  997 | 2013-05-06 12:02:07.974711+09
  996 | 2013-05-06 12:02:06.974711+09
(5 rows)

Refresh the view to display the latest table entries:

postgres=# REFRESH MATERIALIZED VIEW matview_test_view ;
postgres=# SELECT * from matview_test_view ;
  id  |              ts               
 2001 | 2013-05-07 12:03:10.696626+09
 2000 | 2013-05-07 12:03:09.696626+09
 1999 | 2013-05-07 12:03:08.696626+09
 1998 | 2013-05-07 12:03:07.696626+09
 1997 | 2013-05-07 12:03:06.696626+09
(5 rows)

The links below contain more detailed information and examples.


Recursive View Syntax

The CREATE RECURSIVE VIEW syntax provides a shorthand way of formulating a recursive common table expression (CTE) as a view.

Taking the example from the CTE documentation:

    VALUES (1)
    SELECT n+1 FROM t WHERE n < 100

This can be created as a recursive view as follows:

    VALUES (1)
    SELECT n+1 FROM t WHERE n < 100;


Updatable Views

Simple views can now be updated in the same way as regular tables. The view can only reference one table (or another updatable view) and must not contain more complex operators, join types etc.

If the view has a WHERE condition, UPDATEs and DELETEs on the underlying table will be restricted to those rows it defines. However UPDATEs may change a row so that it is no longer visible in the view, and an INSERT command can potentiall insert rows which do not satisfy the WHERE condition.

More complex views can be made updatable as before using INSTEAD OF triggers or INSTEAD rules.

Simple example using the following table and view:

CREATE TABLE postgres_versions (
 nickname TEXT NOT NULL
INSERT INTO postgres_versions VALUES
 ('8.0', 'Excitable Element'),
 ('8.1', 'Fishy Foreign Key'),
 ('8.2', 'Grumpy Grant'),
 ('8.3', 'Hysterical Hstore'),
 ('8.4', 'Insane Index'),
 ('9.0', 'Jumpy Join'),
 ('9.1', 'Killer Key'),
 ('9.2', 'Laconical Lexer'),
 ('9.3', 'Morose Module');
CREATE VIEW postgres_versions_9 AS
 SELECT version, nickname
   FROM postgres_versions
  WHERE version LIKE '9.%';

postgres=# SELECT * from postgres_versions_9;
 version |    nickname     
 9.0     | Jumpy Join
 9.1     | Killer Key
 9.2     | Laconical Lexer
 9.3     | Morose Module
(4 rows)

postgres=# UPDATE postgres_versions_9 SET nickname='Maniac Master' WHERE version='9.3';
postgres=# SELECT * from postgres_versions_9;
 version |    nickname     
 9.0     | Jumpy Join
 9.1     | Killer Key
 9.2     | Laconical Lexer
 9.3     | Maniac Master
(4 rows)


Writeable Foreign Tables

Foreign data sources can now be written to, as well as read, provided that the FDW driver supports it. As of this writing, the Redis driver supports this, but other drivers are expected to add this enhancement before 9.3 final.

Backward compatibility

These changes may incur regressions in your applications.


CREATE TABLE will no longer output messages about implicit index and sequence creation unless the log level is set to DEBUG1.

Server settings

  • Parameter 'commit_delay' is restricted to superusers only
  • Parameter 'replication_timeout' has been renamed to 'wal_sender_timeout'
  • Parameter 'unix_socket_directory' has been replaced 'unix_socket_directories'
  • In-memory sorts to use their full memory allocation; if work_mem was set on the basis of the pre-9.3 behavior, its value may need to be reviewed.

WAL filenames may end in FF

WAL files will now be written in a continuous stream, rather than skipping the last 16MB segment every 4GB, meaning WAL filenames may end in FF. WAL backup or restore scripts may need to be adapted.

Personal tools