PostgreSQL 15 streamlines workload management, improves performance

A new version of the popular open-source database PostgreSQL is now available, offering performance improvements over PostgreSQL 14 and new functions and capabilities for managing workloads in local and distributed deployments.

Released on October 13 by the PostgreSQL Global Development Group, which oversees development of the database, PostgreSQL 15 can be downloaded from postgresql.org.

The new capabilities take on added significance as public cloud service providers such as Oracle, Google and Microsoft add PostgreSQL support to their products due to its rising popularity.

Just last week, Microsoft added PostgreSQL support for Cosmos DB, and in May, Google Cloud Platform (GCP) launched a  PostgreSQL-compatible, fully managed database-as-a-service (DBaaS), dubbed AlloyDB, in public preview.

PostgreSQL 15 offers improved sort performance, compression

The new version of PostgreSQL has better in-memory and on-disk sorting algorithms, the development group said, adding that benchmark tests have shown a 25%-to-500% increase in speed, depending on data types.

Sorting is an extra step taken by developers to ensure that a query output table doesn’t come back in an unspecified order. If sorting is not done, then the database management system will return an output table based on the scan and order inside the disk, which is not useful for analysis purposes.

Using row_number(), rank(), dense_rank(), and count() as window functions can offer performance benefits in the new version, the PostgreSQL group said. Window functions allows developers to perform calculations across a set of rows related to the current query.

In addition, queries using SELECT DISTINCT—a SQL statement used to eliminate duplicate rows from the result table—can now be executed in parallel in the new version of PostgreSQL.

While many queries cannot benefit from parallel execution, the ones that can run more than twice as fast using this method, the development group said.

Building on the previous version of PostgreSQL, which provided support for async remote queries, the new version now supports asynchronous commits with the help of a new tool, dubbed PostgreSQL foreign data wrapper.

In contrast to synchronous code architecture, where tasks are dependent on each other (executed in a serial manner), asynchronous architecture allows different tasks to run independently and doesn’t cause the code to stop working or to freeze if one task reports an error.

Other performance improvements include the new archiving and backup facilities.

PostgreSQL 15 now supports LZ4 and Zstandard (zstd) data compression algorithms, which increase the speed of compression and decompression, providing space and performance benefits for certain workloads, the group said.

The new version of the database management system also allows the use of custom modules for archiving, eliminating the overhead of using a shell command.

MERGE command eases coding for PostgreSQL developers

PostgreSQL 15 comes with new features that can help developers code easily. The new version, for example, comes with a SQL standard MERGE command that allows developers to write conditional SQL statements including INSERT, UPDATE, and DELETE actions within a single statement.

“MERGE avoids multiple PL/SQL statements and simplifies SQL scripts for database developers and DBAs. The Postgres implementation conforms to the SQL Standard and helps developers of both Microsoft SQL Server and Oracle Database transition to Postgres,” said EDB, which contributed the feature to the new version.

To enhance security, a new feature dubbed security_invoker has been added to PostgreSQL 15 to add a layer of protection to underlying data. The invoker allows enterprises to create views that query data using the restrictions and permissions of the caller and not the view creator, the group said.

More options with logical replication

PostgreSQL 15 also provides additional flexibility for managing logical replication, the development group said, adding that the new version adds row filtering and column lists for publishers. This allows developers to choose to replicate a specific subset of data from a table, it added.

Other new replication features include support for using two-phase commit with logical replication and tools to simplify conflict management.

The new version also includes the ability to skip replaying a conflicting transaction and can automatically disable a view subscription if an error is detected, the development group explained in its release notes.

Other notable updates include a new logging format designed to ease database configuration and processing. The new log format, dubbed jsonlog, outputs log data using a defined JSON structure, making the data easier to be processed.

New functions streamline work, deprecated features removed

A host of other PostgreSQL changes offer a range of new functions for developers and  changes for perfomance efficiency:

• Server-level statistics are now collected in shared memory, eliminating both the statistics collector process and the need to periodically write this data to disk.
• The new version adds a new built-in extension, pg_walinspect, that lets users inspect the contents of write-ahead log files directly from a SQL interface.
• PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.
• The new version removes both the long-deprecated “exclusive backup” mode and support for Python 2 from PL/Python.
• The latest release also adds new functions for using regular expressions to inspect strings: regexp_count(), regexp_instr(), regexp_like(), and regexp_substr().
• PostgreSQL 15 also extends the range_agg function to aggregate multirange data types, which were introduced in the previous release.