PostgreSQL11 became available on Amazon Relational Database Service (RDS) in March. Have you tried it? We have, and are here to report all of the awesome enhancements. As a preview, there are major improvements to the table partitioning system, added support for stored procedures capable of transaction management, improved query parallelism, added parallelized data definition capabilities, and just-in-time (JIT) compilation for accelerating the execution of expressions in queries. We’ll now go more in depth about each of these improvements, and by the end of this, trust me, you’ll want to go give it a try!
Improvements to partitioning functionality
- Partitioning can now be created on hashing a key column
- Supports for PRIMARY KEY, FOREIGN KEY, and indexes on partitioned tables
- Partitioned tables can have a “default” partition to store data that does not match any of the other defined partitions
- On UPDATES, rows are moved to appropriate partitions if partition key column data changes
- Faster partition elimination during query processing and execution speeds up SELECT queries
Lightweight and Fast ALTER TABLE for NOT NULL Column with DEFAULT Values
- With this new version, ALTER table doesn’t do a table rewrite when adding a column with non-null default values. This significantly helps when altering tables with millions of records where a new column is added with a default value.
Stored Procedures with Transaction Control
- Finally, Postgres 11 supports creating stored procedures. Prior versions of Postgres supported functions, however, functions cannot run transactions. With the support of Stored Procedures you can now COMMIT and ROLLBACK transactions with the Stored Procedure.
Improvements to Parallelism
- CREATE INDEX can now use parallel processing while building a B-tree index
- Parallelization is now possible in CREATE TABLE…AS, CREATE MATERIALIZED VIEW, and certain queries using UNION
- Hash joins performed in parallel
- Improvements to partition scans to more efficiently use parallel workers
- Sequential scans now perform better with many parallel works
Optimizer
- Selection of the most common values (MCVs) has been improved. MCVs earlier were chosen based on their frequency compared to all common values. In Postgres 11, MCVs are chosen based on their frequency as compared to non-MCV values
- Selectivity estimates for >= and <= has been improved. This improves the performance using BETWEEN
- Improvements to the optimizer row counts for EXISTS and NOT EXISTS queries
Optimal Just-in-Time (JIT) Compilation
- Just-in-Time (JIT) compilation is the process of turning some form of interpreted program evaluation into a native program, and doing so at run time. JIT would be beneficial for CPU bound queries. JIT currently aims to optimize two essential parts of query execution: expression evaluation and tuple deforming.
Expression evaluation is used to evaluate WHERE clauses, target lists, aggregates, and projections. It can be accelerated by generating code specific to each case.
Tuple deforming is the process of transforming an on-disk tuple into its in-memory representation. It can be accelerated by creating a function specific to the table layout and the number of columns to be extracted.
I know you won’t believe it, but these aren’t even all of the benefits of the new PostgreSQL 11. There are so many improvements for Window functions, indexes, and monitoring that would be greatly beneficial. If that doesn’t get you excited, I don’t know what will! The best way to use PostgreSQL 11 is with Amazon RDS. Reach out to our team if you’d like to get started with AWS or want to unlock the full potential of your current environment!
