Course Outline
1. Exploring the PostgreSQL Query Planner
- Overview of query execution plans and Query Planner algorithms (classic, genetic)
- Evaluating query execution strategies (data access techniques, join methods)
- Managing plan selection via configuration settings and pg_hint_plan
2. Query Planner Statistical Analysis
- Estimating the cost of execution plans
- Understanding the default statistical model
- The ANALYZE command and the use of extended statistics
3. Leveraging Indexes
- B-tree indexes (single column, composite, function-based, partial)
- Hash indexes
- BRIN indexes
- GiST and GIN indexes
4. Applying Advanced Table Structures
- Partitioned tables
- Unlogged tables
- Temporary tables
- Materialized views
5. Managing Cache Memory
- Buffer Cache configuration
- Work Memory settings
- Maintenance Work Memory settings
6. Parallel Query Execution
- Architectural overview
- Relevant configuration parameters
- Analysis of parallelized query execution plans
7. Monitoring Workloads and Performance
- Capturing logs for slow-running queries
- Utilizing the auto_explain extension
- Employing the pg_stat_statements extension
- Reviewing cumulative statistics
8. Performance Benchmarking with PgBench
Requirements
- Completion of PostgreSQL Server Administration or possession of comparable expertise
- Practical experience in executing SQL and managing PostgreSQL operations
Target Audience
Professionals such as Database Administrators, DevOps Engineers, and Developers who are tasked with optimizing and maintaining PostgreSQL instances in live production settings.
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