Collegedunia Software Engineer Interview Guide

The 60-second brief

Collegedunia is an education platform in India. Candidates describe it as serving students and educational institutions, and the Software Engineer role as building and maintaining its web applications. The work is reported to range from backend services to full-stack features, in Java, Python or JavaScript, with React and Node.js named as example frameworks. It also covers keeping data correct and keeping the product stable as the user base grows.

This guide is for software engineers preparing for Collegedunia. Candidates describe the role as one that typically asks for around 2 to 4 years of development experience, so the guide assumes working knowledge of one mainstream language rather than a graduate syllabus. It covers the six questions candidates report (Java abstract classes and interfaces, polymorphism, SQL query tuning, reversing a linked list, merging two sorted arrays, binary search), the stages they describe, and how to rehearse the HR conversation. No named rounds have been reported, so the loop section is a single preparation-focus stage rather than a round-by-round script.

Candidates describe a coding assessment on data structures and algorithms, then one or more technical interviews with live coding and system design scenarios, then an HR interview about fit and expectations. Round names and exact order are not reported.

Focus areas

• Data Structures & Algorithms (DSA)
• Problem Solving (Logical Reasoning)
• Coding Test (written/onsite coding)

Overview

The reported questions split into two kinds. Three are short coding problems on fundamentals: reverse a linked list, merge two sorted arrays and implement binary search. Three are technical knowledge questions: abstract class versus interface in Java, polymorphism with examples, and how to optimize a SQL query. None is a large design problem, so the preparation that pays off is precision on small things. That means pointer handling on a list, loop bounds in a search, and exact Java semantics that you can explain with a short code example.

Candidates describe the stages as an initial coding assessment on algorithms and data structures, then technical interviews that mix coding with system design scenarios, then an HR interview. The topic breakdown candidates see lists data structures and algorithms, logical problem solving, a written or onsite coding test, linked lists and JavaScript. Take that as a signal to be able to write working code without an editor's help in your strongest language, and to be able to write plain JavaScript as well if the role you are interviewing for is full-stack.

The role as candidates describe it covers building web applications, fixing production issues, reviewing other people's code and working with product managers and designers. The stack named includes Java, Python, JavaScript, React and Node.js, plus SQL in the reported questions. Prepare each reported question so that you can both solve it and say how it shows up in day-to-day work, for example why a query is slow on a growing table or why a shared interface is easier to test than a concrete class.

No system design question has been reported by name, although candidates mention design scenarios in the technical interviews and the role description speaks of scaling for a growing user base. Reserve a short block of preparation for walking through a read-heavy web application with a relational database: where the cache sits, which columns are indexed, and what breaks first as traffic grows. Then spend most of your time on the six reported questions, because those are the ones with evidence behind them.

Where to focus your preparation

Candidates do not report named rounds, so this guide treats the loop as one preparation-focus stage that covers the progression they describe: coding assessment, technical interviews, HR interview.

Preparation focus

No named rounds have been reported, so this stage stands for the whole path candidates describe: a coding assessment on data structures and algorithms, one or more technical interviews that include live coding and system design scenarios, and a closing HR interview about fit and expectations. Candidates also mention a total span of roughly two to four weeks and a reply within a week of the first interview. Confirm the real sequence with your recruiter, then prepare against the question types the reported format covers. The coding stages are where the linked-list, sorted-array and binary search questions are practised, and they reward working code in one language with edge cases handled. The technical interviews are where the Java abstract class and interface question, the polymorphism question and the SQL optimization question are practised, and they reward short, concrete answers backed by an example or a query plan. For the HR stage, prepare a concise account of your experience and what you want from the role.

What to demonstrate

• Working code on linked lists, sorted arrays and binary search, including empty and single-element inputs
• Java object-oriented fundamentals: abstract class versus interface, and polymorphism with examples
• SQL tuning: reading a query plan, indexing and rewriting a slow query
• Clear communication of your reasoning, including comparing more than one solution

