Fundamentals of Database Engineering

Fundamentals of Database Engineering

English | MP4 | AVC 1920×1080 | AAC 44KHz 2ch | 129 lectures (19h 34m) | 12.9 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 Note about Docker

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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