Extensions, PostGIS, and Full Text Search Dictionaries on Heroku Postgres
Last updated November 07, 2023
Table of Contents
Extensions allow related pieces of functionality, such as datatypes and functions, to be bundled together and installed in a database with a single command. Heroku Postgres supports many Postgres extensions as well as features such as full text search that aren’t bundled as part of the extensions system. The PostGIS spatial database extension is also available. Only the following extensions in this article are supported and can be installed on Heroku Postgres.
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...
Install an Extension
To create any supported extension, open a session with heroku pg:psql and run the appropriate CREATE EXTENSION command:
$ 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 hstore;
CREATE EXTENSION
example-app::DATABASE=>
All Heroku Postgres databases on any plan install extensions in the public schema by default. To install an extension in a specific schema, run the CREATE EXTENSION command along with WITH SCHEMA:
$ 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 hstore WITH SCHEMA schema_name;
CREATE EXTENSION
example-app::DATABASE=>
Upgrade an Extension
To see if your Heroku Postgres add-on has extension upgrades enabled, run the heroku pg:info command and check the Upgradable Extensions line. If this value is set to No, then extensions are upgradable after the next scheduled maintenance.
=== DATABASE_URL
Plan: Standard 0
Status: Available
Data Size: 2.15 GB
Tables: 4
PG Version: 15.2
Connections: 32/120
Connection Pooling: Available
Credentials: 1
Fork/Follow: Available
Rollback: earliest from 2023-04-21 17:14 UTC
Created: 2023-04-17 19:06
Region: us
Data Encryption: In Use
Continuous Protection: On
Enhanced Certificates: Off
Upgradable Extensions: Yes
Maintenance: not required
Maintenance window: Mondays 17:30 to 21:30 UTC
Add-on: postgresql-trapezoidal-57207
To upgrade an extension, run the ALTER EXTENSION command on the extension you want to upgrade in a heroku pg:psql session:
$ 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 btree_gin VERSION '1.0';
CREATE EXTENSION
example-app::DATABASE=> ALTER EXTENSION btree_gin UPDATE to '1.1';
ALTER EXTENSION
example-app::DATABASE=>
Remove an Extension
To drop an existing extension from your database, open a session with heroku pg:psql and run the appropriate DROP EXTENSION command:
$ 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=> DROP EXTENSION hstore;
DROP EXTENSION
example-app::DATABASE=>
View Your Extensions
To see which schemas your extensions are installed in, open a session with heroku pg:psql and run the \dx command:
intense-mesa-15917::DATABASE=> \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_statements | 1.10 | heroku_ext | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
xml2 | 1.1 | public | XPath querying and XSLT
(3 rows)
Data Types
| Name | Description | Command |
|---|---|---|
| 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’re 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
Foreign data wrappers aren’t available for Essential-tier databases.
| Name | Description | Command |
|---|---|---|
| postgres_fdw | Allows a Postgres database to talk to another Postgres database as a foreign server | create extension postgres_fdw |
Functions
pg_partman and pg_prewarm aren’t available for Essential-tier databases.
| Name | Description | Command |
|---|---|---|
| Address Standardizer | Single line address parser that normalizes addresses around a set of defined rules. A set of rules for US addresses is already included create extension address_standardizer_data_us |
create extension address_standardizer |
| 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 | create extension pg_partman |
| pg_prewarm | Preload data into the operating system, or database buffer cache to help normalize performance on a freshly started, or quiescent database | create extension pg_prewarm |
| 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 |
| UUID Generation | Generate v1, v3, v4, and v5 UUIDs in-database. Works great with the existing UUID datatype | create extension uuid-ossp |
Statistics
| Name | Description | Command |
|---|---|---|
| pgrowlocks | Show row lock information for a table | create extension pgrowlocks |
Index Types
| Name | Description | Command |
|---|---|---|
| btree-gist | A GiST index operator. It’s generally inferior to the standard B-tree index, except for multi-column indexes that can’t be used with B-tree and exclusion constraints. | create extension btree_gist |
| btree-gin | A GIN index operator. It’s generally inferior to the standard B-tree index, except for developing new GIN operator classes or creating a multicolumn GIN index for both GIN-indexable and btree-indexable columns. | create extension btree_gin |
Full Text Search Dictionaries
| Name | Description | Command |
|---|---|---|
| dict-int | A full-text search dictionary for full-text search that controls how integers are indexed | create extension dict_int |
| unaccent | A filtering text dictionary that 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 isn’t available for Essential-tier databases
dblink adds support for querying between Postgres databases. With dblink you can query between separate Heroku Postgres databases or to and from external Postgres databases.
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.
pg_stat_statements doesn’t capture any queries that were canceled, no matter how long they ran beforehand. This means that long-running queries canceled by statement_timeout isn’t reflected in the pg_stat_statements view. You must use the Postgres logs to diagnose expensive canceled queries effectively.
PostGIS
The PostGIS module adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS “spatially enables” the PostgreSQL server, allowing you to use it as a backend spatial database for geographic information systems (GIS).
Requirements
You can use PostGIS with all Heroku Postgres database plans.
The supported versions of PostGIS on Heroku Postgres are:
- PostGIS v2.5, for databases running on PostgreSQL 13 or earlier
- PostGIS v3.3, for databases running on PostgreSQL 14 or later
Provisioning
You can add PostGIS like any other extension, as long as your database meets the requirements earlier.
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)
pgvector
The pgvector extension adds support for the vector data type and adds functions for working with vectors, such as nearest neighbor search and cosine distance.
Requirements
You can use pgvector on Standard-tier and higher databases running PostgreSQL 15.
If you’re using Heroku Streaming Data Connectors, vector data types don’t sync.
Provisioning
You can install pgvector by running CREATE EXTENSION vector in a psql session on your Heroku Postgres database.
$ 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 vector;
CREATE EXTENSION
example-app::DATABASE=>
To check what version is installed, run the following query from psql:
=> SELECT extversion FROM pg_extension WHERE extname = 'vector';
extversion
------------
0.5.0
(1 row)
Performance
See the pgvector documentation for guidance on creating indexes and optimizing performance on vector data types. You can set settings such as max_parallel_workers_per_gather on a per-session basis. For example:
SET max_parallel_workers_per_gather = 4;
To see the current settings, use the SHOW command:
=> SHOW max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
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. If this changes, we reinstitute PLV8.
chkpassisn’t supported in Postgres 11 and later.