Fundamentals of Database Engineering

ACID which stands for Atomicity, consistency, isolation, and durability are four critical properties of relational database. I think any engineer working with a relational database like postgres, mysql, sqlserver oracle, should understand these properties.

In this course, we will go through the four properties and explain why each is critical to build and use a relational database successfully.

In this video we will demonstrate Atomicity, Isolation, Consistency and Durability on Postgres, fully practical example.

Answer the following questions about ACID properties in databases

This lecture details the inner working of database systems with regards to storage. It is a must watch to understand the difference between tables, pages, IO, rows, indexes and data files.

In this lecture I will discuss the difference between Primary Key and a Secondary Key and how it can affect your performance.

Lots of you asked me how to create a table with millions of rows in postgres, here are the details

In this video, I explain the benefits of Bitmap Index Scan and how it differs from Index scan and table sequential scan. 

If you create an index on a large production table in postgres, the operations blocks writes in order to make sure to pull all the field entries to the index. However most of the time you can't afford to block writes on an active production database table. Postgres new feature which allows create index concurrently allows writes and reads to go in the expense of cpu/memory, time and chance for the index to be invalid. A small price to pay for fast production writes! https://www.postgresql.org/docs/9.1/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

B-tree is a self-balancing tree data structure that maintains sorted data and allows searches, sequential access, insertions, and deletions in logarithmic time. However, most contents explain this data structure from a theoretical point of view, in this lecture I’d like to shed some light on the practical considerations of B-Tree and B+Trees in real production database systems such as Postgres and MySQL.

Link to the original paper https://infolab.usc.edu/csci585/Spring2010/den_ar/indexing.pdf

B-Tree limitation

Assume you have a table that is partitioned on the customer_id field serial 32bit, and you want to partition by range, how do you create all the necessary partitions? this is what I discuss in this video

Source Code

https://github.com/hnasr/javascript_playground/tree/master/automate_partitions

In this lecture we explain the difference between exclusive (write locks) and shared locks (read locks)

In this video, I demonstrate how is it possible to get double booking in database-backed web applications and how to prevent double booking and race conditions with row-level locks.

Source Code https://github.com/hnasr/javascript_playground/tree/master/booking-system

In this video I’ll explain why you should avoid using SQL offset when implementing any kind of paging. I’ll explain what offset does, why is it slow and what is the alternative for better performance This video is inspired by Use the index luke, i’ll have a link to the blog and slides to learn more. Let say you have a web application with an API that supports paging, you user want to request 10 news articles in page 10, this is performed via a simple GET request as shown here The API server receives the GET request and builds the SQL in order to send it to the database hopefully a pool of connections exist here. Page 10 translates to offset 100 assuming each page has 10 records and now the database is ready to execute the query against the table. Offset by design means fetch and drop the first x number of rows, so in this case the database will fetch the first 110 rows and physically drop the first 100 leaving the limit of 10 which the user will get. As the offset increase, the database is doing more work which makes this operation extremely expensive. Furthermore, the problem with offset is you might accidentally read duplicate records. consider the user now want to read page 11 and meanwhile someone inserted a new row in the table, row 111 will be read twice Let us jump and test this against postgres

Use the Index Luke Blog https://use-the-index-luke.com/no-offset

Slides in this video https://payhip.com/b/B6o1

Connection pooling is a pattern of creating a pool of available connections (usually TCP) and allow multiple clients to share the same pool of connections. This pattern is usually used when connection establishment and tearing down is costly, and the server has a limited number of connections. In this video we will learn how to use connection pooling in NodeJs when working with a Postgres Database, we will learn how to spin up a pool of database connections and use stateless pool queries and transactional queries begin/end, and finally, we will

• Node JS Source Code used in this lecture here https://github.com/hnasr/javascript_playground/tree/master/postgresnode-pool

  
  

Scripts and commands

docker run --name pgmaster -v /Users/HusseinNasser/postgres/v/master_data:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_PASSWORD=postgres -d postgres

docker run --name pgstandby -v /Users/HusseinNasser/postgres/v/standby_data:/var/lib/postgresql/data -p 5433:5432 -e POSTGRES_PASSWORD=postgres -d postgres

In standby node update postgresql.conf

primary_conninfo = 'application_name=standby host=husseinmac port=5432 user=postgres password=postgres’

add file standby.signal

touch standby.signal

In master update postgresql.conf

first 1 (standby1)

select * from pg_stat_replication

Scripts and commands

docker run --name pgmaster -v /Users/HusseinNasser/postgres/v/master_data:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_PASSWORD=postgres -d postgres

docker run --name pgstandby -v /Users/HusseinNasser/postgres/v/standby_data:/var/lib/postgresql/data -p 5433:5432 -e POSTGRES_PASSWORD=postgres -d postgres

In standby node update postgresql.conf

primary_conninfo = 'application_name=standby host=husseinmac port=5432 user=postgres password=postgres’

add file standby.signal

touch standby.signal

In master update postgresql.conf

first 1 (standby1)

select * from pg_stat_replication

If using the hostname doesn't work, use the IP address of the container itself. You can get the local IP address of the container by running docker inspect container name

We got through a practical system design exercises, this lecture is two parts. Part 1 is all about backend engineering and scaling and Part 2 focuses on database design.