Announcing Support for 16 new Postgres Extensions

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

DBLink

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

Data Types

  1. 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.
  2. Label Tree: Tree-like hierachies, with associated functions. create extension ltree
  3. Product Numbering: Store product IDs and serial numbers such as UPC, ISBN, and ISSN. create extension isn
  4. Cube: Multi-dimensional cubes. create extension cube

Functions

  1. PGCrypto: Cyptographic functions allow for encryption within the database create extension pgcrypto.

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

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

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

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

  6. Fuzzy Match: Another method for determining the similarity between strings. Limited UTF-8 support. create extension fuzzystrmatch

Database Statistics

  1. Row Locking: Show row lock information for a table. create extension pgrowlocks

  2. Tuple Statistics: Database tuple-level statistics such as physical length and aliveness. create extension pgstattuple

Index Types

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

Full Text Search Dictionaries

  1. Integer Dictionary - A full-text search dictionary for full-text search which controls how integers are indexed. create extension dict_int
  2. Unaccent - A filtering text dictionary which removes accents from characters. create extension unaccent

New Heroku Postgres Plans GA

In past months we've release the public beta of our dev, basic, crane and kappa plans. We've received positive feedback from everyone using the new database plans.

Based on this strong positive response and adoption during the beta, we're officially moving these plans to general availability starting today.

In conjunction with the GA of our starter tier, we're deprecating our shared database plans. The new dev and basic plans offer many improvements including Postgres 9.1 with schemaless SQL, data clips, direct psql access, and a web management interface.

Beginning August 8, 2012, we will begin migrating shared database users over to the new starter tier. Users may control their application down time by migrating their databases ahead of August 8.

For steps in migrating to the starter tier please read here.

New Heroku Postgres Plans GA

In past months we've release the public beta of our dev, basic, crane and kappa plans. We've received positive feedback from everyone using the new database plans.

Based on this strong positive response and adoption during the beta, we're officially moving these plans to general availability starting today.

In conjunction with the GA of our starter tier, we're deprecating our shared database plans. The new dev and basic plans offer many improvements including Postgres 9.1 with schemaless SQL, data clips, direct psql access, and a web management interface.

Beginning August 8, 2012, we will begin migrating shared database users over to the new starter tier. Users may control their application down time by migrating their databases ahead of August 8.

For steps in migrating to the starter tier please read here.

Release of new database plans on August 1st

We are happy to announce that our new line-up of database plans are being released on August 1st. The dev, basic, crane, and kappa plans make many of the most exciting features of our fully-managed database service available to a wider audience. They are now ready for all users.

We will also begin billing for these plans as of August 1st. If you have been beta testing one of these databases and do not wish to incur charges for it going forward, please remove it immediately via the web interface or the command line:

heroku addons:remove HEROKU_POSTGRESQL_COLOR --app app_name

If you have been waiting to use these plans because they have been in beta, then your wait is (almost) over. They can be provisioned now by all users via the web interface or the command line:

heroku addons:add heroku-postgresql:[dev | basic | crane | kappa]

Starter Tier

The dev plan (free) brings many of the best features of our production database plans to development users. This includes Postgres 9.1, data clips, hstore schemaless SQL, direct psql access, support for most pg commands from the Heroku client, a web interface, support for multiple databases connected to a single application, and Continuous Protection. The dev databases are limited to 10,000 total rows. For users that need to store more data, the basic plan ($9 / month) raises the row limit to 10 million rows but does not increase availability or add any additional features.

The dev and basic plans both belong to our Starter tier. These plans are designed to provide 99.5% availability and are ideal for trial, development, testing, and other basic usage. For serious production applications, we recommend using one of our Production plans, designed for 99.95% availability. Please note that these are design parameters, not SLAs, and availability can be further increased by taking advantage of followers.

Production Tier

With this release, the Production tier is expanded to include crane ($50 / month) and kappa ($100 / month). These will also be released on August 1st. They offer all of the same features as our other production databases at an incredible price point. These benefits include production-grade monitoring and operations, as well as support for fork, follow, auto backups, and fast changeovers / upgrades.

Migration From Legacy Shared Databases

For those users that are still using the legacy shared-database plan, we encourage you to upgrade as soon as possible. We will be announcing a deprecation and migration schedule for these plans shortly. We will be working to migrate all users onto these new plans, but we encourage you to move as soon as possible to enjoy the advantages of these improvements. You can also opt-in to creating the dev plan by default for all new applications by enabling the Heroku Labs flag.

If for any reason the scheduled release of these plans causes hardship for your business, please open a support ticket so that we can individually address your needs.

Release of new plans on August 1st

We are happy to announce that our new line-up of database plans are being released on August 1st. The dev, basic, crane, and kappa plans make many of the most exciting features of our fully-managed database service available to a wider audience. They are now ready for all users.

We will also begin billing for these plans as of August 1st. If you have been beta testing one of these databases and do not wish to incur charges for it going forward, please remove it immediately via the web interface or the command line:

heroku addons:remove HEROKU_POSTGRESQL_COLOR --app app_name

If you have been waiting to use these plans because they have been in beta, then your wait is (almost) over. They can be provisioned now by all users via the web interface or the command line:

heroku addons:add heroku-postgresql:[dev | basic | crane | kappa]

Starter Tier

The dev plan (free) brings many of the best features of our production database plans to development users. This includes Postgres 9.1, data clips, hstore schemaless SQL, direct psql access, support for most pg commands from the Heroku client, a web interface, support for multiple databases connected to a single application, and Continuous Protection. The dev databases are limited to 10,000 total rows. For users that need to store more data, the basic plan ($9 / month) raises the row limit to 10 million rows but does not increase availability or add any additional features.

The dev and basic plans both belong to our Starter tier. These plans are designed to provide 99.5% availability and are ideal for trial, development, testing, and other basic usage. For serious production applications, we recommend using one of our Production plans, designed for 99.95% availability. Please note that these are design parameters, not SLAs, and availability can be further increased by taking advantage of followers.

Production Tier

With this release, the Production tier is expanded to include crane ($50 / month) and kappa ($100 / month). These will also be released on August 1st. They offer all of the same features as our other production databases at an incredible price point. These benefits include production-grade monitoring and operations, as well as support for fork, follow, auto backups, and fast changeovers / upgrades.

Migration From Legacy Shared Databases

For those users that are still using the legacy shared-database plan, we encourage you to upgrade as soon as possible. We will be announcing a deprecation and migration schedule for these plans shortly. We will be working to migrate all users onto these new plans, but we encourage you to move as soon as possible to enjoy the advantages of these improvements. You can also opt-in to creating the dev plan by default for all new applications by enabling the Heroku Labs flag.

If for any reason the scheduled release of these plans causes hardship for your business, please open a support ticket so that we can individually address your needs.

Browse the blog archives or subscribe to the full-text feed.