PostgreSQL 15 Overview

A full year has passed and the PostgreSQL community has released version 15 of the database, which includes various powerful new features that will benefit a large user base. The PostgreSQL project has also come a long way and improvements are being added constantly at a rapid pace that make the database more useful, more efficient, and more widely accepted. The times when PostgreSQL was some unknown obscure thing are long gone. PostgreSQL has reached the data center and has been widely adopted in many large companies as well as by governments around the world.

In this chapter, you will learn about new features that made it into PostgreSQL 15. Those features add additional capabilities, more performance, as well as security and enhanced usability.

The following topics are covered in this chapter:

• DBA-related features
• Developer-related features
• Performance-related features
• Additional replication features

Of course, there is always...

Making use of DBA-related features

In PostgreSQL 15, a couple of developer-related features were added. Some features were also finally deprecated and have been removed from the server. In this section, we will go through the most important major changes.

Removing support for old pg_dump

One of the first things that is worth noting is that support for really old databases has been removed from pg_dump. PostgreSQL databases that are older than PostgreSQL 9.2 are not supported anymore. Considering that PostgreSQL 9.2.0 was released to the PostgreSQL community FTP server on September 10, 2012, most people should have gotten rid of their PostgreSQL 9.1 (and older) systems by now.

If you have not been able to upgrade since then, we highly recommend doing that. It is still possible to upgrade from such an old version to PostgreSQL 15. However, you will need an intermediate step and to use pg_dump twice.

Deprecating Python 2

PostgreSQL allows developers to write stored procedures...

Understanding developer-related features

Administration-related features are not everything. PostgreSQL 15 also provides some developer-related features that are useful to build even more powerful applications. In this section, you will learn which ones are available.

Security invoker views

In PostgreSQL, a view is a separate security context. It might happen that somebody is allowed to access a view but not the underlying table. Why does this matter? Consider the following scenario:

• Joe is allowed to see all sales
• Jane is only allowed to see total turnover by country

Jane will be granted rights on a view that sums up the data. She will be able to read that view without having the right to read the sales table directly.

However, in PostgreSQL, it is now possible to make PostgreSQL treat the view more like a preprocessor directive. By setting security_invoker, it will also check the underlying tables and not treat the view as a separate security context anymore...

Using performance-related features

Of course, there are also a couple of performance-related features that have made it into PostgreSQL core.

Adding multiple compression algorithms

It is now possible to define the compression algorithm for various things. This is especially important for TOAST. We can control this behavior using the ALTER TABLE command:

...
   ALTER [ COLUMN ] column_name
SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
...

Depending on what type of data is compressed, different algorithms can be really helpful and speed things up considerably.

Handling parallel queries more efficiently

In every major release since PostgreSQL 9.6, new features to handle parallelism have been added. The same is true for version 15. It is now possible to handle DISTINCT in parallel, which was previously not possible (only single core).

With the introduction of...

Additional replication features

Finally, there are a handful of replication-related features that made it into PostgreSQL, and that are mostly centered around logical decoding.

Two-phase commit for logical decoding

Two-phase commit (2PC) has been an integral part of PostgreSQL for many years. However, up to now, 2PC was not supported by logical decoding. While it was possible to use it with normal replication, logical decoding was not supported. This has now changed. We are now able to decode 2PC transactions and send them to the replica as a logical stream.

Adding row and column filtering

More changes also made it into CREATE PUBLICATION. Two features are especially noteworthy:

• Column filtering
• Row filtering

Let us take a look at row filtering first:

CREATE PUBLICATION my_pub_1
FOR TABLE some_table
WHERE (col1 > 10);

In this case, we only publish a subset of data to the other side. In addition to rows, we can also tailor our publications to...

Summary

PostgreSQL 15 offers various features, including performance improvements and simplified database handling. More parallelism, the introduction of MERGE, and better security are definitely some of the key features of this major new release. The community has also done a lot of groundwork that will allow for more functionality in the future.

In the next chapter, you will be introduced to transactions and locking, which are important for scalability and storage management, as well as performance.