What's new in PostgreSQL 9.3

From PostgreSQL wiki

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 the Release Notes and PostgreSQL 9.3 Open Items.

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 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.

Data Checksums

It is now possible for PostgreSQL to checksum data pages and report corruption. This is a cluster-wide setting and cannot be applied to individual databases or objects. Also be aware that this facility may incur a noticeable performance penalty. This option must be enabled during initdb and cannot be changed (although there is a new GUC parameter "ignore_checksum_failure" which will force PostgreSQL to continue processing a transaction even if corruption is detected).

JSON: Additional functionality

The JSON datatype and two supporting functions for converting rows and arrays were introduced in PostgreSQL 9.2. With PostgreSQL 9.3, dedicated JSON operators have been introduced and the number of functions expanded to 12, including JSON parsing support. The JSON parser has exposed for use by other modules such as extensions as an API.

Additionally, the hstore extension has gained two JSON-related functions, hstore_to_json(hstore) and hstore_to_json_loose(hstore). The former is used when an hstore value is cast to json.

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 unusual 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.

Trigger Features

Event Triggers

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. They will however be automatically populated by pg_restore (more precisely, pg_dump includes a "REFRESH MATERIALIZED VIEW" statement).

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.

Writeable Foreign Tables

"Foreign Data Wrappers" (FDW) were introduced in PostgreSQL 9.1, providing a way of accessing external data sources from within PostgreSQL using SQL. The original implementation was read-only, but 9.3 will enable write access as well, provided the individual FDW drivers have been updated to support this. At the time of writing, only the PostgreSQL driver has write support.

See below for more information on the PostgreSQL driver and a simple example.

Notice a couple of things: first, JOIN push-down to the remote server isn't implemented yet (wait for 9.4!). Second, we're not getting real estimates for the remote tables. This is fixable, but telling Postgres to query the remote DB for EXPLAIN information:

Streaming-Only Remastering

"Remastering" is the process whereby a replica in a set of replicas becomes the new master for all of the other replicas. For example:

Master M1 is replicating to replicas R1, R2 and R3.

Master M1 needs to be taken down for a hardware upgrade.

The DBA promotes R1 to be the master.

R2 and R3 are reconfigured & restarted, and now replicate from R1

That's remastering in a nutshell. It's even more useful in combination with cascading replication (introduced in 9.2).

In prior versions of PostgreSQL, remastering required using WAL file archiving. Cascading replicas could not switch masters using streaming alone; they would have to be re-cloned. That restriction has now been lifted, allowing remastering from just the stream. This makes it much easier to set up large replication clusters; administrators no longer have to set up an online WAL archive if they don't need one for disaster recovery.

Incidentally, this also makes it possible to set up "cycles" where replication is going in a circle. Whether that's a feature or a bug depends on your perspective.

Fast Failover

Allows replicas to be promoted in less than a second, permitting 99.999% uptime. More details TBD.

Architecture-Independent Streaming

Allows streaming of base backups (using pg_basebackup) and log archiving (using pg_receivexlog) between different OSes and hardware architectures. (Note that you still need the same architecture to restore the backups, but this is useful for example with centralized backup servers)

pg_basebackup conf setup

If you use the -R switch, pg_basebackup will create a simple (streaming-only) recovery.conf file in the newly cloned data directory. This means that you can immediately start the new database server without doing additional editing.

Backward compatibility

These changes may incur regressions in your applications.

CREATE TABLE output

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.