The latest version of the PostgreSQL database has been released with several advancements that the company says makes it 96% faster than the previous version.
Version 9.5 comes with improved performance and scalability with an enterprise and big data focus.
One of the added features is called UPSERT – "ON CONFLICT DO NOTHING" (or "ON CONFLICT DO UPDATE"). This is designed to give database developers a more effective and efficient way to insert data, while managing insert conflicts.
Similar technology has been in MySQL for a while but now the company says its own tool removes the last barrier for MySQL users to migrate to PostgreSQL.
Dave Page, VP, Chief Architect, Tools and Installers, EnterpriseDB, told CBR: "It’s a very complex feature and has taken two years to get right; concurrency is difficult to get right.
"It is one of the last barriers to move from MySQL, they have MERGE-INSERT and we have removed that barrier."
Content from our partners
While luring MySQL users to join PostgreSQL is one element of the update, other areas of focus have seen security improved through row-level security.
This feature allows for rules to be set so that a user is limited to viewing their own data. It works by blocking certain rows and Page says it avoids as many covert channels as possible by pretending that the data wasn’t there in the first place.
The big data focus of the release is BRIN indexing, which is a replacement of B-tree indexes on very large tables which can see billions of rows of data.
The challenge with searching the index for billions of rows of data is that it is a very slow process. BRIN, which stands for Block Range Index, provides for very small indexes that use metadata to describe a range of information to improve the performance of queries.
For data in tables that is in a sequential manner, it allows for queries to skip the majority of the table when looking for data within a certain range, making it much faster.
Page said that the company had been using an IBM box with 192 threads and had seen up to 96% improvement in speed while it can also deal with more users accessing the database.
A trio of analytic features have also been added which are designed to summarise large amounts of data across various dimensions. GROUPING SETS/CUBE AND ROLLUP are typically used in data warehousing environments.
The huge update isn’t finished there; Foreign Data Wrappers allow for accessing data across multiple servers, which the company says will be the foundation for future developments in horizontal sharding.
Page said that PostgreSQL has been playing catch-up with hardware vendors, with some of the new features taking five years to develop.
Originally PostgreSQL wasn’t designed for dealing with huge systems but now it has much more vertical stability.
With the features added this latest release appears to offer a lot and EnterpriseDB, which is the leading provider of PostgreSQL software, will be looking for it to equate to more business as it hopes to gain more enterprise customers.
The 9.5 version is generally available and a free version is available through the PostgreSQL community download page.
