Extensions, PostGIS, and Full Text Search Dictionaries on Heroku Postgres
Last updated 17 June 2016
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 Pager usage is off. psql (9.2.4) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. ad27m1eao6kqb1=> CREATE EXTENSION hstore; CREATE EXTENSION ad27m1eao6kqb1=>
HStore: Key value store inside Postgres.
create extension hstore
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.
Label Tree: Tree-like hierarchies, with associated functions.
create extension ltree
Cube: Multi-dimensional cubes.
create extension cube
Foreign Data Wrappers
postgres_fdw Allows a Postgres database to talk to another Postgres database as a foreign server.
redis_fdw Allows a Redis instance to connect to a Postgres database.
Foreign data wrappers are not available for hobby tier databases.
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.
pg_partman and pg_prewarm are not available for hobby tier databases.
Row Locking: Show row lock information for a table.
create extension pgrowlocks
Tuple Statistics: Database tuple-level statistics such as physical length and aliveness.
create extension pgstattuple
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.
For the moment, PLV8 is not available on version 9.5 production databases. Until it is, we suggest looking at the jsonb datatype.
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.
This extension is enabled by default for all new Postgres databases, version 9.2 and later, created after 2014-February-11, and those on the Hobby tier.
pg_stat_statements support on Heroku Postgres is limited to Postgres 9.2 or later databases.
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.
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, v2.1 with Postgres 9.4 and 9.3 or as v2.0 with Postgres v9.2.
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)