How to prepare

• Write reverse-a-linked-list iteratively and recursively, merge two sorted arrays with two pointers, and binary search with both a closed-interval and a half-open loop, all without an editor
• Prepare one short Java example for polymorphism (a base type with two overriding subclasses called through the base reference) and one for abstract class versus interface (state and constructors versus multiple implementation)
• Take one slow query on a table you know, run EXPLAIN, change an index or the query, and describe what the plan did before and after
• Ask the recruiter which languages and platform the coding assessment uses, how many technical interviews to expect, and whether they involve live coding

Where candidates lose points

Binary search that loops forever or skips the answer because of an off-by-one in the bounds

Pick one convention before you type, either lo and hi inclusive with a loop while lo <= hi, or a half-open interval, and keep it throughout. Compute the midpoint as lo + (hi - lo) / 2 and move a bound to mid + 1 or mid - 1 so the interval always shrinks. Trace an empty array, a one-element array, a target below the first element and a target above the last.

Reversing a linked list and losing the rest of the list by overwriting the next pointer too early, or returning the old head

Save the next node first, then point the current node back at the previous one, then advance both pointers. Return the previous pointer, which ends up as the new head. Say aloud that this runs in O(n) time and O(1) extra space, and test it on an empty list and on a single node.

Merging two sorted arrays by concatenating and calling sort, or forgetting the leftover tail of the longer array

Use one index per array and always take the smaller current element, which runs in O(m + n) time. After the loop, copy whatever remains in either input. Decide in advance which array wins ties and check duplicates, an empty input and arrays of very different lengths.

Answering the abstract class versus interface question with a memorised list that is out of date or has no example

Anchor the answer in what actually differs: an abstract class can hold instance fields and constructors and a class extends only one, while a class can implement many interfaces. Include that interfaces can carry default and static methods since Java 8. Close with a short example of when you would choose each, then link it to polymorphism by calling the shared type from a method that takes the abstraction.

Answering the SQL optimization question with 'add an index' and nothing else

Start from the query plan. Look for full scans, joins on unindexed columns, functions wrapped around indexed columns that prevent index use, SELECT * where few columns are needed, and sorting or paging that scans too many rows. Name the specific change you would try, say how you would confirm it with EXPLAIN, and mention the cost of an extra index on writes.

Questions & practice

Write a function to reverse a linked list.

Medium · Coding / Algorithms

Approach

1. Write the iterative three-pointer version: prev = null, curr = head; in the loop, save next = curr.next, set curr.next = prev, then move prev = curr and curr = next; return prev. O(n) time and O(1) extra space.
2. Explain why next must be saved first: once curr.next is overwritten, the rest of the list is unreachable.
3. Give the recursive version as an alternative: reverse the rest of the list, then set head.next.next = head and head.next = null. It is also O(n) time but uses O(n) call-stack space, so a very long list can overflow the stack in Java or JavaScript.
4. Trace the edge cases out loud: an empty list (return null), a single node, and two nodes. Check that the old head ends with next = null, otherwise the result contains a cycle.
5. For a doubly linked list, swap each node's prev and next pointers and return the last node visited as the new head.

Follow-up

• Reverse only the nodes between positions m and n in one pass.
• Reverse the list in groups of k nodes, leaving any leftover tail as it is.
• Check whether a linked list is a palindrome in O(1) extra space (find the middle with slow and fast pointers, then reverse the second half).

Given two sorted arrays, merge them into a single sorted array.

Medium · Coding / Algorithms

Approach

