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 supporting 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 mini 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
DBLink
In a class by itself, DBLink allows data from multiple Postgres databases to be queried simultaneously.
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
Data Types
- 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
- 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: 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
.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
Database 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 constraints.
create extension btree_gist
Full Text Search Dictionaries
- Integer Dictionary - 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