PostgreSQL Performance for Developers

Download the brochure (PDF, French)

Objectives

By the end of this training course, you will be able to write efficient SQL queries thanks to a thorough understanding of PostgreSQL's optimizer and execution engine 🐘. The theoretical part of this course will give you an in-depth knowledge of join algorithms and indexes. The practical sessions will teach you how to use it to analyze execution plans produced by the EXPLAIN command and apply what you learnt to rewrite your SQL queries or create the right indexes.

You will also become independent: able to diagnose recurring SQL query performance issues on your own and transfer your skills to other relational DBMS (Oracle, SQL Server, MySQL, DuckDB, etc.) and to the cloud (Azure SQL, Google BigQuery, etc.).

Target Audience

• Software developers working with databases;
• Database administrators or data engineers wishing to upskill or refresh their knowledge of how a DBMS works.

Course Outline

Day 1

The first day is dedicated to the inner workings of PostgreSQL's optimizer, the EXPLAIN command, and indexes.

Morning

• Functional model of a database optimizer
  • Syntactic and semantic analysis
  • Logical optimization (query rewriting)
  • Physical optimization (join order, choice of access and join algorithms)
  • Cost model
• The EXPLAIN command
  • Command options
  • Reading and understanding the results
• Quiz

Afternoon

• Indexes in PostgreSQL
  • Types of indexes
  • Defining and creating indexes in SQL
• Practical session
  • Identifying opportunities to create indexes
• Quiz

Day 2

The second day is dedicated to joins (their algorithms and how PostgreSQL uses them), as well as CTEs.

Morning

• Join algorithms
  • Overview of the algorithms
  • Comparing performance with EXPLAIN
• Common Table Expressions (WITH)
  • Breaking down complex queries
• Quiz

Afternoon

• Practical session
  • Optimizing the performance of a set of queries
• Quiz

Technical Environment

Learners do not need specific hardware or software, only a computer with Internet access and a recent browser. The learning database is hosted on a PostgreSQL server; access is provided via pgAdmin.

Pricing

The cost of this training course is €1600 (reduced to €1120), per group session on your premises (VAT not applicable, Article 293 B of the General Tax Code). Additional travel fees may apply outside of the Toulouse area.

Interested?

Feel free to contact me 📫 on LinkedIn or via email ! Together, we will refine your training needs according to the characteristics of your teams and your company. See you soon!