Software Update: PostgreSQL 9.4

Version 9.4 has been released from PostgreSQL. This popular ‘open source relational database management system’ runs on a large number of operating systems and can therefore be used in various environments. It is a derivative of Ingres, after its lead developer started his own business and this open source database became closed source. The release notes for this release can be found below.

Flexibility
With the new JSONB data type for PostgreSQL, users no longer have to choose between relational and non-relational data stores: they can have both at the same time. JSONB supports fast lookups and simple expression search queries using Generalized Inverted Indexes (GIN). Multiple new support functions enable users to extract and manipulate JSON data, with a performance which matches or surpasses the most popular document databases. With JSONB, table data can be easily integrated with document data for a fully integrated database environment.

“JSONB brings PostgreSQL to the JavaScript development community by allowing JSON data to be stored and queried natively. Node.js and other server-side JavaScript frameworks can benefit from the safety and robustness of PostgreSQL, while still storing data in the schema-less format that they prefer,” said Matt Soldo, Product Manager, Heroku Postgres.

scalability
In 9.4, Logical Decoding supplies a new API for reading, filtering and manipulating the PostgreSQL replication stream. This interface is the foundation for new replication tools, such as Bi-Directional Replication, which supports the creation of multi-master PostgreSQL clusters. Other improvements in the replication system, such as replication slots and time-delayed replicas, improve management and utility of replica servers.

“The main reason behind our immediate adoption of PostgreSQL 9.4 in production is the new Logical Decoding feature,” said Marco Favale, Cartographic Production Manager of Navionics. “The ability to write custom and flexible output plugins will allow us to transparently collect changes on selected tables and replicate changes where we like, by removing heavier – and more complex to manage – trigger based replication solutions.”

“Zalando relies on the stability and performance of hundreds of PostgreSQL database servers to continuously serve millions of customers all around Europe,” said Valentine Gogichashvili, Team Lead Database Operations at Zalando Technologies. “We are excited to run time-delayed standby servers which work out of the box, and will evaluate the new bi-directional replication tools as soon as they are released.”

Performance
Version 9.4 also introduces multiple performance improvements which will allow users to get even more out of each PostgreSQL server. These include:

• Improvements to GIN indexes, making them up to 50% smaller and up to 3X faster.
• Concurrently updateable Materialized Views for faster, more up-to-date reporting.
• Rapidly reload the database cache on restart using pg_prewarm.
• Faster parallel writing to the PostgreSQL transaction log.
• Support for Linux Huge Pages for servers with large memory.