Free course

PostgreSQL Course

Learn PostgreSQL from installation to production basics: psql, roles, databases, schemas, types, indexes, application pools, migrations, backups, and debugging.

5 modules · 37 lessons · No signup

Prerequisites: SQL and Shell Commands

Download this course as PDF or EPUB

Read it offline, on your Kindle, or print it. Join my free newsletter and you get this course plus every other book and course I published.

Download PDFDownload EPUB
~~~

Your progress

of lessons completed.

Course completed.

What you'll learn

Build and operate a small PostgreSQL-backed application with clear roles, reliable migrations, bounded connections, useful indexes, and a restorable backup.

Course contents

  1. Start using PostgreSQL

    Install PostgreSQL, connect with psql, and navigate a server confidently.

    1. ✓ What PostgreSQL is
    2. ✓ Install PostgreSQL 18 on macOS
    3. ✓ Connect with psql
    4. ✓ Navigate psql
    5. ✓ List PostgreSQL databases
    6. ✓ Switch PostgreSQL databases
    7. ✓ Check your understanding: PostgreSQL basics

  2. Roles, databases, and schemas

    Understand PostgreSQL organization and grant deliberate access.

    1. ✓ Clusters, databases, and schemas
    2. ✓ Separate owner and runtime roles
    3. ✓ Create a PostgreSQL database
    4. ✓ Grant PostgreSQL permissions
    5. ✓ Schemas and search_path
    6. ✓ Check your understanding: roles, databases, and schemas

  3. Tables and data

    Use PostgreSQL types, identity columns, constraints, and transactions.

    1. ✓ Choose PostgreSQL data types
    2. ✓ Use identity primary keys
    3. ✓ Use constraints and foreign keys
    4. ✓ Understand sequences
    5. ✓ Use PostgreSQL transactions
    6. ✓ Check your understanding: tables and data

  4. Queries and performance

    Use PostgreSQL query features and inspect plans before adding indexes.

    1. ✓ Return changed rows
    2. ✓ Upsert with ON CONFLICT
    3. ✓ Use JSONB deliberately
    4. ✓ Read EXPLAIN ANALYZE
    5. ✓ Add the index the query needs
    6. ✓ Check your understanding: queries and performance

  5. Applications and operations

    Connect applications, control pools, migrate safely, restore backups, and debug failures.

    1. ✓ Connect to PostgreSQL from Node.js
    2. ✓ Budget connection pools
    3. ✓ Parameterize PostgreSQL queries
    4. ✓ Run an application transaction
    5. ✓ Migrate PostgreSQL safely
    6. ✓ Back up and restore PostgreSQL
    7. ✓ Fix a PostgreSQL connection failure
    8. ✓ Fix “relation does not exist”
    9. ✓ Choose where PostgreSQL runs
    10. ✓ Understand MVCC and routine maintenance
    11. ✓ Build a PostgreSQL notes application
    12. ✓ Check your understanding: applications and operations