Heroku Postgres Followers Patched

by Harold Giménez - Nov 27

On November 18th, a replication bug was found in Postgres that affected the most recent versions of every Postgres release. The corruption that this bug may introduce could go undetected, and it manifests itself as a follower potentially having an inconsistent view of the data. For example, data could be present in the primary and not on the follower, or data deleted or updated on the primary and not from the follower. The likelihood of triggering this bug is higher for write-heavy workloads, such as many OLTP applications seen at Heroku.

We always recommend placing applications in maintenance mode and scaling down workers when performing a follower based changeover, and following this procedure largely decreases the chances of corruption introduced by this bug.

At Heroku Postgres we recognize the importance of data integrity and consistency in your databases: As of now there are no Heroku Postgres databases vulnerable to this corruption bug. Even though new versions of Postgres have not been released yet and are scheduled to ship early December, a patch was made available to the Postgres community on all git branches for affected versions. We have back-ported this patch to all of our supported Postgres versions, and all affected instances have been replaced. As this bug only impacts followers, no primary databases were at risk and no primary databases required the patch.

If you were worried about this bug affecting your Heroku Postgres database, worry no longer: just sit back and enjoy your healthy followers.

Connection Limit Guidance

by Craig Kerstiens - Nov 22

Many of our customers have recently asked about our connection limit settings on our new Heroku Postgres tiers. Previously we allowed for 500 connections across all production databases, however now there is some variance in the number of connections allowed with only the larger plans offering 500. In individual conversations with customers we’ve detailed the reasoning behind this, and feel its worth sharing this more broadly here now.

For some initial background, our connection limit updates are actually aimed to be an improvement for anyone running a Heroku Postgres database, by both providing some guidelines as well as setting some expectations around what a database instance is capable of. What we’ve observed from running the largest fleet of Postgres in the world (over 750k databases) and heavily engaging with the Postgres community is there are two actual physical considerations in Postgres itself when it comes to the number of connections. Setting a high limit has performance impact under normal operations, even without establishing all available slots. The situation worsens when many connections are established, even if they’re mostly idle. By setting extremely high limits, when you encounter an issue it is masked by a much more vague error, thus making troubleshooting more painful.

The first limitation of PostgreSQL itself is that it doesn’t scale to a large number of connections. The Postgres community and large users of Postgres do not encourage running at anywhere close to 500 connections or above. To get a bit more technical, the size of various data structures in postgres, such as the lock table and the procarray, are proportional to the max number of connections. These structures must be scanned by Postgres frequently.

The second limitation is that each connection is essentially a process fork with a resident memory allocation of roughly 10 MB, along with some query load. We give some slack to this in our 60 connection limit on Yanari and a memory size of 400 MB. The previous experience was that as a user when your connections increased you would start receiving “out of memory” errors within Postgres. These “out of memory” errors can occur for any number of reasons, and having too many connections are just one cause, but by far the most common one. Instead of forcing you to evaluate all possible causes of the error, we want to make it clearer and simpler when you hit a limitation around connections. Now when you hit our connection limit you’ll receive an alert with clear guidance on these details so that you may reduce your connection usage or scale up to a larger instance.

At the same time, we understand that you may want to have a total of more than 500 connections to your database for any number of valid reasons. When that many processes need access to the database, a production grade connection pooler is the correct approach. For that reason, a Heroku Postgres staff member created the pgbouncer build pack, which places what’s considered the best Postgres connection pooler right on your dynos. We’re continuing to productize that further though happy to work with customers today if they’ve run into that limitation.

Our goal with these new connection limits are to make it easier for you to do the right thing for your database. As always we welcome your feedback around this or other product areas at postgres@heroku.com.

Welcome to the Community

by Craig Kerstiens - Nov 14

At Heroku we have long considered PostgreSQL to be a powerful and reliable open-source database for keeping data safe and accessible for serious applications with demanding workflows and use cases.

Over the years we’ve invested heavily in continuing to improve it, whether it’s by employing Postgres major contibutors, employing driver maintainers, funding core development, or being part of language communites such as Ruby and Python to help spread the good news that is Postgres. It’s that interaction with the developer and database communities that help us inform and influence the future of Postgres.

