The course provides the knowledge and experience needed to evaluate and optimize queries and response times to queries. It focuses on the systematic identification and optimization of database factors affecting query performance.
Level
Designed for participants with advanced knowledge and experience
PostgreSQL database system administrators who want to deepen their knowledge of advanced topics, especially in the field of large, high-availability solutions.
Course content
Database performance measurement - Importance of benchmarking, key query performance measurement: Profiler, recommendations for identifying locking and blocking
Query optimization for performance - performance optimization model: queries, what is the logical flow of the query, decisions about the use of subqueries, best practices for building effective queries
Query Optimization - Recommendations for correct query writing. Acceleration of existing indices. JOIN optimization. Fast data synchronization using the MERGE operator.
Index strategy optimization - performance optimization model: indexes, index usage decisions
Management of competing operations - performance optimization model: locking and blocking problems, presentations on the use of effective queries to reduce locking and blocking, locking and blocking reduction strategies, DEADLOCKS
Application processing optimization - Minimize blocking and deadlocks. Transaction optimization. Recommendations from practice.
Materials
Materials are in electronic form.
Objectives
Understand the architecture of the database system
Perform installation and implementation of the database system
Tune performance
Break up tables into sections for optimization
Understand the possibilities of high availability
Understand the replication process
Monitor the database system
Use procedural language
Use extension tools
Use and manage conection pooling
Lecturers
David Solnař
Freelance Consultant and Trainer Oracle, SQL Server, MySQL, .NET, WPF, ASP.NET
