A whole-number column gains 200,000 absent values — under which designs does its per-row width change, and under which does it not?
answer
- where does absence live
- in the value, or beside it
- a borrowed marker forces a wider column
- a validity bit costs one bit per row
- per row, not per absent value
basics
~20 sIt depends where absence is recorded. Borrowing a marker from inside the number range forces the column to a representation wide enough to hold it; a validity bit beside the value leaves the declared width alone and costs roughly one bit per row.
solid answer
~50 sThere are two mechanisms in wide use. One borrows a marker from inside the value range — in practice the **floating-point absent marker**, a value from inside the number range that a design reuses to mean "nothing here". Absence then lives in the value itself, so a narrow whole-number column cannot express it and must move to a representation that can, usually a wider floating-point one. The other records absence in **a validity bit beside the value**: a parallel bitmap saying, one bit per row, whether that row has a value. The value buffer keeps its declared width and the column stays whole numbers with holes. On eight million rows a bitmap costs 1,000,000 bytes — about 1.6% against an 8-byte buffer and 12.5% against a 1-byte one. Both mechanisms charge per row, not per absent row, so 200,000 holes cost exactly what one hole costs.
go deeper
Recall that absence has to be recorded somewhere, and there are two places it can go: inside the value, or in a separate structure beside it. Which one decides whether the column gets wider.
Explain both mechanisms and derive the consequence rather than quoting it. Do the arithmetic for a mask — one bit per row — and contrast it against the cost of widening a narrow column.
When someone reports that a column silently changed representation, your first question should be where that design records absence. Being able to test it with one deliberate hole is what turns a mystery into a one-line answer.
On narrow columns this is a real budget line: a mask adds an eighth, a forced widening multiplies. Choosing representations that record absence beside the value is a standing decision worth making once for every table your teams publish.
## Two places absence can live Every design needs a way to record that a row has no value. Two mechanisms are in wide use, and they have opposite consequences for a column's width. - **A marker borrowed from inside the value range.** The design picks a value the number range already contains and declares that it means "nothing here" — in practice a marker from inside the floating-point range. Absence then lives *in the value itself*. - **A validity bit beside the value.** A second, parallel structure records one bit per row: present, or not. The value buffer carries no marker at all; the bit does the work. ## What each mechanism costs Under the borrowed-marker mechanism the column has to be able to *hold* the marker. A narrow whole-number representation cannot: the marker is not a whole number, and every bit pattern in a fixed-width whole-number column already stands for some number, so there is no spare pattern to steal. The column must therefore move to a representation that can hold the marker, which means a floating-point one, which on most designs is wider than the whole-number column you started from. The width change is not a policy choice; it is forced by where absence was put. Under the validity-bit mechanism nothing about the value buffer changes. A two-byte whole-number column stays two bytes a row and stays whole numbers, with holes. The cost is the mask: one bit per row. | Mechanism | Value buffer | Extra structure | Column stays whole numbers? | |---|---|---|---| | Marker borrowed from the number range | Widens to a representation that can hold the marker | None | No | | Validity bit beside the value | Unchanged | One bit per row | Yes | ## The arithmetic Take eight million rows: - A validity mask costs 8,000,000 bits, which is **1,000,000 bytes** — one megabyte. - Against an 8-byte value buffer (64,000,000 bytes) that is about **1.6%**. - Against a 1-byte value buffer (8,000,000 bytes) it is **12.5%** — the same mask, a much larger share, because the mask's cost is fixed per row while the value's is not. - A 2-byte column that instead widens to 8 bytes pays **four times its original buffer**: 16,000,000 bytes becomes 64,000,000. The two mechanisms are therefore not close. One adds a few percent on a wide column and an eighth on the narrowest; the other can multiply the column. ## Why the number of absent values does not enter it Both mechanisms charge per row, not per absent row: 1. A validity mask has one bit for every row, present or absent. One hole costs the same mask as two hundred thousand holes. 2. A borrowed marker occupies the same declared width as a real value — structurally it *is* a value. The widening happens because the column must be *able* to hold it, which is true from the first absent value onward. The practical consequence is that "we only have a few missing values, so it is cheap" is not an argument about bytes. One absent value in a narrow whole-number column under a borrowed-marker design costs the entire widening. ## Telling which mechanism you have - Put a single absent value into a narrow whole-number column and look at the column's representation afterwards. If it changed, absence lives in the value. If the column is still whole numbers, absence lives beside them. - Compare the column's reported bytes before and after. A jump proportional to a change of declared width points at the first mechanism; a jump of roughly an eighth of the row count in bytes points at a mask. - Note that one tool can do both, for different column representations, in the same table. "Which design am I in?" is a per-column question, not a per-product one. ## The claim that needs bounding "An absent value in a whole-number column forces it to a floating-point representation" is the sentence to be careful with. It is exactly true where absence is a borrowed value inside the number range, and exactly false where absence is a validity bit — in which case the buffer keeps its declared width and the column is still whole numbers. Both designs are in current use, so the interview-grade answer names the mechanism first and derives the consequence from it, rather than asserting the consequence as a law. One boundary on scope: everything above is about **where absence is recorded and what it costs in bytes**. What an absent value then does to a total, an average or a comparison is a separate matter with its own answer, and mixing the two is how a byte question turns into a correctness argument that answers neither.
- Does the cost of a validity bitmap depend on how many values are absent?No. The bitmap has one bit for every row whether that row holds a value or not, so its size is the row count divided by eight, rounded up, plus a small header. A column with one hole and a column that is almost entirely holes carry identical masks.
- What does absence cost in a column of text held as one reference per row?Usually nothing extra in width. The slot is already there, so a design can set aside one distinguished reference to mean absent and spend no additional bytes. Where the design uses a validity bitmap for every column instead, the text column pays the same bit per row as any other, independently of what the references hold.
- If a narrow column must widen to carry a borrowed marker, is the old buffer freed immediately?Not while the conversion is running. Changing a representation means writing new bytes, so the old buffer and the new one are both live for the duration of the step. That is a property of the conversion rather than of absence, but it is why a widening costs more in the moment than the difference between the two widths suggests.
saying these in an interview costs you the question
- Says an absent value always forces a whole-number column wider.
- Says absence never changes a column's declared width.
- Thinks the mask cost scales with the number of absent values.
- Treats a handful of missing values as automatically cheap in bytes.
- Cannot say whether their column records absence in the value or beside it.