Posted by Matt Soldo
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
- 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
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
- 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