From your first SELECT to failover. One path, two courses.

The foundation: the relational model, the tools, queries, types, joins and reports. By the end of module 1 you can answer questions about the course database directly in SQL.

1. Understanding databases and the relational model Name tables, keys and relationships (one-to-many, many-to-many); justify integrity rules and the first three normal forms; understand what PostgreSQL is and what it isn't.
2. First steps with psql and a client Tell instance, database and schema apart; use psql meta-commands and a graphical client; understand `search_path`; recognize connection errors by their message.
3. SELECT, WHERE, ORDER BY, LIMIT Logical evaluation order, three-valued logic with NULL, the `NOT IN` trap, patterns with `LIKE`/`ILIKE`, deterministic sorting and limiting, readable formatting.
4. Choosing the right data types in PostgreSQL `integer`/`bigint`/`numeric` versus floating point, `text` versus `varchar(n)`, `timestamptz` and daylight saving time, `boolean`, UUID with `uuidv7()` (new in PostgreSQL 18), identity instead of `serial`, an overview of arrays and JSONB.
5. Mastering joins systematically INNER, LEFT and FULL OUTER JOIN, join chains, self-joins, subqueries with `IN`, `EXISTS` and `NOT EXISTS`; where a condition goes, `ON` or `WHERE`; cross products and the wrong join column as typical mistakes.
6. Aggregation and grouping Aggregate functions and NULL, `GROUP BY`, `WHERE` versus `HAVING`, the counting traps `count(*)` and multiplication by joins, `FILTER`, intermediate results with `WITH`.

From reading to writing: changing data, designing rules, protecting changes and encapsulating queries.

1. INSERT, UPDATE, DELETE and conflicts Statements that write, `RETURNING` with `old` and `new` (new in PostgreSQL 18), changes based on a second table, upserts with `ON CONFLICT`, an overview of `MERGE`, the habit of “SELECT first, then change”.
2. DDL: designing tables and constraints The range of constraints with meaningful names, adding constraints to existing tables, justifying `ON DELETE` (`RESTRICT`, `CASCADE`, `SET NULL`) in plain language, drawing the line between integrity and process logic.
3. Transactions for beginners, explained properly `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`; the aborted transaction state; what transactions don't do; observing uncommitted changes, waiting writers and lost updates with two sessions.
4. Views, materialized views and simple functions Views as stored queries, materialized views with `REFRESH` and the price of freshness, SQL functions with parameters, dependencies between objects.

Bringing data in from outside and taking the first deliberate look at the execution plan. Unit 12 is the handover to the Engineer Training.

1. Importing and exporting data `COPY` and `\copy`, the four CSV mistakes (delimiter, quoting, encoding, number format), structural versus content errors, the import pattern with a staging table and a log, `ON_ERROR` and `REJECT_LIMIT`.
2. Your first deliberate look at EXPLAIN Read plans, compare estimates with measurements, tell Seq Scan, Index Scan and Bitmap Heap Scan apart, prove the effect of an index, recognize missing indexes on foreign keys.

1. Roles, privileges, reading pg_hba.conf Login and group roles, `GRANT`/`REVOKE` at every level, default privileges of `PUBLIC`, reading and changing `pg_hba.conf`, assigning every error message to its layer of control.
2. Backup and restore basics `pg_dump` in three formats, `pg_restore` in full and in parts, what a dump doesn't contain (roles, configuration, and in PostgreSQL 18 no statistics by default), RPO and RTO, why `pg_dump` is not PITR.

1. Understanding ORMs instead of fearing them Make an ORM's SQL visible, measure and fix the N+1 pattern, transaction boundaries in the framework, connection pools, a deliberate switch to SQL; translation to JPA, EF Core and Django.
2. SQL injection and parameterized queries Understand how injection works, parameterized queries in psycopg, JDBC and ORMs, safe handling of column names, LIKE patterns and value lists, minimal privileges as a second layer.

