AI curriculum
Course outline for PostgreSQL Development
Build and query a PostgreSQL application feature from a sound relational schema. Use constraints, transactions, functions, and JSON deliberately. Inspect actual plans for ordinary indexes, full-text search, and pgvector similarity queries. Compare exact neighbors with an approximate index without confusing distance with relevance. Finally record an offer change and its outbox event atomically without confusing persistence with delivery.
PostgreSQL Development Course Objectives
- Create normalized tables with appropriate keys, types, and constraints.
- Write checked queries using joins, grouping, subqueries, and window calculations.
- Apply transactions and demonstrate rollback and competing-update behavior.
- Select ordinary indexes and explain actual EXPLAIN ANALYZE observations.
- Implement PostgreSQL full-text queries and a matching GIN index.
- Insert compatible vectors and execute exact and HNSW queries in pgvector.
- Measure index recall and filtered results against independently computed exact neighbors.
- Implement a small SQL or PL/pgSQL function with explicit validation.
- Query and validate JSONB without abandoning useful relational constraints.
- Reject stale edits and commit business/outbox records atomically.
Pre-requisites
- Database Systems for AI Applications or equivalent tables, keys, query requirements, and transaction concepts.
- Basic programming or introductory SQL; core SQL receives guided practice.
- Use a terminal, files, and a test script.
- No PostgreSQL administration, vector mathematics, or embedding-model experience is required.
- Vector and AI Data Infrastructure is later specialist practice, not a prerequisite.
Before this course
Complete the required courses or bring equivalent experience. Recommended courses add useful depth; they are not enrolment gates.
Required preparation
Recommended preparation
Readiness checks
- Database Systems for AI Applications or equivalent tables, keys, query requirements, and transaction concepts.
- Basic programming or introductory SQL; core SQL receives guided practice.
- Use a terminal, files, and a test script.
- No PostgreSQL administration, vector mathematics, or embedding-model experience is required.
- Vector and AI Data Infrastructure is later specialist practice, not a prerequisite.
Database Systems for AI Applications or equivalent tables, keys, query requirements, and transaction concepts.
Find your place in the learning mapLab Setup
Delivery requirements: the environment, access, and exercise materials must be prepared and verified before delivery. This outline does not certify that a lab package is ready.
Disposable compatible stable PostgreSQL with pgvector, two psql sessions, migration/query files, tests, synthetic records, and provenance-labeled vectors. Materials include startup/reset scaffolding, fixture generators/manifests, an unindexed-reference loader, measurement helpers, development cases, and separate independent checks. Learners author schema/query changes, index definitions, search SQL, assertions, and explanations. First connection and extension activation stay inside guided hours. Delivery requires verified versions, packaging, extension privileges, platform resources, actual inspectable ordinary/HNSW plans, and cleanup. No production data, model account, GPU, broker, or dedicated vector database is required.
Detailed Course Outline
Module 1: Design records the database can protect (2 hours)
- PostgreSQL and psql foundations: PostgreSQL server, database, role, connection, and psql basics.
- Tables and types: Tables, types, identity keys, nulls, and required fields.
- Database constraints: Primary keys, foreign keys, uniqueness, and check constraints.
- Normalization boundaries: Normalization, relationships, and deliberate duplication boundaries.
- Schema migrations: Repeatable schema migrations and synthetic data loading.
- Data changes and rejection: Inserts, updates, deletes, and rejected invalid records.
- Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.
Practical outcome: Build book, seller, and offer tables that reject invalid business records.
Module 2: Ask questions without losing or multiplying records (2 hours)
- SELECT and nulls: SELECT, filtering, ordering, limits, and null behavior.
- Join cardinality: Inner and outer joins with explicit result cardinality.
- Aggregation and totals: Aggregation, GROUP BY, HAVING, and duplicate-sensitive totals.
- Subqueries and CTEs: Subqueries, common table expressions, and readable query stages.
- Window functions: Window functions for ranked offers and within-group comparisons.
- Independent query results: Learner-authored queries and independently checked result sets.
- Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.
Practical outcome: Answer offer and seller questions and explain one join that produces an incorrect total.
Module 3: Make related changes succeed or fail together (2 hours)
- Transaction boundaries: BEGIN, COMMIT, ROLLBACK, and transaction visibility.
- Atomicity and invariants: Atomicity, constraints, and application business invariants.
- Read Committed behavior: Read Committed behavior and concurrent application read-modify-write.
- Row locks and checked updates: Row locks, checked updates, and waiting observations.
- Two-session conflicts: Two-session conflict tests and bounded deadlock discussion.
- Failure-state checks: Failure injection and independent post-transaction state checks.
- Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.
Practical outcome: Execute competing changes in two sessions and explain the observed commit or rollback outcome.
Module 4: Read plans before changing performance (2 hours)
- Scans and statistics: Sequential, index, and bitmap scans with row estimates and statistics.
- B-tree query fit: B-tree indexes, compound keys, selectivity, and query fit.
- EXPLAIN and execution effects: EXPLAIN versus EXPLAIN ANALYZE and actual query effects.
- Controlled plan comparisons: ANALYZE, estimate error, buffers, and controlled workload comparisons.
- Index maintenance cost: Read benefit versus write, storage, and maintenance cost.
- Planner and timing limits: Small-table scans, index eligibility, warm caches, and timing limits.
- Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.
Practical outcome: Execute a before/after index comparison and explain actual scan choices for selective and broad queries.
Module 5: Search words with a declared language (2 hours)
- Text search needs: Exact identifiers, substring matching, and full-text search as different needs.
- Language and lexemes: Tokens, normalized lexemes, stop words, and explicit language configuration.
- Full-text SQL operators: to_tsvector, tsquery constructors, matching with @@, and null handling.
- Generated text and GIN: Stored generated tsvector columns and matching GIN indexes.
- Text relevance ranking: ts_rank_cd ordering, phrase behavior, and relevance limits.
- Search correction checks: Actual query plans, text corrections, and independently checked matches.
- Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.
Practical outcome: Implement indexed book-description search and explain language-dependent matches, ranking, and one missing identifier match.
Module 6: Compare exact neighbors with a vector index (2 hours)
- pgvector foundations: pgvector extension, vector columns, insertion, dimensions, and provenance.
- Distance operators and index classes: L2, cosine, and inner-product operators with matching operator classes.
- Exact nearest-neighbor SQL: Exact ORDER BY distance and LIMIT queries with checked identifiers.
- HNSW and IVFFlat trade-offs: HNSW graph versus IVFFlat lists, training, memory, and build trade-offs.
- Actual HNSW recall: Actual HNSW creation, plan inspection, and measured index recall.
- Filtered search and lifecycle: Filtered top-k shortfalls, search effort, updates, deletion, and exact fallback.
- Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.
Practical outcome: Run actual exact and HNSW SQL, compare filtered neighbors, and justify an index choice using recorded evidence.
Module 7: Put bounded logic and JSON at the right boundary (2 hours)
- SQL and PL/pgSQL functions: SQL and PL/pgSQL function basics with explicit return behavior.
- Function validation: Input validation, error handling, and transaction participation.
- JSON and JSONB: JSON versus JSONB and typed relational columns.
- JSONB queries: JSONB extraction, required fields, value types, and query predicates.
- Parameterized invoker calls: Parameterized calls and invoker permissions without privileged shortcuts.
- Independent function tests: Function and JSON tests for valid and rejected inputs.
- Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.
Practical outcome: Implement a validated offer function and query a bounded JSONB metadata field.
Module 8: Protect stale edits and record change intent (2 hours)
- Expected-version updates: Version columns, expected-version predicates, and affected-row checks.
- Competing edit checks: Competing edits without silent overwrite or application-only checks.
- Outbox identities: Outbox schema, stable event identity, and business-change linkage.
- Atomic business and outbox changes: Business and outbox insertion within one transaction.
- Rollback failure tests: Injected failure between writes and independently checked rollback.
- Delivery and deduplication boundaries: Publisher retries, acknowledgements, and consumer deduplication as boundaries.
- Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.
Practical outcome: Reject a stale edit and prove atomic business/outbox persistence without claiming event delivery.
Practical exercise
- Implement a books, sellers, and offers feature with migrations, fixture loading, SQL queries, constraints, and test scripts. Answer ranking and aggregate questions. Include an outer-join missing-match case and a duplicate-producing join trap. Write a small validated function and use JSONB only for flexible metadata. Reject malformed JSON input, incompatible types, invalid values, and broken keys.
- Run actual two-session transactions. Observe a waiting update and a stale edit. Use an expected-version condition and check affected rows. Independently inspect committed state after both sessions finish. Introduce a failure after a business change but before its outbox insert. Confirm rollback using a new session. Repeat with success and verify one linked business version and event identity.
- For ordinary indexing, run EXPLAIN and controlled EXPLAIN ANALYZE on a disposable workload before and after creating a learner-selected B-tree index. Compare a selective lookup and a broad scan using actual results and plans. Run ANALYZE. Record row count, data distribution, estimates, actual rows, loops, buffers, repeated timings, index size, and a matched insert/update batch with and without the index. Preserve equivalent data and record cache and measurement conditions. EXPLAIN ANALYZE executes its query. Use safe reads or disposable transactions. Cost units are not elapsed time. A created index need not be used by the planner. Use a scaled fixture and eligible predicates, not disabled sequential scans to claim a production speedup. A legitimate sequential scan is an observation.
- Extend book records with searchable descriptions. Learners create a stored generated tsvector with explicit 'english' configuration and coalesced text, then author tsquery construction, @@ matching, ts_rank_cd ordering, and a GIN index. Inspect token/lexeme output and compare 'english' with 'simple' on a small diagnostic query. Predict stop-word, inflection, phrase, null-description, and identifier behavior before execution. Use consistent document/query language in the actual search. Compare plans and matches with and without the GIN index. Do not claim GIN preorders every ranked result. Correct a description and verify changed matches through the generated column. Use exact keys for identifiers when tokenization loses required meaning. Rank is not a calibrated probability.
- Run CREATE EXTENSION vector in the disposable database. Learners create a keyed book-vector table with vector(n), insert numeric vectors, and author parameterized SQL with ascending ORDER BY on the distance operator and LIMIT. Begin with a few transparent, hand-authored nonzero vectors to explain distance. Then load a reproducible scaled numeric fixture for the index experiment. These are teaching vectors, not claimed live model embeddings or proof of semantic quality. Record the generator, seed, dimension, row IDs, checksum, and metric. If prepared embeddings replace the numeric fixture, provide source text, model revision, license, preprocessing, normalization, and query-vector provenance. Document and query vectors must share that contract. Equal dimensions alone do not establish compatibility. A model API call is not required in this course.
- Use L2 <-> with vector_l2_ops for the required HNSW experiment. Explain cosine <=> with vector_cosine_ops and negative inner product <#> with vector_ip_ops as alternatives, not interchangeable operators. Record normalization assumptions. Reject wrong dimensions and invalid fixture manifests. Explain NULL exclusion and why zero vectors are unsuitable for cosine indexing. Keep indexed vector dimensions within the verified type/index limits. Compare HNSW and IVFFlat mechanisms and resource trade-offs; only HNSW implementation is required.
- Compute exhaustive exact neighbors before creating HNSW, and retain an identical unindexed reference table for independent recomputation. Check both corpus hashes. Record exact plans and ordered identifiers, then create HNSW and run ANALYZE. Capture actual EXPLAIN ANALYZE results and returned IDs for both paths. Use eligible distance ordering, selective LIMIT, and a piloted fixture scale. Confirm the observed HNSW plan before calling a run approximate. If the planner chooses a sequential scan, report it and diagnose scale, statistics, or query eligibility. Do not force index usage and call that evidence of normal production behavior.
- Measure recall@k as the exact-neighbor IDs recovered divided by the exact target count. Use min(k, eligible rows) and report undefined recall when this is zero. Declare a tie policy and use tie-free test queries or an explicit tie-aware check. Keep query vectors, corpus, dimensions, metric, and filters identical between paths. Separate development queries from held-out index-recall queries. Compare two bounded hnsw.ef_search settings, repeated observations, build duration, index size, and write cost. Record misses and no improvement as valid results. Exact neighbors are index-recall ground truth, not human relevance ground truth.
- Apply a seller/category SQL restriction and compare with exhaustive filtered neighbors. Approximate scanning can yield fewer than k eligible rows even when more exist. Retain the SQL restriction in every path. Inspect the actual plan, increase bounded search effort, or fall back to exact filtered search. Explain version-specific iterative-scan limits without promising complete recall. Update a vector and delete a record, then verify current SQL results and the reference table after matching mutations. Vector indexes do not generate new embeddings when source text changes. Explain re-embedding responsibility, index maintenance, and retained-copy limits without implementing a generation service.
- Finish with a bounded comparison of separate lexical and vector ranked lists on the same book IDs. Inspect a literal-title question and a similarity question. Do not add incomparable rank and distance scores or promise hybrid improvement. End-to-end embedding generation, rank fusion, and retrieval-service lifecycle remain deeper work in Vector and AI Data Infrastructure. Dedicated vector database deployment is outside PostgreSQL Development.
- Complete a new SQL query, transaction fault, text-search case, and labeled vector query/filter case independently before opening feedback. Submit the independent work before opening the separate expected IDs and explanations. Learners author changes and run actual checks, then explain their observations. A supplied query plan, result list, or recall number cannot replace the operation. Human relevance labels, if used, stay separate from exact-neighbor labels and must not be produced by the distance scores under evaluation.
Delivery and timing
- Guided hours include teaching, demonstrations, learner practice, feedback, questions, and contingency as allocated above. Breaks, bulk downloads, and prerequisite setup are additional. Allocations need a timed learner pilot before delivery; they are not measured completion times.
What you will produce
Implement PostgreSQL data integrity, full-text and pgvector search, transactions, and recoverable change intent.
How the work is checked
- Schema constraints and learner-authored SQL match expected business results.
- Join cardinality and null handling do not corrupt aggregates.
- Actual two-session behavior is recorded and explained correctly.
- Functions and JSON validation reject the specified bad inputs.
- Ordinary index comparisons explain observed plans, work, write cost, and timing limits.
- Full-text SQL and GIN execute with checked language, phrase, rank, and correction behavior.
- Actual vector insertion and similarity SQL use compatible dimensions, provenance, and metrics.
- Exact and HNSW results include observed plans and independently computed recall.
- Filtered top-k shortfalls and exact fallback retain the stated restriction.
- Vector update and deletion checks distinguish index maintenance from re-embedding.
- Held-out learner operations precede feedback and supplied answers.
- Stale edits fail through a checked database update.
- Business and outbox state commit or roll back together independently of delivery.
16 guided hours. The full ten-hour PostgreSQL foundation and two-hour stale-edit/outbox work remain, with separate two-hour full-text and pgvector modules. Actual PostgreSQL, GIN, and HNSW operation is required; IVFFlat is comparison-only. Controlled supplied vectors avoid an embedding-model account requirement and do not establish semantic relevance. Deeper retrieval integration is a later course, not a prerequisite. No advanced RAG, model training, production tuning, every isolation level, or exactly-once messaging claim is made. Index choice needs a workload and maintenance reason.
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
Contact us for full course details, including duration, delivery options and lab requirements.
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 goalsRelated Courses
These courses list this course as preparation.