This work over the years has continued to advance Postgres to be a better database for all and even expand it beyond its relational roots. It’s been a great database for us to build our offering on and has enabled us to continue to add further value such as our new operational expertise that’s built right in or dataclips. It’s both this great database as well as the additional value that’s allowed us to see the great growth we’ve seen today, now running a fleet of over 750,000 Postgres databases.

All PostgreSQL users, hackers and service providers reap the benefits of any and all improvements to the project. It’s with that in mind that we welcome Amazon to this community and look forward to their contributions and collaboration to help further the PostgreSQL project.

Introducing Heroku Postgres 2.0

by Craig Kerstiens - Nov 11

Today we’re excited to announce an evolution of what it means to be a database as a service provider. Along with new features such as the ability to roll back your database to an arbitrary point in time and high availability, we now provide an entirely new level of operational expertise that’s built right in. This new level of service allows you to feel at ease while taking advantage of your database in ways you never expected. All of this is rolled into new tiers which make it as easy as ever to choose what’s right for you.

We are introducing Heroku Postgres 2.0 today, the result of these improvements. Let’s dig in a little further on what’s available today.

Operational Expertise built-in

As the number of databases we run continues to grow we’re seeing people take advantage of their database in new and exciting ways daily. At the same time we’ve continued to see a key need for better management of the day to day ins and outs involved with using a database. With Heroku Postgres this is now delivered to you in a way like never before. Here’s just a few ways you’ll begin to experience this as a customer:

  • If you have indexes that are not needed and slowing down write throughput on your database, we’ll notify you so you can easily make the choice to remove them.
  • If you have accumulated bloat in your data or indexes, we’ll notify you so that you can act on it accordingly.
  • If critical security incidents arise around your database we’ll make sure its patched without you lifting a finger.
  • If underlying disks get corrupted, with mechanisms already in place we’ll dig in to get you back up and running as safely as possible for your data.

And this is just the start. Stay tuned for more improvements in this area as we iterate further based on your feedback.

Rollback

In a world of agility where we bring applications to market soon and constantly iterate, there’s undeniable risk for things going wrong as we deploy to production leading to data loss or inconsistencies. Even with robust testing, having a safety net available to minimize the risk you’re exposed to is still critical, as these inconsistencies can render one of your businesses’ most precious assets useless: your data. Heroku Postgres forks allow you to even test a data migration on a production-like environment with no impact on production, but some bugs are difficult to anticipate and can cause data problems over time.

For years Heroku has had the ability to undo a deploy trivial with the rollback command. Just as we brought the ability to fork and follow your database over from git, we’re drawing inspiration from the Heroku platform’ rollback, now available for your database. This means should you run a bad migration that drops a table, or have a need to do some historical forensics with a view of your data as of some time in the past you’re now able to, all with a single command.

High availability

Keeping your data safe and secure is a must do for any database-as-a-service provider, yet at the same time your database is still no use to you when it’s down. With our introduction of followers we saw many of our customers using them to improve the uptime of their applications by manually unfollowing and promoting them when primary databases failed. We continued to build on the foundations of this, and available today is built in HA on our new Premium and Enterprise tiers.

New Tiers and Pricing

With the addition of fork and follow you had the ability better control your uptime, your read scaling, and how you worked with your data. While you can still be flexible in working with your data we’re making it easy for you to make the right choice when it comes to your database availability needs. Our new tiers make it easy to decide what is right for your business, all tiers already offer continuous protection for your data ensuring its safe, the biggest difference now when choosing is the uptime you should expect on each tier. While we strive for higher uptime for all of our tiers, mechanisms in place for Premium and Enterprise are meant for where uptime for your app is critical.

Here’s a clearer look at what each tier provides:

TierDowntime ToleranceBackups AvailableForkFollowRollbackHASLA
HobbyUp to 4 hrs downtime per mo.YesNoNoNoNoNo
StandardUp to 1 hr downtime per mo.YesYesYes1 hourNoNo
PremiumUp to 15 min downtime per mo.YesYesYes1 weekYesNo
EnterpriseUp to 15 min downtime per mo.YesYesYes1 monthYesYes

For those already familiar with our pricing our new standard tier is very similar to our now legacy production tier. For some of you this means migrating could actually provide over 45% in cost savings on your production database. For full details on pricing visit our pricing page to explore further.

