Team: Adil Reyaz (ARRAEYY, lead) · Utkarsh Singh (utksh1) · Renuka Yadav (Renuka11aug) · Shubham (ShubhamManglaw)
Everything below is based on the live tracker + upstream CHECKPOINTS.md + project README.md
(fetched today) and a re-verification of our own loaded database (all 640k rows still in place,
30/30 workload queries known-good). Two alarms first.
| Item | Status | Risk |
|---|---|---|
DE repo ARRAEYY/nosh_DE_2026 |
3 commits, all by Adil (Nov 4 setup) | CP1 files exist only locally — not on GitHub |
AP repo ARRAEYY/nosh_AP_2026 |
COMPLETELY EMPTY — 0 commits | dbms/01_normalization.md missing → CP1 AP = not done |
| CP1 deadline | Mon 16 Nov 23:59 (tomorrow) | Tracker counts only what is on main; it re-reads GitHub every ~3 h |
| Commits per author | 1 of 4 members has commits | Tracker requirement: every member must have commits; counts shown per author |
team.json (DE) |
Wrong format (team:[...] instead of team_number/group_lead/members/...) |
Fix today, cheap |
| README team table | Names / Team No. / Section still blank | Fix today, cheap |
Everything needed for CP1 is already written and tested (see CP1_HANDOFF.md — schema runs clean,
zero rejected rows, counts match the brief). The only thing missing is pushing it, by 4 authors.
| CP | Due | DE repo must have on main |
AP repo must have on main |
|---|---|---|---|
| CP1 | Mon 16 Nov | docs/01_problem_understanding.md, docs/02_schema_design.md, sql/01_schema.sql, sql/02_load_data.sql, diagrams/er_diagram.png |
dbms/01_normalization.md |
| CP2 | Tue 17 Nov | docs/03_baseline_timings.md, benchmarks/results/baseline/summary.csv (+ raw plans) |
dbms/02_er_diagram.png, dbms/03_schema.sql |
| CP3 | Thu 19 Nov | sql/05_indexes.sql, sql/06_optimized_queries.sql, docs/04_optimization_report.md, benchmarks/results/optimized/summary.csv |
dbms/04_queries.sql |
| CP4 | Mon 23 Nov | docs/05_scalability_report.md, sql/08_partitioning.sql, benchmarks/results/large/summary.csv |
dbms/05_query_timings.md, dbms/plans/ (one file per query) |
| CP5 | Wed 25–26 Nov | In-class individual analysis — nothing to push, 40 marks depend on it | — |
Marks map (100): DE 80 = query execution 10 (Phases 1–3) + code optimization 30 (Phases 4–5) + individual analysis 40 (CP5). AP 20 = normalization 5 + ER 5 + queries 5 + timings 5. CP5 alone is worth more than everything done so far — plan section 7.
large_data.zip (≈20× rows: ~4 M watch events, ~1.14 M ad impressions, ~2.59 M subscriptions,
~600 k videos, ~600 k users) is released by faculty Thu 19 Nov — CP3 day. Expect a busy Thursday.
Adil: add Utkarsh, Renuka, Shubham as collaborators on both repos (Settings → Collaborators). The AP repo owner must do the same for the AP repo.
All four: clone both repos. In each clone set your own identity (this is what GitHub attributes commits by — the e-mail must match your GitHub account):
git config user.name "Utkarsh Singh" # your own name
git config user.email "utksh1@users.noreply.github.com" # or your real GitHub e-mail
(GitHub no-reply form: <userid>+<username>@users.noreply.github.com, visible under
Settings → Emails. Members with private e-mails MUST use the no-reply form or their commits
won't count for their account.)
Push CP1, split by author (files are in nosh_DE_2026_CP1/ and nosh_AP_2026_CP1/):
| Who | Repo | Commit (files) | Suggested message |
|---|---|---|---|
| Adil | DE | sql/01_schema.sql + sql/02_load_data.sql |
CP1: schema + load script (all seed rows load clean, 30/30 queries pass) |
| Utkarsh | DE | docs/01_problem_understanding.md |
CP1: problem understanding - stakeholders + classification P1-P9 |
| Renuka | DE | docs/02_schema_design.md |
CP1: schema design - data dictionary, FDs, normal forms, integrity checks |
| Shubham | DE | diagrams/er_diagram.png |
CP1: ER diagram (crow's foot) |
| Adil | DE | fixed team.json + README team table (see below) |
CP0 follow-up: team.json in required format, fill README team table |
| Adil | AP | dbms/01_normalization.md |
CP1: normalization - Nosh order+payment data 1NF->2NF->3NF->BCNF |
Each member runs git pull right before committing (avoid push races), then pushes their own
commit themselves.
Fix team.json — required format (paste, fill Team No./Section; postgres version: run
SELECT version(); on the benchmark laptop):
{
"team_number": "",
"group_lead": { "name": "Adil Reyaz", "enrollment_no": "2501010025", "github": "ARRAEYY" },
"members": [
{ "name": "Utkarsh Singh", "enrollment_no": "2501010488", "github": "utksh1" },
{ "name": "Renuka Yadav", "enrollment_no": "2501010373", "github": "Renuka11aug" },
{ "name": "Shubham", "enrollment_no": "2501010672", "github": "ShubhamManglaw" }
],
"postgres_version": "",
"laptop_used_for_benchmarks": ""
}
Designate the ONE benchmark laptop (Phases 3, 4 and 5 must all be measured on it —
brief rule). Recommended: Adil's, since his load already works. Write its name into
laptop_used_for_benchmarks. On it tonight: createdb youtube → 01_schema.sql →
02_load_data.sql → confirm the ten row counts. (~2 min, commands in CP1_HANDOFF.md.)
Push by this evening — the tracker re-reads GitHub every ~3 h; don't ride the Monday 23:59 wire.
seed_data/ unzipped):
./benchmarks/run_benchmark.sh youtube baseline
→ writes benchmarks/results/baseline/summary.csv (avg/median/min/max + scan nodes for all 30)
plus R01.txt … W05.txt (full EXPLAIN (ANALYZE, BUFFERS) plans, 3 runs each).
BEGIN … ROLLBACK automatically; data is unchanged.docs/03_baseline_timings.md: the 30-row table (Run 1/2/3 + Average per query, from the
raw plan files), the total row, then under the table for each query: scan type (from
summary.csv last column) + one line why it is slow/fast. Expect mostly Seq Scans — the
schema intentionally has only PK/UNIQUE indexes (Phase-2 rule), so that is correct and is the
whole point of the baseline.dbms/02_er_diagram.png — ER diagram of the Nosh order+payment tables from
01_normalization.md (entities, attributes, PK/FK, cardinalities). Same crow's-foot style as
the DE diagram.dbms/03_schema.sql — CREATE TABLE for those final tables with keys + constraints, must run
on PostgreSQL (add CREATE SCHEMA if we namespace it — keep it consistent with what the
normalization doc says).I can generate both from the already-tested normalized design on request (see §9).
Rules that matter: results must be identical (same rows; order only where ORDER BY exists),
no server-setting tricks, no caching outside the DB. Every index goes in sql/05_indexes.sql
with a comment (which queries, why this type/order), every rewrite in sql/06_optimized_queries.sql
(keep the -- @R01 markers, copy the file and change only what you rewrite).
The brief hides a query that returns a wrong answer because of NULL semantics. It is R13 (and it explains P5's complaint "the report always comes back empty"). Proven on our loaded DB:
channel 217's videos have 97 watch events, 24 of them with user_id IS NULL (logged-out)
R13 as written (NOT IN): 0 rows <- wrong, silently, every time
R13 fixed (NOT EXISTS): 88 rows <- the true answer
x NOT IN (subquery) can never be true when the subquery returns even one NULL. Fix in
06_optimized_queries.sql with NOT EXISTS (… WHERE … AND w.user_id = s.user_id), and put the
explanation + the proof counts in docs/04_optimization_report.md. This is explicitly worth marks.
sql/05_indexes.sql), query by query| Query | Baseline problem | Index / technique |
|---|---|---|
| R01 | Seq Scan + sort on videos | partial B-tree (channel_id, published_at DESC) WHERE visibility='PUBLIC', INCLUDE (title, duration_sec, view_count) for index-only |
| R02 | PK lookups | already Index Scan — none |
| R03 | subscriptions by user is PK-prefix; videos lookup per channel | shares R01's (channel_id, published_at DESC) index |
| R04 | ILIKE '%tutorial%' |
GIN USING gin (title gin_trgm_ops) + CREATE EXTENSION IF NOT EXISTS pg_trgm |
| R05 | Seq Scan on 200k watch events | B-tree (user_id, started_at DESC) INCLUDE (video_id, watch_seconds) |
| R06 | rare SCHEDULED rows | partial (published_at) WHERE visibility='SCHEDULED' |
| R07 | DATE(w.started_at) non-sargable |
rewrite to started_at >= '2026-06-01' AND started_at < '2026-07-01' + watch_events(video_id) |
| R08 | group by video | (video_id) INCLUDE (traffic_source, watch_seconds) covering |
| R09 | per-channel join | videos(channel_id) + the R08 covering index |
| R10 | time-range scan | BRIN (started_at) — file is stored in watch_id/time order |
| R11 | month aggregate + window | BRIN (started_at); honest: reads a fixed share of the table |
| R12 | group whole table by device | honest: no index helps, Seq Scan is optimal |
| R13 | wrong result (see 4.1) | rewrite NOT EXISTS + subscriptions(channel_id) + watch_events(video_id, user_id) |
| R14 | reactions lookup | video_reactions(video_id) INCLUDE (reaction) (PK is (user_id, video_id) — no help for by-video probes) |
| R15 | comments by video | partial (video_id, is_pinned DESC, like_count DESC) WHERE parent_comment_id IS NULL |
| R16 | ad impressions by month | BRIN or B-tree (shown_at) on ad_impressions |
| R17 | big join aggregate | videos(channel_id) + R08 index; partial on channels WHERE monetization_status='NOT_ELIGIBLE' optional |
| R18 | rare kids flag + EXISTS probe | partial videos WHERE is_made_for_kids + comments(video_id) |
| R19 | correlated subquery per channel | rewrite to LATERAL joins + (channel_id, uploaded_at DESC) |
| R20 | started_at::date non-sargable |
rewrite sargable range + BRIN (started_at) |
| R21 | PK order | already Index Scan — none |
| R22 | group whole videos table | honest: must read the table |
| R23 | OFFSET 30000 |
rewrite keyset: WHERE (created_at, comment_id) < (:last_seen…) ORDER BY created_at DESC, comment_id DESC LIMIT 25 + index (created_at DESC, comment_id DESC) |
| R24 | by channel + month | subscriptions(channel_id, subscribed_at) |
| R25 | joins by PKs, 181 rows out | nothing meaningful — joins already indexed |
| W01–W03 | single-row writes | no fix; these get measured slower — that's the write-overhead section |
| W04 | aggregate all subscriptions | subscriptions(channel_id) helps the GROUP BY |
| W05 | scheduled→public flip | served by R06's partial index |
docs/04_optimization_report.md)For every rewritten query prove identical results: EXCEPT in both directions + row counts
(R13 is the documented exception — results change because the original was wrong, show 0 → 88).
Then benchmark again: ./benchmarks/run_benchmark.sh youtube optimized 5 sql/06_optimized_queries.sql
(note: 5 runs this time per the README example — record which you used). Fill the 30-row
before/after table, add: top-5 before/after plans, write overhead (W01–W05 before vs after),
index storage (pg_relation_size of each index vs its table), and the honest "could not speed up"
list (R12, R22, big aggregates).
Thu 19 Nov: large_data.zip appears. On the benchmark laptop: createdb youtube_large →
01_schema.sql → load script with the folder changed to large_data/ (only change) → 05_indexes.sql
→ ANALYZE. (~20× rows ⇒ expect a few minutes of loading.) Then run only the optimized
workload: ./benchmarks/run_benchmark.sh youtube_large large 3 sql/06_optimized_queries.sql.
Do not run the original 30 on large data.
docs/05_scalability_report.md must contain:
watch_events (RANGE by started_at, monthly) → sql/08_partitioning.sql
as a demo table (keep the benchmark tables untouched); note the PK rule — the partition key
must be part of the primary key, so PK becomes (watch_id, started_at); include an EXPLAIN
showing partition pruning.watch_events (lower
autovacuum_vacuum_scale_factor, e.g. 0.02), DETACH/archive old partitions, refresh summary
tables CONCURRENTLY.Also final-pass the DE README: team table filled, headline numbers (baseline total → optimized total → large totals), how-to-run section.
Nothing to push — each member is questioned individually, likely against what they committed. So the commit split below is not bureaucracy, it is your study guide.
Who owns what (aligned with their commits):
| Member | Can explain (and will be asked about) |
|---|---|
| Adil | schema choices (types, composite PKs, FK delete policy), load order + zero-rejection story, 05_indexes.sql rationale, large-data benchmark |
| Utkarsh | P1–P9 problem mapping, baseline methodology (3 runs, avg, BUFFERS), optimization report numbers, AP query set |
| Renuka | data dictionary, FDs + why each table is 3NF/BCNF, baseline scan analysis, scalability report, AP timings |
| Shubham | both ER diagrams, the R13 NOT IN+NULL fix, R23 keyset rewrite, partitioning DDL + PK rule |
Rehearse Tuesday 24 Nov: each member re-runs their own parts live and answers mock questions ("why composite key (user_id, video_id) on reactions?", "prove R05 returns the same rows after the rewrite", "why must the partition key be in the PK?", "which index made W02 slower and why is that acceptable?"). Anyone who cannot answer about their own commits sinks 40 marks.
Mechanics (non-negotiable):
user.name/user.email (no-reply form if private). No --author spoofing — it breaks
attribution and looks bad at CP5.git pull → git add <your files> →
git commit → git push. Never one person pushing everyone's work.git shortlog -sne main and GitHub → Insights → Contributors.
Target: every member commits in every checkpoint (at least one repo), roughly equal totals
by CP4.Assignment matrix (suggested — swap freely, keep the balance):
| CP | DE repo | AP repo |
|---|---|---|
| CP1 (today) | Adil: sql/01+02 · Utkarsh: docs/01 · Renuka: docs/02 · Shubham: er_diagram · Adil: team.json+README | Adil: dbms/01_normalization.md (repo is empty — push today!) |
| CP2 (Tue 17) | Utkarsh: runs benchmark + results/baseline/ · Renuka: docs/03_baseline_timings.md |
Shubham: 02_er_diagram.png · Adil: 03_schema.sql |
| CP3 (Thu 19) | Adil: 05_indexes.sql · Shubham: 06_optimized_queries.sql · Utkarsh: docs/04_optimization_report.md · Renuka: runs benchmark + results/optimized/ |
Utkarsh: 04_queries.sql |
| CP4 (Mon 23) | Renuka: docs/05_scalability_report.md · Shubham: 08_partitioning.sql · Adil: runs large benchmark + results/large/ · Utkarsh: README headline numbers |
Renuka: 05_query_timings.md · Utkarsh or Renuka: plans/ |
Resulting totals (excluding Adil's 3 setup commits): DE = 4/4/4/3 and AP = 2/2/1/1
(Adil/Utkarsh/Renuka/Shubham, with plans/ going to Utkarsh) — every member commits in every
checkpoint in at least one repo, and every member has ≥1 AP commit. Swap owners freely, keep the
balance. Whoever commits a result file must be the one who ran (or re-verified) it.
| Date | What must happen |
|---|---|
| Sat 15 (today) | CP1 pushed to both repos by 4 authors · team.json fixed · collaborators added · benchmark laptop designated + load verified on it |
| Sun 16 | buffer: tracker shows CP1 green by evening; dry-run run_benchmark.sh |
| Mon 16 | CP1 deadline 23:59 |
| Tue 17 | CP2: baseline results + docs/03 + AP ER + AP schema pushed |
| Wed 18 | draft 05_indexes.sql + 06_optimized_queries.sql, prove same results (I can pre-build both, §9) |
| Thu 19 | CP3 pushed · large_data.zip drops → start the large load the same evening |
| Fri 20–Sun 22 | large benchmark, scalability report, partitioning demo, README final numbers |
| Mon 23 | CP4 final push (last commit on main before 23:59) |
| Tue 24 | CP5 rehearsal per §6 |
| Wed 25–Thu 26 | CP5 in class |
The database is still loaded and healthy (users 30,000 · watch_events 200,000 · videos 30,000 · ad_impressions 57,014 spot-checked), and the R13 proof above was run on it just now. On request I can produce, tested end-to-end like CP1:
run_benchmark.sh baseline run + summary.csv + all 30 raw plan files +
a filled draft of docs/03_baseline_timings.md.02_er_diagram.png + 03_schema.sql from the tested normalized Nosh design.05_indexes.sql + 06_optimized_queries.sql (including the proven R13 fix
and R23 keyset) + the same-results proofs + draft 04_optimization_report.md.04_queries.sql derived from the Inovix app's actual Prisma models (every app query,
-- READ/-- WRITE, R/W ids), ready for timing in CP4.Say which one to start with — given the deadline order, CP2 (1 + 2) is the natural next move.