The developers behind open source SQL database PostgreSQL, long vaunted as both an underappreciated gem and a potential replacement for Oracle, has officially released PostgreSQL version 11.
Current users of PostgreSQL shouldn’t find the changes in the new edition too jarring, but several new and revised features are worth a look—either for future database projects or incremental changes to existing ones.
PostgreSQL has long supported partitioning tables for better performance and easier management. PostgreSQL 11 adds many table-partitioning options, such as supporting “upsert” (UPDATE or INSERT) functions on partitions to simplify database application code, faster queries for partitioned tables generally, and the ability to partition tables using the hash function of a given key, as well as ranges for a column value or a list of possible keys. This last feature includes automatic rebalancing of shards when rows have their hash key altered, so that rebalancing doesn’t have to be done as a regular maintenance task.
Stored procedures in PostgreSQL 11 can now perform their own transaction management. This makes it easier to write database-native functions that perform complex server-side operations like bulk alteration of data.
For faster queries, PostgreSQL 11 offers the option, disabled by default, to perform just-in-time (JIT) compilation on some query expressions, using the LLVM compiler framework. JIT compilation is most useful for queries that are heavily CPU-bound—for instance, when row results are being transformed on the fly.
Benchmarks demonstrate that JIT compilation provides a general speedup of about 20 percent, with performance boosts up to five times when paired with other optimizations. PostgreSQL’s developers have left open the possibility that other operations could be JIT-accelerated in the future as well.
Finally, many data operations in PostgreSQL 11, including table and view creation, now run faster when run in parallel, especially when performed on partitioned data.