1. Mention the brute force first: concatenate the arrays and sort, O((m+n) log(m+n)). Then use the fact that both inputs are already sorted.
2. Two pointers: i over the first array, j over the second. Append the smaller of a[i] and b[j] and advance that pointer. When one array runs out, copy the rest of the other. O(m+n) time and O(m+n) space for the output.
3. Take from the first array on ties (a[i] <= b[j]) so the merge is stable. This is the same merge step that merge sort uses.
4. Know the in-place variant, where the first array has m+n slots with its last n empty. Fill from the back with k = m+n-1, comparing a[i] and b[j] from their ends, so nothing is overwritten before it is read. Afterwards only the leftover elements of the second array need copying.
5. Test the edges: one or both arrays empty, duplicates across the two arrays, every element of one array smaller than every element of the other, and negative numbers.

Follow-up

• Merge k sorted arrays efficiently (min-heap of k heads, O(N log k) for N total elements).
• Do the merge in place when the first array has buffer space at its end.
• How would you merge two sorted files too large to load into memory (stream both through buffered readers and write the output as you go)?

How would you implement a binary search algorithm?

Medium · Coding / Algorithms

Approach

1. State the precondition first: the array must be sorted or, more generally, the predicate must be monotonic. Then write the closed-interval version: lo = 0, hi = n-1, while lo <= hi, mid = lo + (hi - lo) / 2, compare, move lo = mid + 1 or hi = mid - 1, and return -1 if not found. O(log n) time and O(1) space.
2. Explain the midpoint: (lo + hi) / 2 can overflow int in Java on very large arrays, and lo + (hi - lo) / 2 (or (lo + hi) >>> 1) avoids that.
3. State the loop invariant: if the target exists, it is in [lo, hi]. Off-by-one bugs come from mixing a closed [lo, hi] interval with a half-open [lo, hi) one, so pick one convention and keep its update rules.
4. Give the lower-bound variant for duplicates or insertion points: half-open with lo = 0, hi = n, while lo < hi; if a[mid] < target then lo = mid + 1, else hi = mid; return lo. Java's Arrays.binarySearch returns -(insertionPoint) - 1 when the key is missing and does not guarantee which index it returns among duplicates.
5. Test the edges: an empty array, one element, a target below the minimum or above the maximum, and runs of duplicates. Mention binary search on the answer (for example the minimum capacity that ships all packages within D days) as the same technique applied to a monotonic predicate.

Follow-up

• Return the first and last positions of a target that appears several times.
• Search for a target in a sorted array that has been rotated at an unknown pivot.
• Which is preferable here, iterative or recursive binary search, and what does the recursive version cost in stack space?

How do you optimize a SQL query for better performance?

Medium · Technical / Domain Questions

Approach

1. Measure before changing anything. Run EXPLAIN (EXPLAIN ANALYZE in PostgreSQL, or EXPLAIN ANALYZE in MySQL 8.0.18+) and read the plan for full table scans, estimated rows far from actual rows, sorts or hashes spilling to disk, and nested loops over large inputs.
2. Index for the actual predicates. In a composite index, put equality columns first and the range or ORDER BY column after them (the leftmost-prefix rule). Make it a covering index when the query reads only a few columns, so it can skip the table lookups.
3. Keep predicates sargable. WHERE YEAR(created_at) = 2024, LOWER(email) = ?, a leading-wildcard LIKE '%term' and implicit type casts all stop a plain index from being used. Rewrite them as ranges (created_at >= '2024-01-01' AND created_at < '2025-01-01') or add an expression index.
4. Read less data. Select only the columns you need instead of SELECT *, filter before joining or aggregating, use EXISTS rather than a join plus DISTINCT for existence checks, replace deep OFFSET pagination with keyset pagination (WHERE id > last_id ORDER BY id LIMIT n), and remove N+1 query loops in application code.
5. Name the trade-offs. Every index slows inserts and updates and takes space. A low-selectivity index can be ignored by the planner. Stale statistics give bad plans (fix with ANALYZE). For read-heavy aggregates, a cache or materialised view may be the better fix.

Follow-up

• You added an index and the plan still shows a sequential scan. What are the possible reasons?
• What index would you create for WHERE city = ? AND fee < ? ORDER BY rating DESC, and in what column order?
• Page 500 of a listing query is slow while page 1 is fast. Why, and how do you fix it?

