Overview

Are your SQL queries taking too long to execute? Do you find yourself wondering how databases decide how to retrieve and filter data? If you're comfortable writing SQL but want to learn more about how to make your queries more efficient, then this course is for you. This course explains the basics of SQL performance principles and optimization techniques. Building on your existing SQL knowledge, we'll look into SQL's query planner and exploring how to evaluate query performance and choose among different approaches to writing queries.Query Analysis and Execution Deep DiveTo tune queries, it helps to understand a databases query planner. In this course you will learn how query plan builders work, including how different types of query plan nodes implement core operations, such as retrieving data, joining tables, and filtering results. Learn to read execution plans, compare cost calculations, and evaluate alternative implementations of your queries. Through hands-on exercises, you'll analyze various query patterns and see how rewriting queries can impact their performance.Advanced Performance Optimization TechniquesThe course explores several optimization strategies:Indexing techniques including covering, full-text, and expression indexesReviewing join algorithms with real-world scenarios demonstrating when each type is optimalSeeing options for optimizing correlated subqueries and complex window functionsWeighing when to use materialized views and common table expressions (CTEs) for query performanceLearning about different pattern matching techniques including regular expressions and full-text search strategiesBecoming familiar with the performance implications of different GiST, GIN, and SP-GiST index typesImplementing Production-Grade SQL SolutionsTackle enterprise-level scenarios including:Implementing efficient table partitioning strategies for large tablesUnderstanding time-series optimization techniques for IoT dataImplementing full-text search in large-scale applicationsPerformance Monitoring and TuningLearn how to use tools and techniques for ongoing performance optimization:Advanced usage of pg_stat views for performance monitoringUnderstanding and tuning autovacuum for optimal performanceMaintaining statistics in tablesThroughout the course, you'll work with datasets that include a basic a sales database as well as a time-series IoT vehicle sensor system generating millions of readings per day. The hands-on exercises give you an opportunity to apply theoretical knowledge to practical application scenarios.By the end of this course, you'll have practiced the skills you need to analyze complex SQL queries and the knowledge to make informed decisions about database performance trade-offs in production systems.Note: This course was formally listed as Hands-On SQL for Performance Tuning

Skills

Reviews