This post is by Camille Baldock from Heroku
Click here to view on the original site: Original Post
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:
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
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.
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 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 ...
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 email@example.com.