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. Mastering the PostgreSQL Query Planner
- Examining execution plans and understanding Query Planner algorithms (including classic and genetic methods)
- Evaluating execution plans to identify data access and join methods
- Influencing plan selection through configuration parameters and the pg_hint_plan extension
2. Query Planner Statistics
- Understanding the logic behind execution plan cost estimation
- Reviewing the default statistical models
- Utilizing the ANALYZE operation and leveraging extended statistics
3. Leveraging Indexes
- Implementing B-tree indexes (covering single column, composite, function-based, and partial types)
- Applying Hash indexes
- Utilizing BRIN indexes
- Working with GiST and GIN indexes
4. Advanced Table Structures
- Managing partitioned tables
- Using Unlogged tables
- Working with Temporary tables
- Leveraging Materialised views
5. Optimizing Cache Memory
- Configuring Buffer Cache
- Tuning Work Memory
- Adjusting Maintenance Work Memory
6. Parallel Query Execution
- Understanding the underlying architecture
- Configuring relevant parameters
- Analyzing execution plans for parallelized queries
7. Workload and Performance Monitoring
- Configuring logs to capture slow queries
- Implementing the auto_explain extension
- Utilizing the pg_stat_statements extension
- Analyzing Cumulative Statistics
8. Benchmarking with PgBench
Requirements
- Successful completion of the PostgreSQL Server Administration course or possession of equivalent foundational knowledge
- Practical experience with SQL and daily PostgreSQL administration
Target Audience
This course is ideal for Database Administrators, DevOps Engineers, and Developers who are 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.
