Release of Postgres15

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 recovery
  • wal_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 table
  • recursive_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 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.

Need help on your Ruby on Rails or React project?

Join Our Newsletter