Fundamentals of Database Engineering

Examine transaction isolation and read phenomena, including dirty reads, non-repeatable and phantom reads, and lost updates, then compare isolation levels from read uncommitted to serializable.

Explore how data consistency and read consistency are maintained across databases, comparing relational, NoSQL, and graph models, and discuss referential integrity, atomicity, and replication trade-offs.

Explore durability in database engineering by understanding how committed transactions persist to non-volatile storage, using write-ahead logs, fsync, and techniques that balance speed and reliability.

Explore phantom reads in concurrent transactions, distinguish them from dirty and non-repeatable reads, and apply serializable isolation and mvcc concepts to prevent inconsistent results in Postgres.

Compare serializable vs non-repeatable reads and repeatable read in Postgres, showing how serializable can produce a fully serialized state but may fail with a retry due to read-write dependencies.

Examine the two types of consistency: data consistency and read consistency, and how relational and NoSQL databases experience eventual consistency during horizontal scaling and replication.

Compare row-based and column-based databases, explain how they store data on disk, how queries access data, and why row stores suit transactions while column stores excel at analytics.

Explore how Postgres explain reveals query plans, including sequential scans, index scans, sorting costs, and row estimates, and learn how to optimize queries using explain insights.

Explore how single and composite indexes on A and B affect query performance, including when bitmap vs direct index scans occur and how or versus and changes behavior.

Bloom filters use an in-memory bitset to quickly flag nonexistence via hashing and mod operations, reducing database queries. Unset bits guarantee nonexistence; set bits may indicate existence (false positives possible).

Explore how random uuid version four values disrupt b+ tree leaf pages, triggering page splits and io thrashing in inserts, and how ordered identifiers improve reads and writes.

Examine full table scans, reading disk pages of fixed size, and how DBMS threading and multi‑worker tricks reduce the search space when locating a row by id.

Beatrice explains the balanced b-tree, detailing nodes, the degree m, and elements with keys and data pointers to rows, plus root, internal, and leaf distinctions.

Understand how a b-tree index on the id field uses keys and data pointers in internal and leaf nodes to enable efficient search, while highlighting page splits and memory implications.

Explain how b+ trees store keys in internal nodes and values in leaves, with linked leaves enabling efficient range queries and fast sequential access.

Analyze B+ trees in DBMS considerations, including leaf pointers, internal nodes, and the choice to adopt a log-structure merge tree variant in MongoDB, for efficient traversal.

Compare the storage costs of B+ trees in MySQL and Postgres by examining secondary versus primary indexes, pointers to tuples, and the impact of UUID primary keys on index size.

Explore horizontal partitioning by rows using range or list partitions and vertical partitioning by columns, including separating a blob column into its own table space to speed access.

Compare horizontal partitioning and sharding: partitioning hides partition selection from clients in one database, while sharding distributes data across separate servers, with clients remaining agnostic to shard location.

Spin up a Postgres instance with Docker, insert 10 million rows into a grades table, enforce not null, and index G while preparing partitions and joining them to master table.

Analyze executing multiple queries on grades table with explain analyze, showing bitmap index scans and parallel index scans for where G equals 30 and range queries between 30 and 35.

Create the main partitioned table grades parts with range partitioning, then attach partitions for 0–35, 35–60, 60–80, and 80–100.

Use explain analyze and pg_relation_size to show that an index scan on a single partition speeds queries, and enable partition pruning to avoid scanning multiple partitions.

Evaluate the pros and cons of partitioning, including improved query performance and memory/io considerations. Let partitions simplify bulk loading and decide between sequential scans and scattered index scans.

Introduces database sharding, explains consistent hashing and horizontal partitioning, demonstrates a NodeJS and Postgres shard example building a URL shortener, and weighs pros and cons.

Learn how consistent hashing maps an input to a database shard with a hash ring, ensuring same input hits the same server; includes a NodeJS modulo example and Cassandra shards.

Differentiate horizontal partitioning and sharding: horizontal partitioning uses multiple tables or schemas in the same database, while sharding distributes the same table across multiple servers with the same name.

Spin up docker postgres shards by creating an init.sql that defines a url table, building a custom pg shard image, spinning up multiple containers, and managing via pgadmin.

Learn how to read from a shard by mapping a URL id to the correct server, run a query, and handle not found cases, while understanding sharding trade-offs.

Harness database sharding to achieve horizontal scalability across data, memory, and cpu. Enhance security with shard-based access control and improve query performance with smaller indexes.

Identify read performance issues before sharding and explore partitioning, replication, caching, and read balancing across backups to scale relational databases.

Demonstrates how deadlocks arise when two transactions acquire exclusive locks on the same resource in a Postgres database, leading to rollbacks and a last-in transaction failing.

Explore how an update with an exclusive row lock prevents double bookings, and compare update without select for update to select for update, while discussing read committed and pessimistic locking.

Master replication uses one master node for writes and DDL, with standby replicas receiving changes; clients write to the master and reads can target master or replicas with eventual consistency.

Explore multi-master replication to scale writes with multiple master leader nodes and potential conflicts. Compare it to one-way replication, where a single master and backup followers deliver simpler, easier implementation.