What’s new in PG15

PostgreSQL releases major versions relatively often, about once a year. This is because the PostgreSQL Global Development Group (PGDG) is committed to providing new features and improvements to the database on a regular basis.

Of course, there are some drawbacks to releasing major versions so often. One is that it can be difficult for users to keep up with the latest changes. Another is that it can be more challenging to upgrade to a new major version, as the internal data storage format may change. However, the benefits of releasing major versions often outweigh the drawbacks.

By releasing new features and improvements on a regular basis, the PGDG is able to keep PostgreSQL at the forefront of database technology. This makes PostgreSQL a good choice for organizations that need a powerful and reliable database.

The current stable version of PostgreSQL is 15.3. In this blog, we will review features, improvements, and major updates in PostgreSQL 15.

The MERGE operation

The SQL MERGE statement is a powerful tool for data manipulation that allows you to conditionally insert, update, or delete data. It was officially introduced in the SQL ANSI 2003 standard, and expanded in the SQL ANSI 2008 standard.

The SQL MERGE statement works by merging data from two tables into a single table. The two tables are called the target table and the source table. The merge condition is a Boolean expression that determines which rows in the target table should be updated, inserted, or deleted.

The MERGE statement has three clauses:

• WHEN MATCHED specifies what should happen when a row in the target table is matched by a row in the source table.
• WHEN NOT MATCHED specifies what should happen when a row in the target table is not matched by a row in the source table.
• WHEN MATCHED AND specifies what should happen when a row in the target table is matched by a row in the source table and the search_condition is true.

The search_condition is an optional expression that can be used to further refine the rows that are updated, inserted, or deleted.

PostgreSQL added the SQL MERGE feature in this release which will add value for the organization who uses it for processing the data.

WAL compression options

PostgreSQL 15 introduced Zstandard and LZ4 as an option for compression method, so we have more options instead of pglz for WAL compression. Some benefits of the new options include:

• LZ4 offers a superior balance between compression ratio and speed.
• Zstandard is highly configurable, allowing users to choose different compression levels (speed vs. compression ratio).
• Both support both single-threaded and multi-threaded compression, which can be beneficial for systems with multiple CPU cores.

We can also use the compression method for the backup on the server side to compress the backup size; thus, it will reduce the size significantly resulting in less data transfer over the network.

Logging Format

PostgreSQL 15 includes a new feature that enables you to convert your database logs into JSON format, ensuring compatibility with the preferred data structure among tech professionals. This “structured log” can also be utilized by various tools for storage and examination purposes.

Parameters and monitoring views

Several enhancements have been made in PostgreSQL 15, such as:

• A new statistics view, pg_stat_subscription_stats, has been introduced for subscription monitoring.
• Another view, pg_stat_recovery_prefetch, has been included to monitor pre-fetching during the recovery process.
• In the updated pg_stat_statements, there are additional fields that provide information on temporary file I/O and JIT counters.

Additionally, two new server variables have been introduced:

• shared_memory_size, which allows you to check the allocated shared memory size.
• shared_memory_size_in_huge_pages, which indicates the number of huge memory pages required.

PostgreSQL 15 in ClusterControl

ClusterControl supports PostgreSQL version 15 as the latest version, so you can deploy PostgreSQL database both standalone and clusters database using streaming replication through the ClusterControl UI easily.

You can utilize the new feature after the PostgreSQL is deployed using ClusterControl.

Major Upgrade to PostgreSQL 15 in ClusterControl

As of ClusterControl 1.9.8, PostgreSQL users can now perform major in-place version upgrades in ClusterControl. Currently, you can only upgrade one step ahead, for example from version 13 to 14 or from version 14 to 15. If your current version is further than one step away from your destination version, PG 13 to PG 15 for example, you can still upgrade to your destination by repeating the process until you get there.

Thankfully, performing major version upgrades is very easy — I’ll show you how by upgrading from PostgreSQL version 14 to 15:

1.Go to the PostgreSQL cluster in ClusterControl, choose the Cluster Menu -> Upgrades. You will see the following dialogue:

2.Choose the Major Upgrades. You will see the following page to choose the upgrade method:
N.B. If PostgreSQL is running the latest version, the Major Upgrade button will be grayed out.

ClusterControl currently provides 3 methods for the upgrades, copy, link, and pgdumpall.

• The copy method will clone or copy the existing data directory into the target directory for the new version. This means your disk needs to have double the free space to store the cloned data.
• The link method will create a hard link to your current data directory, it’s faster than the copy method.
• The pgdumpall method will take a backup for your current database and try to restore the data after the upgrade has finished.

3.The next step is to configure the temporary ports for the new cluster.

4.You will preview the parameters of your major upgrade before you submit the job.

5.Later you can see the Job Activity log about the Major Upgrade to version 15 as below:

Conclusion

PostgreSQL continually makes significant advancements in enhancing its performance and introducing fresh capabilities with each new release, and version 15 is no exception. It comes as no surprise that PostgreSQL stands out as the most beloved and highly sought-after database in Stackoverflow’s recent developer survey.

Additionally, it ranks as one of the fastest-growing in popularity according to DB-Engines. What’s crucial, though, is grasping which features can be harnessed to optimize PostgreSQL for your specific use case, ensuring it performs at its best. ClusterControl will always support newest version of PostgreSQL database, so customer can use the latest version for deployment, monitoring and operations of the database.