Write the update path that detects a concurrent edit

Medium · optimistic concurrency · transactional outbox · audit history

resource carries version INT NOT NULL DEFAULT 1. resource_revision holds revision_id, resource_id, version, actor_user_id, change_kind, patch JSONB, request_id, created_at with UNIQUE (resource_id, version). outbox_event holds aggregate_type, aggregate_id, aggregate_version, event_type, payload, status. A PUT carries the version the client read. Write the exact statements for the single transaction that applies the edit, records the revision and enqueues 'resource.updated', and give the handler's branch on zero affected rows. Then say what PostgreSQL 16 does under READ COMMITTED when two of these updates hit one row at once.

Approach

1. One transaction, three writes, no network call inside it: UPDATE resource SET title = $3, version = version + 1, updated_at = now() WHERE resource_id = $1 AND tenant_id = $4 AND version = $2; then INSERT the resource_revision row at version $2 + 1; then INSERT the outbox_event row at the same aggregate_version. The event goes to a table rather than a broker because no transaction spans both.
2. Branch on the affected-row count before doing anything else. Zero has three causes — stale version, wrong tenant, row gone — so re-read once and map to 409 carrying the current version, or 404 for an id outside the caller's tenant, which also stops the endpoint confirming that another tenant's id exists.
3. State the engine behaviour instead of assuming it. Under READ COMMITTED the second UPDATE blocks on the row lock, and when the first commits PostgreSQL re-evaluates the WHERE clause against the newly committed row, so the version predicate now fails and the statement reports zero rows. Under REPEATABLE READ the identical collision raises SQLSTATE 40001 instead, so the handler must fold both shapes into one conflict response.
4. Keep UNIQUE (resource_id, version) even though the predicate already serialises writers. It is what makes a lost update unwritable if any other path ever reaches the revision table, and it converts a logic bug into 23505 rather than into a silently missing history row.

Follow-up

• A client sends the version it read ten minutes ago and the resource has moved three versions. What is in your 409 so it can resolve the conflict without a full re-fetch?
• Two editors, two disjoint fields, no overlap. Does your answer still refuse the second write, and should it?

What is the difference between an abstract class and an interface in Java?

Medium · Technical / Domain Questions

Approach

1. Start with state. An abstract class can hold instance fields, constructors and methods of any visibility. An interface holds no instance state: its fields are implicitly public static final, and it has no constructors.
2. Cover inheritance. A class extends one abstract class but can implement many interfaces, so an interface is the way to give unrelated types a shared capability (Comparable, Runnable). An abstract class suits closely related types that share code and fields.
3. Give the version history. Since Java 8, interfaces can have default and static methods; since Java 9, private methods. Interface methods without a body are implicitly public abstract. Default methods let a library add a method to an interface without breaking existing implementors.
4. Give a concrete choice. I would make an abstract base class when subclasses share a template method and protected state, and an interface when callers only need a contract, for example a PaymentGateway that several unrelated classes implement and tests can mock.
5. End with a functional interface: an interface with exactly one abstract method can be the target of a lambda, and an abstract class cannot.

Follow-up

• If a class implements two interfaces that both declare the same default method, what does the compiler do and how do you resolve it (override it and call A.super.method())?
• An abstract class cannot be instantiated, so why can it have a constructor?
• Where do Java 17 sealed classes and interfaces fit in this comparison?

Explain the concept of polymorphism with examples.

Medium · Technical / Domain Questions

Approach

