PostgreSQL 15 Released: What To Expect?
The much awaited PostgreSQL 15 has been finally released. This release came with noticeable performance improvement gains for managing workloads in both local and distributed deployments. It also enhances the developer experience with the addition of the “MERGE” command and adds more capabilities to observe the database.
Table of Contents
Exciting New Improvements
1. Improved Sort Performance and Compression
PostgreSQL 15 gives improvements on its in-memory and on-disk sorting algorithms, with benchmarks showing speedups up to 400 % based on which data types are sorted. There are also several window functions that also have performance benefits in PostgreSQL 15 mentioned below:
- row_number()
- rank()
- dense_rank()
- count()
Another improvement regarding the sort improvement is that any query which uses SELECT DISTINCT now can be executed parallelly.
2. Expressive Developer Features
Postgres 15 includes the new SQL “MERGE” command. Which lets you write conditional SQL statements that can include INSERT, UPDATE, and DELETE actions in a single statement. Another thing that helps developers more is Postgres 15 lets users create views that query data using the permissions of the caller, not the view creator. This option, called security_invoker, adds an additional layer of protection to ensure that view callers have the correct permissions for working with the underlying data.
3. More Options with Logical Replication
PostgreSQL 15 provides more flexibility for managing logical replication. This release introduces row filtering and column lists for publishers, letting you choose and replicate a subset of data from a table. PostgreSQL 15 adds features to simplify conflict management, including the ability to skip replaying a conflicting transaction and to automatically disable a subscription if an error is detected. This release also includes support for using a two-phase commit (2PC) with logical replication.
4. Logging and Configuration Enhancements
PostgreSQL 15 comes with a new logging format: JSON log. This new format logs using a defined JSON structure, which allows PostgreSQL logs to be processed in structured logging systems. It gives DBA and developers more flexibility in how they can manage PostgreSQL configuration and logs. Apart from that, users can now search for information about configuration using the \d config command from the psql command-line tool.
5. In-memory server stats
In the new version of Postgres 15 server-level statistics which were previously part of a separate statistics collector process are stored in shared memory. It is eliminating the separate statistics collector process. Postgres 15 tracks all activities of each process to accumulate stats and all the information is available through different “pg_stat_” views.
6. Optional LZ4 and Zstandard compression for WAL (write-ahead log) files
Optional LZ4 and Zstandard compression for WAL is useful for those not using btrfs or zfs filesystem built-in compression In this release, we have to use LZ4 and Zstandard compression on WAL files. We know that many of our users have been using btrfs and zfs filesystem built-in compression, but now they can take advantage of these advanced features without changing their storage system.
Deprecated feature
- PostgreSQL 15 revokes the CREATE permission from all users except a database owner from the public schema.
- Removed stats collector. Now statistics are stored in shared memory.
- PostgreSQL 15 removes both the long-deprecated “exclusive backup” mode and support for Python 2 from PL/Python.
New Configuration Parameter
1. Reporting and Logging parameters
- log_startup_progress_interval: Time between progress updates for long-running startup operations
2. Write-ahead log parameters
- recovery_prefetch: Prefetch referenced blocks during recovery
- wal_decode_buffer_size: Buffer size for reading ahead in the WAL during recovery
3. Query Tuning
- Archive_Library: Sets the planner’s estimate of the average size of a recursive query’s working table
- recursive_worktable_factor: Sets the planner’s estimate of the average size of a recursive query’s working table
4. Statistics
- stats_fetch_consistency: Sets the consistency of accesses to statistics data
The Popular “MERGE” Command
MERGE performs at most one action on rows from the target table, driven by the rows from the source query. This provides a way to specify a single SQL statement that can conditionally UPDATE or INSERT rows, a task that would otherwise require multiple procedural language statements. MERGE actions have the same effect as regular UPDATE, INSERT, or DELETE commands of the same names, though the syntax is slightly different. MERGE can be used in some situations where MERGE makes a lot of sense:
- data loading from external sources, thru foreign data wrappers
- staged and batched process jobs
EXAMPLE:
A. Create table sample and sample2
sample=# create table sample (id int primary key, x int, y int, z int);
sample=# create table sample2 (id int primary key, x int, y int, z int);
B. Inserting data to sample table
sample=# insert into sample2 values (generate_series(1,10),generate_series(1,10),generate_series(1,10),generate_series(1,10));
C. Basic MERGE command
merge into sample s1
using sample2 s2
on s1.id = s2.id
when matched then
update set x = s2.x, y = s2.y, z = s2.z
when not matched then
insert (id, x, y, z)
values (s2.id, s2.x, s2.y, s2.z);
Upgrade to PostgreSQL 15
If you are interested and want to upgrade your Postgres database to Postgres 15, watch this video below.
How can Ashnik help you?
We at Ashnik can help you by offering consulting services, technical services, migration services, managed services, and training programs in several open-source technologies and share with you our experience of helping several enterprises across SEA and India. We can help you put together database platforms and high-speed data pipelines, re-architect your applications using DevOps automation, Kubernetes, and microservices architecture and facilitate multi-cloud or hybrid adoption.
This is very important for us to welcome and understand the new features that PostgreSQL 15 has. Ashnik works with several leading enterprise open-source technologies like PostgreSQL that provide reliable and stable support.
Ashnik also provides you with support for your PostgreSQL database, with meaningful services to the industry. We are ready to help you take timely action through our expertise in open-source services, support, and solutions.
Get in touch today for a free consultation with our team of experts!
Found this article useful? You may like to check out how Ashnik helped with simplifying data migration and automation for a multinational mobile advertising company.
