Fundamentals of Database Engineering

Fundamentals of Database Engineering

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 131 lectures (20h 24m) | 9.16 GB

Learn ACID, Indexing, Partitioning, Sharding, Concurrency control, Replication, DB Engines, Best Practices and More!

Database Engineering is a very interesting sector in software engineering. If you are interested in learning about database engineering you have come to the right place. I have curated this course carefully to discuss the Fundamental concepts of database engineering.

This course will not teach you SQL or programming languages, however, it will teach you skillsets and patterns that you can apply in database engineering. A few of the things that you will learn are Indexing, Partitioning, Sharding, Replication, b-trees in-depth indexing, Concurrency control, database engines and security, and much more.

I believe that learning the fundamentals of database engineering will equip you with the necessary means to tackle difficult and challenging problems yourself. I always compare engineering to math, you never memorize specific formulas and equations, you know the basic proves and derive and solve any equation one throws at you. Database engineering is similar, you can’t possibly say MongoDB is better than MySQL or Postgres is better than Oracle. Instead, you learn your use case and by understanding how each database platform does its own trade-offs you will be able to make optimal decisions.

Database engines or storage engines or sometimes even called embedded databases is a software library that a database management software uses to store data on disk and do CRUD (create update delete) Embedded means move everything in one software no network client-server. In this video course, I want to go through the few popular database engines, explain the differences between them, and finally, I want to spin up a database and change its engine and show the different features of each engine.

What you’ll learn

  • 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
Table of Contents

Course Updates
1 Welcome to the Course
2 Course Update Oct 2020
3 Course Update April 2021
4 Course Update December 2021
5 Note about Docker

6 Introduction to ACID
7 What is a Transaction
8 Atomicity
9 Isolation
10 Consistency
11 Durability
12 Eventual Consistency
13 ACID by Practical Examples
14 Phantom Reads
15 Serializable vs Repeatable Read

Understanding Database Internals
16 How tables and indexes are stored on disk (MUST WATCH before continue)
17 Row-Based vs Column-Based Databases
18 Primary Key vs Secondary Key – What you probably didn’t know

Database Indexing
19 Create Postgres Table with a million Rows (from scratch)
20 Getting Started with Indexing
21 Understanding The SQL Query Planner and Optimizer with Explain
22 Index Scan vs Index Only Scan
23 Key vs Non-Key Column Database Indexing
24 Combining Database Indexes for Better Performance
25 How Database Optimizers Decide to Use Indexes
26 Bitmap Index Scan vs Index Scan vs Table Scan
27 Create Index Concurrently – Avoid Blocking Production Database Writes
28 Bloom Filters
29 Working with Billion-Row Table
30 Article – The Cost of Long running Transactions
31 Article – Microsoft SQL Server Clustered Index Design

B-Tree vs B+Tree in Production Database Systems
32 B-Tree Section’s Introduction & Agenda
33 Full Table Scans
34 Original B-Tree
35 How the Original B-Tree Helps Performance
36 Original B-Tree Limitations
37 B+Tree
38 B+Tree DBMS Considerations
39 B+Tree Storage Cost in MySQL vs Postgres
40 B-Tree Section’s Summary

Database Partitioning
41 Introduction to Database Partitioning
42 What is Partitioning
43 Vertical vs Horizontal Partitioning
44 Partitioning Types
45 The Difference Between Partitioning and Sharding
46 Preparing Postgres, Database, Table, Indexes
47 Execute Multiple Queries on the Table
48 Create and Attach Partitioned Tables
49 Populate the Partitions and Create Indexes
50 Class Project – Querying and Checking the Size of Partitions
51 The Advantages of Partitioning
52 The Disadvantages of Partitioning
53 Section Summary – Partitioning
54 How to Automate Partitioning in Postgres

Database Sharding
55 Introduction to Database Sharding
56 What is Database Sharding
57 Consistent Hashing
58 Horizontal partitioning vs Sharding
59 Sharding with Postgres
60 Spin up Docker Postgres Shards
61 Writing to a Shard
62 Reading from a Shard
63 Advantages of Database Sharding
64 Disadvantages of Database Sharding
65 Database Sharding Section Summary
66 When Should you consider Sharding your Database

Concurrency Control
67 Shared vs Exclusive Locks
68 Dead Locks
69 Two-phase Locking
70 Solving the Double Booking Problem (Code Example)
71 Double Booking Problem Part 2 ( Alternative Solution and explination)
72 SQL Pagination With Offset is Very Slow
73 Database Connection Pooling

Database Replication
74 Introduction to Database Replication
75 MasterStandby Replication
76 Multi-master Replication
77 Synchronous vs Asynchronous Replication
78 Replication Demo with Postgres 13
79 Pros and Cons of Replication

Database System Design
80 Twitter System Design Database Design
81 Building a Short URL System Database Backend

Database Engines
82 Introduction
83 What is a Database Engine
85 InnoDB
86 XtraDB
87 SQLite
88 Aria
89 BerkeleyDB
90 LevelDB
91 RocksDB
92 Popular Database Engines
93 Switching Database Engines with mySQL

Database Cursors
94 What are Database Cursors
95 Server Side vs Client Side Database Cursors
96 Inserting Million Rows with Python in Postgres using Client Side Cursor
97 Querying with Client Side Cursor
98 Querying with Server Side Cursor
99 Pros and Cons of Server vs Client Side Cursors

Database Security
100 How to Secure Your Postgres Database by Enabling TLSSSL
101 Deep Look into Postgres Wire Protocol with Wireshark
102 Deep Look Into MongoDB Wire Protocol with Wireshark
103 What is the Largest SQL Statement that You can Send to Your Database
104 Best Practices Working with REST & Databases
105 Database Permissions and Best Practices for Building REST API

Homomorphic Encryption – Performing Database Queries on Encrypted Data
106 Introduction to Homomorphic Encryption
107 What is Encryption
108 Why Can’t we always Encrypt
109 What is Homomorphic Encryption
110 Homomorphic Encryption Demo
111 Clone and Build the Code
112 Going Through the Code and the Database
113 Searching The Encrypted Database
114 Is Homomorphic Encryption Ready

Q&A – Answering your Questions
115 Q&A – October 2021
116 Q&A – November 2021
117 Q&A – December 2021

Database Discussions
118 SELECT COUNT () can impact your Backend Application performance, here is why
119 How does the Database Store Data On Disk
120 Is QUIC a Good Protocol for Databases
121 What is a Distributed Transaction
122 Why Uber Moved from Postgres to MySQL (Discussion)
123 Can NULLs Improve your Database Queries Performance
124 Write Amplification Explained in Backend Apps, Database Systems and SSDs
125 Optimistic vs Pessmistic Concurrency Control

Archived Lectures
126 Introduction to ACID (Archived)
127 What is a Transaction (Archived)
128 Atomicity (Archived)
129 Isolation (Archived)
130 Consistency (Archived)
131 Durability (Archived)