Database Systems and PostgreSQL Internals

About this course

Design, diagnose and tune PostgreSQL systems using advanced SQL, indexes, query plans, transaction control, MVCC and routine maintenance.

This is an 8-week specialist track - our most in-depth program, taking you to a professional, hireable standard. Learn online at your own pace. Practice the concepts with the listed assessments.

What's included

• Self-paced lessons and practice (40 estimated hours).
• Hands-on coding quests you solve inside the EchoLens browser compiler - nothing to install.
• Gems, stages and a leaderboard that keep you moving instead of grade anxiety.
• A verified certificate with a scannable QR code, ready to share on LinkedIn, when you satisfy the course requirements.
• Completely free - no fee, just create an account and start.

What you will learn

Course outline - level by level

12 leveles, each with hands-on quests you clear in the portal.

• Level 1. Joins, Set Operations and NULL Semantics - Analogy: Matching records from separate archives to assemble one complete case file, while noting which folders had no match at all. Covered: Inner, left, right and full outer joins, self joins, lateral joins, UNION, INTERSECT and EXCEPT, and how NULL behaves in comparisons, aggregates and joins.
• Level 2. Window Functions - Analogy: Ranking staff within each department while everyone stays visible on the roster, rather than collapsing the roster into one line per department. Covered: OVER with PARTITION BY and ORDER BY, ROW NUMBER, RANK and DENSE RANK, LAG and LEAD, frame clauses, running totals, and window functions against grouped aggregates.
• Level 3. Common Table Expressions and Recursion - Analogy: Tracing an organisation chart from the chief executive down through every reporting line, level by level. Covered: Structuring queries with WITH, materialisation behaviour, recursive CTEs, cycle protection, and when a CTE hurts rather than helps.
• Level 4. Pages, Tuples and the Write Ahead Log - Analogy: A warehouse that stores everything in identical fixed size boxes on numbered shelves, and keeps a running logbook of every box touched. Covered: The 8 kB page layout, item pointers and tuple headers, TOAST for oversized values, the fill factor, the write ahead log and why it exists, and checkpoints.
• Level 5. B-tree and Composite Indexes - Analogy: A telephone directory sorted by surname and then by first name, which is useless for finding everyone called James unless you already know the surname. Covered: B-tree structure, index only scans and covering indexes with INCLUDE, composite column ordering rules, selectivity and cardinality, and the write and storage cost every index adds.
• Level 6. GIN, GiST, BRIN and Partial Indexes - Analogy: The keyword index at the back of a book, which works differently from the alphabetical contents at the front. Covered: GIN for JSONB, arrays and full text search, GiST for geometric and range types, BRIN for naturally ordered large tables, partial indexes, expression indexes, and index bloat.
• Level 7. Reading EXPLAIN ANALYZE - Analogy: An x ray that shows exactly which section of the pipe is narrowing the flow, rather than a guess based on where the noise is loudest. Covered: Plan node types, startup against total cost, estimated against actual rows, loops and how per loop timing is reported, buffer hits and reads, and the cost of ANALYZE itself.
• Level 8. Join Algorithms, Statistics and Memory - Analogy: Two ways to match two lists: check every pair by hand, or sort both piles first and walk them together. Covered: Nested loop, hash join and merge join and when each is chosen, the role of table statistics and ANALYZE, extended statistics for correlated columns, and tuning work mem and shared buffers.
• Level 9. Table Partitioning - Analogy: Filing tax records in one binder per year rather than stacking twenty years into a single crate. Covered: Declarative partitioning by range, list and hash, choosing a partition key, partition pruning, indexes on partitions, constraints and foreign key limits, and the maintenance cost of partitioning.
• Level 10. ACID and Isolation Levels - Analogy: A deposit box that either locks completely or stays open, with no state in between that a second person could exploit. Covered: Atomicity, consistency, isolation and durability in practice, read committed, repeatable read and serializable in PostgreSQL, dirty reads, non repeatable reads, phantom reads, and serialisation failures the application must retry.
• Level 11. Row Level Locking - Analogy: Placing a physical clip on a single ledger line while a transfer is posted, rather than closing the whole ledger. Covered: SELECT FOR UPDATE and FOR SHARE, SKIP LOCKED and NOWAIT, lock escalation behaviour, advisory locks, and preventing lost updates and oversold inventory.
• Level 12. MVCC, VACUUM and Deadlocks - Analogy: A city that keeps photographs of every previous version of a street while a night crew clears away the rubble from demolished buildings. Covered: Multiversion concurrency control, xmin and xmax, dead tuples and table bloat, VACUUM and autovacuum tuning, transaction identifier wraparound, and how PostgreSQL detects and resolves deadlocks.

Who it's for

Database Systems and PostgreSQL Internals suits learners at a intermediate to advanced level who want a practical, project-based route into Database Systems and PostgreSQL Internals. You need only a browser and an internet connection - all coding runs inside the EchoLens compiler, so there is nothing to set up.

Certificate

Pass every required assessment at its stated threshold to earn your verified certificate. Optional practice and watching videos do not determine eligibility. Anyone can scan its QR code to verify it on our site. You can add it to your CV or share it to LinkedIn in one click.