Extensions, PostGIS, and Full Text Search Dictionaries on Heroku Postgres

Last Updated: 23 February 2015

extensions hstore json plv8 postgis postgres search

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.

You can always 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 heroku 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=>

Data types

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

• Case Insensitve Text: Case insenstive 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 hierachies, with associated functions. create extension ltree

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

• Cube: Multi-dimensional cubes. create extension cube

Functions

• PGCrypto: Cyptographic 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.

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

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

• 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.

• 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.

Statistics

• 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

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 constrations. create extension btree_gist

Languages

• PLV8: The full V8 engine embedded within Postgres allowing you to create full JavaScript functions. create extension PLV8. PLV8 is only available on non-hobby tier databases currently.

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.

This extension is enabled by default for all new Postgres 9.2 databases, created after 2014-February-11, and those on the Hobby tier.

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. It is available on non-hobby tier databases. Additionally, PostGIS is only available as v2.1 with Postgres 9.3 or as v2.0 with Postgres v9.2.

Provisioning

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

$ heroku addons:add heroku-postgresql:standard-yanari

and then simply run create extension postgis. Note also that this functionality is only availble on newer databases. If your database meets the requirements above but you still receive an error when running create extension postgis, you can use the fast changeover 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)