Last updated August 03, 2022

PostgreSQL extensions allow related pieces of functionality, such as data types and functions, to be bundled together and installed in a database with a single command.

Overview

Heroku Postgres supports many Postgres extensions as well as features such as full text search that are not bundled as part of the extensions system. A beta version of the PostGIS spatial database extension is also available.

Query your database for the list of supported extensions:

$ echo 'show extwlist.extensions' | heroku pg:psql
     extwlist.extensions
-----------------------------
...bloom,btree_gin,btree_gist,cube,dblink,dict_int...

Check the extensions currently installed in your database with the \dx command from a heroku pg:psql session:

$ heroku pg:psql DATABASE_URL -a example-app
--> Connecting to postgresql-octagonal-12345
psql (13.2, server 13.7 (Ubuntu 13.7-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

example-app::DATABASE=> \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis            | 2.5.5   | public     | PostGIS geometry, geography, and raster spatial types and functions
(3 rows)

Installing extensions

To install a supported extension, open a session with heroku pg:psql and run the CREATE EXTENSION command:

$ heroku pg:psql DATABASE_URL -a example-app
--> Connecting to postgresql-octagonal-12345
psql (13.2, server 13.7 (Ubuntu 13.7-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

example-app::DATABASE=> CREATE EXTENSION hstore;
CREATE EXTENSION

All newly created extensions on Heroku Postgres are installed in the heroku_ext schema. Heroku Postgres automatically creates the heroku_ext schema for use with PostgreSQL extensions, and includes this extension schema as part of the database search_path by default.

In the past extensions were installed on either the public schema or a user-defined schema.

Data types

• Bloom provides space-efficient bloom filter indexes. create extension bloom

• Case Insensitive Text. Case insensitive text datatype. Although strings stored in citext do retain case information, they are case insensitive when used in queries. create extension citext

• Cube: Multi-dimensional cubes. create extension cube

• HStore: Key value store inside Postgres. create extension hstore

• Label Tree: Tree-like hierarchies, with associated functions. create extension ltree

• Large Objects: Provides stream-style access to user data that is stored in a special large-object structure. create extension lo

• Product Numbering: Store product IDs and serial numbers such as UPC ISBN and ISSN. create extension isn

• Seg: Provides a type for representing segments or floating point intervals. create extension seg

Foreign Data Wrappers

• postgres_fdw Allows a Postgres database to talk to another Postgres database as a foreign server.

Functions

• Address Standardizer: Single line address parser that normalizes addresses around a set of defined rules create extension address_standardizer. A set of rules for US addresses is already included create extension address_standardizer_data_us.

• Autoinc: Stores the next value of a sequence into an integer field. create extension autoinc

• Earth Distance: Functions for calculating the distance between points on the earth. create extension earthdistance

• Fuzzy Match: Another method for determining the similarity between strings. Limited UTF-8 support. create extension fuzzystrmatch.

• Insert Username: Inserts the current user’s name into a text field. create extension insert_username

• Intarray: Sorting, manipulate, and create indexes on null-free arrays of integers. create extension intarray

• ModDateTime: Inserts the current timestamp into a timestamp field. create extension moddatetime

• PGCrypto: Cryptographic functions allow for encryption within the database create extension pgcrypto.

• pg_partman: Create and manage both time-based and serial-based table partition sets. Sub-partitioning is also supported.

• pg_prewarm: Preload data into the operating system, or database buffer cache to help normalize performance on a freshly started, or quiescent database.

• sslinfo: Provides functions to query SSL information about connecting clients. create extension sslinfo

• Table Functions & Pivot Tables: Functions returning full tables, including the ability to manipulate query results in a manner similar to spreadsheet pivot tables create extension tablefunc.

• tcn: Provides a trigger function for notifying listeners of changes to tables. create extension tcn

• Timetravel: Adds functions for querying historical data. create extension timetravel

• Trigram: Determine the similarity (or lack thereof) of alphanumeric string based on trigram matching. Useful for natural language processing problems such as search. create extension pg_trgm.

• tsm_system_rows Provides table sampling via system rows.create extension tsm_system_rows

• tsm_system_time Provides table sampling via system time.create extension tsm_system_time

• UUID Generation: Generate v1, v3, v4, and v5 UUIDs in-database. Works great with the existing UUID datatype create extension "uuid-ossp".

Statistics

• Row Locking: Show row lock information for a table. create extension pgrowlocks

Index types

• btree-gist: A GiST index operator. It is generally inferior to the standard btree index, except for multi-column indexes that can’t be used with btree and exclusion constraints. create extension btree_gist

• btree-gin: A GIN index operator. It is generally inferior to the standard btree index, except for developing new GIN operator classes or creating a multicolumn GIN index for both GIN-indexable and btree-indexable columns.

Full text search dictionaries

• dict-int - A full-text search dictionary for full-text search which controls how integers are indexed. create extension dict_int

• unaccent - A filtering text dictionary which removes accents from characters. create extension unaccent

Additionally, the following dictionaries are installed by default and don’t require creation via the extension system:

$ heroku pg:psql
=> \dFd
                             List of text search dictionaries
   Schema   |      Name       |                        Description
------------+-----------------+-------------------------------
 pg_catalog | danish_stem     | snowball stemmer for danish language
 pg_catalog | dutch_stem      | snowball stemmer for dutch language
 pg_catalog | english_stem    | snowball stemmer for english language
 pg_catalog | finnish_stem    | snowball stemmer for finnish language
 pg_catalog | french_stem     | snowball stemmer for french language
 pg_catalog | german_stem     | snowball stemmer for german language
 pg_catalog | hungarian_stem  | snowball stemmer for hungarian language
 pg_catalog | italian_stem    | snowball stemmer for italian language
 pg_catalog | norwegian_stem  | snowball stemmer for norwegian language
 pg_catalog | portuguese_stem | snowball stemmer for portuguese language
 pg_catalog | romanian_stem   | snowball stemmer for romanian language
 pg_catalog | russian_stem    | snowball stemmer for russian language
 pg_catalog | simple          | simple dictionary: just lower case and check for stopword
 pg_catalog | spanish_stem    | snowball stemmer for spanish language
 pg_catalog | swedish_stem    | snowball stemmer for swedish language
 pg_catalog | turkish_stem    | snowball stemmer for turkish language

dblink

dblink Adds support for querying between Postgres databases. With dblink you can query between separate Heroku Postgres databases or to/from external Postgres databases.

pg_stat_statements

pg_stat_statements:

The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server.

Usage

pg_stat_statements can be used to track performance problems. It provides a view called pg_stat_statements that displays each query that has been executed, and associated costs, including the number of times the query was executed, the total system time execution has taken in aggregate, and the total number of blocks in shared memory hit in aggregate.

PostGIS

PostGIS:

adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS “spatially enables” the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS)

Requirements

Currently, PostGIS can only be used on Production tier Heroku Postgres plans and on hobby plans with Postgres version 9.6 and above.

The current latest supported versions of PostGIS on Heroku Postgres is:

• PostGIS v2.5, for databases running on PostgreSQL 13 or earlier
• PostGIS v3.2, for databases running on PostgreSQL 14

Provisioning

PostGIS support can be added like any other extension, as long as your database meets the requirements above.

To install PostGIS in an existing Heroku Postgres database, open a psql session, and run CREATE EXTENSION postgis;:

$ heroku pg:psql DATABASE_URL -a example-app
--> Connecting to postgresql-octagonal-12345
psql (13.2, server 11.12 (Ubuntu 11.12-1.pgdg16.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

example-app::DATABASE=> CREATE EXTENSION postgis;
CREATE EXTENSION

To check if PostGIS is installed on a database, run the following query from psql:

=> SELECT postgis_version();
            postgis_version
---------------------------------------
 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

Extensions That Are No Longer Supported

• PLV8 - During the Heroku beta period for this extension, the PLV8 package maintenance was discontinued for PG11+ for Debian/Ubuntu. Read more here. Should this change, we may re-institute PLV8.