AI curriculum

Course outline for PostgreSQL Development

A query can return correct-looking results while concurrent updates break a business invariant. Build a PostgreSQL offer feature with keys, constraints, version-checked updates, and an atomically recorded change event. Inspect query plans separately from correctness checks.

Technology: Data Engineering and Databases
Duration: 6 hours
Modules: 3 · 2 hours each
Level: Level 2

About PostgreSQL Development

PostgreSQL Development Course Objectives

Design normalized tables and SQL queries for books, sellers, and offers.
Enforce transaction invariants and reject stale version updates.
Compare query plans before and after an index change.
Use a small function and JSON input with explicit validation.
Write business and outbox records in one transaction and explain duplicate-delivery limits.

Pre-requisites

Relational database concepts and workload-based database selection.
Familiarity with introductory SQL or programming and ability to read keys and constraints.

Before this course

Complete the required courses or bring equivalent experience. Recommended courses add useful depth; they are not enrolment gates.

Required preparation

Database Systems for AI Applications

Understands relational data and workload-based choices before practicing SQL, transactions, indexes, and query plans.

Find your place in the learning map

Lab Setup

Preconfigured local PostgreSQL instance, SQL client with two sessions, and a test or SQL script runner.
Schema/update-function starters, synthetic book/seller/offer data, prepared transaction fixtures, and a bounded benchmark dataset.
No production database, message broker, or pgvector extension is required.

Detailed Course Outline

Module 1: Model and query correct records

SQL: Combine SELECT, joins, filtering, and grouping to query related records; verify row counts before using the results in application decisions.
Schema design: Define normalized tables, primary and foreign keys, and business constraints that reject invalid offers at the database boundary.
Indexing: Choose indexes for defined query patterns and measure their effect rather than assuming additional indexes improve every workload.

Practical outcome: Build book/seller/offer tables with keys and constraints, then establish a measured query baseline.

Module 2: Inspect transaction and query behavior

Transactions: Commit related changes atomically and inject failure to verify that business updates and associated outbox records roll back together.
Query optimization: Compare EXPLAIN results against workload requirements, separating execution-plan improvements from correctness and transaction-invariant checks.
Functions: Implement a small constrained update function with explicit input validation and test valid changes alongside rejected business values.

Practical outcome: Test an atomic update and a small function, then compare EXPLAIN results for a defined workload rather than assuming every index helps.

Module 3: Validate inputs and protect concurrent changes

JSON: Validate required fields and values in JSON inputs before applying offer updates, rejecting malformed or incompatible payloads.
Performance: Record EXPLAIN ANALYZE measurements and row counts before and after changes to evaluate observed query behavior reproducibly.
Optimistic updates and outbox records: Reject stale edits with version checks and persist change events atomically; distinguish outbox persistence from delivery and consumer deduplication.

Practical outcome: Validate JSON fields, reject stale version updates, and commit an offer change with its outbox record atomically.

Practical exercise

Produce schema migrations, SQL queries, a constrained update function, and a repeatable workload script.
Accept a valid offer and reject malformed JSON, a foreign-key violation, and an invalid business value.
Use two database sessions to attempt a stale edit; verify one expected version update and atomic rollback of both business and outbox writes on injected failure.
Record EXPLAIN ANALYZE results and row counts before and after an index change; explain remaining consumer deduplication needs for outbox delivery.

What you will produce

Implement a PostgreSQL feature with tested transaction invariants and measured query behavior.

How the work is checked

SQL queries and constraints match the supplied expected results and invalid cases.
Two-session stale edits fail through explicit version checks.
Business and outbox records commit or roll back together.
Plan and timing comparisons report dataset size and avoid unsupported performance guarantees.

Six-hour guided local feature lab. An atomic outbox write does not deliver a message; a separate publisher needs retry and acknowledgement handling, and consumers need idempotency. Publisher implementation is outside this lab. Not complete isolation-level expertise, production tuning, or universal exactly-once messaging.

When another approach is better

Do not add indexes or database functions without a workload and maintenance rationale.
An outbox is unnecessary when no reliable external change publication is required; it does not remove consumer deduplication.

How we train

Ask about exercises, instructor feedback and the prior knowledge you need. Tool-specific courses marked provisional may become modules in a broader course.

Discuss your learning goals