We are excited to announce that Postgres version 15 is now generally available! The developers of Postgres release a new version around October every year, and we aim to release it on Heroku Postgres each Q1. Additionally, we track Postgres end-of-life dates to ensure that our service and our customers are always on the latest supported releases.
If you are new to Heroku, great! Your new database defaults to Postgres 15. If you already have a Heroku Postgres database on an older version, we make the upgrade process simple. And if you are still on one of the deprecated versions, such as 9.6 and 10, we urge you to upgrade off of them as soon as possible. We strongly recommend using the latest versions of the software for better performance and security. We keep up with the latest developments and actively support its current versions to make it easy for you to do the same.
Postgres 15 comes with notable performance improvements, as well as new features. You can also review the official documentation, as well docs for Postgres 14 and 13. Meanwhile, our engineering team has curated a short summary of some of the key Postgres 15 features below for you.
Performance Improvements
Sorting Improvements
Sorting functionality is an essential part of Postgres query execution, especially when you use queries like ORDER BY, GROUP BY, and UNION. With Postgres 15, single-column sorting gets a huge boost in performance by switching from tuple-sort to datum-sort. It also has better memory management while sorting which avoids rounding up memory allocation for tuples. This means that unbounded queries use less memory, avoid disk spills, and have better performance. The creators also switched from the polyphase merge algorithm to the k-way merge algorithm. Overall, sorting improvements have up to 400% performance gain.
DISTINCT runs in parallel
To remove duplicate rows from the result, you can use the DISTINCT clause in the SELECT statement, which is a standard operation in SQL. With Postgres 15, you can now perform the operation in parallel instead of doing so in a single process.
Nothing looks different here:
SELECT DISTINCT * FROM table_name;
However, you can adjust the number of workers by changing the value of the max_parallel_workers_per_gather parameter. The expected performance gain can be significant, but it depends on factors such as table size, if an index scan was used, and workload vs. available CPU. This is a welcomed addition to the family of operations that can leverage parallelization, which has been the trend since Postgres 9.6.
Window Function for Performance Gains
Window functions are built into Postgres and it’s similar to an aggregate function, but it avoids grouping the rows into a single output row. They become especially handy when you are trying to analyze the data for reporting. With Postgres 15, you should see performance improvements on the following window functions: row_number(), rank(), dense_rank(), and count().
New Features
Introducing MERGE
A long-awaited command, MERGE, is now available on Postgres 15. From the documentation, “MERGE lets you write conditional SQL statements that can include INSERT, UPDATE, and DELETE actions within a single statement.“ This is the command on Postgres that essentially allows you to “upsert” based on condition, so you no longer need to come up with a workaround using INSERT with ON CONFLICT.
Regular Expressions Enhancement
New functions were added to work with more regular expression patterns. The following four regular expressions were added to Postgres 15: regexp_count(), regexp_instr(), regexp_like(), and regexp_substr(). They all work differently for their own use cases, but here’s an example to perform a count using Postgres 15:
SELECT regexp_count(song_lyric, ‘train’, ‘ig’);
Instead of this example in previous versions:
SELECT count(*) FROM regexp_matches(song_lyric, ‘train’, ‘ig’);
The examples result in the count of how many times the word “train” was mentioned in song lyric(s).
Summary
Postgres 15 brings many benefits to developers. Heroku continues to add value by providing a fully managed service with an array of additional features, giving developers maximum focus on building amazing applications. Please do not hesitate to contact us through our Support team if you encounter issues. As always, we welcome your feedback and suggestions on the Heroku public roadmap.