PostgreSQL has entered an exciting new chapter with the release of version 11 (10/18/18). This blog highlights some of the version 11 additions and improvements to PostgreSQL.
** The information shared in this blog was largely gathered from the postgresql.org website as well as a recent public webinar presented by EDB Postgres on 12/18/18.
Partitioning Improvements - Partitioning is a database process that allows for faster data access and processing by dividing very large tables into smaller tables. There are different types of partitioning. In version 10, Postgres included 'List' and 'Range' partitioning. List partitioning is based on the membership of a column value in one of a set of value lists whereas Range partitioning uses contiguous ranges of values.
'Hash' partitioning was introduced in version 11.
Hash partitioning - When organizing data in group form may make less sense (e.g. ProductID), hash keys are used. The partitions are approximately the same size by the data being spaced randomly to evenly space out the load.
'Default' partitions (for List and Range partitions) were also added. This helps ensure successful data insertions if a partition wasn't explicitly created to hold the new row.
Some SQL features previously unavailable for partitioned tables were added. Now, instead of needing to do the additional manual steps to accomplish this on the individual partitions, more of these SQL features can be used against the partitioned tables including creating Indexes, Foreign Keys, and Triggers.
In version 11, rows can be updated from one partition to another.
Partition pruning efficiency was improved. So, SELECT performance is improved for queries that can benefit from partition pruning.
Parallelism Improvements - Version 11 builds upon the capability to process queries in parallel.
Parallel creation of Indexes (~2-3x improvement). Now, there is a shorter wait for the creation of large indexes.
Queries (like CREATE Table As) that populate newly created relations can now be run in parallel.
Performance was improved for parallel sequential scans too.
Already available for EDB Postgres Advanced Server for some time, Stored Procedures have been introduced in PostgreSQL version 11. These SQL procedures add the ability to perform full transaction management within the body of a function.
Just-In-Time Compilations of SQL Expressions - With the introduction of support for Just-In-Time (JIT) compilation, execution is accelerated for certain expressions (in WHERE clauses, target lists, aggregates etc.) during query execution.
Check out the postgresql.org site or www.enterprisedb.com for more information.
And, if you're "turning up the volume" on your PostgreSQL adoption and usage, visit https://www.quest.com/products/foglight-for-postgresql/ to learn about monitoring your PostgreSQL databases with Foglight.
** The blog title ("These go to eleven.") is from a line by the character, Nigel in the movie, This Is Spinal Tap.
