Course Updates
  • Welcome to the Course
  • Course Note 1
  • Course Note 2
  • Course Note 3
  • Note about Docker
  • Slides
ACID
  • Introduction to ACID
  • What is a Transaction?
  • Atomicity
  • Isolation
  • Consistency
  • Durability
  • ACID by Practical Examples
  • Phantom Reads
  • Serializable vs Repeatable Read
  • Eventual Consistency
  • ACID Quiz
Understanding Database Internals
  • How tables and indexes are stored on disk (MUST WATCH before continue)
  • Row-Based vs Column-Based Databases
  • Primary Key vs Secondary Key - What you probably didn't know
  • Databases Pages (Article)
Database Indexing
  • Create Postgres Table with a million Rows (from scratch)
  • Getting Started with Indexing
  • Understanding The SQL Query Planner and Optimizer with Explain
  • Bitmap Index Scan vs Index Scan vs Table Scan
  • Key vs Non-Key Column Database Indexing
  • Index Scan vs Index Only Scan
  • Combining Database Indexes for Better Performance
  • How Database Optimizers Decide to Use Indexes
  • Create Index Concurrently - Avoid Blocking Production Database Writes
  • Bloom Filters
  • Working with Billion-Row Table
  • Article - The Cost of Long running Transactions
  • Article - Microsoft SQL Server Clustered Index Design
  • Indexing Quiz
B-Tree vs B+Tree in Production Database Systems
  • B-Tree Section's Introduction & Agenda
  • Full Table Scans
  • Original B-Tree
  • How the Original B-Tree Helps Performance
  • Original B-Tree Limitations
  • B+Tree
  • B+Tree DBMS Considerations
  • B+Tree Storage Cost in MySQL vs Postgres
  • B-Tree Section's Summary
Database Partitioning
  • Introduction to Database Partitioning
  • What is Partitioning?
  • Vertical vs Horizontal Partitioning
  • Partitioning Types
  • The Difference Between Partitioning and Sharding
  • Preparing: Postgres, Database, Table, Indexes
  • Execute Multiple Queries on the Table
  • Create and Attach Partitioned Tables
  • Populate the Partitions and Create Indexes
  • Class Project - Querying and Checking the Size of Partitions
  • The Advantages of Partitioning
  • The Disadvantages of Partitioning
  • Section Summary - Partitioning
  • How to Automate Partitioning in Postgres
Database Sharding
  • Introduction to Database Sharding
  • What is Database Sharding?
  • Consistent Hashing
  • Horizontal partitioning vs Sharding
  • Sharding with Postgres
  • Spin up Docker Postgres Shards
  • Writing to a Shard
  • Reading from a Shard
  • Advantages of Database Sharding
  • Disadvantages of Database Sharding
  • Database Sharding Section Summary
  • When Should you consider Sharding your Database?
Concurrency Control
  • Shared vs Exclusive Locks
  • Dead Locks
  • Two-phase Locking
  • Solving the Double Booking Problem (Code Example)
  • Double Booking Problem Part 2 ( Alternative Solution and explination)
  • SQL Pagination With Offset is Very Slow
  • Database Connection Pooling
Database Replication
  • Introduction to Database Replication
  • Master/Standby Replication
  • Multi-master Replication
  • Synchronous vs Asynchronous Replication
  • Replication Demo with Postgres 13
  • Pros and Cons of Replication
Database System Design
  • Twitter System Design Database Design
  • Building a Short URL System Database Backend
Database Engines
  • Introduction
  • What is a Database Engine?
  • MyISAM
  • InnoDB