1. Define polymorphism as one interface with many implementations, then separate the two kinds. Compile-time polymorphism is method overloading: the compiler picks the method from the static types of the arguments. Runtime polymorphism is overriding: the JVM dispatches on the actual class of the object.
2. Use a runnable example. Shape declares area(); Circle and Rectangle override it; a loop over List<Shape> calls area() on each one and gets the subclass result, with no instanceof checks or switch on type.
3. Say why it matters. Adding a new Shape subclass needs no change to the calling loop (open/closed principle), and code can depend on an interface instead of a concrete class, which makes substitution and mocking in tests easy.
4. State the rules that catch people out. Static methods are hidden, not overridden. Fields are not polymorphic. Private and final methods cannot be overridden. An override cannot reduce visibility or throw broader checked exceptions, but it may return a covariant (narrower) type.
5. Mention the other forms if the stack allows: generics (List<T>) give parametric polymorphism, implemented in Java by type erasure. JavaScript and Python rely on duck typing, and JavaScript resolves methods through the prototype chain.

Follow-up

• With overloads foo(Object) and foo(String), which one does foo(null) call, and why?
• If a parent reference points to a child object and both declare a static method with the same signature, which method runs?
• How would you replace a long switch on an order type with polymorphism, and when is the switch the better choice?

One log partition stops advancing while the others drain

Easy · poison message · head-of-line blocking · consumer lag

Search results for a subset of tenants are hours stale; the rest are current. The projection consumer reports lag of zero on 15 of 16 partitions and 400,000 on one. Its error rate is flat and its CPU is idle. outbox_event has no pending rows older than a second, so the relay has published everything it holds. Identify the mechanism, give the ordered checks, and state what you do in the first ten minutes versus what you change permanently.

Approach

1. Read the lag distribution first. A slow consumer lags everywhere; zero on fifteen partitions and 400,000 on one is not throughput. Idle CPU on the stuck partition means the consumer is not advancing its offset at all, which points at one message it cannot get past rather than at a rate problem.
2. Exonerate the producer before touching the consumer. No pending outbox rows older than a second means the relay published, so the event exists in the log. This separates never sent from sent and never applied, which are different code paths and usually different owners.
3. Read the message at the stuck offset and the handler's log lines for its event_id. A flat error rate with no progress has two explanations and you must distinguish them: the handler is throwing and the retry loop is swallowing it, or the handler is blocking on something and never returning. Idle CPU with no error lines favours the second.
4. Mitigate before diagnosing further. Move the offending event to a dead-letter store and commit the offset past it. Adding consumers does nothing here, because a partition is consumed by exactly one member of the group, and the blast radius is every aggregate hashed to that partition, not only the aggregate that produced the bad event.

Follow-up

• The dead-lettered event carried aggregate_version 7 and the projection had applied 6. What must the replay do differently if 8 and 9 landed in the meantime?
• How do you show staleness to the user while the partition is behind, given the API already returns the projection's watermark?

Suggested practice checklist

The plan starts with the format and the smallest reported coding problem, moves through each reported question in turn, adds JavaScript and a short design walk-through for the full-stack side of the role, then ends with a mixed timed session and the HR conversation.

Day 1 — Confirm the format and lock down binary search

• Write the questions to send your recruiter: how many stages, which languages the coding assessment accepts, whether the technical interviews are live coding, and whether a system design scenario is included
• Choose the language you will code in and write binary search for a sorted array from memory, then adapt it to return the first occurrence of a duplicate value and the insertion point for a missing value
• Test your version on an empty array, a single element, a target below and above the range, and all-equal elements

Deliverable: A list of questions for the recruiter, plus a tested binary search function with the first-occurrence variant and the cases it passed.

Day 2 — Linked lists and merging sorted data

• Reverse a singly linked list iteratively, then recursively, and note the stack depth of the recursive version
• Solve two related list problems in the same session: find the middle node with slow and fast pointers, and merge two sorted linked lists
• Merge two sorted arrays into a new array with two pointers, then redo it in place when the first array has spare capacity at the end, filling from the back

Deliverable: Four working functions (reverse list, merge lists, merge arrays, in-place merge) with a one-line complexity note each.

Day 3 — Java object-oriented fundamentals

