Designing Highly Scalable and Highly Available SQL Databases

Introduction

• Challenges to scaling relational databases
• What you should know

1. Understanding Scalability Requirements

• Business requirements for database scalability
• Identify use cases for data
• Identify security and compliance requirements
• Estimate data growth
• Challenge: Identify business requirements in a scenario
• Solution: Identify business requirements in a scenario

2. Database Architecture and Relational Databases

• Choose a datastore: SQL, NoSQL, or analytical?
• Identify schemas
• Identify key entities
• High-level physical design
• Challenge: Revised database architecture
• Solution: Revised database architecture

3. Data Ingestion

• Human-scale and machine-scale data
• Different data ingestion strategies
• Designing scalable user interfaces
• Message queues to buffer ingested data
• Data modeling for scale: Event sourcing
• Distributing workload: Command Query Response Separation (CQSR)
• Challenge: Services and APIs for a scable user interface
• Solution: Services and APIs for a scable user interface

4. Designing for Scalable Querying

• Transactional vs. analytical queries
• Indexing for transactional queries
• Materialized views for transactional queries
• Using read replicas to improve query performance
• Understanding write-ahead logging
• Denormalizing for analytical queries
• Aggregation and sampling for analytical queries
• Challenge: Optimize a data model for an analytical queries
• Solution: Optimize a data model for an analytical queries

5. DevOps for Scalable Relational Databases

• Monitoring relational databases
• Reducing latency with caching
• Partitioning for scalability
• High-availability architectures
• Data lifecycle management
• Challenge: Understanding database DevOps
• Solution: Understanding database DevOps