PostgreSQL Programming (EN)

Description

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. 

Goals

• 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 

Target

Contents

• Database performance measurement - Importance of benchmarking, key query performance measurement: Profiler, recommendations for identifying locking and blocking
• Optimization of physical database design - performance optimization model, schema optimization strategy: keys, schema optimization strategy: responsible denormalization, schema optimization strategy: generalization
• 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. 

Study Materials