Before it gets to performance, operations and architecture, there is an understanding of the mechanics: how PostgreSQL plans and executes a query, how transactions see and lock each other, and how MVCC and VACUUM work together. The whole training builds on these five units.

1. Understanding query processing in PostgreSQL Know the five phases parser, rewrite, planner, executor, portal. Understand how an SQL statement runs internally.
2. Mastering EXPLAIN and EXPLAIN ANALYZE Read plan operators (Seq Scan, Index Scan, Nested Loop, Hash Join, Merge Join), interpret costs versus actual rows, use buffers output (on by default with ANALYZE since PG 18) and timing output, know `MEMORY`/`SERIALIZE` (PG 17).
3. Statistics and the query planner `pg_statistic` and `pg_stats`, the meaning of `n_distinct`, histograms and correlation. `ANALYZE`, autovacuum analyze, `default_statistics_target`.
4. Transactions, isolation and locks MVCC, snapshot isolation, the four isolation levels in PostgreSQL, serialization through SSI. Table and row locks, lock queue, deadlocks, `pg_locks`, lock timeouts. Durability through WAL, commit and checkpoints, `synchronous_commit`.
5. Understanding VACUUM and MVCC maintenance Page layout, tuple versions, dead tuples, bloat, HOT updates and fillfactor, free space map, visibility map. Autovacuum tuning, `VACUUM` versus `VACUUM FREEZE` versus `VACUUM FULL`, the danger of wraparound.

1. Index types at a glance B-tree, hash, GIN, GiST, SP-GiST, BRIN. Which index fits when, how GIN (posting lists) and BRIN (block ranges) work.
2. Advanced index techniques Partial indexes, expression indexes, multicolumn indexes and column order, covering indexes (`INCLUDE`), `CREATE INDEX CONCURRENTLY`, `REINDEX CONCURRENTLY`, `pg_stat_user_indexes` (hit rate, unused indexes).
3. Query optimization in practice Anti-patterns (N+1, `SELECT *`, OR contortions, `OFFSET` pagination), controlling CTE materialization (`MATERIALIZED`/`NOT MATERIALIZED` since PG 12), using window functions efficiently, keyset pagination, `work_mem` and spills.
4. Connection management and pooling PostgreSQL process model, cost per connection, `max_connections`, PgBouncer (session versus transaction versus statement mode), prepared statements in the pool, timeout strategies.

1. Data modeling beyond normal form Normal forms, choice of keys and data types, deliberate denormalization, materialized views, array and JSONB columns (when the document model, when the relational model), `CHECK` constraints, composite types.
2. Partitioning Declarative range, list and hash partitioning, partition pruning, partitionwise joins, routing through partition keys, managing partitions, limits (indexes per partition).
3. Running schema migrations professionally Lock effects of DDL, `NOT VALID` + `VALIDATE`, concurrent index and constraint builds, squashing migrations. Flyway, Liquibase and Sqitch compared, rollback strategies, baseline from an existing database.

1. Backup and recovery `pg_dump` versus `pg_basebackup` versus WAL-based backups, incremental backups with built-in tools since PG 17 (`pg_basebackup --incremental`, `pg_combinebackup`). Setting up pgBackRest (incremental backups, retention, compression), PITR (point-in-time recovery), `recovery_target_time`, verifying backups.
2. Replication Streaming replication (synchronous versus asynchronous), WAL internals, replication slots (failover slots since PG 17, `idle_replication_slot_timeout` since PG 18), `hot_standby` and query conflicts, logical replication (publications, subscriptions, `pg_createsubscriber`, conflict statistics since PG 18).
3. High availability with Patroni Architecture of Patroni (etcd/consensus, leader election, `pg_hba` automation), pg_auto_failover as a simpler alternative, avoiding split brain, routing layer (HAProxy, health checks).
4. PostgreSQL configuration and tuning `shared_buffers`, `effective_cache_size`, `work_mem`, `maintenance_work_mem`, WAL settings (`max_wal_size`, `synchronous_commit`), `autovacuum_*` basics, asynchronous I/O since PG 18 (`io_method`, `effective_io_concurrency`), restart versus reload, configuration management through `ALTER SYSTEM`.

