Search, explore and find the perfect course for you
Advanced PostgreSQL for Programmers: Functions and Data Structures
PostgreSQL is the World's Most Advanced Open Source Database. It features a comprehensive set of data types, robust relational operations, and an ability to extend its capabilities in a number of different languages.
More recently there has been an effort to see PostgreSQL as a database that can bridge the SQL and NoSQL worlds.
This course describes how to extend PostgreSQL in order to work with trees, graphs, semi-structured and unstructured data while maximizing performance and scalability.
Backend developers and DBA's who are looking to improve their skills regarding performance, semistructure/unstructured data, and more.
Must be familiar with SQL as a query language, and comfortable with writing queries incuding JOINs complex expressions.
What you will learn
Part 1: Review of Relational Theory
- Relational model assumptions
- SQL/Relational mismatch
- Atomic fields and First Normal Form
- ACID as consistency model
Part 2: Object/Relational Database Theory
- Intelligent data types
- Ability to extend SQL
- Overview of PostgreSQL data types
Part 3: Query plans and Performance Basics
- How a query is executed
- Overview of table scan types
- Overview of join types
- Join type limitations
- How to read a query plan
Part 4: Large field storage (TOAST) and performance
- PostgreSQL on-disk layout
- How indexes work on PostgreSQL
- How we store data that is too large.
- How we can measure performance of retrieving large fields.
Part 5: Tree structures of arbitrary depth
- Introducing the Recursive Common Table Expression
- How a recursive CTE is executed
- Performance and recursive CTE's
Part 6: Graphs
- What is a graph?
- Modelling graphs in the database
- Recursive CTE's and breadth-first searches
- Handling Cycles and Undirected Graphs
Part 1: Functional Programming and the RDBMS
- Basic theory of FP
- Referential Transparency
Part 2: Advanced functional techniques with PostgreSQL
- parsing semi-structured files in PostgreSQL in pl/perl
- Treating tables as classes and rows as objects
- Lateral join and hard-wired plans via recursive cte's
- Loose index scan
Part 3: Generating XML
- generating complete XML documents
- Generating XML based on a full table.
- Turning a query into an xml document
Part 4: Querying XML
- Regular expressions
- Untrusted languages
Part 5: Generating JSON and JSONB
- JSON vs JSONB demo and history
- Generating JSON and JSONB from queries
- Comparing and contrasting with XML functionality
- manipulating json/jsonb values in sql
Part 6: Querying json
- iterating through arrays
- turning objects into rows
- Limitations in current json support
- jsonb and indexing