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
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.
This can be tremendously useful, and the in-progress Postgres 9.4 development already has some exciting improvements.
from https://postgres.heroku.com/blog/past/2013/9/9/postgres_93_now_available/
----------------------------------------
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 asCREATE 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 ...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.3from https://postgres.heroku.com/blog/past/2013/9/9/postgres_93_now_available/
----------------------------------------
Connection Limit Guidance about postgres
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.
from https://postgres.heroku.com/blog/past/2013/11/22/connection_limit_guidance/
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.
from https://postgres.heroku.com/blog/past/2013/11/22/connection_limit_guidance/
