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
- Examine execution plans and the algorithms driving the Query Planner (including classic and genetic approaches)
- Analyze data access and join methods within execution plans
- Manage plan selection through configuration parameters and the pg_hint_plan extension
2. Query Planner Statistics
- Estimate costs associated with execution plans
- Understand the default statistical models
- Apply the ANALYZE operation and leverage extended statistics
3. Leveraging Indexes
- Utilize B-tree indexes, including single-column, composite, function-based, and partial variants
- Implement Hash indexes
- Apply BRIN indexes
- Use GiST and GIN indexes
4. Advanced Table Structures
- Implement Partitioned tables
- Use Unlogged tables
- Create Temporary tables
- Manage Materialised views
5. Optimizing Cache Memory
- Configure the Buffer Cache
- Adjust Work Memory
- Tune Maintenance Work Memory
6. Parallel Query Execution
- Understand the architecture of parallel processing
- Configure relevant parameters
- Analyze execution plans for parallelized queries
7. Monitoring Workload and Performance
- Log slow-running queries
- Leverage the auto_explain extension
- Utilize the pg_stat_statements extension
- Review Cumulative Statistics
8. Benchmarking with PgBench
Requirements
- Completion of PostgreSQL Server Administration or an equivalent level of understanding
- Practical experience working with SQL and PostgreSQL operations
Target Audience
Database Administrators, DevOps Engineers, and Developers who are responsible for tuning and maintaining PostgreSQL in production environments.
Testimonials (2)
Tuning strategies.
Jeffrey Zieg - Matrix Consulting
Course - PostgreSQL Performance Tuning
Logging behaviour when the instance is under stress, and the hierarchy/nomenclature of instances, databases, files, etc.
