Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Duration 14 hours
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 (2)
Tuning strategies.
Jeffrey Zieg - Matrix Consulting
Course - PostgreSQL Performance Tuning
Logging behaviour when the instance is under stress, and the hierarchy/nomenclature of instances, databases, files, etc.
