Today PostgreSQL® version 11 was released. The new release brings improvements in many areas.
Since version 9.6 query plans can be executed on multiple CPU cores in parallel, this is now supported for other plan types, especially the creation of B-tree indexes. Sequential scans and UNION queries have also been improved.
Brand new is the possibility to optimize queries via Just-in-Time Compilation (JIT). When PostgreSQL® is compiled, the source code is stored as LLVM bit code. When executing a query whose planner cost exceeds a limit, libllvm then translates this bit code into native machine code specifically for that query. Since all used data types etc. are known in advance, the machine code eliminates all case distinctions that are generally necessary. The feature is disabled by default and can be enabled with “SET jit = on;”. In PostgreSQL® 12 it should then be active by default.
Until now only functions could be defined on SQL level. New are now procedures which can manage BEGIN/COMMIT independently. Batch operations can now be completely transferred to the database side.
Table Partitioning section has been further improved to support hash partitioning. Integration with partitions using postgres_fdw has also been improved. It is now possible to create a default partition that holds data that does not fall into any of the existing partitions.
This Amp goes to 11!
Other improvements include the ability to add columns with default values to tables without having to rewrite the table completely. “Covering Indexes” allows more index accesses than “Index Only Scan”. Window functions now also support the RANGE and GROUPS keywords.
More information can be found in the release announcement of the PostgreSQL® project.
The shirt is available for free at our booth. If you don’t attend the conference, you can order a shirt. The profits will be donated to the PostgreSQL® project.
About the author
Christoph Berg ist als Senior Berater im credativ Datenbank-Team tätig. Als Debian-Developer und PostgreSQL-Contributor kümmert er sich außerdem um die PostgreSQL-Paketierung und andere Belange im Debian Quality-Assurance-Team. In der Freizeit ist er Funkamateur auf Kurzwelle und Satelliten.