PostgreSQL / Intermediate: concurrency and maintainable SQL

PostgreSQL โ€” Intermediate: concurrency and maintainable SQL

Coordinate snapshots, indexes, analytic queries, privileges and version cleanup.

6 lessons ยท No account required

What you will learn

  • Reason about isolation, plans and locking failures.
  • Design query structure, role scope and maintenance evidence.

  1. Transactions, MVCC and isolation

    Reason about committed versions and atomic business work. Use PostgreSQLโ€™s READ COMMITTED default deliberately and distinguish snapshot stability from serializable correctness.

    Start learning

  2. Indexes and EXPLAIN plans

    Choose a PostgreSQL index for a concrete lookup, then read estimates and measured execution as different evidence. Balance read benefits with write and maintenance cost.

    Start learning

  3. Views, CTEs and window queries

    Structure a report into understandable relations and add per-row analytics without losing detail. Choose explicit scope, tie and frame rules.

    Start learning

  4. Roles, authentication and schema privileges

    Separate login identity, database access and object privileges. Design a constrained report role without weakening PostgreSQL authentication or trusting writable schemas.

    Start learning

  5. VACUUM, autovacuum and planner statistics

    Explain why version cleanup and statistics maintenance are normal PostgreSQL work. Diagnose cleanup delays without disabling safeguards or treating VACUUM FULL as routine.

    Start learning

  6. Locks, deadlocks and aborted transactions

    Coordinate two writers and recover from errors without partial business work. Distinguish waiting, deadlock, serialization failure and the PostgreSQL aborted-transaction state.

    Start learning
Back to course