With support for bigger tables, faster indexing, and connections to Hadoop, the new version of the enterprise-grade database aims for both SQL and NoSQL users Credit: Thinkstock Many of PostgreSQL’s recent changes, from scaling via sharding to adding JSON document-processing functions, have been inspired by NoSQL. The new PostgreSQL 9.5 continues in that vein, but doesn’t neglect PostgreSQL’s first and most important audience: the conventional RDBMS market, which has its own specific needs. Version 9.5 adds support for what are called UPSERT operations. With it, the database checks to see if inserted data conflicts with existing data, and if so, can update existing rows with the new data instead of simply returning an error. A feature like this long been in demand by PostgreSQL users and has been worked on “over the last two years by Heroku programmer Peter Geoghegan,” according to the product’s news release. MERGE, a similar SQL command implemented elsewhere (in Microsoft SQL Server, for instance), was considered, but Geoghegan has stated that user demand and the relative simplicity of implementation drove PostgreSQL to add UPSERT instead. Version 9.5 also has new features that target users of large databases. PostgreSQL’s new indexing system, BRIN (block range indexes), allows for fast queries on large tables by storing only the smallest and largest values for a given range of database table pages. A new sorting optimization called “abbreviated keys” speeds up sorting, mainly on text columns, and new SQL clauses named CUBE, ROLLUP, and GROUPING SET allow users to create reports with multiple levels of summarization. Another new PostgreSQL feature, foreign data wrappers, allows PostgreSQL to query systems like Hadoop and Cassandra at arm’s length. It’s unclear how different the performance would be versus using, say, a native Hadoop SQL-style querying engine. Databases have a long history of security problems, mainly because of the way data is still commonly exposed by way of SQL injection attacks. Another long-germinating PostgreSQL feature, row-level security, addresses this by allowing security policies to be applied to specific tables. When applied, data that can’t be seen by a given user because of their permissions won’t show up in a query. PostgreSQL has had column-level security for some time, but this adds another level of granularity, especially since it can be applied with policy systems like SELinux. DatabasesOpen SourceData Management