•  

Trees and Graphs in the database

A session at PGCon 2011

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

About the speaker

This person is speaking at this event.
Lorenzo Alberton

CTO at DataSift

Coverage of this session

Sign in to add slides, notes or videos to this session

Tell your friends!

When

Date Thu 19th May 2011

Short URL

lanyrd.com/stqgx

View the schedule

Share

Topics

See something wrong?

Report an issue with this session