Extensions, PostGIS, and Full Text Search Dictionaries on Heroku Postgres
Last updated 31 May 2018
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 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 ----------------------------- btree_gist,chkpass,cube,dblink,dict_int...
To create any supported extension, open a session with
pg:psql and run the appropriate command:
$ heroku pg:psql psql (9.6.2) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. ad27m1eao6kqb1=> CREATE EXTENSION hstore; CREATE EXTENSION ad27m1eao6kqb1=>
Bloom provides space-efficent 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
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.
Foreign data wrappers are not available for hobby tier databases.
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
Insert Username: Inserts the current user’s name into a text field.
create extension insert_username
ModDateTime: Inserts the current timestamp into a timestamp field.
create extension moddatetime
Timetravel: Adds functions for querying historical data.
create extension timetravel
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
PGCrypto: Cryptographic functions allow for encryption within the database
create extension pgcrypto.
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.
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.
Intarray: Sorting, manipulate, and create indexes on null-free arrays of integers.
create extension intarray
pg_partman: Create and manage both time-based and serial-based table partition sets. Sub-partitoning 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.
tcn: Provides a trigger function for notifying listeners of changes to tables.
create extension tcn
sslinfo: Provides functions to query SSL information about connecting clients.
create extension sslinfo
pg_partman and pg_prewarm are not available for hobby tier databases.
Show row lock information for a table.
create extension pgrowlocks
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
create extension btree_gist
create extension PLV8. PLV8 is only available on non-hobby tier databases.
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 Adds support for querying between Postgres databases. With dblink you can query between separate Heroku Postgres databases or to/from external Postgres databases.
The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server.
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 does not capture any queries that were cancelled, no matter how long they ran beforehand. This means that long running queries cancelled by
statement_timeout will not be reflected in the pg_stat_statements view. You will need to use the Postgres logs to diagnose expensive cancelled queries effectively.
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)
PostGIS support on Heroku Postgres is in beta and is subject to change in the future.
Currently, PostGIS can only be used on Production tier Heroku Postgres plans and on hobby plans with Postgres version 9.4 and above. Additionally, PostGIS is only available as v2.2 with Postgres 9.5, 9.4 and 9.3 or as v2.1 with Postgres 9.4 and 9.3.
PostGIS support can be added like any other extension, as long as your database meets the requirements above.
$ heroku addons:create heroku-postgresql:standard-0
and then simply run
create extension postgis. Note also that this
functionality is only available on newer databases. If your database
meets the requirements above but you still receive an error when
create extension postgis, you can use the
procedure to move to a fresh database with PostGIS support.
To detect if PostGIS is installed on a database, execute the following query from psql:
=> SELECT postgis_version(); postgis_version --------------------------------------- 2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 (1 row)