PostgreSQL 11 Beta 1 Released!

Posted on 2018-05-24 by PostgreSQL Global Development Group

The PostgreSQL Global Development Group announces that the first beta release of PostgreSQL 11 is now available for download. This release contains previews of all features that will be available in the final release of PostgreSQL 11, though some details of the release could change before then.

In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 11 in your database systems to help us eliminate any bugs or other issues that may exist. While we do not advise for you to run PostgreSQL 11 Beta 1 in your production environments, we encourage you to find ways to run your typical application workloads against this beta release.

Major Features of PostgreSQL 11

PostgreSQL 11 major features center around performance, working with larger data sets, and ease-of-use.

Major Improvements to Partitioning

PostgreSQL 11 contains several features that improve working with data in partitions, including:

  • The ability to partition by a hash key ("hash partitioning")
  • UPDATE statements issued to a partition key now move affected rows to the appropriate partitions
  • Improved SELECT query performance due to enhanced partition elimination during query processing and execution as well as parallelized partition scans
  • Support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitions

and more.

PostgreSQL 11 also introduces the ability for a query to distribute grouping and aggregation to partitioned tables before the final aggregation. This is disabled by default, to enable, you need to set enable_partitionwise_aggregate = on in your configuration file or during your session or transaction.

Improvements to Parallelism

Several features added to PostgreSQL 11 take advantage of the parallel query infrastructure and provide significant performance enhancements, including:

  • Parallelized hash joins
  • Parallelized CREATE INDEX for B-tree indexes
  • Parallelized CREATE TABLE .. AS, CREATE MATERIALIZED VIEW, and certain queries with UNION

SQL Stored Procedures

PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure. Procedures can be created using the CREATE PROCEDURE command and executed using the CALL command.

Just-In-Time (JIT) Compilation Introduced

PostgreSQL 11 introduces support for Just-In-Time (JIT) compilation to optimize the execution of code and other operations at run time. Using components of the LLVM project, the introduction of JIT support in PostgreSQL accelerates queries that utilize expressions (e.g. WHERE clauses), target lists, aggregates, projections, as well as some internal operations.

The JIT framework in PostgreSQL is designed to allow future work for optimizations that can be added in the future. If you are building PostgreSQL 11 from source, you can enable JIT compilation using the --with-llvm flag.

Window Functions Fully Support SQL:2011 Standard

As of PostgreSQL 11, window functions now support all options shown in the SQL:2011 standard, including RANGE _distance_ PRECEDING/FOLLOWING, GROUPS mode, and frame exclusion options.

Channel Binding for SCRAM Authentication

The previous version of PostgreSQL introduced SCRAM authentication to improve the storage and transmission of passwords using an accepted standard protocol. PostgreSQL 11 introduces channel binding for SCRAM authentication, which adds to the security of using SCRAM by preventing man-in-the-middle attacks.

User Experience Enhancements

The PostgreSQL Global Development Group recognizes that while PostgreSQL itself contains a robust set of features, not all of them may be easy-to-use and could require workarounds in both development and production environments.

Prior to PostgreSQL 11, one such feature was using the ALTER TABLE .. ADD COLUMN command where the newly created column had a DEFAULT value that was not NULL. Prior to PostgreSQL 11, when executing aforementioned statement, PostgreSQL would rewrite the whole table, which on larger tables in active systems could cause a cascade of problems. PostgreSQL 11 removes the need to rewrite the table in most cases, and as such running ALTER TABLE .. ADD COLUMN .. DEFAULT .. will execute extremely quickly.

Another feature that fell into this category was the inability to intuitively quit from the PostgreSQL command-line (psql). There has been numerous recorded complaints of users trying to quit with the quit and exit commands, only to learn that the command to do so was \q.

We have heard your frustrations and have now added the ability to quit the command-line using the keywords quit and exit and hope that quitting a PostgreSQL session is now as enjoyable as using PostgreSQL.

Additional Features

Many other new features and improvements have been added to PostgreSQL 11, some of which may be as or more important to specific users than what is mentioned above. Please see the Release Notes for a complete list of new and changed features.

Testing for Bugs & Compatibility

The stability of each PostgreSQL release greatly depends on your, the community, to test the upcoming version with your workloads and testing tools in order to find bugs and regressions before the release of PostgreSQL 11. As this is a Beta, minor changes to database behaviors, feature details, and APIs are still possible. Your feedback and testing will help determine the final tweaks on the new features, so please test in the near future. The quality of user testing helps determine when we can make a final release.

A list of open issues is publicly available in the PostgreSQL wiki. You can report bugs using this form on the PostgreSQL website:

https://www.postgresql.org/account/submitbug/

Beta Schedule

This is the first beta release of version 11. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2018. For further information please see the Beta Testing page.

Links

PostgreSQL Project