PGCon2011 - Add 4 Video (2015.09.18)

PGCon 2011
The PostgreSQL Conference

Speakers
Lorenzo Alberton
Schedule
Day Tutorials - 2 - 2011-05-18
Room DMS 1120
Start time 13:00
Duration 03:00
Info
ID 357
Event type Workshop
Track Advanced Features
Language used for presentation English

Trees and Graphs in the database

RDBMS in the social networks age

Despite the NoSQL movement trying to flag traditional databases as a dying breed, the RDBMS keeps evolving and adding new powerful weapons to its arsenal. In this talk we'll explore Common Table Expressions (SQL-99) and how SQL handles recursion, breaking the bi-dimensional barriers and paving the way to more complex data structures like trees and graphs, and how we can replicate features from social networks and recommendation systems. We'll also have a look at window functions (SQL:2003) and the advanced reporting features they make finally possible.

The first part of this talk will cover several different techniques to model a tree data structure into a relational database: parent-child (adjacency list) model, materialized path, nested sets, nested intervals, hybrid models, Common Table Expressions. Then we'll move one step forward and see how we can model a more complex data structure, i.e. a graph, with concrete examples from today's websites. Starting from real-world examples of social networks' and recommendation systems' features, and with the help of some graph theory, this talk will explain how to represent and traverse a graph in the database. Finally, we will take a look at Window Functions and how they can be useful for data analytics and simple inline aggregations, among other things.

All the examples have been tested on PostgreSQL >= 8.4