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.

  • SQL

Target audience

Backend developers and DBA's who are looking to improve their skills regarding performance, semistructure/unstructured data, and more.

Pre-requisites

Must be familiar with SQL as a query language, and comfortable with writing queries incuding JOINs complex expressions.

What you will learn

Day 1

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

Day 2

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

  • xml_forest
  • generating complete XML documents
  • Generating XML based on a full table.
  • Turning a query into an xml document

Part 4: Querying XML

  • XPath
  • 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

  • accessors
  • iterating through arrays
  • turning objects into rows
  • Limitations in current json support
  • jsonb and indexing

Course info

Course code: S310
Duration: 2 days
Price: 18 600 SEK
Language: English

Teachers

Share

Related courses

Contact us for details

+46 40 61 70 720
info@edument.se


All prices excluding VAT