Postgres Weekly Issue 18
July 10, 2013
Featured
PGConf.EU 2013 Now Open for Registration
PGConf.EU is the largest PostgreSQL dedicated conference in the world. It's in Dublin, Ireland, on October 29th - November 1st, and is now open for registration.
A Tour of PostgreSQL Data Types
Want to learn a little more about Postgres datatypes? Here's 150+ thorough slides that will walk you through them including the built in datatypes, custom types, and types available through extensions such as cube and hstore.
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.
Focus on 'hstore'
hstore vs. JSON - Which to use in Postgres
Over the past year both the hstore and JSON datatypes have gotten a lot of attention, and deservingly so. But, when to use which is a common question still. Here a take a quick stab at some guidance of when you should use which.
JSON vs hstore: Which will get you into a cool bar in the Mission?
A rebuttal to the other hstore vs JSON included in this weeks newsletter. This article by @XoF attempts give some even further guidance of what cases you prefer one over the other.
Hstore development for 9.4 release
To really add to the hstore vs. JSON discussion this week the creator of hstore, Oleg, has a preview of where hstore is headed in Postgres 9.4. While we're still waiting for 9.3 to fully arrive, these kind of improvements to hstore in 9.4 will already create some great excitement for it.
The rest
First steps with pgbouncer, set and run it
For most production applications at some point you want a connection pooler for your application. Pgbouncer is a common server side one thats heavily recommended, check out how to get it up and running here.
Simple Case for Pivoting in SQL
Last week I highlighted the tablefunc extension for pivoting in Postgres. This week @tapoueh highlights doing the same with another convenient approach.
Distributed locking in Postgres
Postgres has a great set of primitives for row level locking, which can be handy in handling coordination among distributed processes. Here's a great overview with some clear examples of how you can take advantage of it.
django-postgres-fuzzycount: A Django model manager providing fast / fuzzy counts
Select (*) is typically quite slow in Postgres, but approximate counts can be much faster. Here's a package which provides providing faster fuzzy counts for PostgreSQL database tables natively in Django.
GitHub