The long awaited release of the most popular advanced open source database is finally here carrying many bells and whistles. It includes performance improvements on managing workloads in both local and distributed deployments and enchantments that will also make the developers out there happy.

Let's start with the highlight of the release, which arrived on October 13, the introduction of the MERGE statement. It's not just a more convenient way of doing SELECT followed by UPDATE/INSERT, an operation that would be transmitting the selected rows over the network and keeping the rows in-memory on the client side. With MERGE, you now keep the rows in-memory on the server without incurring the network penalty.

However use with care since :

At scale, MERGE may introduce complicated concurrency issues or require advanced troubleshooting. As such, plan to thoroughly test any MERGE statement before deploying to production.

The next most important new feature has to be the security_invoker. This option lets users create views that query data using the permissions of the caller, not the view creator , as such adding an additional layer of protection to ensure that view callers have the correct permissions for working with the underlying data.

It's a feature that will simplify view definitions and policies by creating views that use row-level security directly in multi-tenant applications. Users can now query such a view that will return the correct results per the user, thus not exposing data from one user/customer to any other.

Parallelism enchantments - Queries using SELECT DISTINCT can now be executed in parallel.

Logical replication- row and column level filters are now added with which you can selectively replicate data from your primary database to your replica.

jsonlog - this release allows for formatting your database logs in JSON, something that makes it compatible with the popular log aggregators like ELK or Graylog.

This latest release also adds new functions for using regular expressions to inspect strings: regexp_count(), regexp_instr(), regexp_like(), and regexp_substr(). PostgreSQL 15 also extends the range_agg function to aggregate multirange data types, which were introduced in the previous release.

Statistics - Previously the statistics collector received statistics updates via UDP and shared statistics data by writing them out to temporary files regularly. These files can reach tens of megabytes and are written out up to twice a second. This has repeatedly prevented PostgreSQL from adding additional useful statistics. PostgreSQL now adds server-level statistics which get collected in shared memory instead of using the files and filesystem, eliminating both the statistics collector process and periodically writing this data to disk.

Finally the release adds improved sorting algorithms and miscellaneous performance enhancements as well as several bug fixes. For the full changes check out the release's
change log.

More Information

PostgreSQL 15 Released!

Release Notes

Related Articles

pgsqlite - Load SQLite databases into PostgresSQL

Running PostgreSQL Inside Your Browser

pg_ivm - Materialised Views On Steroids

The PostgresSQL Transition Guide Helps You Make The Switch

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info