PostgreSQL DBA Course

Lesson 1 • Starting, Stopping, and Reloading the Server

Covers pg_ctl, systemd service management, and signal-based control. Students practice safe server lifecycle operations used in every DBA workflow.

Lesson 2 • Relational Database Core Concepts

Covers RDBMS principles, ACID properties, and how PostgreSQL fits the relational model. Provides the conceptual baseline for all subsequent administration topics.

Lesson 3 • Directory Layout and Key Files

Maps the data directory structure, configuration files, and log locations. Familiarity with file layout is essential for configuration and recovery tasks.

Lesson 4 • PostgreSQL Process and Memory Architecture

Examines postmaster, backend processes, shared buffers, and WAL. Understanding these internals guides tuning and troubleshooting decisions throughout the course.

Lesson 1 • Databases, Schemas, and Tablespaces

Explains the three-level namespace hierarchy and tablespace storage mapping. Proper namespace design prevents object conflicts and supports storage management.

Lesson 2 • Sequences and Identity Columns

Explains sequence objects and the GENERATED AS IDENTITY syntax for surrogate keys. Students configure auto-incrementing columns correctly for production schemas.

Lesson 3 • Views, Materialized Views, and Functions

Introduces read-only and updatable views, materialized view refresh, and basic PL/pgSQL functions. These objects encapsulate logic and simplify application interfaces.

Lesson 4 • Index Types and Usage

Surveys B-tree, Hash, GIN, GiST, and BRIN indexes and their appropriate use cases. Index selection directly affects query performance covered in later chapters.

Lesson 1 • Object-Level Privilege Management

Explains GRANT and REVOKE on tables, schemas, sequences, and functions. Students apply the principle of least privilege across a realistic multi-schema database.

Lesson 2 • Roles and Role Attributes

Covers CREATE ROLE, LOGIN, SUPERUSER, and CREATEDB attributes. Role design is the foundation of every privilege and authentication decision in PostgreSQL.

Lesson 3 • Row-Level Security Policies

Introduces RLS policy creation, USING and WITH CHECK clauses, and policy testing. RLS enforces data isolation without requiring application-layer filtering logic.

Lesson 4 • Auditing and Security Hardening

Covers pgaudit extension, connection logging, and hardening parameters. Students produce an audit-ready configuration aligned with common security standards.

Lesson 1 • Query Planner Cost Parameters

Explains seq_page_cost, random_page_cost, cpu_tuple_cost, and statistics targets. Accurate cost parameters enable the planner to choose optimal execution plans.

Lesson 2 • WAL and Checkpoint Tuning

Covers wal_buffers, checkpoint_completion_target, max_wal_size, and fsync settings. WAL tuning balances write throughput against crash-recovery durability guarantees.

Lesson 3 • Memory Configuration Parameters

Explains shared_buffers, work_mem, maintenance_work_mem, and effective_cache_size. Correct memory allocation is the highest-impact tuning lever for most workloads.

Lesson 4 • OS-Level Tuning for PostgreSQL

Addresses kernel parameters, I/O scheduler selection, NUMA, and transparent huge pages. OS configuration often limits gains achievable through PostgreSQL parameters alone.

Lesson 1 • VACUUM and VACUUM FULL

Covers manual VACUUM, VACUUM FULL, and VACUUM ANALYZE syntax and effects. Students distinguish when each form is appropriate without causing unnecessary locking.

Lesson 2 • MVCC and Table Bloat Mechanics

Explains how MVCC creates dead tuples and page bloat over time. Understanding MVCC is prerequisite to designing effective VACUUM and autovacuum strategies.

Lesson 3 • Transaction ID Wraparound Prevention

Covers XID age monitoring, autovacuum_freeze_max_age, and emergency VACUUM. Wraparound failure causes total database unavailability and must be actively prevented.

Lesson 4 • Index Maintenance and REINDEX

Covers index bloat detection, REINDEX CONCURRENTLY, and pg_stat_user_indexes. Regular index maintenance preserves query performance without requiring table rewrites.

Lesson 1 • Logical Backup with pg_dump

Covers pg_dump formats, pg_dumpall, parallel dumps, and selective restore with pg_restore. Logical backups enable object-level recovery and cross-version migration.

Lesson 2 • Physical Backup with pg_basebackup

Explains streaming base backups, checkpoint modes, and WAL inclusion options. Physical backups are the foundation of PITR and standby server provisioning.

Lesson 3 • Point-in-Time Recovery Procedure

Guides restore from base backup, recovery.conf or postgresql.conf parameters, and target specification. Students execute and verify a complete PITR scenario end-to-end.

Lesson 4 • WAL Archiving Configuration

Covers archive_mode, archive_command, and archive_cleanup_command setup. Continuous WAL archiving enables recovery to any point after the base backup.

Lesson 1 • Failover and Patroni-Based HA

Covers manual promotion, pg_ctl promote, and automated HA with Patroni and etcd. Students simulate a primary failure and verify automatic failover completes correctly.

Lesson 2 • Logical Replication Fundamentals

Explains publications, subscriptions, and row filtering for selective replication. Logical replication supports cross-version upgrades and partial data distribution.

Lesson 3 • Streaming Replication Setup

Covers replication slots, pg_hba.conf replication entries, and primary_conninfo. Students build a working streaming standby from a live primary server.

Lesson 4 • Monitoring Replication Lag

Explains pg_stat_replication, pg_stat_wal_receiver, and LSN-based lag calculation. Lag monitoring is essential for SLA compliance and failover decision-making.

Lesson 1 • Statistics and Planner Accuracy

Covers pg_statistic, ANALYZE, extended statistics, and histogram interpretation. Stale or insufficient statistics cause the planner to choose suboptimal execution plans.

Lesson 2 • Join Strategies and Plan Hints

Explains nested loop, hash join, and merge join selection criteria and forcing techniques. Join strategy mismatches are a leading cause of severe query performance regressions.

Lesson 3 • Identifying Slow Queries with pg_stat_statements

Covers pg_stat_statements installation, key columns, and query normalization. Aggregate workload data reveals the highest-impact queries to prioritize for optimization.

Lesson 4 • Reading EXPLAIN and EXPLAIN ANALYZE

Decodes plan nodes, cost estimates, actual rows, and timing output from EXPLAIN. Accurate plan reading is the prerequisite skill for every query optimization technique.