Fundamentals of Database Engineering

Fundamentals of Database Engineering

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 146 lectures (24h 52m) | 16.47 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
Welcome to the Course
Course Note 1
Course Note 2
Course Note 3
Note about Docker

Introduction to ACID
What is a Transaction
ACID by Practical Examples
Phantom Reads
Serializable vs Repeatable Read
Eventual Consistency

Understanding Database Internals
How tables and indexes are stored on disk MUST WATCH before continue
RowBased vs ColumnBased Databases
Primary Key vs Secondary Key What you probably didnt know

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 NonKey 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 BillionRow Table
Article The Cost of Long running Transactions
Article Microsoft SQL Server Clustered Index Design

BTree vs BTree in Production Database Systems
BTree Sections Introduction Agenda
Full Table Scans
Original BTree
How the Original BTree Helps Performance
Original BTree Limitations
BTree DBMS Considerations
BTree Storage Cost in MySQL vs Postgres
BTree Sections Summary

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

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

Concurrency Control
Shared vs Exclusive Locks
Dead Locks
Twophase 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

Database Replication
Introduction to Database Replication
MasterStandby Replication
Multimaster Replication
Synchronous vs Asynchronous Replication
Replication Demo with Postgres 13
Pros and Cons of Replication

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

Database Engines
What is a Database Engine
Popular Database Engines
Switching Database Engines with mySQL

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

Database Security
How to Secure Your Postgres Database by Enabling TLSSSL
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

Homomorphic Encryption Performing Database Queries on Encrypted Data
Introduction to Homomorphic Encryption
What is Encryption
Why Cant 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

Answering your Questions
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 Im 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

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

Archived Lectures
Introduction to ACID Archived
What is a Transaction Archived
Atomicity Archived
Isolation Archived
Consistency Archived
Durability Archived