Syed Jafer K

Its all about Trade-Offs

Postgres Database – Syllabus

PreRequisites:

  1. Docker – https://www.youtube.com/playlist?list=PLiutOxBS1Mizi9IRQM-N3BFWXJkb-hQ4U

Module 0: Need of Database and Relational Database

  1. Flat Files for Storage
  2. Repeating Groups & Other Problems
  3. Relational Database Model

Module 1: Introduction to PostgreSQL and Database Fundamentals

  1. What is PostgreSQL?
  2. Fundamental DBMS Concepts
    • Basics of Database Management Systems (DBMS)
    • Database Design

Module 2: Basic Data Manipulation

  1. Creating Tables
    • Analyzing CREATE TABLE
  2. Inserting Data
  3. Retrieving Data with SELECT
    • SELECT
    • SELECT DISTINCT
    • COUNT
    • ORDER BY
    • LIMIT
    • BETWEEN
    • IN
    • LIKE and ILIKE

Module 3: Filtering and Modifying Records

  1. Filtering Records
    • WHERE clause basics
    • Using lists with WHERE
    • Updating and Deleting Rows
  2. Sorting Records
    • Sorting Basics
    • Variations in Sorting (Offset and Limit)

Module 4: Relationships and Constraints

  1. Working with Tables and Relationships
    • One-to-One, One-to-Many, Many-to-Many
    • Primary Keys and Foreign Keys
    • Auto-Generated IDs
    • Creating Foreign Columns and Constraints
    • Foreign Key constraints on insert/delete operations
  2. Database Validation & Constraints
    • NULL Constraints
    • Default Column Values
    • Unique Constraints and Multi-column Uniqueness
    • Checks and Multi-column Checks

Module 5: Joins and Set Operations

  1. Joins
    • Basics and Types (Inner, Left, Right, Full Joins)
    • Using WHERE with Joins
    • Handling Missing Data in Joins
  2. Union and Intersections
    • Using Union and Intersect
    • Set Differences with EXCEPT

Module 6: Aggregation and Grouping

  1. Aggregation of Records
    • Aggregate Functions
    • GROUP BY and Combining Aggregates
    • Advanced Grouping

Module 7: Subqueries and Complex Queries

  1. Subqueries
    • Basics and Types of Subqueries (SELECT, FROM, JOIN)
    • Using Correlated Subqueries
  2. Views
    • Simplifying Queries with Views
    • Managing Database Design with Schema Migrations

Module 8: PostgreSQL Data Types and Advanced Constraints

  1. Data Types in PostgreSQL
    • Numeric, Character, Boolean, Date/Time Types
  2. Advanced Constraints
    • Polymorphic Associations
    • Designing a Reaction, Like, Mention, and Hashtag System

Module 9: Indexes and Query Tuning

  1. Understanding Indexes
    • Full Table Scans vs. Index Scans
    • Types of Indexes
    • Indexing for Performance
  2. Query Tuning
    • EXPLAIN ANALYZE and the SQL Planner
    • Cost Analysis of Queries

Module 10: Database Design Patterns and Data Access

  1. Design Patterns
    • Repository Pattern
    • Case Handling
  2. Implementing Patterns in PostgreSQL
    • Tables with Checks
    • Managing Relationships (Likes, Comments, Tags)

Module 11: Advanced Database Internals

  1. PostgreSQL Internals
    • Storage Mechanisms (Heaps, Blocks, Tuples)
    • Block and File Layouts
    • Primary vs. Secondary Key Differences
  2. Database Indexing
    • B-Tree and B+Tree
    • Bitmap Index Scans and Combined Indexing

Module 12: Partitioning and Sharding

  1. Database Partitioning
    • Types and Approaches (Vertical, Horizontal)
    • Partitioning in PostgreSQL
  2. Sharding
    • Sharding Basics and Consistent Hashing
    • Sharding with PostgreSQL (using Docker)

Module 13: Concurrency and Transactions

  1. Concurrency Control
    • Locks (Shared, Exclusive)
    • Deadlocks and Two-Phase Locking
    • Pagination and Connection Pooling
  2. ACID Properties
    • Transactions, Isolation, Atomicity, Consistency, Durability
    • Phantom Reads, Consistency Levels

Module 14: Replication and High Availability

  1. Database Replication
    • Master/Standby and Multi-Master Replication
    • Synchronous vs. Asynchronous Replication
  2. Security and SQL Injection Prevention
    • Preventing SQL Injection
    • Secure Data Access