We're constantly involved in improving Postgres on behalf of our users. That kind of work includes building new features into our platform like data clips, tracking down bugs uncovered by our users and getting them fixed, and working to bring the needs of our users to the attention of the developer community driving the project forward.
Of course, all that pales in comparison to the work the community does every day, and there's no bigger demonstration of that than the major PostgreSQL releases which introduce new features and generally come out once a year. When they do, we build support for that version almost immediately, and roll it out to gradually larger audiences as we receive requests, uncover its use cases, and prove it operationally on our own internal projects.
I'm pleased to announce that as of today, all new databases created will run PostgreSQL 9.1. There are no known issues with 9.0 compatibility (here are the release notes), so if you're excited about any of the new functionality, please feel free to upgrade today using pgbackups to migrate your data.
Cancelable Queries
In addition to making 9.1 the new default, I also wanted to draw your attention to a feature we cherry-picked from the upcoming 9.2 release and made available to all new databases as of today.
For most users, queries come and go in an instant, and a slow query is anything that takes longer than a hundred milliseconds. Query cancellation is a problem that really only rears its head once you reach a certain size. For example, occasionally a migration which was instantaneous in your testing environment might surprise you with a long-lasting lock on a table in production and bring your site to its knees. Unfortunately, when our users were faced with this problem in the past, they weren't able to cancel those queries without opening a support ticket.
Fortunately, it's no longer a problem. Here's how the solution looks to a user:
=> SELECT pg_cancel_backend(procpid)
FROM pg_stat_activity
WHERE current_query LIKE '%your giant accidental query%';
pg_cancel_backend
-----------------
t
=>
So how did this get fixed? This exact problem was causing pain to some of our customers with very large datasets. We brought the issue to the Postgres community's attention, and proposed a patch. The patch that eventually became a part of 9.2 ended up being dramatically different (and much better) than the one we put forward, but now because of the feedback we got from our customers and the relationship we have with the Postgres developer community, everyone's database is just a little bit better.