Open-source database fans were given reason to celebrate this week with the release of PostgreSQL 14, an iteration of the RDBMS featuring performance improvements for heavy and distributed workloads.
The seasonal autumn release was posted yesterday, and though PostgreSQL 14 may not include a set of big wow new features, more than 220 updates add up to performance improvements for high-end systems, said Umair Shahid, head of PostgreSQL at open-source technology consultancy Percona.
“The sheer number of additional features is significantly larger, but no single feature carries the entire release. The release focuses on the performance of the database: not just the database internals performing better, but also the SQL queries running faster and a focus on parallelism,” Shahid said.
While PostgreSQL was designed as a vertically scaling database, the open-source community has been focused on trying to implement features that help with horizontal scalability, which makes it much easier to scale applications and improve database performance, he said.
Foreign data wrappers – which have supported federated workloads across PostgreSQL for some time – can now perform parallel table scans allowing developers to create foreign tables in horizontally scaling or distributed databases to run queries in parallel, making performance “significantly faster,” Shahid said.
Then there’s the “bulk data insert” implemented for the foreign data wrapper which also improves performance on distributed workloads. “I would say that these two major features focusing on distributed workloads help with horizontal scaling,” he added.
There are options if you’re looking for a distributed relational database though. Just a few weeks ago, The Register reported on news from Yugabyte, a distributed system inspired by Google’s Spanner and compatible with PostgreSQL on the top half, for example.
While there are other systems on the market distributed by design, they target specific use cases, Shahid said. PostgreSQL – with OSI-approved PostgreSQL Licence – is an ACID-compliant general-purpose database designed for a very broad range of use cases, he said.
PostgreSQL 14 is also designed to improve throughput with tweaks to the management of B-tree indexes — a self-balancing tree data structure — by reducing index bloat on tables with frequently updated indexes.
Bruce Momjian, veep and PostgreSQL evangelist at support firm and contributor EDB, said: “Once we split a B-tree page in the index, we can’t join it again, because of the pathology of B-tree indexes, so there’s some code that improves that.”
Improvements to parallelism and indexing were among the updates which target performance at the top end of PostgreSQL implementations, he said.
“It’s sort of a focus on some of the limitations we’ve had at the top end. I admit that has always been a challenge for us,” said Momjian, who is also a core member of the PostgreSQL Global Development Group. “We’ve always started at the low end and worked our way up. That top end, where you would see Oracle mostly, has sort of been the one little piece that we haven’t really done [which is] the big takeaway for this.
“We see reports in Russia, Europe, US and Japan where people are pushing the upper limits of PostgreSQL and, fortunately, we have the developers to push up that ceiling up a little bit.”
Typically these kinds of workloads would be characteristic of less than 5 per cent of the PostgreSQL implementations in the wild, he said. ®