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. Exploring the PostgreSQL Query Planner
- Execution plan analysis and Query Planner mechanisms (traditional and genetic algorithms)
- Interpreting execution plans, focusing on data access and join strategies
- Guiding plan selection via configuration settings and pg_hint_plan
2. Query Planner Statistical Data
- Estimating execution plan costs
- The default statistical model
- Utilizing the ANALYZE command and extended statistics
3. Leveraging Indexes
- B-tree indexes, including single-column, composite, functional, and partial variants
- Hash-based indexes
- BRIN (Block Range Indexes)
- GiST and GIN indexes
4. Advanced Table Architectures
- Table partitioning strategies
- Unlogged tables
- Temporary tables
- Materialized views
5. Managing Cache Memory
- Buffer Cache optimization
- Work Memory configuration
- Maintenance Work Memory settings
6. Parallel Query Processing
- Underlying architecture
- Relevant configuration parameters
- Analyzing execution plans for parallelized queries
7. Monitoring Workloads and Performance
- Logging and tracking slow queries
- Implementing the auto_explain extension
- Leveraging the pg_stat_statements extension
- Reviewing cumulative statistics
8. Performance Benchmarking with PgBench
Requirements
- Completion of the PostgreSQL Server Administration course or possession of equivalent proficiency
- Practical experience in SQL and day-to-day PostgreSQL administration
Target Audience
Database Administrators, DevOps Engineers, and Developers tasked with optimizing and maintaining PostgreSQL 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.
