Improving the Heroku Postgres Extension Experience

PostgreSQL extensions are powerful tools that allow developers to extend the functionality of PostgreSQL beyond its basic types and functions. These extensions can connect your database to an external PostgreSQL instance (postgres_fdw), add native GIS functionality (postgis), standardize address information (address_standardizer), and more. Extensions are arguably one of PostgreSQL’s greatest features and are partially responsible for the massive adoption PostgreSQL has received over the years.

We’re pleased to announce a change to the Heroku Postgres extension experience. You can once again install Heroku Postgres extensions in the public schema or any other!

Previously, in response to incident 2450, we required all PostgreSQL extensions to be installed to a new schema: heroku_ext. We’ve listened to our customers, who let us know that this change broke many workflows. We’ve been focusing our recent engineering efforts on restoring the previous functionality. Our goal is to offer our users more flexibility and a more familiar Postgres experience. With this release, we are closing the public roadmap item.

At the moment, installing extensions on schemas other than heroku_ext is an opt-in configuration. We plan on making this the default at a later date. Note that this feature is available for non-Essential-tier databases.

Enable any schema

To enable any schema on new databases, you simply pass the --allow-extensions-on-public-schema flag at provisioning. You can also use the Heroku Data Labs feature to enable any schema on existing databases. Any forks or followers you create against that database will automatically have this support enabled.

To enable any schema for new add-ons:

$ heroku addons:create heroku-postgresql:standard-0 --allow-extensions-on-any-schema

To enable any schema for existing add-ons (this may take up to 15 minutes to apply):

$ heroku data:labs:enable extensions-on-any-schema --addon DATABASE_URL

Once either of these steps are complete, you can verify extensions are installed to public. To do this, first install a new extension:

demo::DATABASE => CREATE EXTENSION address_standardizer;

Then check the output of \dx, which is a command in PostgreSQL to view all installed extensions. The Schema value for address_standardizer will be set to public.

         Name         | Version |   Schema
----------------------+---------+------------
 plpgsql              | 1.0     | pg_catalog
 pg_stat_statements   | 1.10    | heroku_ext
 address_standardizer | 3.3.3   | public
(3 rows)

Previously, Postgres extensions were installed to heroku_ext by default. After enabling this support, extensions install to the first schema in your search_path, which in most cases is public.

Enabling the feature does not change existing extensions or anything about your database structure. If an extension is already installed to heroku_ext, it remains there unless you relocate it to another schema. You can reinstall or relocate your extension to any schema you want after enabling the Heroku Data Labs feature. Once enabled, extensions going forward will have their types and functions go to their appropriate schemas (usually public) and nothing new will be added to heroku_ext.

Verify your apps

If your application code assumes extensions will always be in heroku_ext, this change could potentially impact loading your database schema into new add-ons for review apps or developer setups. The following steps ensure your apps continue to work after this change is made:

1. Check your code for hard-coded references to heroku_ext and remove them.
2. Ensure your automated tests pass and all tables, indexes, views, etc. load correctly into a local database with heroku_ext removed.
3. Provision a new app with a Postgres database using either of the methods listed above.
4. Deploy your code and run through some test workflows to ensure no errors.
5. Update your code accordingly.

Timing

This behavior will be the default for all Heroku Postgres add-ons in three phases:

• July 10th, 2023: The extensions-on-any-schema Heroku Data Labs feature became the default on new Heroku Postgres add-ons.
• July 24th, 2023: All Essential tier databases will be updated to permit extensions to be installed to any schema.
• August 7th, 2023: We enable extensions-on-any-schema on existing Heroku Postgres add-ons and retire the Labs feature.

You can test for issues by enabling the feature using Heroku Data Labs before July 10th, or by creating a new database after that date. If you have any concerns about how this change can impact your existing database, make sure to verify your database before August 7th, 2023.

We want to hear from you

Heroku’s mission is to provide customers with a great platform and take the headache out of running your apps in the cloud. We prioritize keeping your data, and our platform, safe above all else. As we say at Salesforce, Trust is our #1 value.

We value your feedback and never want to make changes that harm the customer experience. After we made the initial change with the heroku_ext schema, we listened to users like Justin Searls, who made this comment in his blog post:

"[It’s] disappointing that this change rolled out without much in the way of change management. No e-mail announcement. No migration tool. No monkey patches baked into their buildpacks."

We agree. Unforeseen situations can arise which force difficult decisions. Although the user experience took a backseat in the short term, we worked hard to restore the seamless Heroku Postgres experience you’d expect without compromising on security. We always welcome feedback and never stop looking for ways to make your experience as great as we safely can.

Thanks to all of you for your continued support over the years. Some really exciting things are in the pipeline, and we can’t wait to show them to you. In case you don’t already know, we maintain a public roadmap on GitHub and encourage you to comment on planned enhancements and offer suggestions.