PostgreSQL

What you'll learn

• Design PostgreSQL tables with appropriate types, keys and constraints, and handle NULLs correctly.
• Query data with joins, aggregation, CTEs and window functions.
• Store and query semi-structured data with JSONB, and write upserts with ON CONFLICT.
• Read EXPLAIN plans and choose B-tree, composite, partial and GIN indexes.
• Use transactions, isolation levels and row locks to keep data correct under concurrency.
• Secure and operate a database with roles, row-level security, backups and safe migrations.

Syllabus

PostgreSQL Foundations

1. What PostgreSQL Is
2. psql and Describing Tables
3. Choosing Data Types

Tables, Keys and Constraints

1. Constraints in Action
2. Identity Columns and RETURNING
3. NULL: Unknown, Not Empty

Querying Data

1. Filtering, Sorting and Limiting
2. Joins
3. Aggregation With GROUP BY and HAVING
4. CTEs and Subqueries

Window Functions, JSONB and Upserts

1. Window Functions
2. JSONB
3. Upserts With ON CONFLICT

EXPLAIN and Indexes

1. Reading EXPLAIN and B-Tree Indexes
2. Composite and Partial Indexes
3. GIN Indexes for JSONB, Arrays and Text Search

Transactions and Concurrency

1. Transactions and Savepoints
2. Isolation Levels
3. Row Locks and Lost Updates

Security and Operations

1. Roles and Privileges
2. Row-Level Security
3. Backup and Restore

Maintenance, Migrations and a Checklist

1. MVCC, VACUUM and Monitoring
2. Safe Schema Migrations
3. A PostgreSQL Review Checklist