Fundamentals of Database Engineering

Description

In this course, you will :

• Learn and understand ACID Properties
• Database Indexing
• Database Partitioning
• Database Replication
• Database Sharding
• Database Cursors
• Concurrency Control (Optimistic, Pessimistic)
• B-Trees in Production Database Systems
• Database System Designs
• Difference between Database Management System, Database Engine and Embedded database
• Database Engines such as MyISAM, InnoDB, RocksDB, LevelDB and More
• Benefits of Using one database engine over the other
• Switching Database Engines with MySQL
• Database Security
• Homomorphic Encryption

Syllabus:

1. ACID

• Introduction to ACID
• What is a Transaction?
• Atomicity
• Isolation
• Consistency
• Durability
• ACID by Practical Examples
• Phantom Reads
• Serializable vs Repeatable Read
• Eventual Consistency

2. 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)

3. 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

4. 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

5. 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

6. 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?

7. 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

8. 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

9. Database System Design

• Twitter System Design Database Design
• Building a Short URL System Database Backend

10. Database Engines

• What is a Database Engine?
• MyISAM
• InnoDB
• XtraDB
• SQLite
• Aria
• BerkeleyDB
• LevelDB
• RocksDB
• Popular Database Engines
• Switching Database Engines with mySQL

11. Database Cursors

• What are Database Cursors?
• Server Side vs Client Side Database Cursors
• Inserting Million Rows with Python in Postgres using Client Side Cursor
• Querying with Client Side Cursor
• Querying with Server Side Cursor
• Pros and Cons of Server vs Client Side Cursors
• Article - Server Side Cursor Types in SQL Server

12. SQL vs NoSQL Architecture

• MongoDB Architecture
• MongoDB Internals
• MongoDB Clustered Collections
• MemCached In-Memory database Architecture

13. Database Security

• How to Secure Your Postgres Database by Enabling TLS/SSL
• Deep Look into Postgres Wire Protocol with Wireshark
• Deep Look Into MongoDB Wire Protocol with Wireshark
• What is the Largest SQL Statement that You can Send to Your Database
• Best Practices Working with REST & Databases
• Database Permissions and Best Practices for Building REST API

14. Homomorphic Encryption - Performing Database Queries on Encrypted Data

• Introduction to Homomorphic Encryption
• What is Encryption?
• Why Can't we always Encrypt?
• What is Homomorphic Encryption
• Homomorphic Encryption Demo
• Clone and Build the Code
• Going Through the Code and the Database
• Searching The Encrypted Database
• Is Homomorphic Encryption Ready?

15. Answering your Questions

• Heap Index scan instead of Index only scan why?
• What is the unit of the Cost in Postgres Planner?
• All Isolation Levels - Explained Details
• Snapshot and Repeatable Read Isolation difference?
• I have an Index why is the database doing a full table scan?
• Why Databases Read Pages instead of Rows?
• How does Indexing a column with duplicate values work?
• Should I drop unused indexes?
• Why use serializable Isolation Level when we have SELECT FOR UPDATE?
• Can I use the same database connection for multiple clients?
• Do I need a transaction if I'm only reading?
• Why does an update in Postgres touches all indexes?
• What is the value of bitmap index scan?
• What does Explain Analyze actually do?
• Postgres vs MySQL (The fundamental differences)
• Does Create Index block writes and Why?

16. Database Discussions

• WAL, Redo and Undo logs
• SELECT COUNT (*) can impact your Backend Application performance, here is why
• How Shopify Switched from UUID as Primary Key
• How does the Database Store Data On Disk?
• Postgres Architecture
• Is QUIC a Good Protocol for Databases?
• What is a Distributed Transaction?
• Hash Tables and Consistent Hashing
• Indexing in PostgreSQL vs MySQL
• Why Uber Moved from Postgres to MySQL (Discussion)
• Can NULLs Improve your Database Queries Performance?
• Write Amplification Explained in Backend Apps, Database Systems and SSDs
• Optimistic vs Pessmistic Concurrency Control
• Avoid SELECT *, even on a single-column tables
• Postgres Locks - A Deep Dive
• How Slow is select * in row store
• MySQL InnoDB Advanced Locking Techniques