PG
PostgreSQL — Advanced
Run PostgreSQL like a professional: tune queries with the right index types, handle concurrency and locking safely, automate with triggers, add full-text search, partition large tables, lock data down with roles and row-level security, and operate databases in production. Uses the school database from the Intermediate track.
Lessons
- 1Query Tuning & Index TypesEXPLAIN (ANALYZE, BUFFERS), partial, expression, covering and BRIN indexes.
- 2Concurrency, MVCC & LockingHow PostgreSQL handles simultaneous users, lost updates, row locks and SKIP LOCKED queues.
- 3Writable CTEs & LATERAL JoinsChain data changes in one statement and run a subquery once per row.
- 4Triggers & Audit LogsRun functions automatically on INSERT, UPDATE or DELETE — timestamps and audit trails.
- 5Full-Text SearchSearch documents by meaning with tsvector, tsquery, ranking, highlighting and GIN.
- 6Table PartitioningSplit huge tables by range so queries and maintenance touch only the data they need.
- 7Roles, Privileges & Row-Level SecurityLeast-privilege access with roles and GRANT, and per-user row filtering with RLS.
- 8Backups, Maintenance & Monitoringpg_dump and restore, migrations, VACUUM, connection pooling and health queries.
- 9Schema Migrations Without DowntimeChange a live database safely: versioned migrations, lock timeouts and the expand-and-contract pattern.