Why PostgreSQL 11?
Explore the highlights of the PostgreSQL 11 release
PostgreSQL 11 was released in October 2018, and includes a number of enhancements to performance, robustness, transaction management, query parallelism, and more. PostgreSQL 11 makes significant improvements to the table partitioning system, adds support for stored procedures capable of transaction management, improves query parallelism, adds parallelized data definition capabilities, and introduces just-in-time (JIT) compilation for accelerating the execution of expressions in queries.
Let's take a look at these features, and see how Amazon RDS, a fully managed database service, offers the benefits of PostgreSQL 11 while reducing administrative burden so you can focus on your applications.
What's New in PostgreSQL 11
PostgreSQL 11 includes a number of key partitioning improvements. Version 11 introduces hash partitioning, the ability to partition by hash key, which adds to the current ability to partition data in PostgreSQL by a list of values or by a range. PostgreSQL 11 also brings improvements to partitioning management, including the ability to create primary keys, foreign keys, indexes, and AFTER triggers on partitioned tables that are passed down to all partitions. Version 11 improves query performance when reading partitions, with faster partition pruning and partition pruning at execution time. Finally, PostgreSQL 11 brings data federation improvements, including the ability to shard data using the postgres_fdw module.
PostgreSQL 11 adds a number of performance improvements for parallelism. PostgreSQL 11 enables parallel hash joins and improves the performance of parallel sequential scans. PostgreSQL 11 allows you to execute SELECT queries that use UNION in parallel even if the underlying queries are unable to be parallelized. Version 11 adds parallelism to several additional data definition commands, including CREATE INDEX (for generating B-tree indexes), CREATE TABLE .. AS, SELECT INTO, and CREATE MATERIALIZED VIEW. Version 11 also introduces Just-in-Time (JIT) compilation, which speeds up expressions during query execution, including WHERE clauses, target lists, aggregates, and projections.
Stored Procedures with Transaction Control
PostgreSQL 11 introduces the ability for stored procedures to provide transaction management for users to commit and roll back transactions. User-defined functions have been supported in PostgreSQL for more than 20 years, but prior to PostgreSQL 11, these functions were unable to manage their own transactions. PostgreSQL 11 adds SQL procedures that can perform transaction management within the body of a function, enabling developers to create more advanced server-side applications, such as those involving incremental bulk data loading. SQL procedures can be created using the CREATE PROCEDURE command, executed using the CALL command, and are supported by the server-side procedural languages PL/pgSQL, PL/Perl, PL/Python, and PL/Tcl.
For a deeper dive into the PostgreSQL 11 release, check out the PostgreSQL community documentation.
Amazon RDS for PostgreSQL
Amazon Relational Database Service (RDS) supports PostgreSQL major version 11. Amazon RDS for PostgreSQL makes it easy to set up, operate, and scale PostgreSQL deployments in the cloud.
With Amazon RDS, you can deploy scalable PostgreSQL databases in minutes with cost-efficient and resizable hardware capacity. Amazon RDS manages complex and time-consuming administrative tasks including software installation and upgrades; storage management; replication for high availability and read throughput; and backups for disaster recovery. See Amazon RDS for PostgreSQL Pricing for pricing details and regional availability. To use the new version, you can create a new Amazon RDS for PostgreSQL database instance with just a few clicks in the AWS Management Console.
In this tutorial, you will learn how to create an environment to run your PostgreSQL database (we call this environment an 'instance'), connect to the database, and delete the DB instance. We will do this using Amazon Relational Database Service (Amazon RDS) and everything done in this tutorial is free-tier eligible.
If you have an existing PostgreSQL deployment that you want to move to Amazon RDS, the complexity of your task depends on the size of your database and the types of database objects that you're transferring. This guide will help you navigate your options for migrating data to Amazon RDS for PostgreSQL, including native PostgreSQL database migration tools and AWS Database Migration Service .
Set up, operate, and scale a relational database in the cloud with just a few clicks.
Instantly get access to the AWS Free Tier.
Get started building with Amazon RDS in the AWS Console.