Designing Highly Scalable and Highly Available SQL Databases
3h 22mIntermediate2025-01-14
Authors
Dan Sullivan
Enterprise Architect, Big Data Expert
Course details
Relational databases are often used to manage large scale volumes of data so developers and data modelers need to understand scalable architecture and data modeling patterns.
In this course, Dan Sullivan—a data architect, data modeler and data scientist with decades of experience —begins with scalability requirements, particularly business requirements, data volumes and data growth. Next, the course moves on to examine database architecture and data modeling patterns followed by an in depth look at supporting scalable data ingestion. Querying, of course, is a crucial component of any database architecture. Learn about the differences between transactional and analytical queries and how we can use database resources like indexes and materialized views for improving performance. The course concludes with a discussion of DevOps for scalable relational databases so we can keep our databases performant and operating as expected.
In this course, Dan Sullivan—a data architect, data modeler and data scientist with decades of experience —begins with scalability requirements, particularly business requirements, data volumes and data growth. Next, the course moves on to examine database architecture and data modeling patterns followed by an in depth look at supporting scalable data ingestion. Querying, of course, is a crucial component of any database architecture. Learn about the differences between transactional and analytical queries and how we can use database resources like indexes and materialized views for improving performance. The course concludes with a discussion of DevOps for scalable relational databases so we can keep our databases performant and operating as expected.
Skills covered
SQLDatabase AdministrationDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware DevelopmentOne-Off
Concepts
0. Introduction
- 01 - Challenges to scaling relational databases
- 02 - What you should know
1. Understanding Scalability Requirements
- 03 - Learning about business requirements for database scalability
- 04 - Identifying use cases for data
- 05 - Identifying security and compliance requirements
- 06 - Estimating data growth
- 07 - Challenge - Identify business requirements in a scenario
- 08 - Solution - Identified business requirements
2. Database Architecture and Relational Databases
- 09 - Choosing a data store - SQL, NoSQL, or analytical
- 10 - Identifying schemas and domains
- 11 - Identifying key entities
- 12 - High-level physical design
- 13 - Challenge - What is missing in a database architecture example
- 14 - Solution - Revised database architecture
3. Data Ingestion
- 15 - Human and machine scale data
- 16 - Different data ingestion strategies
- 17 - Ingesting at human and machine scale
- 18 - Message queues to buffer ingested data
- 19 - Data modeling for scale - Event sourcing
- 20 - Command Query Responsibility Segregation (CQRS)
- 21 - Challenge - Design a scalable data acquisition service to support streaming time series data
- 22 - Solution - Services and APIs for a scalable user interface
4. Designing for Scalable Querying
- 23 - Transactional vs. analytical queries
- 24 - Indexing for query performance
- 25 - Materialized views for transactional queries
- 26 - Using read replicas to improve query performance
- 27 - Understanding write ahead logging
- 28 - Denormalizing for analytical queries
- 29 - Aggregation and sampling for analytical queries
- 30 - Challenge - Optimize a data model for analytical queries
- 31 - Solution - Denormalized star schema for analytical queries
5. DevOps for Scalable Relational Databases
- 32 - Monitoring relational databases
- 33 - Reducing latency with caching
- 34 - Partitioning for scalability
- 35 - High-availability architectures
- 36 - Data lifecycle management
- 37 - Challenge - Understanding database DevOps
- 38 - Solution - Understanding database DevOps
Conclusion
- 39 - Next steps