Course Outline
1. Grasping the PostgreSQL Query Planner
- Exploring query execution paths and Planner algorithms (both classic and genetic)
- Interpreting execution plans, with a focus on data access and join methodologies
- Managing plan selection through configuration adjustments and tools like pg_hint_plan
2. Query Planner Statistics
- Understanding cost estimation within execution plans
- Reviewing the default statistical models
- Leveraging the ANALYZE operation and extended statistics
3. Leveraging Indexes
- B-tree index types (single column, composite, function-based, and partial)
- Hash-based indexing
- BRIN indexing structures
- GiST and GIN index implementations
4. Advanced Table Structures
- Managing partitioned tables
- Utilizing unlogged tables
- Working with temporary tables
- Implementing materialised views
5. Optimizing Cache Memory
- Buffer Cache management
- Work Memory configuration
- Maintenance Work Memory tuning
6. Parallel Query Execution
- Understanding the underlying architecture
- Configuring relevant parameters
- Analyzing execution plans for parallelized queries
7. Monitoring Workloads and Performance
- Logging and reviewing slow queries
- Implementing the auto_explain extension
- Utilizing the pg_stat_statements extension
- Reviewing cumulative statistics
8. Performance Benchmarking with PgBench
Requirements
- Completion of the PostgreSQL Server Administration module, or possession of equivalent expertise
- Demonstrated hands-on experience with SQL syntax and daily PostgreSQL operations
Target Audience
This course is tailored for Database Administrators, DevOps Engineers, and Developers who are tasked with tuning and maintaining PostgreSQL within live production environments.
Testimonials (3)
I expanded my knowledge on DB performance management, some are directly usable while others provide great insight/context for me.
Longze Chen - Center for Open Science
Course - PostgreSQL Advanced - Performance Management
Different index and table types
Brian J. Geiger - Center for Open Science
Course - PostgreSQL Advanced - Performance Management
performance knowledge