On October 18th, 2018, the PostgreSQL Global Development Group released PostgreSQL v11, which is the latest version of the ORDBMS. In this blog post you’ll find the perks of this version and how it made improvements to the usage of PostgreSQL (as a service.) The new release majorly focuses on enhancing performance and ease-of-use. It also makes working with large datasets easier.
Main features of PostgreSQL v11
Enhanced parallelism
One of the most significant performance enhancements in v11 can be credited to parallel query execution. By introducing parallelized hash joins and parallelized queries, including CREATE MATERIALIZED VIEW, CREATE TABLE .. AS and some UNION queries, significant speedups are guaranteed.
Partitioning improvements
PostgreSQL v11 has also improved the process of data partitioning via these new features (among others):
- Hash partitioning: The ability to divide data across different partitions based on a hash key.
- Significant improvements in SELECT query performance are obtained by more efficiently skipping partitions during query execution.
- Partitions will now also support PRIMARY and FOREIGN keys, triggers and indexes.
- Ability to move all rows associated with a partition key to the correct partitions, using the UPDATE statement.
Introduction of Just-In-Time (JIT) compilation
Added to v.11 is the Just-In-Time (JIT) compilation, which makes another reason to expect performance improvements. JIT allows code execution to be optimized by accelerating queries that use expressions, aggregates, projections and target lists, in addition to certain internal operations. Components from the LLVM compiler infrastructure project have been used for this purpose.
If you are using the source code to build PostgreSQL 11, you can use the -llvm flag while compiling, to enable JIT compilation.
SQL stored procedures with embedded transactions
SQL stored procedures can now be created via the CREATE PROCEDURE command. These procedures will allow developers to embed transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure. To execute a SQL procedure, the CALL command can be used.
User Experience improvements:
PostgreSQL has a very extensive feature set, but a few of these features can occasionally be difficult to access/use. Thus, the PostgreSQL Global Development Group decided to make the system more usable for the community in v11.
To that end, the following enhancements have been made:
- The execution flow of the ALTER TABLE .. ADD COLUMN command has been improved. Previously, whenever the aforementioned command was run, in a scenario where the added column had a not-null default value, the whole table was rewritten. This could cause serious performance setbacks for large tables in live systems. V11 eradicates the need to rewrite the whole table in a majority of the cases. Simply executing ALTER TABLE .. ADD COLUMN .. DEFAULT .. will do the job a lot quicker in the new version.
- Now you can use both quit and exit commands to close a psql session. Prior to v11, there was no intuitive way to quit from psql, the PostgreSQL command line. Users complained a lot when they failed to quit using the exit and quit commands (the actual command was \q).
Channel binding for SCRAM authentication
SCRAM or Salted Challenge Response Authentication Mechanism authentication was a major feature of the last PostgreSQL release. It ensured safer and more reliable storage and transfer of passwords. In v11, channel binding support has been added in SCRAM. This enhances the security even further by preventing any possible man-in-the-middle attacks.
In this article, we talked about some of the worth-mentioning features of the PostgreSQL v11 release. There are various other features that also come as part of the release; to see a complete list of new features and changes, you can see the official release notes here.
If you have any questions, you can reach out to support@elephantsql.com.