PostgreSQL 15 Beta 1 was announced on May 15th and to keep with the spirit of past peeks at MySQL and MariaDB products, this is a cursory glance at the release notes to see what is coming. This is a preview of all the features that will be in the General Available version when it is released this fall. Beta 1 is not in production but the developers encourage you to test this code to find bugs or other problems before the GA release. My comments are in italics and I tend to highlight the stuff I find of note.
The TL;DR
If you are used to the once-a-quarter releases of MySQL or MariaDB, this is an overwhelming amount of new goodies being delivered. PostgreSQL releases less frequently but packs plenty of solid engineering into each release. The claim of being the most advanced open source relational database is well-founded. My one-word summary is ‘wow’. It is very evident that the code is delivered when it is ready and not due to a deadline.
For Developers
SQL codes get MERGE from the SQL Standard for conditionally performing writes (INSERT, UPDATE, or DELETE) when synchronizing two tables. This could be emulated in previous releases by using INSERT .. ON CONFLICT but more batch orientated. This is also known as an upsert. The ‘how do I combine two tables based on the values’ type question on Reddit and Stackoverflow can now be pointed here.
More JSON support including a very impressive JSON_TABLE function to temporarily turn unstructured JSON data into a relational table. Very handy!
And more regular expression functions – regexp_count, regexp_instr, regexp_like, regexp_substr, and range_agg.
In prior versions, NULL values were always indexed as distinct values but this can be changed by using UNIQUE NULLS NOT DISTINCT.
Performance
Reportedly there is a big gain for sorting large dataset, a paralleled SELECT DISTINCT, faster window functions (row_number(), rank(), and count()), and a \copy bulk loader built into psql. If you use the foreign data wrapper’s postgres_fdw you now can have transactions committed in parallel.
Compression
LZ4 is added to Zstd compression into many components. The WAL can not take advantage of those two compression tools plus pg_basebackup can now take advantage of those two plus gzip.
Logical Replication
Row filtering and column filtering are now offered and there is also the ability to publish all the tables in a schema instead of just the tables in the database. Yes, MySQL-ites, there is a difference.
DBAs/SREs Get Goodies Too
Logging can now be done using the jsopnlog format for the consumption of programs that aggregate and analyze logs. Slow checkpoint and autovacumms are automatically logged. You can now see configuration values by using \dconfig with psql and the default shows you the non-default values. This will be loved by anyone who tried to figure out why two servers behave differently and get tired of using diff. All server-level stats are stored in shared memory and adios to statistic collector processes. And there is now a pager option for /watch.
Views now can execute using the privileges of the user instead of the creator of the view. Anyone bit by this type of problem is now sighing with relief.
There is now the ability to get extended statistics for a parent and all children to augment the existing ability to record parent/child stats with regular statistics separately.
Generic periodic log messages telling you the case of the delay are generated during slow server starts. Finally, the reason for ‘why the #$@ is in not up yet?’ can be told.
Conclusion
PostgreSQL 15, like all its predecessors, has an impressive amount of solid engineering in each release. For someone coming from less featured open source relational databases like me, the amount of work being delivered is staggering. My 10,000-foot view is that an awesome amount of things to make PostgreSQL more reliable, more useful, and maintain the reputation of being on the cutting edge has handed over for us to kick the tires rev the engine, and take it for a spin. I am sure a lot of backend database specialists will be muttering ‘gee, I could really use that as they pour over the items in the release notes.
In the proposed MERGE syntax there’s no “WHEN NOT MATCHED BY SOURCE” clause, which is essential to handle data deleted in source tables, e.g. for ETL or other data warehouse-related tasks. Are there plans to support it?
If I read https://www.postgresql.org/message-id/attachment/23520/sql-merge.html correctly then no. You would have better luck on the PG developers lists to see if/when this is being developed.