Intermediate friendly Free

PostgreSQL in Depth Tutorial

Master PostgreSQL schema design, querying, indexes, transactions, security, backup, replication, and evidence-based performance tuning.

  • 30lessons
  • 10modules
  • ~10hstudy time
  • Intermediatestart level
Start learning PostgreSQL in Depth View curriculum

What you will learn

  • PostgreSQL Foundations
  • Relational Design
  • Querying Data
  • Indexes and Planning
  • Transactions and Concurrency
  • Advanced Data Features
  • Server-Side Design
  • Security
  • Operations
  • Performance and Capstone

Course curriculum

10 modules · 30 lessons · follow them in order or jump to any topic

1 PostgreSQL Foundations 3 lessons
  1. 1PostgreSQL Architecture and Processes
  2. 2Install PostgreSQL and Use psql
  3. 3Databases, Schemas, Search Paths, and Ownership
2 Relational Design 3 lessons
  1. 4Data Types and Domain Modeling
  2. 5Primary Keys, Foreign Keys, and Constraints
  3. 6Normalization, Denormalization, and Schema Evolution
3 Querying Data 3 lessons
  1. 7SELECT, Filtering, Expressions, and NULL
  2. 8Joins, Subqueries, and Common Table Expressions
  3. 9Grouping, Aggregates, and Window Functions
4 Indexes and Planning 3 lessons
  1. 10B-Tree and Multicolumn Indexes
  2. 11Partial, Expression, GIN, GiST, and BRIN Indexes
  3. 12EXPLAIN, Statistics, and Query Planner Decisions
5 Transactions and Concurrency 3 lessons
  1. 13ACID Transactions and Isolation Levels
  2. 14MVCC, Vacuum, and Row Visibility
  3. 15Locks, Deadlocks, and Safe Concurrent Updates
6 Advanced Data Features 3 lessons
  1. 16JSONB Modeling and Indexing
  2. 17Arrays, Ranges, Enums, Domains, and Composite Types
  3. 18Full-Text Search and Generated Columns
7 Server-Side Design 3 lessons
  1. 19Views, Materialized Views, and Refresh Strategies
  2. 20Functions, Procedures, and PL/pgSQL
  3. 21Triggers, Audit Trails, and Event-Driven Patterns
8 Security 3 lessons
  1. 22Roles, Privileges, and Default Permissions
  2. 23Row-Level Security and Tenant Isolation
  3. 24TLS, Authentication, Secrets, and SQL Injection Defense
9 Operations 3 lessons
  1. 25Backup, Restore, and Point-in-Time Recovery
  2. 26Streaming Replication and High Availability
  3. 27Monitoring, Connection Pooling, and Routine Maintenance
10 Performance and Capstone 3 lessons
  1. 28Slow Query Investigation and Index Tuning
  2. 29Partitioning, Bulk Loading, and Data Lifecycle
  3. 30Capstone: Design and Operate a Production Database

About this course

This tutorial suits complete beginners as well as learners who already know some programming and want a structured reference. If PostgreSQL in Depth is new to you, start from the first lesson and work through each module in order. Experienced learners can use the curriculum above to jump directly to a specific concept.

How to get the most from it

  1. Read each lesson in order and run every example yourself.
  2. Change the examples to test your understanding instead of only copying them.
  3. Complete the exercises and review the common-mistake sections before moving on.
  4. Mark lessons complete as you go — your progress is saved in this browser.

Frequently asked questions

Is this PostgreSQL in Depth tutorial free?

Yes. Every lesson listed on this page is free to read and can be completed at your own pace.

Do I need previous experience?

No previous experience is required for the introductory lessons. Some advanced modules are easier if you first complete the fundamentals in this course.

How should I practise?

Type and run the examples, deliberately change their inputs, predict the result before execution and build a small project after completing each major module.