Postgres Weekly Issue 29
September 25, 2013
Featured
Postgres Open 2013 Wrapup
Postgres Open was last week in Chicago, with talks targeting a broad range of users, many delivered from major contributors to Postgres. While videos are still to come, many of the slides from the conference are becoming available.
PostgreSQL Wiki
PostgreSQL 9.3 and JSON [PDF]
JSON support in PostgreSQL 9.3 is much improved from PostgreSQL 9.2. One of the major contributors to the JSON support was at Postgres Open in Chicago last week and gave a great deeper dive into what changes came along with 9.3.
Andrew Dunstan
From our sponsor
Heroku Postgres - Fully managed Postgres on EC2
There's a lot to worry about when building your application; ensuring your database is secured, backed up, available shouldn't have to be part of that. Let Heroku Postgres focus on keeping your database running so you can take advantage of all the awesome features within Postgres.

Heroku Postgres is the worlds largest provider of Postgres as a Service, offering fully managed databases on top of EC2. Get started today for free and scale easily as your application needs to.
The rest
Returning Hierarchical Data in a Single SQL Query
Hierarchical data is common in a lot of applications, working with and reporting against it isn’t always as easy as getting it there in the first place. Taking advantage of CTEs (Common Table Expressions) which are still a heavily underrated feature can make it much easier.
Andrew Bender
Using JSON data type in production with Socorro
JSON data is everywhere, but those using it in large applications within Postgres may still be a pretty small number as support only came in Postgres 9.2. With it continuing to improve in 9.3 we’ll see more adopt, and having large applications such as Socorro by Mozilla take advantage of it will on further validation of its value.
Selena Deckelmann
Federating Queries Using postgres_fdw [PDF]
Foreign data wrappers promise to be a great improvement now and going forward for Postgres. Whether its dealing with disparate data sources or looking to build a full federated database they make it much easier. Here’s one of two talks at Postgres Open which dig into taking advantage of the Postgres FDW available in 9.3.
Scalable Uniques in Postgres with HLL
If you’ve ever done a SELECT count() in Postgres across a large dataset you’ve experienced the pain of how slow this can be. HyperLogLog is an algorithm for cardinality estimation which can get very close to count() with a much smaller footprint. Here’s slides from a brief talk by myself at Postgres Open on the PostgreSQL extension for HLL.