Free to read ยท Three levels

PostgreSQL: relational reasoning and dependable operations

A complete PostgreSQL theory course connecting accurate SQL with MVCC, indexes, JSONB, partitioning and recoverable operations.

18 lessonsEnglish + ArabicContent reviewed
Start learning

In this course

Learn through a synthetic quay logistics domain containing customers, bookings, parcels, shipments, inventory and scan events. Eighteen developed lessons progress from query correctness to concurrency, performance evidence and reliable service changes.

Each lesson provides explained SQL, reasoned expected results, cause/fix/prevention mistakes, worked exercises and official references. Examples are non-runnable because no isolated PostgreSQL engine was executed for this authoring task. They never require access to a configured application database.

What you will learn

  • Design constrained schemas and deterministic reports with PostgreSQL semantics.
  • Coordinate transactional writes, roles, indexes, vacuum and analytic SQL.
  • Evaluate JSONB, partitioning, backup chains, replication and controlled migrations.

Your learning path

Three levels

Basic: relational foundations

Model valid facts and compose deterministic queries and scoped changes.

  1. PostgreSQL servers, sessions and schemas
  2. Types, constraints and generated identity
  3. SELECT, NULL ordering and pagination
  4. Joins and relational cardinality
  5. Aggregates and reporting stages
  6. Writes, RETURNING and conflict handling
Explore this level

Intermediate: concurrency and maintainable SQL

Coordinate snapshots, indexes, analytic queries, privileges and version cleanup.

  1. Transactions, MVCC and isolation
  2. Indexes and EXPLAIN plans
  3. Views, CTEs and window queries
  4. Roles, authentication and schema privileges
  5. VACUUM, autovacuum and planner statistics
  6. Locks, deadlocks and aborted transactions
Explore this level

Advanced: evidence and reliable operations

Plan performance, flexible data, partition lifecycle and recoverable change.

  1. Performance investigation and estimate quality
  2. JSONB data and index strategies
  3. Partitioning and data lifecycle
  4. Backup, restore and point-in-time recovery
  5. Replication, consistency and failover
  6. Operations, migrations and reliability
Explore this level

Who this is for

  • Developers learning PostgreSQL application design and relational SQL.
  • Engineers reviewing database concurrency, performance and recovery practices.

Before you start

  • Basic familiarity with applications, files and tabular data.
  • Read all SQL as theory; optional independent practice requires an explicitly disposable training database.

Related technical solutions

Diagnose PostgreSQL connection, authentication and HBA rulesClassify endpoint failures separately from HBA matching, credentials and object privileges. Use existing authorized read-only evidence without weakening authentication.Diagnose slow queries, index use and bad estimatesSeparate excessive query work, estimation errors and waits before proposing an index or configuration change. Preserve result semantics while comparing evidence.Recover from aborted transactions and deadlocksFind the initiating error behind SQLSTATE 25P02 and distinguish lock waits, deadlocks and serialization failures. Restore clean connection state before reuse.