Postgres Weekly Issue 14: June 12, 2013

Postgres Weekly

Issue 14
June 12, 2013

Featured

PLV8 or Javascript Support inside Postgres has gotten a lot of attention recently including talks from @leinweber and @selenamarie and multiple posts which we've featured in recent weeks. This support for the V8 engine inside Postgres is now available on Heroku Postgres so you don't have to build and install it yourself.

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

PostgreSQL FDW to Hadoop Hive Tutorial

Foreign Data Wrappers (FDWs) are one of the hot topics lately in Postgres, and for good reason they let you pull in data across different systems making working with disparate data manageable. Here's one more tutorial in working with a FDW, in this case accessing data in Hadoop Hive.

Window Functions in Action

Often times when you feel you need to pull data into a higher level language to compute the report you want to see Window functions may be what you need. Though they're commonly a difficult concept to grasp without seeing them in action. Here's a powerful example not only using window functions but combining common table expressions, arrays, composite types and more.

Automatic Scopes and Indexing Hstore

Hstore is great for getting flexibility within your database, though most ORMs don't have great support for it yet. Here's a great example how you can index it and generate automatic scopes for it.

Converting from MySQL to Postgres

Here's one more case of someone migrating from MySQL to Postgres. While short; it highlights that the process was relatively painless and some of the unexpected gains that came along with it.

Aggregating Hierarchies With Postgres

If you have hierarchies within your application going one direction is often easy, but the other is hard. Often you'd resort to pl-pgsql to work with them, here's another good example of how you can be more expressive in Postgres when working with aggregating them.

Schema liberation with JSON and plv8 (and Postgres)

Two weeks ago we had @selenamarie's script for liberating your relational schema by converting it to JSON. Here's the broader set of slides that accompanied the talk around liberating your schema given at JSConf.

A job

Data Systems Administrator at Luth Research

Luth Research research is searching for a data systems administrator. If you've got experience with Postgres or Greenplum, experience working with datasets from 1 TB to 50 TB, and enjoy working with cutting edge technologies consider applying.