After a successful evaluation period, PostgreSQL 12 is now the default version for new Heroku Postgres databases and an available upgrade for existing databases.
I want to emphasize a few key changes and improvements in Postgres 12:
Native Table Partitioning Concurrent Operations
Native Partitioning was introduced in PostgreSQL 10 and performance improvements for improved parallel processing were added in PostgreSQL 11. Updating tables, altering partitions blocking queries, and executing concurrent operations for Native Partitioning were all improved in PostgreSQL 12. New features include allowing tables to modify partitions without blocking queries, allowing foreign keys to reference partitioned tables, and adding introspection functions to understand partitioned tables through their lifecycle.
Reduced Overhead for Indexing
Indexing is a crucial performance component of PostgreSQL, so we are excited to see PostgreSQL add REINDEX CONCURRENTLY options for all indexes that no longer block writes to tables. When users need to alter their indexing structures, pg_stat_progress_create_index progress function shows CREATE and REINDEX operations progress.
For customers using full-text searching or natural clustered data indexing, PostgreSQL 12 writes less information to the Write-Ahead Logs during index creation. GiST, GIN, and SP-GiST indexes have been updated to require less WAL and disk overhead usage during their creation. Additionally, VACUUM operations for these indexes were hardened to clean up more space as data changes in the underlying tables occur, maintaining performance and reducing space costs for your PostgreSQL database.
Generated Columns
In some cases, column data may be helpful during relational or informational operations. With space being the chief commodity in databases, we are excited for the introduction of Generated Columns to PostgreSQL 12. This enables columns to be computed from other columns when read and can be utilized similar to materialized views using the STORED keyword. There are a few limitations to consider when examining this new feature, so please be sure to read the new documentation to find new ways to optimize and save space on your database.
Non-Backward compatible items removed
There are some breaking changes introduced into PostgreSQL. Please make sure your database is capable of migrating to supported features before attempting to upgrade to PostgreSQL 12.
Removed data types:
- abstime
- reltime
- tinterval
Extensions dropped:
- timetravel
Note the changes made to CTE materialization too.
Get Started Today
If you want to try out the new version, provision a new database:
$ heroku addons:create heroku-postgresql -a sushi
$ heroku pg:info DATABASE_URL -a sushi
=== DATABASE_URL
Plan: Hobby-dev
Status: Available
...
PG Version: 12.0
...
For further details about other new features in PostgreSQL 12, please see the PostgreSQL documentation. Let us know what you think at postgres@heroku.com.