Course Outline
1. Understanding the PostgreSQL Query Planner
- Execution plans and Planner algorithms (traditional and genetic)
- Interpreting query execution plans (data access and join methods)
- Guiding plan selection via configuration parameters and pg_hint_plan
2. Query Planner Statistics
- Estimating execution plan costs
- The default statistics framework
- The ANALYZE process and extended statistics
3. Leveraging Indexes
- B-tree indexes (single column, composite, function-based, and partial)
- Hash-based indexes
- BRIN indexes
- GiST and GIN indexes
4. Advanced Table Structures
- Partitioned tables
- Unlogged tables
- Temporary tables
- Materialised views
5. Utilising Cache Memory
- Buffer Cache management
- Work Memory allocation
- Maintenance Work Memory settings
6. Parallel Query Processing
- Architectural overview
- Relevant configuration parameters
- Analysing parallelised execution plans
7. Workload and Performance Monitoring
- Capturing slow queries through logging
- Implementing the auto_explain extension
- Utilising the pg_stat_statements extension
- Reviewing Cumulative Statistics
8. Benchmarking using PgBench
Requirements
- Completion of PostgreSQL Server Administration or comparable proficiency
- Practical experience with SQL and PostgreSQL operations
Target Audience
Database Administrators, DevOps Engineers, and Developers tasked with maintaining and optimising PostgreSQL in production settings.
Custom Corporate Training
Training solutions designed exclusively for businesses.
- Customized Content: We adapt the syllabus and practical exercises to the real goals and needs of your project.
- Flexible Schedule: Dates and times adapted to your team's agenda.
- Format: Online (live), In-company (at your offices), or Hybrid.
Price per private group, online live training, starting from 3200 € + VAT*
Contact us for an exact quote and to hear our latest promotions
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.