1. Monitoring with Prometheus and postgres_exporter Core metrics (connections, WAL, replication lag, autovacuum backlog), postgres_exporter queries and custom metrics, Alertmanager rules for lag, disk usage, transaction age and lock wait times.
2. pg_stat_statements and workload analysis Top queries by total time and calls, interpreting `shared_blks_*`, detecting regressions after releases, complementary views (`pg_stat_user_tables`, `pg_stat_io` from PG 16, `pg_wait_sampling`).
3. Infrastructure as code for databases Docker Compose for local setups, Ansible or Terraform for instance setup, avoiding configuration drift, handling secrets, reproducible environments. Optional: PostgreSQL in Kubernetes (CloudNativePG).

1. Role and privilege concept Role hierarchies (membership options since PG 16), `GRANT` strategies, default privileges, `SECURITY DEFINER` risks, separate application and admin roles, `pg_hba.conf` and SCRAM authentication (MD5 deprecated since PG 18).
2. Row-level security and tenant isolation RLS policies, switching roles (`SET ROLE`), performance effects of RLS, tenancy models (schema per tenant versus shared tables with `tenant_id` + RLS).
3. Auditing, encryption and GDPR operations `pgAudit`, `log_statement` strategies, enforcing TLS, encryption at rest (LUKS, cloud KMS), anonymization with the `anon` extension, retention periods and deletion concepts (`DELETE` versus dropping partitions).

1. PostGIS — spatial data and queries Geometry types, SRIDs, spatial indexes (GiST), distance and containment queries, geocoding workflows.
2. Full-text search with tsvector and pg_trgm `to_tsvector`/`to_tsquery`, ranking (ts_rank), GIN versus GiST, fuzzy search with `pg_trgm` and similarity operators, combining both approaches.
3. pgvector and AI workloads — semantic search and hybrid ranking Storing embeddings (pgvector 0.8: `vector`, `halfvec`), HNSW versus IVFFlat index, similarity search, filtered search with iterative index scans, hybrid search (vector + full text), assessing PostgreSQL as a vector backend.
4. Citus and horizontal scaling — sharding beyond the single instance Sharding concepts, distribution key, co-located joins, rebalancer. Distinction: when a large single instance is enough, when Citus or another system.
5. Migration from Oracle or MySQL to PostgreSQL — tools, traps, cutover Type mapping, sequences versus auto-increment, translating PL/SQL (Ora2Pg), casing and empty-string traps, test strategy with shadow deployments and dual writes.

1. Capacity planning and sizing — measurements, scenarios, calendar Growth models, IOPS and throughput estimates, storage calculation (WAL, bloat, backups), hardware and cloud sizing, estimating the limits of an instance.
2. Incident response and post-mortems — runbooks, roles, escalation thresholds Runbooks (replication lag, disk full, autovacuum backlog, lock storm), escalation paths, communication rules during incidents, a blameless post-mortem culture.
3. Database reliability engineering — SLOs, drills and the reliability pipeline SLOs for databases (availability, replication lag, query latency P99), error budgets, chaos tests (kill the primary under load), automated failover drills, backup restore tests as a CI job.
4. Architecture reviews and team standards — checklists, ADRs, trade-offs Schema and migration review checklist, index standards, naming conventions, documenting technical decisions (ADR), trade-off analyses (for example RLS versus application-layer security, partitioning versus archiving).
5. Mentoring and stakeholder communication — explaining, onboarding, evaluating costs Explaining complex technical matters to non-specialists, creating onboarding material for junior colleagues, bringing database topics into project planning, assessing cost drivers (cloud services versus self-hosting) for the business.