Skip to content
LeadAfrik
Advanced · Self-paced2026 Edition

SQL Advanced — Systems and Optimisation

Systems-level SQL. Query execution and the planner, index strategy, recursive CTEs, advanced window-frame work, JSON / semi-structured data, materialised views and partitioning, concurrency and locking.

8

Modules

~7h 20m

Reading time

Advanced

Level

Self-paced

Format

§

Syllabus

  1. 01

    How a SQL engine executes a query

    Parse → plan → execute. Cost-based optimisation. Why stale statistics break good queries.

    ~50 minModule 01
    →
  2. 02

    Reading EXPLAIN ANALYZE

    Read plans top-to-bottom, spot the four canonical problems, use BUFFERS for the I/O picture.

    ~55 minModule 02
    →
  3. 03

    Indexing strategy

    B-tree, GIN, BRIN. Composite, partial, and covering indexes. The write-amplification tax.

    ~60 minModule 03
    →
  4. 04

    Recursive CTEs

    Anchor + recursive step. Hierarchies, graph traversal, generate-series, cycle prevention.

    ~55 minModule 04
    →
  5. 05

    Window-function mastery

    Complex partitioning, ROWS vs RANGE vs GROUPS frames, named WINDOW clauses, QUALIFY.

    ~55 minModule 05
    →
  6. 06

    JSON and semi-structured data

    Postgres ->/->>, containment with @>, GIN indexing, when to denormalise to columns.

    ~50 minModule 06
    →
  7. 07

    Materialised views and partitioning

    MV refresh strategies (manual/scheduled/concurrent/incremental), range/list/hash partitions, pruning.

    ~60 minModule 07
    →
  8. 08

    Concurrency, isolation, and locking

    ANSI isolation levels, optimistic vs pessimistic locking, FOR UPDATE SKIP LOCKED, deadlock avoidance.

    ~55 minModule 08
    →

How to use this course

Start with module 01 if the material is new; skip ahead if you have prior exposure. Each module is self-contained but the arc is sequential — the projects in the final module assume the toolkit from modules 1-11. Every module ends with key takeaways and a curated further-reading list with primary sources.