Fundamentals of Database Engineering

Fundamentals of Database Engineering

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 157 lectures (26h 52m) | 16.29 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.

One other thing you will learn in this course is the lowest database interface that talks to the OS which is the database engine. 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 Note 1
3 Course Note 2
4 Course Note 3
5 Note about Docker
6 Slides

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

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

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

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

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

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

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

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

Database System Design
82 Twitter System Design Database Design
83 Building a Short URL System Database Backend

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

Database Cursors
96 What are Database Cursors
97 Server Side vs Client Side Database Cursors
98 Inserting Million Rows with Python in Postgres using Client Side Cursor
99 Querying with Client Side Cursor
100 Querying with Server Side Cursor
101 Pros and Cons of Server vs Client Side Cursors
102 Article – Server Side Cursor Types in SQL Server

SQL vs NoSQL Architecture
103 MongoDB Architecture
104 MongoDB Internals
105 MemCached In-Memory database Architecture

Database Security
106 How to Secure Your Postgres Database by Enabling TLSSSL
107 Deep Look into Postgres Wire Protocol with Wireshark
108 Deep Look Into MongoDB Wire Protocol with Wireshark
109 What is the Largest SQL Statement that You can Send to Your Database
110 Best Practices Working with REST & Databases
111 Database Permissions and Best Practices for Building REST API

Homomorphic Encryption – Performing Database Queries on Encrypted Data
112 Introduction to Homomorphic Encryption
113 What is Encryption
114 Why Can’t we always Encrypt
115 What is Homomorphic Encryption
116 Homomorphic Encryption Demo
117 Clone and Build the Code
118 Going Through the Code and the Database
119 Searching The Encrypted Database
120 Is Homomorphic Encryption Ready

Answering your Questions
121 Heap Index scan instead of Index only scan why
122 What is the unit of the Cost in Postgres Planner
123 All Isolation Levels – Explained Details
124 Snapshot and Repeatable Read Isolation difference
125 I have an Index why is the database doing a full table scan
126 Why Databases Read Pages instead of Rows
127 How does Indexing a column with duplicate values work
128 Should I drop unused indexes
129 Why use serializable Isolation Level when we have SELECT FOR UPDATE
130 Can I use the same database connection for multiple clients
131 Do I need a transaction if I’m only reading
132 Why does an update in Postgres touches all indexes
133 What is the value of bitmap index scan
134 What does Explain Analyze actually do
135 Postgres vs MySQL (The fundamental differences)
136 Does Create Index block writes and Why

Database Discussions
137 WAL, Redo and Undo logs
138 SELECT COUNT () can impact your Backend Application performance, here is why
139 How Shopify Switched from UUID as Primary Key
140 How does the Database Store Data On Disk
141 Postgres Architecture
142 Is QUIC a Good Protocol for Databases
143 What is a Distributed Transaction
144 Hash Tables and Consistent Hashing
145 Indexing in PostgreSQL vs MySQL
146 Why Uber Moved from Postgres to MySQL (Discussion)
147 Can NULLs Improve your Database Queries Performance
148 Write Amplification Explained in Backend Apps, Database Systems and SSDs
149 Optimistic vs Pessmistic Concurrency Control
150 Postgres Locks – A Deep Dive
151 How Slow is select in row store

Archived Lectures
152 Introduction to ACID (Archived)
153 What is a Transaction (Archived)
154 Atomicity (Archived)
155 Isolation (Archived)
156 Consistency (Archived)
157 Durability (Archived)