Postgres 15, the latest version of the most notable and advanced Open Source database in the world has come up with some advanced performance refinements for handling workloads in both local and distributed deployments.
It also improves the developer experience with the inclusion of the “MERGE” command and ensures more capacities to monitor the database.
Identify the New Improvements
Better Sort Performance and Compression
PostgreSQL 15 has delivered several advancements in its in-memory and on-disk sorting algorithms, with benchmarks ensuring an increase in speed of up to 400 % based on which the different data types are sorted. One can also view multiple window functions that have further enhanced the database performance. Some of them are:
row_number() rank() dense_rank() count()
Another advantage that developers
can now leverage in sorting the data types is that
any query which uses SELECT DISTINCT now can be executed parallelly.
Definitive Developer Features
With the help of the “MERGE” command,
one can add conditional SQL statements involving
INSERT, UPDATE,
and DELETE actions in a uniform code.
Postgres 15 also allows users to create
views of the query data by employing the permissions of the caller,
and not just the view creator.
This option, known as security_invoker,
includes an extra layer of protection to make sure that
the view callers have adequate permissions
for functioning with the underlying data.
Better Options with Logical Replication
PostgreSQL 15 ensures greater flexibility for handling logical replication. This release brings about row filtering and column lists for publishers, thereby, allowing users to select and duplicate a subset of data from a table. PostgreSQL 15 includes features to further ease conflict management, involving the capacity to bypass replaying a conflicting transaction and to automatically mutilate a subscription if an error is noticed. This release also comprises support for operating a two-phase commit (2PC) with logical replication.
Logging and Configuration Improvements
PostgreSQL 15 also introduces a new logging format: JSON log.
This new format logs through a definitive JSON structure,
that ensures all PostgreSQL logs are processed in structured logging systems.
It provides DBA and developers with more options
for managing PostgreSQL configuration and logs.
Not to mention, SQL users can now search for data about configuration
using the \d config command
from amongst the psql command-line tool.
In-memory server stats
In the latest version of Postgres 15 server-level statistics, the data is stored in shared memory. This, in turn, eliminates the individual statistics collector process. Postgres 15 monitors all activities of every system to collect statistics and data and makes it available through different “pg_stat_” views. Optional LZ4 and Zstandard compression for WAL (write-ahead log) files With Optional LZ4 and Zstandard compression for WAL, users who are utilizing btrfs and zfs filesystem built-in compression can now leverage Postgres 15’s advanced features without changing their storage system.
Deprecated features
- PostgreSQL 15 withdraws the CREATE permission from all users. Only the database owner from the public schema can use this method.
- Removed the stats collector. Now statistics will be accumulated in shared memory.
- PostgreSQL 15 terminates both the long-deprecated “exclusive backup” mode and support for Python 2 from PL/Python.
New Configuration Parameter
Reporting and Logging parameters
log_startup_progress_interval: Calculates the time taken between progress updates for long-running startup procedures
Write-ahead log parameters
recovery_prefetch: Get referenced blocks during recoverywal_decode_buffer_size: Buffer size for evaluating beforehand in the WAL during recovery
Fosters Query Tuning
Archive_Library: Calls the average size of a recursive query’s working tablerecursive_worktable_factor: Assigns the planner’s estimate of the average size of a recursive query’s working table
Improves the storage of Statistics Data
stats_fetch_consistency: Puts the consistency of accesses to stats data
The Popular “MERGE” Command
The MERGE command executes at most one action on rows from the target table, based on the rows from the source query. This ensures a way to determine a single SQL statement that can conditionally UPDATE or INSERT rows in one go, thereby, simplifying the task of adding multiple procedural language statements.
