PGCon2015 - final

PGCon 2015
The PostgreSQL Conference

James Hanson
Day Talks - Day 4 - Friday - 2015-06-19
Room DMS 1140
Start time 13:00
Duration 00:45
ID 780
Event type Podium
Track Applications
Language used for presentation English

If you can't beat 'em, join 'em (… a pun)

Why, when and how you can integrate documents and key-value pairs into your relational model

There is a pitched battle going on between the relational, document-based, key-value and other data models. PostgreSQL is uniquely capable of leveraging many of the strengths of multiple data models with JSON(b), HSTORE, XML, ltree data types, arrays and related functions. This presentation outlines the use-cases, benefits and limitations of document-based, key-value and hierarchical data models. It then presents practical advice and code snippets for incorporating them into PostgreSQL's relational framework. The presentation ends with SQL examples and code snippets for loading, accessing and modifying (where possible) JSON, HSTORE, XML, ltree and array data types.

This presentation begins with a very quick review of the rationale, benefits and implications of the relational data model. It then does the same for document-based models and hierarchical models. The balance of the presentation works with three publicly available data sets, world-wide airports (, Wikipedia Inbox key-value pairs ( and Google address JSON objects, showing how they can be be incorporated into a simple relational model. The presentation also includes snippets of code for loading the files and accessing elements. The full SQL, and shell code will be available on the web site.