Each year there a new version of PostgreSQL is released. That’s the moment in which many new features and improvements become available for all users. During the last PostgreSQL meetup in Amsterdam, Devrim Gündüz, major contributor and passionate PostgreSQL consultant, explained the upcoming release of PostgreSQL 12. In this blog we list the most important changes in PostgreSQL 12 for everyone who does not closely follows all the commits in Github or who did’nt have the opportunity to visit the PostgreSQL Usergroup NL meetup.
Enabling JIT by default
- Just In Time compilation is one of the strong features of PostgreSQL. Compiling functions during the execution of long-term CPU bound queries (analytical queries) can give significant performance differences.
- The plan output shows when JIT is being used. This output is also enriched in PostgreSQL 12.
- Do not be shocked when you install Postgres on RedHat, CentOS or Fedora. An additional dependency is installed: postgresql12-llvmjit.
- An initial start has been made to use other storage engines.
- In later releases, starting from PostgreSQL 13, we expect it to become more useful, as is often the case with new features in PostgreSQL.
DOS prevention for some commands
- A few months ago it emerged that a VACUUM FULL, TRUNCATE and REINDEX can have a Denial of Service. The latter is solved with a patch on PostgreSQL 11. The rest is now solved in PostgreSQL 12. For details, see https://paquier.xyz/postgresql-2/postgres-12-dos-prevention/
- Since the implementation of partitioning in version 10, a lot of energy has been invested in this topic. The improvements are now in making 2 functions available for information about the partitoning tree:
Integration of recovery.conf in postgresql.conf
- PostgreSQL 12 gives an error when recovery.conf is found. This adjustment has a number of consequences for the way in which restores, recovery and trigger files can be handled. For example, the trigger_file has been renamed to promote_trigger_file to be more explanatory. You only need a reload to adjust archive_cleanup_command, promote_trigger_file, recovery_end_command and recovery_min_apply_delay.
Other PostgreSQL performance improvements
Furthermore, there are many different small performance improvements and usability enhancements in the areas of psql and psql tab complete, SKIP_LOCKED for VACUUM, ANALYZE and vacuumdb options for xid and mxid. Too many to list. The complete overview can be found at: https://bit.ly/2VxWUF7
All in all, there will be many great features in the coming release. PostgreSQL 12 Beta is expected to be available at the beginning of the summer. Until that time you can follow the development in the development version on GitHub. Do you want to know more about the new features of PostgreSQL and the practical implementation? Please contact us for information, or visit a PostgreSQL meetup or conference.