The latest and greatest release of PostgreSQL, v11, is due out later this week! Here is a quick look at the new features in this version.
The biggest set of improvements are for partition support. Postgres 11 supports:
- updating rows can move them across partitions
- default partitions
- automatic index creation
- foreign key constraints are supported on partitioned tables
- unique indexes
- aggregation push down to partitions
- paritition by hash
- partition children on remote servers (postgres_fdw) can be updated
- INSERT..ON CONFLICT is supported on partitioned tables
- more efficient query plans and faster query execution
Read more about these improvements here.
There are a bunch of improvements across the board related to increased parallelism. Joins, unions, creation of tables and views with “CREATE..AS SELECT”, btree index creation and more benifit from these changes.
Here is an article on index creation performance improvements in Postgres 11.
JIT Complied Queries
Although disabled by default in 11, LLVM-based JIT compilation of queries is now built into Postgres. The server can now choose to compile certain queries (those with execution estimates higher than a threshold) using LLVM, resulting in faster queries. This compilation is automatic and transparent to the application.
Here are some numbers.
Covering indexes, available in other RDBMSes but not PostgreSQL, has been added now. With this, it is possible to have an index like this (new syntax):
Such an index includes extra attributes (here “empname”) in the index itself, and makes it possible to satisfy queries like:
entirely using index-only scans and not touch the heap at all.
Read more about covering indexes here.
Stored procedures are yet another instance of PostgreSQL playing catch up to other RDBMSes. They are of course, similar to stored functions that PostgreSQL has supported since long, but the one big thing possible only using procedures is transaction control. You can commit and rollback transactions from within stored procedures.
Have a look at the CREATE PROCEDURE documentation.
Adding Columns to Tables
Altering a table to add a new NOT NULL column with a DEFAULT value was a bit of a pain in PostgreSQL, as this caused the server to rewrite the entire table. With v11 it is now possible to have DDL statements like this:
execute in constant time. Rows are not touched when this executed, and are instead updated “lazily”.
Read more about this feature here.
And finally, those of you who used pgcrypto only for SHA functions can now switch to using the ones built-in into PostgreSQL 11:
pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics. pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, alerting, teams and more.
The Diagnostics feature in pgDash examines your PostgreSQL server and databases scanning for potential issues that can impact the health and performance of the deployment. No additional setup is required for Diagnostics - you just need to be actively sending in data to pgDash. Learn more here or signup today for a free trial.