| PostgreSQL 11 RC Available |
| Written by Kay Ewbank |
| Monday, 15 October 2018 |
|
PostgreSQL 11 is here - well, it will be within days, and the release candidate is available for download now. PostgreSQL is an open source database system that was originally created at the University of California, Berkeley. It is now maintained and developed by the PostgreSQL Global Development Group, a coalition of many companies and individual contributors. The current download is the first release candidate, which the developers say should be identical to the initial release of PostgreSQL 1. That is due for release next week. The big changes to the new version are in the areas of partitioning and parallelism. The improved support for partitioning in PostgreSQL comes via support for unique indexes and foreign keys. More importantly, when you create a new partition, a new equivalent index is automatically created. You can now partition using a hash key, meaning administrators no longer need to manage partitions manually in situations where it isn't possible to uniformly separate rows into partitions, and there's a "default" partition for storing data that does not match any of the remaining partitions. There are also improvements when you're using partitioned tables. For example, if you run an Update statement that changes a partition key, the affected rows are now moved to the correct partition for the key. The performance of Select statements when used on partitioned tables has also been improved thanks to better partition elimination strategies during query processing and execution. You can make use of triggers on partitioned tables, and you can write queries that have a Group By clause that contains the partition key. The next main improvement is the support for parallelism. This was introduced in PostgreSQL 9, but is now much stronger. You can now create indexes on tables in parallel, and this will be used for B-Tree index builds where the software thinks a parallel build would be better given resource limits. The developers say that being able to use parallel index creation should noticeably improve performance as the process is CPU bound. Building a parallel B-Tree index should be two to three times faster than doing the same thing without parallel working. Parallel-aware hash joins are also now supported. There was a limited version of this in earlier releases, but the updated version stores the hash table in shared memory and builds the join in parallel. Query plans that build a large hash table in parallel are now much faster. Query performance has also been improved by the addition of JIT compilation of queries. This was added to PostgreSQL 10 but has been improved. The JIT compiler is LLVM-based, and while many operations work faster, others have been shown to be slower, so the JIT compiler is optional and won't be enabled by default. When a query is JIT compiled, optimized intermediate representation bitcode IR is generated. The optimization process has access to the full context of which operators are applied to which columns, the shape of tuples, and whether or not constant folding may be possible. Another improvement to this version is support for Include indexes where extra columns are included in the index though they are not used for comparisons; used carefully this feature can increase the number of index-only scans.
More InformationRelated ArticlesPostgreSQL Improves Declarative Partitioning PostgreSQL Adds Parallel Query Support PostgreSQL Plus Cloud Database To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin. 
Comments
or email your comment to: comments@i-programmer.info |
