PostgreSQL Mastery
Advanced PostgreSQL — SQL mastery, indexing strategies, query optimisation, transactions, replication, and production operations.
100% Free · All lessons unlocked
Course Content
Foundations
Core concepts and groundwork
Introduction to PostgreSQL: Architecture and Installation
Reading
Data Types: Numeric, Text, JSON, Arrays, and UUID
Reading
Tables, Constraints, and Relationships
Reading
Basic SQL: SELECT, INSERT, UPDATE, DELETE
Reading
Foundation Practice: Schema Design and Basic Queries
Exercise
Joins: Inner, Left, Right, Full, and Cross
Reading
Core Skills
Essential techniques and patterns
Aggregations: GROUP BY, HAVING, and Window Functions
Reading
Subqueries, CTEs, and Recursive Queries
Reading
Views and Materialised Views
Reading
Advanced Query Practice: Analytics Dashboard
Exercise
Indexes: B-Tree, Hash, GIN, GiST, and BRIN
Reading
Index Design: Partial, Composite, and Expression Indexes
Reading
Applied Practice
Hands-on, real-world scenarios
EXPLAIN ANALYZE: Reading and Optimising Query Plans
Reading
Table Partitioning: Range, List, and Hash
Reading
Indexing and Query Optimisation Practice
Exercise
Transactions: ACID, Isolation Levels, and Locking
Reading
MVCC: How PostgreSQL Manages Concurrency
Reading
Deadlocks: Detection, Prevention, and Resolution
Reading
Advanced Topics
Deeper, more complex material
Full-Text Search with tsvector and tsquery
Reading
Mid-Course Project: High-Performance Analytics DB
Project
JSONB: Querying, Indexing, and Performance
Reading
Stored Procedures and PL/pgSQL Functions
Reading
Triggers: Auditing and Data Integrity
Reading
pg_cron and Scheduled Maintenance Jobs
Reading
Production & Scale
Building for the real world
Advanced Features Practice: Audit System
Exercise
Replication: Streaming, Logical, and Slot-Based
Reading
High Availability with Patroni and pg_auto_failover
Reading
Connection Pooling with PgBouncer
Reading
Backup and Recovery: pg_dump, pg_basebackup, PITR
Reading
Replication and HA Practice
Exercise
Mastery & Capstone
Projects and final review
PostgreSQL Security: Roles, Row-Level Security, SSL
Reading
Vacuum, Analyse, and Autovacuum Tuning
Reading
PostgreSQL with Docker and Kubernetes
Reading
Extensions: PostGIS, TimescaleDB, and pgvector
Reading
Capstone: Production PostgreSQL Cluster
Project
100% Free · All lessons unlocked
Topic Overview
What you'll learn
- Core concepts and fundamentals of PostgreSQL Mastery
- Industry best practices and design patterns
- Hands-on exercises with real-world scenarios
- Performance optimization and advanced techniques
- Building production-ready applications