Designing Highly Scalable and Highly Available SQL Databases (2020)

2h 39mAdvanced2020-11-11

Authors

Dan Sullivan

Enterprise Architect, Big Data Expert

Course details

Online activities, mobile devices, and Internet of Things (IoT) sensors are generating immense volumes of data. Much of that data requires relational database capabilities, such as consistent reads/writes and complex transaction processing. In this course, get a holistic overview of the essential elements of designing and implementing highly scalable and available relational databases. Instructor Dan Sullivan helps developers and data modelers grasp essential architecture concepts and design patterns to ensure their databases can scale to the needs of their business. Dan goes over key requirements related to both specific functions and nonfunctional requirements, such as availability. He shows how to use your requirements to create data architectures and data models. Plus, he examines the problems of data ingestion at scale, describes design patterns to support a variety of ingestion patterns, discusses how to design for scalable querying, and more.

Skills covered

PostgreSQLSQLDatabase AdministrationAdvancedDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware Development

Concepts

0. Introduction

01 - Challenges to scaling relational databases
02 - What you should know

1. Understanding Scalability Requirements

03 - Business requirements for database scalability
04 - Identify use cases for data
05 - Identify security and compliance requirements
06 - Estimate data growth
07 - Challenge - Identify business requirements in a scenario
08 - Solution - Identify business requirements in a scenario

2. Database Architecture and Relational Databases

09 - Choose a datastore - SQL, NoSQL, or analytical
10 - Identify schemas
11 - Identify key entities
12 - High-level physical design
13 - Challenge - Revised database architecture
14 - Solution - Revised database architecture

3. Data Ingestion

15 - Human-scale and machine-scale data
16 - Different data ingestion strategies
17 - Designing scalable user interfaces
18 - Message queues to buffer ingested data
19 - Data modeling for scale - Event sourcing
20 - Distributing workload - Command Query Response Separation (CQSR)
21 - Challenge - Services and APIs for a scable user interface
22 - Solution - Services and APIs for a scable user interface

4. Designing for Scalable Querying

23 - Transactional vs. analytical queries
24 - Indexing for transactional queries
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 an analytical queries
31 - Solution - Optimize a data model for an 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