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