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
- Examining query execution plans and Planner algorithms (classic, genetic)
- Interpreting execution plans (data access methods, join strategies)
- Influencing plan selection via configuration parameters and pg_hint_plan
2. Query Planner Statistics
- Assessing execution plan cost estimates
- Reviewing the default statistical models
- Running ANALYZE operations and managing 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 Processing
- Understanding the architecture
- Configuring relevant parameters
- Analysing parallelised query execution plans
7. Monitoring Workload and Performance
- Capturing slow queries through logging
- Implementing the auto_explain extension
- Utilising the pg_stat_statements extension
- Tracking Cumulative Statistics
8. Benchmarking with PgBench
Requirements
- Completion of PostgreSQL Server Administration or equivalent foundational knowledge
- Practical experience with SQL and day-to-day PostgreSQL operations
Who Should Attend
This course is designed for Database Administrators, DevOps Engineers, and Developers who are responsible for optimising and maintaining PostgreSQL in 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