The widely deployed PostgreSQL enterprise-grade database management system has been given a number of key enhancements to boost performance, scalability and security, and the latest version is claimed to show a 96 percent improvement in performance for some workloads.
PostgreSQL is a community-developed software project available for free under an open source-style licence. It is also supported by firms such as EnterpriseDB, which contributes to the code and uses it as the basis for its Postgres Plus Advanced Server, which boasts additional features and subscription-based support for customers.
PostgreSQL 9.5 comes with a number of enhancements, many of which have been designed to boost the platform's suitability for handling larger volumes of data and a larger number of simultaneous queries, to reflect the fact that applications and their data sets are increasing in size and complexity.
"The general theme of this release is largely around big data and various enterprise-grade enhancements to go with that, but the headline feature is what we call Upsert, which has been requested for many years but has been very difficult to implement," said Dave Page, vice president and chief architect at EnterpriseDB, who is also a member of the PostgreSQL development team.
Upsert is a database function that attempts to insert data into a table, but if the database row already exists, it will instead update the existing data. This is a key capability for the database management software, because it enables the function to be performed atomically, meaning it is a single-step process.
If the database does not implement Upsert, the application itself has to check whether the row exists before issuing an insert or update command, and it is possible that another application may amend the row in question in the interval between the two actions, page explained.
"This is in many databases already, but usually in a form called Merge, which is a little bit different in the way it works. We took the decision to implement Upsert, which is more difficult, largely because by getting that right first it will enable us in the future to do Merge more easily," he said.
Meanwhile, PostgreSQL 9.5 shows a 96 percent improvement in performance for high concurrency workloads compared with the previous release, according to EnterpriseDB.
These gains are down to numerous enhancements in the new release that address issues such as reducing the amount of locking, where an application locks out others while it performs an action, and reducing contention on locks.
For example, PostgreSQL 9.5 implements a reduced number of page locks and pins holding indexes during scans, enabling it to support more concurrent users in high-transactional systems. It also has enhanced shared buffer management to support a greater number of shared buffer partitions.
"We've put a lot of time and effort in working on large systems and doing benchmarks of large user counts on very large databases to try and improve things," Page said.
With big data in mind, PostgreSQL now supports a new index type called Block Range Index where the index can point to entire groups of rows rather than individual rows, enabling small indexes that cover a very large amount of data, which could deliver huge performance gains when handling certain types of data, according to Page.
Meanwhile, Row Level Security allows a database administrator to apply security policies to control what data is visible in individual tables when accessed by different users or groups of users.
"This allows you to filter data from tables that particular users should never be able to see, and it's been implemented in such a way that you can build policies based on arbitrary expressions, but it can also tie into SE Linux, which is used by a lot of government organisations," Page explained.
Also enhanced in this version is the Foreign Data Wrappers (FDW) capability that PostgreSQL supports to let users run queries and get data from external data sources. PostgreSQL 9.5 can now automatically import a schema from an external database, obviating the need to manually define the table structure before the data can be queried.
FDW also supports the ability to push joins down to be performed on the remote server when accessing multiple tables from an external database. This is more efficient, but also opens the way to support transparent sharding across multiple servers at some point in the future, even if the servers are ‘foreign', i.e. running a different database management system than PostgreSQL.
"We've got the ability to put PostgreSQL at the centre of your data centre and have it as the thing that allows you to do queries across all your data within your organisation," Page said.
PostgreSQL 9.5 supports recent distributions of Linux, Windows and Unix-based platforms such as HP-UX, and Oracle Solaris.
EnterpriseDB is supporting the community version of PostgreSQL 9.5 now and is set to release an updated Postgres Plus Advanced Server version based on it at the end of this month.
Why does Facebook store "my entire call history with my partner's mum", asks developer who requested his Facebook data
Facebook database included text-message metadata - despite not using Facebook Messenger for SMS
Before Ocado could start selling the technology it had developed to other retailers, it had to tear down and rebuild its own monolithic architecture
Successful attack could result in harm to patients and financial loss, warns NHS governing body
Guccifer 2.0 claimed to be a lone Romanian hacker - until a schoolboy error gave him, her or them away