Main Features in PostgreSQL v12
PostgreSQL v12 comes with some significant improvements in regards to performance, space utilization, security, and monitoring. Let’s have a look at some of the most exciting new features:
Enhanced Partitioning Performance
Even though v11 included some improvements in the partitioning department, the work has been furthered in the latest version. Users looking to migrate data from partitioned databases can now get thousands of partitions processed simultaneously. If your tables have thousands of partitions, but you want to source data from only a limited set, you can now do that with a demonstrable performance boost.
The ALTER TABLE ATTACH PARTITION command now has fewer locking requirements on the table. Moreover, adding data to partitioned tables (via INSERT and COPY commands) is no longer a resource-intensive task.
B-tree Performance Improvements
B-trees, the standard way of indexing on Postgres, have also been optimized for efficient handling of workloads that have frequently modified indexes. Overall, a 40% reduction in space utilization has been reported.
Other Performance Improvements
- Just-In-Time compilation (JIT), a feature that has traditionally been known for massive performance boosts (think Java) is now enabled by default in Postgres v12.
- The ALTER TABLE ... SET NOT NULL command now only performs the least number of required table scans.
- String to Integer (Int2 and Int4) conversions have been sped up.
- Calls to GREATEST and LEAST are pre-evaluated in case constants are passed as arguments.
- UNION ALL queries that only have a single child have been optimized.
Common Table Expressions (CTE)
PostgreSQL has been lacking a proper implementation of common table expressions (making the WITH query inline) forever. Finally, in the latest version, new keywords MATERIALIZED and NOT MATERIALIZED have been introduced, which let the user decide whether they want to inline their CTEs with the outer queries, or not.
JSON Path Expressions
V12 is the first version to provide support to query JSON documents using standardized JSON path expressions. This highlights the PostgreSQL team’s adherence to their motto of conformance with the SQL standard. These queries can harness any of the available indexing techniques (for JSONB format documents) for efficient retrieval of data.
Authentication
A database is nothing if not secure; the PostgreSQL team has always known this. In the latest version, improvements have been made to make secure authentication a bit easier to set up:
- If you compile Postgres with OpenLDAP support, you can discover LDAP servers using DNS service (SRV) records.
- GSSAPI authentication support has been added. This encrypts all TCP/IP connections.
- Users can now define an additional authentication mechanism in the pg_hba.conf file which complements the clientcert=verify-full feature.
Monitoring
A few worth-mentioning monitoring improvements are as follows:
- If you want to log statements from only a specific number of transactions, you can now do that using the log_transaction_sample_rate parameter.
- A counter to record checksum failures can be added to pg_stat_database.
- Use the function pg_ls_archive_statusdir() to list the contents of the archive directory.
- Application name can be included in the logs.
- Progress reporting of REINDEX and CREATE INDEX operations have been added. It can be seen from the pg_stat_progress_create_index view.
Concurrent Re-indexing
Concurrent indexing has been a hallmark of Postgres databases over the years, but now, the development team has also added support for concurrent re-indexing. Using the REINDEX CONCURRENTLY command, indices can be rebuilt without blocking writes. No long downtimes for tables with lots of indices to rebuild.
Internationalization
In their bid to promote internationalization, the Postgres team has included support for nondeterministic collations (two strings can be equal even if they have different sets of bytes). This is particularly relevant for people looking for accent-insensitive or case-insensitive comparisons. All you have to do to use the feature is add deterministic = false while running the CREATE COLLATION command.
ElephantSQL - PostgreSQL as a Service
ElephantSQL automates every part of the setup and running of PostgreSQL clusters and automated backups are performed every day, which is stored in a cloud file storage so that they are always accessible to you. We provide 24/7 support to thousands of customers. If you don’t already have an ElephantSQL account, visit our plan site for more information.
To see a complete list of added features, please visit the official release notes here.
If you have any questions, you can reach out to support@elephantsql.com.
Best, ElephantSQL Team