<- Back to all courses

Working with High Performance PostgreSQL

Advanced Course for Developers

Deep-dive course by the recognised PostgreSQL contributing sponsor, Data Egret GmbH, designed to give developers the skills to work with PostgreSQL in production and keep it running smoothly at scale.

Covers indexing, query optimisation, SQL best practices, DDL/DML migrations, EXPLAIN troubleshooting, and more.

Register
hours
0

of focused content

from recognised top PostgreSQL experts

days
0

of intensive PostgreSQL learnings

with live demos and field-tested techniques

zoom sessions
0

to earn your ‘Working with High Performance PostgreSQL’ attendance certificate

What You’ll Learn

Introduction to Execution Plans

This chapter introduces how PostgreSQL decides the best way to execute SQL queries. Key concepts include:

  • Query planning and optimization: the optimizer evaluates multiple execution paths and chooses the most efficient based on cost estimation.
  • Execution nodes: each plan is a tree of nodes like Seq Scan, Index Scan, or Join.
  • Cost computation: the planner uses statistics about tables and indexes to estimate the execution cost.
  • Tools for visualization: some online visualizers (e.g., explain.depesz.com) help interpret query plans.


This chapter explains index structures and how to use them effectively:

  • Types of indexes: introduction to PostgreSQL-supported index types (B-tree, GIN, GiST, BRIN, and others).
  • Partial and functional indexes: these target specific use cases and optimize performance.
  • Common pitfalls: some queries may not use indexes due to function usage, non-selective patterns (e.g., LIKE '%...'), or invalid indexes.
  • Best practices: choose indexes based on query patterns and ensure statistics are up to date.


This section explores the role of statistics in query optimization:

  • Types of statistics and their collection: managed by ANALYZE, which can be configured for frequency and detail.
  • Challenges: stale or misleading stats can lead to poor plan choices.
  • Extended stats: from version 10+, PostgreSQL can store correlations and dependencies across columns.


This chapter lists anti-patterns and common mistakes in SQL development:

  • Bad modeling practices: avoid key-value schemas, multi-column fields, overly wide tables, or non-normalized designs.
  • Misuse of SQL features: improper use of NULLs, implicit column orders, and excessive procedural logic in queries.
  • Atomicity and constraints: always enforce data integrity with constraints.
  • Inefficient queries: avoid overusing subqueries, and be mindful of indexing and pagination practices.
  • Transaction management: manage locking, deadlocks, and transaction length to avoid contention.


This chapter gives the first advice and best practices on writing performant SQL:

  • Optimization targets: identify which queries to optimize through analysis.
  • Query simplification: avoid unnecessary subqueries, rewrite complex joins, and remove unused views.
  • Data access: limit the amount of data retrieved, avoid application-side filtering, and reduce connections overhead.
  • Transaction management: manage locking, deadlocks, and transaction length to avoid contention.
  • Scalability: introduction to data archiving and retention, best practices for large datasets including partitioning.


This chapter explains how to read and interpret PostgreSQL’s execution plans:

  • EXPLAIN options: EXPLAIN, EXPLAIN ANALYZE, with options like BUFFERS, WAL, and SETTINGS.
  • Reading plans: focus on estimated vs actual rows, cost metrics, and node types.
  • Common nodes: Seq Scan, Index Scan, Hash Join, etc.
  • Troubleshooting: look for mismatched estimates, redundant joins, and costly sorts or writes.


This chapter provides practical techniques to diagnose PostgreSQL performance issues:

  • PostgreSQL activity monitoring: internal views such as pg_stat_activity, pg_stat_database, and pg_locks reveal database session activity, transaction status, lock contention, and performance metrics.
  • Connection and lock management: learn to detect idle or blocked sessions, cancel queries, or terminate sessions safely using PostgreSQL functions.
  • Object and table insights: track table/index size, fragmentation, and usage statistics to guide vacuuming and indexing decisions.
  • Query analysis: leverage pg_stat_statements to identify slow, frequent, or resource-heavy queries, and monitor temporary files and I/O patterns.


This chapter outlines key strategies to safely manage schema (DDL) and data (DML) changes in PostgreSQL environments:

  • Prepare and test: use versioned, idempotent scripts, and test thoroughly in staging with rollback plans.
  • DDL guidelines: apply changes incrementally (e.g., add nullable columns first), avoid locking operations during peak traffic, and use IF EXISTS/IF NOT EXISTS for safe updates.
  • DML guidelines: perform updates in small batches, avoid long transactions, and stage complex changes using CTEs or temp tables.
  • Execution tips: automate with tools, monitor live metrics during rollout, and validate post-deployment integrity.

Format and Schedule

This extensive PostgreSQL course consists of seven 2.5-hour live sessions, delivered over five days.

To receive a certificate of attendance, participants must attend all seven sessions.

  • Sessions will be conducted in English via Zoom.
  • Participants must have the latest version of Zoom installed on their computer.
  • Please note: Sessions will not be recorded.
  • The course format combines theoretical slides with live demos, led by the instructor in real time. Each session includes a live Q&A for direct interaction.
  • There are no scheduled breaks during the 2.5-hour sessions.

Cost: €850 before taxes.

Session Schedule

(all times CEST)

Monday,
15 September

13:00–15:30

 

Wednesday,
17 September

09:00–11:30

13:00–15:30

 

Friday,
19 September

09:00–11:30

Monday,
22 September

13:00–15:30

 

Wednesday,
24 September

09:00–11:30

13:00–15:30

Register now!

📝 After Registration: 

You’ll receive an invoice by email.

💳 Payment Methods: 

Credit card or direct bank transfer.

⚠️ Important: 

Your spot is only confirmed once payment is received.

*By providing your information, you consent to its processing as described in our Privacy Policy and agree to receive updates and communications from Data Egret GmbH. You may withdraw your consent at any time.

Instructors

Stefan has close to 15 years of expertise in the database space, boasting extensive experience in client support, maintenance, and architectural consulting.

He is recognised PostgreSQL contributor and is one of the contributors to pgBackRest - a reliable backup and restore solution for PostgreSQL.
Stefan Fercot
Senior PostgreSQL Expert

Daria Nikolaenko is a database specialist with a strong focus on monitoring, high availability, performance tuning, and scalable system architecture. She has hands-on experience with PostgreSQL exporters and metric analysis, and brings deep expertise in building robust data infrastructures.

Daria Nikolaenko
PostgreSQL Database Administrator

© 2025 All Rights Reserved.