Course Outline
1. Mastering the PostgreSQL Query Planner
- Execution plans and Planner algorithms (classic, genetic)
- Examining execution plans (data access techniques, join strategies)
- Influencing plan selection (config settings, pg_hint_plan)
2. Query Planner Statistics
- Estimating the cost of execution plans
- The default statistical model
- ANALYZE operations and extended statistics
3. Leveraging Indexes
- B-tree indexes (single column, composite, function-based, partial)
- Hash indexes
- BRIN indexes
- GiST and GIN indexes
4. Advanced Table Structures
- Partitioned tables
- Unlogged tables
- Temporary tables
- Materialized views
5. Optimizing Cache Memory
- Buffer Cache
- Work Memory
- Maintenance Work Memory
6. Parallel Query Processing
- Architectural overview
- Relevant configuration parameters
- Analyzing parallelized execution plans
7. Workload and Performance Monitoring
- Capturing slow queries via logging
- Utilizing the auto_explain extension
- Employing the pg_stat_statements extension
- Tracking Cumulative Statistics
8. Benchmarking with PgBench
Requirements
- Successfully completed PostgreSQL Server Administration or possess equivalent expertise
- Demonstrated proficiency in SQL and day-to-day PostgreSQL operations
Target Audience
This course is tailored for Database Administrators, DevOps Engineers, and Developers who are accountable for the optimization and maintenance of PostgreSQL systems within 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