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