Personal blog of Yzmir Ramirez

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.

Today saw the launch of PostgreSQL14. For me, the important area to focus on is what the community has done to improve performance for heavy transactional workloads and additional support for distributed data.

The amount of data that enterprises have to process continues to grow exponentially. Being able to scale up and out to support these workloads on PostgreSQL 14 through better handling of concurrent connections and additional features for query parallelism makes a lot of sense for performance, while the expansion of logical replication will also help.

Performance Enhancements

Server Side Enhancements

Reducing B-tree index bloat

Frequently updated indexes tend to have dead tuples that cause index bloat. Typically, these tuples are removed only when a vacuum is run. Between vacuums, as the page gets filled up, an update or insert will cause a page split – something that is not reversible. This split would occur even though dead tuples inside the existing page could have been removed, making room for additional tuples.

PostgreSQL 14 implements the improvement where dead tuples are detected and removed even between vacuums, allowing for a reduced number of page splits thereby reducing index bloat.

Eagerly delete B-tree pages

Reducing overhead from B-trees, the vacuum system has been enhanced to eagerly remove deleted pages. Previously, it took 2 vacuum cycles to do so, with the first one marking the page as deleted and the second one actually freeing up that space.

Enhancements for Specialized Use Cases

Pipeline mode for libpq

High latency connections with frequent write operations can slow down client performance as libpq waits for each transaction to be successful before sending the next one. With PostgreSQL 14, ‘pipeline mode’ has been introduced to libpq allowing the client to send multiple transactions at the same time, potentially giving a tremendous boost to performance. What’s more – because this is a client-side feature, PostgreSQL 14’s libpq can even be used with older versions of the PostgreSQL server.

Replicating in-progress transactions

Logical replication has been expanded to allow streaming in-progress transactions to subscribers. Large transactions were previously written to disk till the transaction was completed before replicating to the subscriber. By allowing in-progress transactions to be streamed, users gain significant performance benefits along with more confidence in their distributed workloads.

Add LZ4 compression to TOAST

PostgreSQL 14 adds support for LZ4 compression for TOAST, a system used to efficiently store large data. LZ4 is a lossless compression algorithm that focuses on the speed of compression and decompression. LZ4 can be configured at the column as well as the system level. Previously, the only option was pglz compression – which is fast but has a small compression ratio.

Enhancements for Distributed Workloads

The PostgreSQL foreign data wrapper – postgres_fdw – has been instrumental in easing the burdens of handling distributed workloads. With PostgreSQL 14, there are 2 major improvements in the FDW to improve performance for such transactions: Query parallelism can now be leveraged for parallel table scans on foreign tables, and Bulk insert of data is now allowed on foreign tables.

Both these improvements further cement the increasing ability of PostgreSQL to scale horizontally and natively handle distributed databases.

Improvement to SQL parallelism

PostgreSQL’s support for query parallelism allows the system to engage multiple CPU cores in multiple threads to execute queries in parallel, thereby drastically improving performance. PostgreSQL 14 brings more refinement to this system by adding support for RETURN QUERY and REFRESH MATERIALIZED VIEW to execute queries in parallel. Improvements have also been rolled out to the performance of parallel sequential scans and nested loop joins.

Security

SCRAM as default authentication

SCRAM-SHA-256 authentication was introduced in PostgreSQL 10 and has now been made the default in PostgreSQL 14. The previous default MD5 authentication has had some weaknesses that have been exploited in the past. SCRAM is much more powerful, and it allows for easier regulatory compliance for data security.

Predefined roles

Two predefined roles have been added in PostgreSQL 14 – pg_read_all_data and pg_write_all_data. The former makes it convenient to grant read-only access for a user to all tables, views, and schemas in the database. This role will have read access by default to any new tables that are created. The latter makes it convenient to create super-user-styled privileges, which means that one needs to be quite careful when using it!

Convenience for Application Developers

Access JSON using subscripts

From an application development perspective, I have always found support for JSON in PostgreSQL very interesting. PostgreSQL has supported this unstructured data form since version 9.2, but it has had a unique syntax for retrieving data. In version 14, support for subscripts has been added, making it easier for developers to retrieve JSON data using a commonly recognized syntax.

Multirange types

PostgreSQL has had Range types since version 9.2. PostgreSQL 14 now introduces ‘multirange’ support which allows for non-contiguous ranges, helping developers write simpler queries for complex sequences. A simple example of practical use would be specifying the ranges of time a meeting room is booked through the day.

OUT parameters in stored procedures

Stored procedures were added in PostgreSQL 11, giving developers transactional control in a block of code. PostgreSQL 14 implements the OUT parameter, allowing developers to return data using multiple parameters in their stored procedures. This feature will be familiar to Oracle developers and welcome addition for folks trying to migrate from Oracle to PostgreSQL.

Observability

Observability is one of the biggest buzzwords of 2021, as developers want more insight into how their applications are performing over time. PostgreSQL 14 adds more features to help with monitoring, with one of the biggest changes being the move of query hash system from pg_stat_statement to the core database. This allows for monitoring a query using a single ID across several PostgreSQL systems and logging functions. This version also adds new features to track the progress of COPY, WAL activity, and replication slots statistics.

The above is a small subset of the more than 200 features and enhancements that make up the PostgreSQL 14 release. Taken altogether, this version update will help PostgreSQL continue to lead the open source database sector.

As more companies look at migrating away from Oracle or implementing new databases alongside their applications, PostgreSQL is often the best option for those who want to run on open source databases.

Read Our New White Paper:

Why Customers Choose Percona for PostgreSQL

The future of technology adoption belongs to developers.

The future of application deployment belongs in the cloud.

The future of databases belongs to open source.

That’s essentially the summary of Percona’s innovation strategy and that’s the reason I decided to join Percona – I believe the company can provide me with the perfect platform for my vision of the future of PostgreSQL.

So What Does That Mean, Really?

Gone are the days when deals were closed by executives on a golf course. That was Sales-Led Growth. The days of inbound leads based on content are also numbered. That is Marketing-Led Growth.

We are living in an age where tech decision-making is increasingly bottom-up. Where end-users get to decide which technology will suit their needs best. Where the only way to ensure a future for your PostgreSQL offerings is to delight the end users – the developers.

That’s Product-Led Growth.

PostgreSQL has been rapidly gaining in popularity. The Stack Overflow Developer survey ranked it as the most wanted database and DB-Engines declaring it the DBMS of the year 2020. DB-Engines shows steady growth in popularity of PostgreSQL, which outpaces any other database out there. It is ACID compliant, secure, fast, reliable, with a liberal license, and backed by a vibrant community.

PostgreSQL is the World’s Most Advanced Open Source Relational Database.

Being a leader in open source databases, I believe Percona is well-positioned to drive a Product Led Growth strategy for PostgreSQL. We want to offer PostgreSQL that is freely available, fully supported, and certified. We want to offer you the flexibility of using it yourself, using it with our help, or letting us completely handle your database.

Our plan is to focus on the end-users, make it easy for developers to build applications using our technology, delight with our user experience, and deliver value with our expertise to all businesses – large or small.

Data is exciting. Data has the ability to tell stories. Data is here to stay. 

Data is increasingly stored in open source databases. 

PostgreSQL is the most wanted database by developers. 

That is why I believe: Percona and PostgreSQL – Better Together.

In our latest white paper, we explore how customers can optimize their PostgreSQL databases, tune them for performance, and reduce complexity while achieving the functionality and security your business needs to succeed.

Download to Learn Why Percona and PostgreSQL are Better Together