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
- 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 (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.
