Team Plan — CP1 Rescue through CP5 (DBMS Project, Nov 15–26, 2026)

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.


0. Where you actually stand (audited Sat 15 Nov, ~1:30 pm IST)

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.


1. The road ahead — exact file list per checkpoint

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.


2. TODAY (Sat 15 Nov) — CP1 rescue checklist

  1. Adil: add Utkarsh, Renuka, Shubham as collaborators on both repos (Settings → Collaborators). The AP repo owner must do the same for the AP repo.

  2. 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.)

  3. 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.

  4. 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": ""
    }
    
  5. 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.)

  6. Push by this evening — the tracker re-reads GitHub every ~3 h; don't ride the Monday 23:59 wire.


3. CP2 — Baseline timings (due Tue 17 Nov)

DE repo

  1. On the benchmark laptop, from the repo root (with 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).
  2. Fill 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.

AP repo

  1. 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.
  2. 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).


4. CP3 — Optimization (due Thu 19 Nov) — the strategy, pre-analyzed

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).

4.1 The marked NULL bug — already found and proven on our data

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.

4.2 Index plan (→ 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

4.3 Proof + report (→ 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).


5. CP4 — Large dataset + final report (due Mon 23 Nov)

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:

  1. Results table (30 rows): optimized-on-seed vs optimized-on-large, growth ratio, target met? Targets: real-time (R01 R02 R03 R05 R15 R21 W01 W02 W03) < 5 ms · interactive (R04 R06 R07 R08 R09 R13 R14 R24 W05) < 250 ms · reports (R10–R12 R16–R20 R22 R23 R25) < 5 s · batch (W04) < 2 s. Any miss → read the plan, fix, show before/after.
  2. Growth analysis in three groups: ~flat (index lookups, log n), ~20× (reads a fixed share of the table), >20× (e.g. plans that degrade at scale — R23-class, or hash spills).
  3. What broke at scale + fixes with plans.
  4. Storage: DB size, table vs index size, seed vs large.
  5. Architecture for 100×:
  6. Conclusion: ready or not + remaining risks.

Also final-pass the DE README: team table filled, headline numbers (baseline total → optimized total → large totals), how-to-run section.


6. CP5 — Individual analysis, Wed 25–26 Nov, in class (40 marks)

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.


7. Commit uniformity — the protocol and the matrix

Mechanics (non-negotiable):

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.


8. Day-by-day calendar

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

9. What I can generate next (sandbox re-verified today)

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:

  1. CP2 DE: the actual run_benchmark.sh baseline run + summary.csv + all 30 raw plan files + a filled draft of docs/03_baseline_timings.md.
  2. CP2 AP: 02_er_diagram.png + 03_schema.sql from the tested normalized Nosh design.
  3. CP3 DE: complete 05_indexes.sql + 06_optimized_queries.sql (including the proven R13 fix and R23 keyset) + the same-results proofs + draft 04_optimization_report.md.
  4. CP4 DE: partitioning DDL with a real pruning demo, and the report skeleton with the target table pre-filled.
  5. CP3 AP: 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.