PostgreSQL
Design tables, write SQL, read a query plan, manage roles, then run PostgreSQL in production: backups you have actually restored, WAL archiving, replication and monitoring.
Lessons in order
- 01Beginner 10 min
PostgreSQL Basics
What PostgreSQL actually is, how a connection reaches your data, and the difference between a server, a database, a schema and a table.
- 02Beginner 12 min
Installing PostgreSQL
Install PostgreSQL on Windows, Ubuntu and RHEL/CentOS, then start, stop, restart and check the service, and understand versions.
- 03Beginner 12 min
Using psql
Connect to PostgreSQL with psql, create and list databases, inspect tables and schemas, and check users and permissions.
- 04Beginner 16 min
SQL Fundamentals
The core SQL you will use every day: SELECT, INSERT, UPDATE, DELETE, WHERE, ORDER BY, GROUP BY, LIMIT, NULL handling and CASE.
- 05Beginner 14 min
PostgreSQL Data Types
Which PostgreSQL type to use and why: integers, numeric versus float for money, text, timestamptz, UUID, JSON versus JSONB, arrays and enums.
- 06Beginner 14 min
Table Design and Constraints
Create and alter tables properly: primary keys, foreign keys, UNIQUE, NOT NULL, CHECK, DEFAULT, identity columns and generated columns.
- 07Beginner 13 min
Table Relationships
Model real data with one-to-one, one-to-many and many-to-many relationships, including junction tables for users, roles and permissions.
- 08Beginner 14 min
Joins
Understand every join type with diagrams and real examples, and learn why LEFT JOIN with a WHERE clause quietly becomes an INNER JOIN.
- 09Beginner 12 min
Aggregation and Grouping
Turn rows into answers with COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING, using realistic reporting examples.
- 10Intermediate 14 min
Subqueries and CTEs
Use subqueries and common table expressions to break complex queries into readable steps, including EXISTS, NOT EXISTS and recursive CTEs.
- 11Intermediate 12 min
Views and Materialized Views
Create views to name complex queries, and materialized views to store expensive results, including how and when to refresh them.
- 12Intermediate 16 min
Indexes
How indexes make reads fast and writes slower, which index type to use, and how to choose composite and partial indexes properly.
- 13Intermediate 16 min
Query Performance and EXPLAIN
Read EXPLAIN and EXPLAIN ANALYZE output, understand scan types and cost estimates, and work through a slow query step by step.
- 14Intermediate 13 min
Transactions and ACID
Group statements into all-or-nothing units with BEGIN, COMMIT and ROLLBACK, use savepoints, and understand what ACID actually guarantees.
- 15Advanced 14 min
Isolation Levels
What dirty reads, non-repeatable reads and phantom reads are, and how PostgreSQL isolation levels prevent them.
- 16Advanced 15 min
Locks and Concurrency
How row and table locks work, when to use SELECT FOR UPDATE, why deadlocks happen, and how to find what is blocking a query.
- 17Advanced 15 min
Functions and Procedures
Write SQL and PL/pgSQL functions and procedures, with parameters, return values, variables, loops and exception handling.
- 18Advanced 14 min
Triggers
Write BEFORE and AFTER triggers for INSERT, UPDATE and DELETE, build an audit history table, and know when triggers hurt more than help.
- 19Advanced 16 min
Users, Roles and Security
Create roles, grant least-privilege access, configure pg_hba.conf and SCRAM authentication, enable SSL and use row-level security.
- 20Advanced 15 min
PostgreSQL Configuration
Understand postgresql.conf settings that matter: memory, connections, WAL, checkpoints and logging, and how to change them safely.
- 21Advanced 13 min
How PostgreSQL Stores Data
What happens on disk: the data directory, pages and tuples, TOAST for large values, shared buffers, WAL and checkpoints.
- 22Advanced 15 min
VACUUM and ANALYZE
Why PostgreSQL needs VACUUM, how dead tuples cause bloat, how to tune autovacuum, and why VACUUM FULL is not routine maintenance.
- 23Intermediate 13 min
Backup Fundamentals
The difference between logical backups with pg_dump and physical base backups, and when each approach is the right one.
- 24Intermediate 15 min
Daily Backup on a VPS
Set up a daily PostgreSQL backup on a VPS: backup user, directory, compression, timestamped files, retention and verification.
- 25Advanced 16 min
Automated Backup Script
Build a production backup script with error handling, exit codes, logging, configurable retention and alerting when it fails.
- 26Intermediate 14 min
Restoring a Backup
Restore into an empty or existing database with pg_restore and psql, recover a single table, and verify the restore properly.
- 27Advanced 14 min
WAL — The Write-Ahead Log
What the write-ahead log is, why it exists, and how WAL segments, LSNs, archiving and checkpoints fit together.
- 28Advanced 16 min
Point-in-Time Recovery
Recover a PostgreSQL database to any chosen moment by restoring a base backup and replaying archived WAL up to a recovery target.
- 29Advanced 15 min
A WAL Recovery Walkthrough
A complete worked example of recovering from an accidental deletion using a base backup and archived WAL, start to finish.
- 30Intermediate 10 min
Backup vs WAL
How backups and WAL complement each other, what each one alone can and cannot recover, and how to choose a strategy.
- 31Advanced 16 min
Replication and High Availability
Set up streaming replication, understand synchronous versus asynchronous, replication slots, read replicas and failover.
- 32Intermediate 15 min
Monitoring PostgreSQL
What to monitor and how: connections, slow queries, locks, disk and table sizes, replication lag, autovacuum and wraparound.
- 33Intermediate 12 min
Production Checklist
A practical checklist for running PostgreSQL in production: authentication, privileges, backups, WAL, monitoring and a documented recovery plan.
- 34Beginner 25 min
Project: User and Order Database
Build a working users and orders schema with proper constraints, relationships, indexes and seed data, then query it.
- 35Intermediate 30 min
Project: E-commerce Database Design
Design a fuller e-commerce schema with variants, inventory, addresses, payments and an audit trail, handling real business rules.
- 36Intermediate 30 min
Project: Optimize a Slow Query
Generate a realistic dataset, find a genuinely slow query, read its plan, and fix it measurably with the right index.
- 37Intermediate 35 min
Project: Daily Backup System
Build a full backup system end to end: least-privilege role, script with error handling, retention, offsite copy, alerting and a restore test.
- 38Intermediate 30 min
Project: Build a Restore Environment
Create a repeatable environment for restoring and verifying backups, with data checks, timing measurements and anonymisation.
- 39Advanced 35 min
Project: Primary and Standby Lab
Build streaming replication between two PostgreSQL instances, measure lag, test synchronous mode, and practise a failover.
- 40Advanced 40 min
Project: Point-in-Time Recovery Practice
Rehearse a full PITR: set up archiving, cause a realistic incident, recover to a chosen moment, verify and promote.