PostgreSQL Course

Lesson 1 • Installing and Configuring PostgreSQL

Guides installation on major operating systems and initial server configuration. Students gain a working local environment for all hands-on exercises.

Lesson 2 • Using psql and pgAdmin

Introduces the psql command-line interface and the pgAdmin graphical tool. Students can execute queries and browse database objects using both interfaces.

Lesson 3 • Creating Your First Database and Tables

Demonstrates CREATE DATABASE, CREATE TABLE, and basic data insertion. Ties architecture concepts to practical object creation within a live PostgreSQL instance.

Lesson 4 • Relational Database Fundamentals

Covers tables, rows, columns, keys, and relationships as the basis of relational design. Establishes the conceptual framework needed for all subsequent PostgreSQL work.

Lesson 1 • Working with PostgreSQL Data Types

Explores numeric, text, boolean, date/time, and JSON types specific to PostgreSQL. Correct type selection improves storage efficiency and query accuracy.

Lesson 2 • Sorting, Limiting, and Paging Results

Teaches ORDER BY, LIMIT, and OFFSET for controlling result sets. Directly supports pagination patterns used in application development.

Lesson 3 • Inserting, Updating, and Deleting Data

Covers DML commands INSERT, UPDATE, DELETE, and TRUNCATE with safe practices. Students can maintain table data while avoiding accidental mass changes.

Lesson 4 • Selecting and Filtering Data

Covers SELECT, WHERE, comparison operators, and logical connectors. Forms the foundation for every query written throughout the course.

Lesson 1 • Set Operations: UNION, INTERSECT, EXCEPT

Teaches combining result sets with set operators and their ALL variants. Enables students to merge or compare outputs from structurally similar queries.

Lesson 2 • Advanced Filtering Techniques

Covers ANY, ALL, EXISTS, and lateral joins for sophisticated row selection. Extends filtering vocabulary beyond basic WHERE conditions.

Lesson 3 • Common Table Expressions

Introduces WITH clauses (CTEs) for readable, modular query construction. Students refactor complex nested queries into maintainable CTE chains.

Lesson 4 • Subqueries and Derived Tables

Covers scalar, row, and table subqueries in SELECT, FROM, and WHERE clauses. Builds query composition skills needed for advanced filtering and transformation.

Lesson 1 • Schemas, Namespaces, and Search Path

Explains PostgreSQL schemas as namespaces and the search_path setting. Students organise objects across schemas for multi-tenant or modular applications.

Lesson 2 • Constraints and Data Integrity

Explains NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY constraints. Enforcing integrity at the database level prevents invalid data from entering tables.

Lesson 3 • Altering and Evolving Schemas

Teaches ALTER TABLE operations and safe schema migration strategies. Students modify live schemas without causing downtime or data loss.

Lesson 4 • Sequences and Auto-Generated Keys

Covers SERIAL, BIGSERIAL, and GENERATED ALWAYS AS IDENTITY for surrogate keys. Students choose the appropriate key generation strategy for each table.

Lesson 1 • Reading EXPLAIN and EXPLAIN ANALYZE

Teaches interpreting execution plans, node types, and actual vs. estimated rows. Students identify bottlenecks in query plans and prioritise optimisation efforts.

Lesson 2 • B-Tree and Other Index Types

Covers B-Tree, Hash, GIN, GiST, and BRIN indexes with appropriate use cases. Students select the correct index type based on data characteristics and query patterns.

Lesson 3 • How PostgreSQL Executes Queries

Explains the parser, planner, and executor pipeline and cost-based optimisation. Understanding this pipeline is prerequisite to interpreting EXPLAIN output.

Lesson 4 • Query Optimisation Strategies

Applies rewriting techniques, statistics tuning, and planner hints to improve performance. Students resolve common anti-patterns such as implicit casts and function wrapping.

Lesson 1 • Handling Concurrency Conflicts

Addresses optimistic vs. pessimistic locking strategies and retry logic. Students implement patterns that handle concurrent updates without data corruption.

Lesson 2 • Transaction Fundamentals

Covers BEGIN, COMMIT, ROLLBACK, and SAVEPOINT for atomic data changes. Students wrap multi-step operations in transactions to guarantee consistency.

Lesson 3 • Locking Mechanisms

Covers table-level, row-level, and advisory locks and their acquisition modes. Students prevent deadlocks and minimise lock contention in concurrent workloads.

Lesson 4 • MVCC and Isolation Levels

Explains Multi-Version Concurrency Control and the four SQL isolation levels. Students choose isolation levels that balance consistency requirements with throughput.

Lesson 1 • Window Functions

Covers OVER, PARTITION BY, ORDER BY, and frame clauses for analytical queries. Students compute running totals, rankings, and moving averages without self-joins.

Lesson 2 • Arrays, Ranges, and Composite Types

Explores PostgreSQL array operators, range types, and user-defined composite types. Students model complex data structures natively without auxiliary tables.

Lesson 3 • Table Partitioning

Covers range, list, and hash partitioning strategies and partition pruning. Students partition large tables to improve query performance and simplify data lifecycle management.

Lesson 4 • JSON and JSONB Data Handling

Teaches storing, querying, and indexing semi-structured data with JSONB operators. Students integrate document-style data into relational schemas effectively.

Lesson 1 • Backup and Restore Strategies

Teaches pg_dump, pg_restore, and pg_basebackup for logical and physical backups. Students design backup schedules that meet recovery time and point objectives.

Lesson 2 • Server Monitoring and Diagnostics

Uses system catalog views, pg_stat_* views, and logging to monitor server health. Students detect and respond to performance degradation before it impacts users.

Lesson 3 • Connection Pooling and Scaling

Covers connection overhead, PgBouncer pooling modes, and read replica routing. Students configure pooling to support high-concurrency application workloads.

Lesson 4 • Write-Ahead Logging and Replication

Explains WAL mechanics and configures streaming replication for high availability. Students set up a primary-standby pair and verify replication lag.