Postgres Weekly Issue 7
April 24, 2013
If you want to follow us or refer to the newsletter on Twitter at all, we now have an official Postgres Weekly Twitter account at @postgresweekly. It's still very new so you could even become the first follower ;-)
PGStrom: Postgres on the GPU
Foreign Data Wrappers allow the ability to connect from within Postgres to external sources. PGStrom is a module of an FDW that allows developers to take advantage of the GPU for additional performance gains in specific use cases.
Running PostgreSQL on Compression-enabled ZFS
It's a common request for wanting Postgres to have some form of compression. If size is a concern for you the people over at Citus DB show how Postgres can play well with taking advantage of compression enabled ZFS – providing up to 4x compression.
Using JSON in Postgres and Node.js
With Postgres 9.2 supporting JSON natively it was only a matter of time before more languages and frameworks started taking advantage of it. Here's a post that highlights how you can do it, or even better get it natively as of 2 days ago in node-postgres
Postgres 9.3 feature highlight: JSON parsing functions
Last week Michael highlighted some of the additional JSON operators that will be available in Postgres 9.3. This week he gives attention to various JSON functions that will be available – making the JSON support even richer.
A very simple custom aggregate
Postgres has a robust set of functions out of the box, though if you're looking for more flexibility you can create your own. Here's a great example of creating a simple custom aggregate which highlights how you could use them to solve a variety of problems.
Using array_agg in Postgres
In addition to the array datatype theres a variety of functions dealing with arrays that create powerful functionality within Postgres. Learn how to take advantage of array_agg to aggregate across rows and group data in a way that makes both developing and reporting easier.
Finding your One Metric That Matters using SQL
Limiting the number of things you're tracking early on can help you focus. Here's a great post that gives some of that info; but perhaps most interestingly shows a great example of calculating user retention by using window functions within Postgres.