Build scalable and resilient databases to support any application. Learn how to write optimized SQL, use indexes, partition data, and use established design patterns.
Overview
Syllabus
Introduction
- Getting started with SQL for application developers
- Designing an ecommerce application database
- Creating tables and loading data
- Using environment variables for connection parameters
- Connecting to a database
- Parameterizing SELECT statements
- Avoiding N+1 queries
- Challenge: Write a parameterized query in Python
- Solution: How to write a parameterized query in Python
- Overview of index types
- Using B-tree indexes and range scans
- Using hash indexes and equality operations
- Covering indexes
- Indexes and bulk data loading
- Avoiding index locks
- Challenge: Define a B-tree and a hash index
- Solution: Defining B-tree and hash indexes
- Overview of object-relational mapping
- Introduction to SQLAlchemy
- Installing SQLAlchemy
- Query with SQLAlchemy
- Limitations of ORM and cautionary advice
- Challenge: Create an ORM model
- Solution: Create an ORM model
- Overview of partitioning
- Range partitioning
- List partitioning
- Hash partitioning
- Querying partitioned tables
- Using read-only replicas
- Challenge: Define a partitioned table
- Solution: Defining a partitioned table
- Database errors
- Error trapping
- Database not responding, timeouts, and exponential backoff
- Connection pooling and bulk heads
- Monitoring and logging best practices
- Challenge: When your Python application fails
- Solution: When your Python application fails
- Understanding schema changes
- Ad hoc scripts for schema changes
- Automated schema migration with Flyway
- Automated schema migration with Liquibase
- Challenge
- Solution
- Key considerations
Taught by
Dan Sullivan
