Earlier this month, PostgreSQL 10.0 was released. Today, we are excited to announce PostgreSQL 10 is available in beta on Heroku, bringing a number of notable feature and performance improvements to our managed PostgreSQL database service.
The beta provides customers who want to try out the new release an easy way to do so, while customers who are happy with the current version can continue to stay on version 9.6 until we make version 10 generally available. Also, new databases will continue to default to version 9.6 until we release version 10 to GA.
While PostgreSQL 10 has many feature and performance benefits, we’d like to highlight several that we are most looking forward to:
Performance Improvements
Most Heroku Postgres users will notice a performance boost when using PostgreSQL 10 for certain types of queries. The introduction of improved parallel queries, which optimize common types of JOINs and table scans, will result in faster queries on many kinds of datasets.
Additionally, the introduction of multivariate statistics objects enables users to significantly enhance query performance on any datasets which contain correlated data: STATISTICS objects can be used to let the query planner know about relationships within your data. This section of the PostgreSQL 10 manual explains the feature in more detail.
Native Table Partitioning
You may know we support table partitioning on previous PostgreSQL versions through the pg_partman extension which allows both time-based and serial-based table partition sets. By automatically segmenting data into seperate physical stores, Postgres table partitioning can be a great way to keep good query performance for very large tables. In PostgreSQL 10, users will have the option to leverage native table partitioning.
PostgreSQL 10 native partitioning is possible by column but also by any arbitrary expression: in the following example we partition the users table by the first letter of a user's name.
CREATE TABLE users (
id serial not null,
name text not null,
created_at timestamptz not null
)
PARTITION BY RANGE ( LOWER( LEFT( name, 1 ) ) );
CREATE TABLE users_0
partition of users (id, primary key (id), unique (name))
for values from ('a') to ('f');
CREATE TABLE users_1
partition of users (id, primary key (id), unique (name))
for values from ('f') to ('z');
INSERT INTO users (name, created_at)
VALUES ('camille', now()),
('greg', now());
SELECT * FROM users_0;
id | name | created_at
----+---------+-------------------------------
1 | camille | 2017-10-13 18:45:17.882299+00
(1 row)
SELECT * FROM users_1;
id | name | created_at
----+------+-------------------------------
2 | greg | 2017-10-13 18:45:17.882299+00
(1 row)
You can find more information on PostgreSQL native partitioning and its limitations on the PostgreSQL wiki.
Other improvements and features we are excited about include replicated hash indexes and transaction status checking.
Getting Started and Caveats
You can add a Postgres 10 instance via the CLI:
heroku addons:create heroku-postgresql --version 10
heroku pg:info DATABASE_URL
=== DATABASE_URL
Plan: Hobby-dev
Status: Available
...
PG Version: 10.0
...
At present, all Heroku Postgres extensions - except for pg_partman, plv8 and redis_fdw - are supported while PostgreSQL 10 is in beta on Heroku. pg:backups is also supported on PostgreSQL 10.
Additionally, pg:upgrade to PostgreSQL 10 is temporarily unavailable pending extension support and testing by our team. If you want to transfer data from your existing database, you can use pg:copy for now.
Along with version 10 in beta, we currently support PostgreSQL versions 9.3, 9.4, 9.5 and 9.6 in GA. Once we gain confidence in this release through user testing and validation, we will make PostgreSQL 10 the default for all new databases on Heroku.
Please contact us with any feedback, bugs, or questions at postgres@heroku.com.