After a successful two-month Beta period, PostgreSQL 11 is now the default version for all new provisioned Heroku Postgres databases. All Postgres extensions, tooling, and integration with the Heroku developer experience are ready to use, giving you the power of PostgreSQL 11 with the ease and usability of Heroku for building data-centric applications.
We'd like to re-emphasize a few features - among the many released in Postgres 11 - that we are particularly excited about.
Native Table Partitioning Additions
Introduced in Postgres 10, native table partitioning enables more performant queries, data segmentation and faster access to large growth tables. In Postgres 11, native table partitioning features have been expanded to now include default partitions, foreign table inheritance for partitions, and general support for primary keys, foreign keys, indexes, and triggers.
A pattern we often see in databases in our fleet is one or two tables growing at a rate that’s much larger and faster than the rest of the tables in the database. Query times within the application will start to rise, bulk loads will take longer, and creating indexes can take a long time. Making use of patterns such as Year/Month (YOMO) and Hash Key partitions have been streamlined in Postgres 11. Even without explicitly setting partitions, Postgres 11 will perform default partitioning to improve baseline query performance without any additional steps from creating tables.
Postgres table partitioning can be a great way to maintain good query performance for very large tables by partitioning what is logically one big table into smaller physical pieces. You can visit Postgres 11 Partitioning to dive deeper into the enhancements.
Introduction of Stored Procedures
Postgres has long supported functions to handle more complex workloads. Functions have, unfortunately, the drawback of not supporting transactions. Postgres 11 introduces Stored Procedures, bringing closer feature parity to the SQL standard and enabling Postgres users to trigger ACID data manipulation. You can visit Postgres 11 Stored Procedures to learn more.
Parallelized Indexing and Joining Improvements
Postgres 11 includes improved parallelism for more types of joins, scans, indexing and queries. A notable example is the addition of parallelism to the CREATE INDEX command that generates B-tree indexes. B-tree indexes are now able to use additional columns as "non-keys" in the index scans. This means that querying additional columns not in your B-tree index can still utilize that same B-tree index for faster query times on these tables.
The biggest improvement in parallel joins, now scales significantly across multiple tables, indexes and data types for some of the heaviest, most complex usage out there. Execution plans and analytics for queries that perform aggregation across many tables will see much more efficient use of the database and faster turnarounds because of the fully-integrated CPU utilization now added in Postgres 11.
Customer Success Spotlight: Quikly
Many Heroku customers are already taking advantage of Postgres 11's features to better work with advanced data use cases. Heroku customer Quikly is an engagement marketing platform that is using Postgres 11's native table partitioning to deliver insights from time series data to its customers and internal product teams—
“We wanted to store a lot of time-series data from events on our platform in order to provide client-facing reports on campaign performance as well as provide some behavioral analytics to our internal product team.
We capture a stream of events from our platform and use Postgres 11’s native partitioning along with pg_partman to partition our events table by month. Anytime we query over events during a period of time, Postgres can efficiently scan just the tables it needs and the indexes remain small and fast, even with a large dataset. We were able to use use follower databases to test out a few different database schemas with production data before settling on one that worked best and roll it out without any downtime. As our data set grows or our queries get more complex, we know it’s painless to scale up to more storage and performance.” —Scott Meves, Chief Technology Officer, Quikly
Get Started Today
If you want to try out the new version, it's is as simple as provisioning a new database:
$ heroku addons:create heroku-postgresql -a sushi
$ heroku pg:info DATABASE_URL -a sushi
=== DATABASE_URL
Plan: Hobby-dev
Status: Available
...
PG Version: 11.2
...
If you have an existing database on the platform, we encourage you to look into upgrading your Postgres version: see our documentation for upgrading. For further details about other new features in PostgreSQL 11, please see the PostgreSQL documentation. Let us know what you think at postgres@heroku.com.