• Write a small example with a base abstract class, two subclasses and one interface, and call everything through the base reference to show polymorphism
• Write a comparison of abstract class versus interface covering state, constructors, single versus multiple inheritance and default methods, and add the case where you would choose each
• Separate method overriding from overloading in one example and explain which is resolved at compile time and which at run time

Deliverable: One compilable Java file with the example plus a half-page answer you can speak aloud for each of the two reported OOP questions.

Day 4 — SQL query optimization

• Create or pick a table with a few hundred thousand rows, write a slow query with a filter, a join and an ORDER BY, and read its EXPLAIN output
• Fix it three ways: add a suitable index, rewrite a predicate that wraps a column in a function, and replace SELECT * with the columns needed. Record the plan after each change
• Work the practice question on an update path that detects a concurrent edit, to practise reasoning about SQL beyond read performance

Deliverable: A before-and-after plan comparison for one query and a spoken checklist of the first five things you check on a slow query.

Day 5 — JavaScript and the web side of the role

• Write plain JavaScript for closures, promises with async and await, and array methods (map, filter, reduce), and explain the order of output for a short snippet that mixes timers and promises
• Build a small React component that fetches a list from a Node.js endpoint, shows loading and error states, and handles an empty result
• Sketch a read-heavy web application with a relational database: the main tables, which columns get indexes, where a cache would sit and what breaks first when traffic grows

Deliverable: A working fetch-and-render component, and a one-page design sketch with tables, indexes, cache location and the first bottleneck.

Day 6 — Timed coding session and a debugging drill

• Solve the linked-list reversal, sorted array merge and binary search problems back to back without notes, speaking your reasoning and edge cases out loud
• Repeat anything you got wrong or hesitated on, writing the specific mistake beside the corrected code
• Work the practice debugging question about a log partition that stops advancing, narrating how you narrow down the cause

Deliverable: Three timed solutions, a short list of the slips you made, and a written debugging sequence for the practice scenario.

Day 7 — HR conversation and full dry run

• Write a two-minute account of your experience that links your work to web applications, code review and collaboration with product and design
• Rehearse the practice answers on unblocking a colleague without taking over, arguing against a design and still committing, and estimating unfamiliar work
• Run one full mock: a coding problem, a Java or SQL knowledge question, then the HR conversation, and write down two questions to ask the team

Deliverable: A written two-minute introduction, three rehearsed stories and a completed mock with two questions for the team.

Working with product, designers and reviewers

No behavioral question has been reported by name. Candidates do describe a closing HR interview about fit and expectations, and the role includes code reviews, troubleshooting and work with product managers and designers. Prepare short, specific stories from your own work that cover those situations, and be ready to say what you want from the role.

1. Tell me about a technical issue you tracked down and fixed in a running application. How did you narrow it down, and what did you change so it would not recur?
2. Describe a code review where you disagreed with the reviewer or the author. How did the disagreement end, and what did you take from it?
3. Tell me about a feature you built with a product manager or designer. What changed between the first request and what shipped, and how did you handle it?
4. Tell me about a time you had to learn a language, framework or tool quickly to deliver something. How did you decide what to learn first?
5. What are you looking for in your next role, and what experience of yours is most relevant to a web application team?

Unblock an engineer without taking the keyboard

Easy · mentoring · leases · at-least-once

A teammate has spent two days on a job handler that occasionally writes duplicate rows. They are certain the queue is delivering twice by mistake. You suspect a lease expiring under a slow handler, so the job is running concurrently with itself. Describe how you have unblocked someone in this position: what you asked before offering a hypothesis, what you showed them rather than told them, and what you left them owning. Then say what you would do if their theory turned out to be the right one.

Approach