Availability

Heroku Postgres 2.0 is available today for all Heroku customers, and is available for PostgreSQL 9.3 databases. If you’re already running a PostgreSQL 9.3 database on us today you can move to a new tier by creating a follower and conducting a fast changeover to your new tier. If you’re not running PostgreSQL 9.3, you can upgrade your database with pgbackups. Finally, for those where uptime is critical and your data size is prohibitive for an upgrade with pgbackups, we will be reaching out directly around upgrade paths that address this.

PostgreSQL 9.3 now GA on Heroku Postgres

by Craig Kerstiens - Nov 06

Several weeks ago we added support for Postgres 9.3 in public beta the day it was released to the community. We’ve had many customers use it so far and it has proven to be robust and reliable. Early adopters have started to take advantage of the great new features in this version including:

Today we’re excited to release Postgres 9.3 in General Availability and setting it as the default version when provisioning a new Heroku Postgres database. Defaulting to the latest version of Postgres ensures our customers can make use of the latest features and performance improvements available.

If you haven’t upgraded to take advantage all the great new functionality yet then do so today and let us know how you’re using it at postgres@heroku.com.

Monitoring your Heroku Postgres Database

by Craig Kerstiens - Oct 16

There are two axes of database monitoring. One axis is immediate insight. You can see what is happening right now, getting just-in-time visibility to solve problems and observe production behavior as it happens. The other axis is historical monitoring. This provides long-term persistence and reporting on the most important metrics over time, helping you make better decisions and understand trends.

With Heroku Postgres, you can get immediate insight with the pg-extras CLI plugin. Furthermore, we provide key metrics about your database right in your logs already for all applications.

For storage and reporting of your most important metrics, you can quickly set up rich historical reporting with Librato, which is available in our Add-ons marketplace. In this post, we walk through how to get started with Heroku Postgres monitoring for immediate visibility and long-term insight.

A healthy value for your cache hit will be as close to 100% as possible

It Starts with Logs

Heroku automatically collates and routes logs from every part of your app into a single channel, providing truly comprehensive, extensible, app-centric logging. With production Postgres databases on Heroku, robust logs and key metrics from your persistence level are routed directly into this stream. Key data including table-cache-hit, db_size, active-connections and more, outputting directly into your Heroku logs periodically. You can get immediate visibility into this stream by combining tail with a filter argument:

$ heroku logs --tail --ps heroku-postgres

Even better than viewing these logs manually is you can configure a log drain to be able to send your logs elsewhere. Fortunately when taking advantage of some of our add-ons they can automatically consume these logs without having to configure anything else.

Getting started with Librato

Librato is one such add-on that consumes your Heroku logs and gives you the ability to monitor on historical trends and set up alerts. You can get started with it right away by provisioning the add-on:

$ heroku addons:add librato

Librato will now be automatically consuming data from your Heroku logs and you can simply open it up to get immediate visibility into your Heroku Postgres cache hit, database connections, and insight around request queueing. If you have log runtime metrics already enabled on your application then you’ll see those insights as well.

Going further

While the initial dashboard is already helpful, you can gain additional insights by adding your own instruments to your dashboard. And if you need data thats not already available in your Heroku log stream you can add custom data yourself.

Introducing Postgres 9.3

by Craig Kerstiens - Sep 09

As of today PostgreSQL 9.3 is available on Heroku Postgres as a public beta. This new version of Postgres brings you even more useful features so you can be as powerful as ever. Whether its richer JSON functionality, materialized views, or richer join support in lateral joins this version has a little something for everyone.

Provision your Postgres 9.3 database by running heroku addons:add heroku-postgresql:crane --version=9.3 and get started working with it today, or check out some of our favorite features included in this new version below.

Foreign Tables

Foreign data wrappers (FDWs), which allow you to query from within Postgres to an external datasource, have been available for a couple of releases. Now Postgres ships with a built-in Postgres FDW as an extension. With the Postgres FDW aggregating and reporting against your data from disparate Heroku Postgres databases is as simple as CREATE EXTENSION postgres_fdw, followed by setting up your foreign tables.

