Course Outline
1. Understanding the PostgreSQL Query Planner
- Query execution plans and Query Planner algorithms (classic, genetic)
- Analysing query execution plans (data access methods, join strategies)
- Influencing plan selection (configuration parameters, pg_hint_plan)
2. Query Planner Statistics
- Cost estimation for execution plans
- Default statistics model
- The ANALYZE command and extended statistics
3. Effective Use of Indexes
- B-tree indexes (single column, composite, function-based, partial)
- Hash indexes
- BRIN indexes
- GiST and GIN indexes
4. Utilizing Advanced Table Structures
- Partitioned tables
- Unlogged tables
- Temporary tables
- Materialised views
5. Managing Cache Memory
- Buffer Cache
- Work Memory
- Maintenance Work Memory
6. Parallel Query Execution
- Architecture overview
- Configuration parameters
- Analysing parallelized query execution plans
7. Workload and Performance Monitoring
- Logging slow queries
- Utilizing the auto_explain extension
- Utilizing the pg_stat_statements extension
- Understanding Cumulative Statistics
8. Benchmarking with PgBench
Requirements
- Completion of PostgreSQL Server Administration or equivalent expertise
- Practical experience with SQL and PostgreSQL operations
Audience
Database Administrators, DevOps Engineers, and Developers tasked with tuning and maintaining PostgreSQL in production environments.
Testimonials (2)
The provided examples and labs
Christophe OSTER - EU Lisa
Course - PostgreSQL Advanced DBA
1. A very well-structured training program 2. The warm atmosphere the trainer created, along with his outstanding personal professionalism 3. That the trainer explained everything as if he were talking to a complete beginner, without slipping into any technical jargon.