1. Ask before diagnosing, and ask for things answerable from data they already have: the attempt count on the job rows that produced duplicates, the handler's observed duration against its lease expiry, and whether the duplicate rows share a natural key that a unique constraint could have caught.
2. Teach the shape rather than the answer. A lease cannot distinguish a dead worker from a slow one, so a handler that outruns its lease is running twice by design, and deploys deliver the other half by killing handlers mid-run on every rollout. Both of their candidate theories produce identical duplicate rows, which is why the evidence has to come from timings rather than from argument.
3. Hand over a checklist they execute: a natural key on every write the handler performs so the second copy collides rather than appends, the record of intent written before any external effect, a lease heartbeat while running, and the metric that shows it working.
4. Keep ownership with them deliberately. Pair on the first write, then step back; if you finish it yourself you have closed one ticket and left the same person stuck on the next redelivery.

Follow-up

• How would you distinguish a genuine double-delivery from a lease expiry using only the data already stored?
• Their handler calls an external endpoint before recording that it did. What do you tell them to change first?

Argue against a design, lose, and commit anyway

Medium · disagreement · service boundaries · decision records

Describe a design you argued against and lost. State the failure you predicted as a named mechanism, not a feeling about complexity: two services that would need one transaction, a projection with no rebuild path, a write path with no idempotency key. Say what evidence you brought, what the decision maker weighed instead, and what you did after the decision was made: what you instrumented, what you wrote down, and whether the prediction came true. Five minutes.

Approach

1. State the prediction in falsifiable form up front: the mechanism, the condition that triggers it, and the observable outcome. A prediction that cannot be checked also cannot be credited to you later.
2. Show the evidence you had at the time and label each piece honestly as measured, analogous, or intuition. Keeping the intuition is fine; disguising it as data is the thing that erodes your standing in the next argument.
3. Represent the opposing case at full strength, including the constraint you did not control: a fixed date, a team boundary, or the fact that the decision was cheap to reverse and yours was not.
4. Make disagree-and-commit concrete. Name the artefact you left behind so the prediction could be settled without you: the alert and its threshold, the counter on the dashboard, the decision note that recorded the trade-off and the condition that would revisit it.

Follow-up

• What threshold on that alert would have proved you right, and did anyone ever look at it?
• If the same proposal arrived tomorrow with the same deadline, would you argue it the same way?

Estimate work you have never done and defend the range

Hard · estimation · backfills · expand-contract

You are asked to estimate a change you have never attempted: add a column to a 100-million-row table, populate it, move reads across, and drop the old shape. Give a range with the assumptions that generate it, including batch size, the signal your backfill throttles on, and wall-clock hours, and name the three unknowns that would move the number most. Then describe a real estimate you gave under comparable ignorance: how you expressed its uncertainty, what you committed to, and how wrong you turned out to be.

Approach

1. Decompose into independently deployable steps before estimating anything: add the column nullable, write both shapes, backfill in batches, verify, move reads, stop writing the old shape, drop it. That is four deploys spread over days, and the calendar estimate is dominated by them rather than by the loop's runtime.
2. Do the arithmetic aloud for the part that has arithmetic in it: batch size times number of batches times per-batch duration, at a write rate the primary can absorb alongside roughly 1.2k writes per second of production traffic. The loop is throttled by replication lag and lock waits, not by how fast it can issue statements.
3. Price the schema step by its lock rather than its statement duration. In PostgreSQL an ALTER TABLE taking ACCESS EXCLUSIVE waits for every open transaction on that table while later queries queue behind it, so a millisecond change issued during a thirty-second analytics query stalls that table for thirty seconds. Adding a nullable column with a non-volatile default avoids a rewrite from version 11; a new index wants CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block and leaves an invalid index behind if it fails.
4. Express the answer as a range whose endpoints each trace to a stated assumption, then name the cheapest experiment that collapses it, which is almost always running one real batch against the real table and multiplying.

Follow-up

• How do you verify the backfill genuinely finished, given rows written by production traffic while it ran?
• Where does the backfill resume from after a worker is killed mid-batch, and what makes that resume point trustworthy?