Tune Concurrency Control on a Multi-Tenant OLTP Database
Overview
What this challenge is about.
Capture 6 hours of pg_locks + pg_stat_activity snapshots during peak. Identify the top 8 contention hotspots and classify each (row-level conflict, deadlock cycles, FOR UPDATE patterns, advisory lock misuse, long-running idle-in-transaction). Redesign each: convert from SERIALIZABLE to READ COMMITTED + advisory locks where safe, introduce SELECT FOR UPDATE SKIP LOCKED on worker queues, shorten or eliminate idle-in-transaction patterns. Validate on a 1-hour captured workload replay. Deliver a contention audit, the applied changes, a replay-validation report, and a 6-page concurrency-tuning memo.
The Brief
What you'll do, and what you'll demonstrate.
Audit and fix lock contention on a multi-tenant PostgreSQL 16 OLTP database and prove the win on a captured workload replay.
Earning criteria — what you'll demonstrate
- Diagnose lock contention from pg_locks + pg_stat_activity in production
- Choose the right isolation level + locking pattern per workload type
- Apply SKIP LOCKED, advisory locks, and idle-in-transaction killers correctly
- Validate concurrency-tuning changes on a real workload replay
Program Fit
Where this fits in your program.
Sharpens the same skills your degree expects you to demonstrate.
Skills
Skills you'll demonstrate.
Each one shows up on your verified credential.
Careers
Roles this prepares you for.
Real titles. Real skill bridges. Pick the one closest to your trajectory.
Career mappings coming soon.