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. Exploring the PostgreSQL Query Planner
- Understanding query execution plans and Planner algorithms (classic, genetic)
- Analyzing execution plans (data access methods, join methods)
- Influencing plan selection (configuration parameters, pg_hint_plan)
2. Query Planner Statistics
- Cost estimation for execution plans
- The default statistics model
- The ANALYZE operation 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
- Materialised views
5. Managing Cache Memory
- Buffer Cache
- Work Memory
- Maintenance Work Memory
6. Parallel Query Execution
- Underlying architecture
- Relevant configuration parameters
- Analysis of parallelised query execution plans
7. Workload and Performance Monitoring
- Logging slow queries
- Utilizing the auto_explain extension
- Leveraging the pg_stat_statements extension
- Cumulative Statistics
8. Benchmarking with PgBench
Requirements
- Completion of PostgreSQL Server Administration or equivalent foundational knowledge
- Hands-on experience with SQL and PostgreSQL operations
Target Audience
Database Administrators, DevOps Engineers, and Developers who are responsible for optimizing and maintaining PostgreSQL in 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