Course Outline
1. Understanding PostgreSQL Query Planner for Government
- Overview of query execution plans and Query Planner algorithms, including classic and genetic methods.
- Techniques for analyzing query execution plans, focusing on data access and join methods.
- Methods for controlling plan selection through configuration parameters and the use of pg_hint_plan.
2. Query Planner Statistics for Government
- Techniques for estimating execution plan costs.
- Explanation of the default statistics model.
- Importance of the ANALYZE operation and extended statistics in query optimization.
3. Using Indexes for Government
- Detailed discussion of B-tree indexes, including single column, composite, function-based, and partial indexes.
- Overview of hash indexes and their appropriate use cases.
- Introduction to BRIN indexes for large datasets.
- Exploration of GiST and GIN indexes for specialized data types.
4. Using Advanced Table Structures for Government
- Implementation and management of partitioned tables.
- Use cases and benefits of unlogged tables.
- Strategies for utilizing temporary tables effectively.
- Creation and maintenance of materialized views for performance optimization.
5. Using Cache Memory for Government
- Overview of the Buffer Cache and its role in query performance.
- Explanation of Work Memory and its configuration.
- Discussion on Maintenance Work Memory and its impact on database operations.
6. Parallel Query for Government
- Architecture of parallel query execution in PostgreSQL.
- Configuration parameters for optimizing parallel queries.
- Techniques for analyzing parallelized query execution plans.
7. Workload and Performance Monitoring for Government
- Methods for logging slow queries to identify performance bottlenecks.
- Utilization of the auto_explain extension for detailed query analysis.
- Use of the pg_stat_statements extension for monitoring and optimizing query performance.
- Importance of cumulative statistics in long-term performance assessment.
8. Benchmarking with PgBench for Government
Requirements
- Completion of PostgreSQL Server Administration or equivalent expertise
- Practical experience with SQL and PostgreSQL operations
Audience
This course is designed for Database Administrators, DevOps Engineers, and Developers who are responsible for optimizing and maintaining PostgreSQL in production environments for government and other public sector organizations.
Testimonials (5)
A factual and practical approach to the subject. A nice atmosphere, conducive to asking questions.
Marcin - Santander Bank Polska S.A.
Course - Implementing High Availability in PostgreSQL
It was docker and container plus postgres DB inheritance feature which is not available in Oracle Database.
Manish Kumar
Course - PostgreSQL Essentials
It was clearly tailored to be very appropriate to our purpose which made it extra helpful. I really liked the zoom format as it helped me focus with the material right in front of you all the time and the trainer able to add illustrations and markings on to it as he explained. The trainer answered all our questions in a very satisfying way and I felt surprised at how much I was able to learn in one day. Usually i get tired of training by the afternoon but this was very engaging right through to the end. Pace was perfect and detail went into the right areas.
Davina Sanders - Scottish Government
Course - PostgreSQL Performance Tuning
Michal was very knowledgable and engaging as a teacher.
Nate Clifton - NIIT (USA) Inc.
Course - PostgreSQL Advanced DBA
new knowledges that can be apply in our system
