What you'll learn:
- Generate and understand query execution plans
- Define indexes and partitioning strategies to improve query performance
- Choose optimal ways to implement business logic using joins, subqueries, and common table expressions (CTEs)
- Monitor database statistics to improve query performance
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 Dive
To 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 Techniques
The course explores several optimization strategies:
Indexing techniques including covering, full-text, and expression indexes
Reviewing join algorithms with real-world scenarios demonstrating when each type is optimal
Seeing options for optimizing correlated subqueries and complex window functions
Weighing when to use materialized views and common table expressions (CTEs) for query performance
Learning about different pattern matching techniques including regular expressions and full-text search strategies
Becoming familiar with the performance implications of different GiST, GIN, and SP-GiST index types
Implementing Production-Grade SQL Solutions
Tackle enterprise-level scenarios including:
Implementing efficient table partitioning strategies for large tables
Understanding time-series optimization techniques for IoT data
Implementing full-text search in large-scale applications
Performance Monitoring and Tuning
Learn how to use tools and techniques for ongoing performance optimization:
Advanced usage of pg_stat views for performance monitoring
Understanding and tuning autovacuum for optimal performance
Maintaining statistics in tables
Throughout 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 SQLfor Performance Tuning
