Hands-On High Performance SQL

Tuning Queries, Optimizing Indexes, Partitioning Tables, Enabling Text Search & Optimizing Joins, Subqueries, and CTEs

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

Requirements
Some experience with SQL SELECT statements

Description
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 master the art of query optimization, this hands-on course is designed for you. Building on your existing SQL knowledge, we'll dive deep into SQL's query planner, exploring how to write high-performance queries for modern data-intensive applications.This hands-on course goes beyond basic SQL performance principles to provide comprehensive coverage of advanced optimization techniques. Using real-world sales and Internet of Things (IoT) sensor datasets, you'll learn to tackle performance challenges that commonly arise in production environments.Query Analysis and Execution Deep DiveTaking your EXPLAIN skills to the next level, you'll master the intricacies of PostgreSQL's query planner. Learn to decode complex execution plans, understand cost calculations, and predict when the planner might choose suboptimal strategies. Through hands-on exercises, you'll analyze various query patterns and learn to rewrite them for optimal performance.Advanced Performance Optimization TechniquesThe course explores sophisticated optimization strategies:Advanced indexing techniques including partial, covering, and expression indexesDeep dive into join algorithms with real-world scenarios demonstrating when each type shinesTechniques for optimizing correlated subqueries and complex window functionsStrategic use of materialized views and CTEs for query performanceAdvanced pattern matching optimization including regular expressions and full-text search strategiesPerformance implications of different GiST, GIN, and SP-GiST index typesImplementing Production-Grade SQL SolutionsStudents tackle enterprise-level scenarios including:Implementing efficient table partitioning strategies for billion-row tablesAdvanced time-series optimization techniques for IoT dataHandling concurrent queries in high-throughput OLTP systemsConverting complex analytical queries into high-performance solutionsImplementing and optimizing full-text search in large-scale applicationsPerformance Monitoring and TuningMaster the tools and techniques for ongoing performance optimization:Advanced usage of pg_stat views for performance monitoringUnderstanding and tuning autovacuum for optimal performanceStrategies for maintaining statistics in large, frequently-updated tablesUsing extended statistics for complex multi-column correlationsThroughout the course, you'll work with realistic datasets that mirror common production scenarios, including a high-volume sales database and a time-series IoT vehicle sensor system generating millions of readings per day. The hands-on exercises challenge you to optimize ly complex queries, teaching you to balance theoretical knowledge with practical constraints.The course concludes with advanced troubleshooting techniques and a framework for systematic query optimization, ensuring you can tackle performance challenges in any database environment. By the end, you'll have the skills to optimize complex SQL queries and the knowledge to make informed decisions about database performance trade-offs in production systems.

Who this course is for
Data analysts
Report writers
Developers working with relational databases
Database administrators
Data Engineers
ETL developers
Data modelers
Data architects