Course Outline
1. Demystifying the PostgreSQL Query Planner
- Insight into query execution plans and Planner algorithms (including classic and genetic approaches)
- Deep-dive analysis of execution plans, focusing on data access and join methodologies
- Strategies for influencing plan selection via configuration parameters and the pg_hint_plan extension
2. Query Planner Statistics & Intelligence
- Understanding cost estimation mechanics within execution plans
- Exploration of the default statistical models
- Application of the ANALYZE command and utilization of extended statistics
3. Strategic Indexing Techniques
- B-tree index variations (single column, composite, function-based, and partial)
- Implementation and use of Hash indexes
- Leveraging BRIN indexes for large datasets
- Advanced indexing with GiST and GIN structures
4. Advanced Table Structures for Performance
- Optimization through partitioned tables
- Benefits and usage of Unlogged tables
- Efficient management of Temporary tables
- Utilization of Materialised views for complex reporting
5. Effective Cache Memory Management
- Optimization of Buffer Cache settings
- Tuning Work Memory allocations
- Configuring Maintenance Work Memory
6. Parallel Query Execution
- Overview of parallel processing architecture
- Key configuration parameters for parallelism
- Analysis of parallelized query execution plans
7. Workload Visibility & Performance Monitoring
- Techniques for logging and analyzing slow queries
- Integration and usage of the auto_explain extension
- Leveraging the pg_stat_statements extension for aggregation
- Interpretation of Cumulative Statistics
8. Performance Benchmarking with PgBench
Requirements
- Successful completion of PostgreSQL Server Administration or demonstrated proficiency in equivalent topics
- Practical experience with SQL syntax and day-to-day PostgreSQL operations
Target Audience
This course is tailored for Database Administrators, DevOps Engineers, and Developers who are responsible for optimizing and sustaining PostgreSQL systems in live production environments.
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