PostgreSQL is the world's most advanced open-source relational database. This training equips developers and data engineers with the knowledge to design robust schemas, write efficient queries, leverage PostgreSQL's powerful server-side programming features, and diagnose performance bottlenecks using EXPLAIN and EXPLAIN ANALYZE.

Who is this training for?

Developers, database engineers, and architects who work with PostgreSQL and want to go beyond simple SELECT queries to understand advanced data modelling, performance tuning, and server-side logic.

Agenda

Introduction

• SQL overview and relational concepts
• PostgreSQL architecture

Table Management

• Primary key and unique constraints
• Foreign keys and referential integrity
• PostgreSQL data types: money, binary, enums, geometric types
• Managing columns and constraints
• Sequences and auto-increment
• User-defined functions
• Mathematical, string, and date/time functions
• Type casting with CAST

Views

• Simple and nested views
• Subqueries
• View permissions
• Updatable views and data management through views
• Materialized views and caching

Query Rewrite Rule System

• Query tree internals
• Defining and using rules
• Qualified rules
• Views and rules interaction

Client Programming

• ORM usage and anti-patterns
• The N+1 query problem
• Writing raw SQL instead of ORM
• Transactions and isolation levels
• Savepoints

Server-Side Programming

• PL/pgSQL and other procedural languages
• Function parameters and return values
• Control flow: branches, loops, and exceptions
• Stored functions and SQL integration
• Triggers and trigger functions

SQL Performance

• Reading EXPLAIN output
• EXPLAIN ANALYZE for runtime profiling
• Common performance optimisation techniques