PostgreSQL 9.3 begins beta cycle

PostgreSQL 9.3 has begun its testing cycle with the release of its first beta. The new version brings writing support for foreign tables, including those on other PostgreSQL servers, updatable views and the ability to declare a materialised view, new JSON construction and extraction functions, indexed regular-expression-based searches, and new resilience features. Together, the changes place PostgreSQL in the position of being able to be the backbone of many enterprises' data storage and integration systems.

The foreign table support originally appeared in PostgreSQL 9.2 in a read-only form, allowing tables to be joined to other tables in other databases or legacy data storage, using an appropriate "fdw" (Foreign Data Wrappers) driver. In 9.3, this functionality now supports the ability to also write to those foreign tables so that an application could push changes out to wherever the data was stored. With appropriate drivers, PostgreSQL administrators can therefore use PostgreSQL as the data hub of the enterprise. It even has its own "fdw" driver, so that one PostgreSQL instance can be federated with another, rather than just supporting replication and synchronisation.

Where a view is created with a simple enough query, it is now possible to update that view and have those changes reflected in underlying table. Materialised views are a new way to cache views, in that the view, rather than executing the view query on every access, saves the view output in a special table which also stores the view definition so it can be easily recreated. There is also new syntax that allows recursive views to be declared.

PostgreSQL 9.3 now has 12 functions in its JSON support rather than 9.2's two. These include JSON data generation functions such as to_json to convert values to JSON and json_agg to convert records into JSON arrays. The indexed regular expression search speeds up searches with regexps by extracting trigrams from the expression and creating the appropriate indexes.

Resilience and performance features include support for fast fail-over to replicas (with promotion in less than a second), disk page checksums which will spot filesystem failures, and streaming-only re-mastering of replicas, which allows for larger replication clusters. Faster backups from a new parallel option in pg_dump is also available in the beta and, for quick checks, a new pg_isready command can tell shell scripts the state of a database instance. Internally, PostgreSQL 9.3 has switched to using Posix shared memory and mmap for memory management, which, among other things, means that system parameters like SHMMAX and SHMALL no longer need to be adjusted. The developers would like special attention paid to this change to ensure no memory management issues have been introduced.

Full details of the new features and changes are available in the beta version of What's new in PostgreSQL 9.3, with further information in the release notes. Links to download installers and source code for the beta, along with information on how to report bugs, are on the Beta Download page. PostgreSQL is under its own PostgreSQL licence which is similar to the MIT or BSD licences.

(djwm)