Course Outline
1. Demystifying the PostgreSQL Query Planner
- Understanding execution plans and Planner algorithms (including classic and genetic approaches)
- Deep-dive analysis of execution plans (focusing on data access and join strategies)
- Directing plan selection through configuration parameters and the pg_hint_plan tool
2. Query Planner Statistics
- Estimating execution plan costs accurately
- Examining the default statistical models
- Utilising the ANALYZE command and leveraging extended statistics
3. Strategic Use of Indexes
- B-tree implementations (single column, composite, function-based, and partial indexes)
- Hash index applications
- BRIN index optimisation
- GiST and GIN index strategies
4. Advanced Table Structures for Performance
- Implementing partitioned tables for scalability
- Managing unlogged tables for high-write scenarios
- Utilising temporary tables effectively
- Designing and refreshing materialised views
5. Optimising Cache Memory
- Configuring and managing Buffer Cache
- Tuning Work Memory for sorting and hashing
- Allocating Maintenance Work Memory for background processes
6. Parallel Query Execution
- Understanding the parallel architecture
- Configuring parallelism parameters
- Anlysing execution plans to assess parallel query efficiency
7. Workload and Performance Monitoring
- Strategies for logging and analysing slow queries
- Implementing the auto_explain extension for automated insights
- Leveraging the pg_stat_statements extension for query statistics
- Interpreting cumulative statistics for long-term trends
8. Benchmarking with PgBench
Requirements
- Successfully completing the PostgreSQL Server Administration module or possessing equivalent foundational expertise
- Demonstrable practical experience with SQL standards and PostgreSQL administrative tasks
Target Audience
This course is tailored for Database Administrators, DevOps Engineers, and Developers tasked with maintaining high-availability PostgreSQL systems and optimising production workloads.
Testimonials (3)
I expanded my knowledge on DB performance management, some are directly usable while others provide great insight/context for me.
Longze Chen - Center for Open Science
Course - PostgreSQL Advanced - Performance Management
Different index and table types
Brian J. Geiger - Center for Open Science
Course - PostgreSQL Advanced - Performance Management
performance knowledge