Postgres 9.3 Beta Available

With each new release, Postgres brings new powerful functionality to your fingertips – Postgres 9.3 is shaping up to be no different. Postgres 9.3 will include richer JSON operators for the JSON datatype, SQL-standard LATERAL subquery support, materialized views, and of course much more. Postgres 9.3 Beta was made available earlier this week and we’re excited to announce a public alpha of it ourselves.

You can get started immediately with the alpha today by provisioning a Postgres 9.3 database on our production tier:

$ heroku addons:add heroku-postgresql:crane --version=9.3
...
Use `heroku pg:wait` to track status.
! WARNING: Postgres 9.3 is in alpha. alpha releases have
!          a higher risk of data loss and downtime.
!          Use with caution..
Use `heroku addons:docs heroku-postgresql:crane` to view documentation.

Our support of Postgres 9.3 Beta is alpha and comes with several conditions:

  • The 9.3 beta version will be supported for 7 days after new beta or GA releases for PostgreSQL 9.3 occur. At which point existing databases will be deprovisioned or migrated to the newest release. At this time all Postgres 9.3 databases are running the GA release of PostgreSQL 9.3.
  • Heroku Postgres uptimes are expected uptime guidelines and not a guarantee or SLA for uptime of your production database. As with many alpha or beta features the expected uptime is lower for Postgres 9.3 beta.
  • Forks, followers, and other functionality may see a higher level of issues during the alpha and may cease to work entirely due to changes that could occur between 9.3 beta and GA.

We're very excited to make PostgreSQL 9.3 available to you today, nevertheless we urge caution when using it as this is a very early beta PostgreSQL release. Once you do provision your 9.3 beta database, we want to hear from you at postgres@heroku.com. Let us know how you’re taking advantage of new features, and especially if you encounter any bugs.

Database Insight with pg-extras

When building your application there's a lot to worry about, from choice of framework and stack to designing the application itself, to questions of when to worry about scalability. Your database shouldn't have to be one extra layer of concern. You should be able to put data in, trust it will stay safe, and finally get data back out – in a performant manner. Yet, with all the moving parts in your application, understanding issues in your database can be a huge pain. Today we're releasing pg-extras, a heroku toolbelt plugin, to provide additional insights into your database and to make working with your database easier.

Get started by installing the plugin for the heroku toolbelt:

$ heroku plugins:install git://github.com/heroku/heroku-pg-extras.git

Now you have many more commands available to provide you the insight you need within the pg namespace of the heroku toolbelt:

$ heroku help pg
...
  pg:bloat [DATABASE]                 #  show table and index bloat in your database ordered by most wasteful
  pg:blocking [DATABASE]              #  display queries holding locks other queries are waiting to be released
  pg:cache_hit [DATABASE]             #  calculates your cache hit rate (effective databases are at 99% and up)
...

You can read more on each command available and what insights you can get from it within the pg-extras readme. Lets highlight a few:

cache_hit

Each production tier plan’s RAM size constitutes the total amount of System Memory on the underlying instance’s hardware, most of which is given to Postgres and used for caching. While a small amount of RAM is used for managing each connection and other tasks, Postgres will take advantage of almost all this RAM for its cache. You can read more about how this works in our article on understanding postgres data caching.

As a guide for most web applications cache hit ratio should be in the 99%+ range.

$ heroku pg:cache_hit
       name      |         ratio
----------------+------------------------
 index hit rate | 0.99985155862675559832
 cache hit rate | 0.99999671620611908765
(2 rows)

index_usage

Premature optimization has both the cost of losing time on feature development and the risk of wasted optimizations. Indexes are one area that’s easy to ignore until you actually need them. A good index should be across a table of some reasonable size and highly selective. However indexes aren’t free, as there is a measurable cost to keeping them updated and storing them, so unused indexes which you can see with heroku pg:unused_indexes are to be avoided. With pg:index_usage you can begin to get a clear idea of how to manage/maintain your indexes. Running the command will give you output like the following:

$ heroku pg:index_usage --app dashboard
       relname       | percent_of_times_index_used | rows_in_table
---------------------+-----------------------------+---------------
 events              |                          65 |       1217347
 app_infos           |                          74 |        314057
 app_infos_user_info |                           0 |        198848

From the above you can see that the app_infos_user_info has never had an index used and could likely benefit from adding an index. Even the events table could benefit from some additional indexes. From this you could then follow a more detailed guide for getting your indexes setup.

locks

Locks are bound to happen within your database, usually these are very short lived on the order of milliseconds. In PostgreSQL fortunately writing data does not hold a lock preventing it from being read. However, you can still encounter unintentional cases where you have long lived locks due to contention. Such cases can create follower lag, cause issues for other queries and in general start to impact application performance. With the pg:locks command you can easily view all current locks and how long they've been held.

kill

Whether its lock contention, a long running analytics query, or a bad cross join there are times where you want to stop a query. The pg:kill statement will allow you to kill any currently running query by specifying its pid which is displayed with commands pg:locks and pg:ps. Or if your database is in dire straights you also have the abilty to run pg:killall to kill all currently running queries. Having the ability to stop runaway queries will allow you to feel even safer when others need access to your database to get the reports they need.

The future

We've already found pg-extras incredibly useful and expect you will too. Going forward pg-extras will be the playground for new commands available power users that install the plugin. Over time some commands may leave pg-extras and become part of the toolbelt or be removed if they’re not beneficial. We welcome your input on which commands you find helpful or what else you’d like to see in pg-extras at postgres@heroku.com.

London Fork-a-thon

On 15 May, join Heroku for the London Fork-a-thon, a hack-a-thon-like event (hands-on and live coding), where Heroku engineers will be available to answer any questions you might have regarding Heroku in Europe, and to help you to fork your app to the Europe region.

Space is limited, register now.

Read more →

New Dyno Networking Model

Today we're announcing a change to how networking on Heroku works. Dynos now get a dedicated, virtual networking interface instead of sharing a network interface with other dynos. This makes dynos behave more like standard unix containers resulting in better compatibility with application frameworks and better parity between development and production environments.

Read more →

Heroku Postgres - GIS Support Now Available

Today we're announcing geospatial support for Heroku Postgres with PostGIS 2.0.

Heroku Postgres is increasingly enabling rich use cases – adding services from key/value datatype in hstore, querying across postgres databases with dblink, and now adding rich geospatial functionality. Adding PostGIS within your Postgres database reduces the number of services you need to add to your stack, reducing complexity and allowing you to build in location-based functionality into apps faster.

Get started integrating location into your apps today by provisioning your Heroku Postgres database and exploring the functionality of PostGIS 2.0 or read more about it on the Heroku Postgres Blog.

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