The PostgreSQL community has a new version of its database to play with. Version 9.5 arrived today, and with it came a number of updated features, the most prominent of which promises to bridge the final gap between MySQL and PostgreSQL.
In version 9.5, PostgreSQL gets an UPSERT feature for the first time. This was one of the most requested additions to the platform, and it should help Web and mobile developers the most. UPSERT is short for “Insert, on conflict update.”
This change allows PostgreSQL to handle conflicts between concurrent data changes. UPSERT is also significant because its inclusion removes the last major barrier to migrating legacy MySQL applications to PostgreSQL. PostgreSQL 9.5’s implementation of UPSERT was created by Heroku’s Peter Geoghegan.
(Related: How developers use databases today)
Version 9.5 also adds greater security thanks to new Row-Level Security (RLS). This is the culmination of more than five years of security work on the database, and the inclusion of RLS will allow administrators to set security policies that filter rows according to individual users’ access guidelines.
Other new features in this release are focused on Big Data usage. Block Range Indexes (BRIN) are a new type of index, allowing for faster searching with some types of data. Logs and other naturally ordered data can be searched in about 5% of the time a B-tree search would require.
PostgreSQL 9.5 also offers faster text and numeric data sorts. A new algorithm called “abbreviated keys” speeds things up between 2x to 1212, and index creation can be sped up as much as 20x.
Other changes in this release include fixes to inter-database connecting code, as well as support for significantly larger write-ahead logging (WAL) files. WAL files are now handled when they’re no longer in use, meaning more space can be offered for temporarily storing them. The default maximum size for WAL files has been increased to 1GB thanks to this change.
Version 9.5 is not without breaking changes. Some older databases may experience issues when upgrading. The changelog contains a comprehensive list of fixes for common version incompatibility issues.