Beyond the built in Postgres FDW available to all Heroku Postgres 9.3 users today, the API for foreign data wrappers now supports them writing as well as reading data. This lays the groundwork for more powerful wrappers to be built which in the future will enable Postgres to be a fully federated database.

We’ve already begun taking advantage of foreign data wrappers internally at Heroku for reporting and look forward to hearing how you take advantage of them yourselves.

A more powerful JSON

With version 9.2 we saw PostgreSQL get support for JSON starting on its path of bridging the gap between the dynamics of schemaless databases and the robustness of the traditional relational world. This support got even richer by our addition of full Javascript support with the V8 engine inside Postgres. It continues to get even better today with more built in functions and operators to make working with your JSON data even easier.

Materialized Views

For many applications, pre-computing expensive queries can be a great way to improve overall performance. Materialized views do just this by caching the results of a view and then allowing you to periodically refresh those results.

This can be tremendously useful, and the in-progress Postgres 9.4 development already has some exciting improvements.

And more

There are a number of less prominent additions and fixes, ranging from performance improvements, to more flexible DDL commands (e.g., CREATE SCHEMA ... IF NOT EXISTS), to event triggers for better tooling hooks. There have been over 1700 commits since 9.3 development started in earnest here:

commit bed88fceac04042f0105eb22a018a4f91d64400d
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Wed Jun 13 20:03:02 2012 -0400

    Stamp HEAD as 9.3devel.
    
    Let the hacking begin ...

You can read further on whats new over at the PostgreSQL wiki.

Beta status

As Postgres moves from 9.3 beta releases to a 9.3.0 GA release, we are moving out support for this version from alpha to beta. As it is still a beta product, it comes with several conditions:

  • Existing 9.3 beta databases must be upgraded to 9.3.0 via pgbackups. Followers and forks of 9.3 beta are no longer supported.
  • 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.
  • Forks, followers, and other functionality may see a higher level of issues during the beta. Please let us know about any problems you run into.

Conclusion

Postgres 9.3 continues the tradition of adding great features, performance improvements, and maintaining a serious concern for data integrity. Get started by provisioning your Postgres 9.3 database:

$ heroku addons:add heroku-postgresql:crane --version 9.3

Since this is a beta offering, we are especially interested in hearing your feedback. Please let us know what you think and how you’re using it by contacting us at postgres@heroku.com.

Win a ticket to Postgres Open and visit us there

by Craig Kerstiens - Aug 26

The Heroku Postgres team is hitting the road in coming months and we’d love to connect with you. If you’d like to connnect with us at any of the events below drop us a line postgres@heroku.com or @HerokuPostgres

Postgres Open

The first opportunity to connect with us is in September at Postgres Open. If you’ve already got your tickets for Postgres Open join us for drinks and/or pizza at Clark Ale House on Tuesday September 17, and make sure to check out talks by me and Peter Geoghegan at the conference.

If you don’t already have your ticket for Postgres Open but are interested in going, we’ve got a chance for you to win a ticket from us for free.

Win a ticket to Postgres Open

We’re giving away 3 tickets to PG Open. For your chance to win a ticket we want to see the creative ways you’re using dataclips with your Heroku Postgres database. Submissions can be a really impressive query or a great integration, the key to either is that it should be empowering you to better run your business with this data.

To enter 1. create your dataclip or integration then 2. submit the following to postgres@heroku.com:

  1. Your name
  2. Your role
  3. Your organization and what the company does
  4. A link to the dataclip or to the integration that you’ve built.

We will be announcing winners on August 29, 2013. The winners will be chosen at the sole discretion of the Heroku Postgres team.

PostgreSQL Conf EU

Those of you in Europe will have an opportunity to connect with the team as well. A large part of the team will be at PG Conf EU, including talks by 4 of our team members:

Conclusion

If you’re going to be at either of the above conferences we’d love to talk to you. However, you don’t have to wait until these conferences, if you ever have operational issues with you database you can find help at help.heroku.com or for product related questions and feedback you can contact us at postgres@heroku.com

JavaScript in your Postgres

by Craig Kerstiens - Jun 05

The same JavaScript engine that powers the web today is now available in your database.

