Databases are the well known solution for storing data for your application. However they sometimes lack functionality required by application developers such as data encryption or cross database reporting. As a result developers are forced to write the needed functionality at their application layer. Postgres 9.1, which already has an extensive collection of data types and functions, took the first step towards mitigating this by creating an extension system which allows the database’s functionality to be expanded.
Today we are releasing support for 16 new Postgres extensions which add exciting new functionality including the ability to query from multiple database (dblink), a case-insensitive text datatype (citext), in-database encryption (pgcrypto), and UUID generation (uuid-ossp). A list of the new extensions is available below.
Extensions allow related pieces of functionality, such as datatypes and functions, to be bundled together and installed in a database with a single command.
We began supporing extensions in March with our release of hstore - the schemaless datatype for SQL. Users have taken advantage of hstore to increase their development agility by avoiding the need to pre-define their schemas.
These extensions are available on all Heroku Postgres plans, including the Starter tier dev and basic plans as well as our Production tier plans. To install an extension, use the CREATE EXTENSION command in psql:
$ heroku pg:psql --app sushi psql (9.1.4) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) => CREATE EXTENSION citext; CREATE EXTENSION
In a class by itself, DBLink allows data from multiple Postgres databases to be queried simultaneuosly.
DBLink is useful for sharding, distributed application constellations, or any other environment where data from more than one physical database must be compared, joined, or collated.
=> CREATE EXTENSION dblink
create extension citext.
create extension ltree
create extension isn
create extension cube
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.
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
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
create extension btree_gist
create extension dict_int
create extension dict_xsyn
create extension unaccent