The new PostgreSQL 9.3 can send foreign data home too

elephant copyThe PostgreSQL team have released PostgreSQL 9.3 ending the beta cycle which started in May. 9.3’s headline feature is the newly writable Foreign Data Wrappers (fdw). In 9.1 and 9.2, foreign data wrappers were read-only, allowing the database to only ingest information made available through an “fdw” driver, taking them from a legacy source or other database and materialising them as a table. In 9.3 though, these “fdw” drivers can be enhanced and support changes to the fdw tables being reflected back in the source. There’s also a PostgreSQL “fdw” driver for federating PostgreSQL instances.

Also new in 9.3 are twelve JSON functions (rather than 9.2’s two) for working with JSON data within the database, including path based extraction so JSON fields need not be extracted externally to be tested. Materialised views are also new and cache the output of a view as a physical table rather than repeatedly issuing the query that the view is based on – there’s also support for recursive views too. Simple views can now be updated too, rather than going back to the view’s source and updating that.

Other enhancements include a parallel version of pg_dump to speed up backups, a switch to POSIX shared memory which will make configuration easier, new trigger events from CREATE, ALTER and DROP and an implementation of lateral joins. Modules can now opt to work in the background as custom server processes and there’s support for data checksums and corruption reports.

Basically, it’s oodles of improvements in one big update – full details in the what’s new in 9.3 notes and release notes. You’ll find the open source licensed PostgreSQL ready for download on the PostgreSQL Download page.