In my previous article, I had shared some very interesting features of PostgreSQL 10. After almost a year of its release, the community has now come up with PostgreSQL 11, which has bought in some new, interesting features. And, you certainly wouldn’t want to miss taking a note of these features. I will also be using screenshots as required, to showcase it better.

1. PARTITIONING FUNCTIONALITY COMES WITH A MAJOR IMPROVEMENT:
   Native partitioning syntax was first made available in Postgres 10, which ended up making partitioning syntax, quite simple. And, now in the latest release, hash based partitioning is supported. Let me show you an example of this below:

When the data in inserted in tables, it goes to appropriate partitions based on the hash value of partition key.

In the above example, if you noticed – the data goes to specific partitions based on their hashed values of input data and hence the row for sales_id=973 is residing in sales_5 partition whereas for sales_id=970, the corresponding row is inserted in sales_1 partition. So in Hash partition rows will go to specific partition based on calculated hash value  and two successive input values may resides in different partition based on its corresponding hash value.

2. DEFAULT PARTITION:

This has been a long-awaited feature, especially for those who have used a similar feature in Oracle. Starting version 11, it is now possible to create a default partition whether it is for range or list partitioning. If the row that is being inserted does not fit into existing partitions, then it will go into a default partition.

If I insert the rows, they will go to appropriate partition whereas rows which doesn’t match any partitioning key value will go to default partition. As you can see in the below case, the last Insert for date 30-05-2019 will go to default partition.

3. INDEX ON PARTITION:

In PostgreSQL 11, one can create index on the base table and then indexes on all partitions will be created automatically. The index will also be created on new partitions, if you create any in the future.

4. PARTITION KEY UPDATE

Until version10, if the update command changes the partition key of the row and if a new value requires the row to be migrated to a new partition then the update would fail with error. This is no longer an issue in Postgres 11, where the update statement would move the row to the new partition. While at the backend, the corresponding row would be deleted from the existing partition and will be inserted in the new partition.

5. FOREIGN KEYS

PostgreSQL 11 has also added a support for Foreign key and Primary key. In the earlier versions, it was not possible to have a column in the partitioned table as a Foreign key. But now, Foreign keys are allowed in PostgreSQL 11.

6. QUERY PARALLELISM IMPROVEMENT

The Parallelism was first introduced in Postgres 9.6 and since has been evolving with every release. The Parallel index scan was introduced in version 10 and now the latest release 11 has a parallel hash for an efficient scan of partitioned tables.
Even the B-tree index creation can be parallelized with the addition of parallel clause in CREATE INDEX command. PostgreSQL can utilize multiple CPUs to process the rows faster while building the index that makes the parallel build faster.
Several other DDL commands like CREATE TABLE AS, CREATE MATERIALIZED VIEW can also be executed in parallel making object creation faster.

7. JUST-IN-TIME COMPILATION

PostgreSQL 11 has introduced a Just-in-Time compilation that helps improve the execution of certain expressions during the query implementation.

8. PROCEDURE SUPPORT FOR TRANSACTIONS

PostgreSQL11 also has added support for SQL procedures that can do full transaction management within the body of a function. CREATE PROCEDURE syntax can be used for procedure creation and executing using the CALL command.

9. FASTER COLUMN ADDITION WITH DEFAULT VALUES

Though, alter table ADD COLUMN feature has been there for a long time, the addition of new columns with non-null default values needed a new copy of table to be created and it required the exclusive locks that would not allow any further transactions on the table until the lock is released, making this DML operation expensive.  For large data volume, the copy would also take more time. With this latest release, this has changed. Adding a column is almost instant now.
The Community keeps on enhancing the current features and keeps adding newer features. To sum it up, with PostgreSQL 11, the focus is majorly on performance improvement through the addition of many partitioning features, JIT compilation and improvement in query executions.
If you’d like to experience this update yourself, EDB Postgres is hosting a webinar on: Introduction to EDB Postgres, including BART, EFM, and Containers with a brief demo. Check it out here.
Cheers to many more new and shiny things in 2019! Happy holidays! :)