Database Services for EdTech & Learning Platforms

Identifies root blocking PIDs, executing SQL statements, and contending lock types across assessment and gradebook tables without taking shared catalog locks.

-- 1. Isolate blocking PIDs and waiting assessment/gradebook transactions
SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS student_session_user,
       blocking_locks.pid    AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_statement,
       blocking_activity.query   AS blocking_statement,
       blocked_activity.wait_event_type,
       blocked_activity.wait_event
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 2. Cumulative deadlock and conflict telemetry for current DB
SELECT datname, deadlocks, conflicts, xact_commit, xact_rollback
FROM pg_stat_database
WHERE datname = current_database();

Calculates buffer cache hit ratios across student assessment tables and tracks submission query execution time to prevent disk I/O bottlenecks.

-- 1. Assessment submission table cache hit ratio (target > 99.5%)
SELECT relname AS assessment_table,
       heap_blks_read,
       heap_blks_hit,
       ROUND(
         heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 2
       ) AS buffer_hit_pct
FROM pg_statio_user_tables
WHERE relname IN ('assessment_submissions', 'student_answers', 'gradebook_entries', 'exam_sessions')
ORDER BY heap_blks_read DESC;

-- 2. Top slowest assessment write queries by mean latency
SELECT LEFT(query, 80) AS query_signature,
       calls,
       ROUND(mean_exec_time::numeric, 2) AS mean_latency_ms,
       ROUND(max_exec_time::numeric, 2) AS max_latency_ms,
       rows
FROM pg_stat_statements
WHERE query ILIKE '%INSERT INTO %' OR query ILIKE '%UPDATE %grade%'
ORDER BY mean_exec_time DESC
LIMIT 5;