This is one more step in evolving a data platform to meet all of your data needs. With a key/value store inside Postgres you gained agility in working with your schema. This agility was further improved with the JSON data type in Postgres 9.2. With geospatial support you removed the need for relying on additional tools for building location based apps. And today we’re continuing to expand, going beyond SQL bringing the full power of the V8 JavaScript engine to your Heroku Postgres database. This offering is available immediately in public beta on all production tier databases.

More on V8

V8 is a powerful and fast JavaScript engine that was developed by Google, in addition to powering Google Chrome it can be found in Node.js and MongoDB. From its initial design V8 was intended to work both for browsers to run client side JavaScript and be integrated into other projects such as powering server side execution in the case of Node.js.

PL/V8, thanks to a lot of work from Hitoshi Harada, is this same V8 but as a procedural language within Postgres. PL/V8 is fully trusted language giving you a peace of mind when it comes the safety of your data, but enables a whole new powerful set of functionality. Want to write functions on your data without touching pl-pgsql? Want to put documents within your database? Want to run your CoffeeScript unit tests closer to your data? You now can do all of it with PL/V8.

Getting started

If you’re already taking advantage of the JSON datatype for some of your applications and want to begin using PL/V8, now you can by simply enabling the extension:

> CREATE EXTENSION plv8;

From here we can create a simple JavaScript procedure that returns the values for an array of keys we pass in:

> CREATE OR REPLACE FUNCTION plv8_test(keys text[], vals text[]) RETURNS
text AS $$
var o = {};
for(var i=0; i<keys.length; i++){
 o[keys[i]] = vals[i];
}
return JSON.stringify(o);
$$ LANGUAGE plv8 IMMUTABLE STRICT;
Of note in the above function is `IMMUTABLE` and `STRICT`. Immutable specifies that the function given the same inputs will return the same result. The optimizer therefore knows that it can pre-evaluate the function. If you lie to the optimizer, it will give you wrong answers. Strict means that if you send in NULL values you’ll get a null result.

And then take advantage of it:

> SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Craig', '29']);
          plv8_test
-----------------------------
 {"name":"Craig","age":"29"}
(1 row)

More Advanced PL/V8 Usage

Lets take a look at a more practical use case. Given some example JSON data such as:

> SELCT * FROM zips;
                                 data
---------------------------------------------------------------------
 {"city": "ACMAR", "loc": [-86.5, 33.5], "pop": 6055, "state": "AL"}
 {"city": "ARAB", "loc": [-86.4, 34.3], "pop": 13650, "state": "AL"}
...

It may be common to filter this data for some report, i.e. all cities with population greater than 10,000. To do this you first create a function – by creating a generic function that returns numeric value of a given key from a set of JSON, you can also re-use it elsewhere:

> CREATE OR REPLACE FUNCTION 
get_numeric(key text, data json)
RETURNS numeric AS $$
return data[key];
$$ LANGUAGE plv8 IMMUTABLE STRICT;
CREATE FUNCTION

Then we can use the function in our query:

> SELECT * 
FROM zips 
WHERE get_numeric('pop', data) > 10000;
                                 data
------------------------------------------------------------------------
{"city": "PERU", "loc": [-89.1, 41.3], "pop": 10050, "state": "IL"}
{"city": "JUNO", "loc": [-84.1, 34.3], "pop": 10196, "state": "GA"}
...

Functional Indexes

The ability to use JavaScript as part of your query through user defined functions provides great flexibility and continues to expand beyond just including javascipt snippets inline in your queries. Postgres allows you to create indexes on any expression, including functions. With PL/V8, it is possible to create an index on the function above:

> CREATE INDEX idx_pop 
ON zips(get_numeric('pop'::text, data));

Functional indexes that take advantage of V8 can also prove some great performance benefits. By adding the above index the query time goes from 206.723 ms down to 0.157 ms.

Summary

The world of application development is rapidly changing delivering new tools every day to make you more productive. Postgres and the database world are no different, now with JavaScript and JSON support. This powerful functionality is now available on all Heroku Postgres production tier databases – run CREATE EXTENSION plv8; on your database to get started today.

Postgres 9.3 Beta Available

by Craig Kerstiens - May 15

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.

Archives Browse the post archives.

Subscribe Subscribe to the full-text RSS feed.