After four beta releases and one Release Candidate, the RC2 for PostgreSQL 15 was announced earlier today. If all goes well – fingers crossed! – this release will be promoted to General Availability (or GA) on October 13.
PostgreSQL Global Development Group (PGDG) has been really good about releasing one major version and four minor versions each year. As always, this year’s major version, i.e. v15, has loads of new features and enhancements to offer. I describe below some of the features that I find especially exciting.
MERGE
Part of the SQL Standard and focusing on making the developer’s job easier, the MERGE command is a very useful enhancement, making it simple to conditionally insert, update, or delete data. The graphic below is a simple representation of the statement.
The business logic that would have otherwise required many Lines of Code (LOC) is made simple with this conditional statement. Reducing the LOC count also reduces the maintenance overhead over the long term. MERGE has been around in Oracle and SQL Server for a while, and an interesting benefit that the implementation in PostgreSQL brings is that it makes it easier for people to move their SQL code from Oracle to PostgreSQL.
Row and column filters in logical replication
Logical Replication was added to core PostgreSQL in version 10. Since then, it has taken strides and added lots of enhancements and functionality in-core. Before v10, logical replication could only be achieved with the help of the extension, pglogical.
With v15, logical replication adds the much-awaited feature of row and column-level filters.
Row and column level filters help you selectively replicate data from your primary database to your standby. This selective replication is not just helpful for performance gains (in case not all data needs to be replicated). It can also be used for use cases like security and geo-fencing of data for regulatory compliance, implementation of multi-tenancy, and stripping off personally identifiable information for reporting purposes.
Server-side compression and client decompression in pg_basebackup
pg_basebackup is a powerful utility that takes the physical backup of an entire PostgreSQL cluster, thus making point-in-time recovery as well as starting off a new backup cluster simpler and faster. With PostgreSQL 15, pg_basebackup now supports server-side compression as well as client decompression enabling efficient storage of data. Compression algorithms supported include lz4, which is perhaps the fastest lossless compression, thereby adding performance efficiency to the process.
Logging format – jsonlog
The PostgreSQL 15 release adds the option to format your database logs in JSON, thereby making the logs compatible with the most popular structure among technologists. This “structured log” can also be used by other utilities for storage and analysis.
Performance enhancements
There has been a string of performance improvements over the past few years in PostgreSQL targeting both local and distributed workloads. While some of these improvements require an understanding of the new features and leveraging them explicitly, many ‘just work’ behind the scenes to make your database perform better.
Improved sorting algorithms
Improvements to on-disk and in-memory sorting algorithms in PostgreSQL 15 have resulted in benchmark speedups of 25% – 400%. What’s even better is that you don’t have to make any changes to your database or to your application to get these performance gains … they just work!
Support for parallelism
Leveraging the power offered by multi-core CPUs, parallelism has been increasing with each new PostgreSQL release. v15 offers parallel SELECT DISTINCT and parallel commits in postgres_fdw. These are some of those features that you need to specifically leverage in order to gain performance using them – they are not the default behavior and neither do they ‘just work’ behind the scenes.
Server stats in shared memory
Starting PostgreSQL 15, server-level statistics – which were previously part of a separate statistics collector process – are stored in shared memory. This eliminates the separate statistics collector process. PostgreSQL tracks all activities of each process to have cumulative stats and all the information is available through different pg_stat_* views. Jobin Augustine has written a fairly detailed article on this topic: PostgreSQL 15: Stats Collector Gone? What’s New?
Summary
PostgreSQL is taking massive strides in improving its performance and offering new features in each new release. v15 is no different. No surprise that PostgreSQL is the most loved as well as the most wanted database in Stackoverflow’s latest developer survey along with being the database that is one of the fastest growing in popularity at DB-Engines. What is important, however, is to understand what features can be leveraged for your use case to make your PostgreSQL perform at its optimum.
