Working with High Performance PostgreSQL
Advanced Course for Developers
Deep-dive course by the recognised PostgreSQL contributing sponsor, Data Egret, designed to help developers build fast and reliable applications with PostgreSQL at scale.
The course covers advanced indexing techniques, query optimisation, SQL best practices, interpreting EXPLAIN plans, and more.
of focused content
from recognised top PostgreSQL experts
of intensive PostgreSQL learnings
with live demos and field-tested techniques
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.
Indexing Techniques
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.
Statistics for the Planner
This chapter 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.
SQL - What not to do
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.
Introduction to SQL Optimisations
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.
Understanding EXPLAIN
This chapter explains how to read and interpret PostgreSQL’s execution plans:
- EXPLAIN options:
EXPLAIN,EXPLAIN ANALYZE, with options likeBUFFERS,WAL, andSETTINGS. - 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.
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,
6 October
13:00–15:30
Wednesday,
8 October
09:00–11:30
13:00–15:30
Friday,
10 October
09:00–11:30
Monday,
13 October
13:00–15:30
Wednesday,
15 October
09:00–11:30
13:00–15:30
Register now!
Course duration: 7 sessions; 2.5 hours each
Course cost: €850 before taxes
📝 Following the form submission: You’ll receive payment instructions via email.
💳 Payment Methods: Credit card or direct bank transfer.
⚠️ Important: Your spot is only confirmed once payment is received.
Instructors
He is recognised PostgreSQL contributor and is one of the contributors to pgBackRest - a reliable backup and restore solution for PostgreSQL.
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.
PostgreSQL Database Administrator