A list endpoint using SELECT * slowed sharply after a large TEXT column was added — why?
answer
- the query did not change; the table did
- the star re-expands on every prepare
- bytes per row times rows per page
- sorts buffer the whole projected row
- big values often live outside the row
basics
~20 sThe star silently grew: the same query now projects kilobytes per row that nobody renders. Payload, server buffers and any buffered sort all widen at once, and engines that store oversized values apart from the row must now fetch them.
solid answer
~50 sNothing in the query changed — which is the point. `SELECT *` expands against the table as it is now, so the day the column landed, every row of every page started carrying the new value. Three things get worse together: the bytes serialised and pushed to the client scale with rows times average value size; anything the plan buffers, such as a sort feeding `ORDER BY ... FETCH FIRST`, now buffers wider rows and may spill to temporary storage; and engines commonly store oversized values away from the main row, so projecting the column adds a separate fetch and possibly decompression per row that not projecting it skipped entirely. The fix is to name the columns the list actually renders and fetch the large one only on the detail request — and the star is what let a schema change become a latency change.
go deeper
Recall that SELECT * includes columns added later, so a big new text column starts being sent on every row of every page even though no query was edited.
Explain the compounding effects: payload size, wider rows through any buffered sort, and the extra per-row fetch when large values are stored outside the main row.
Show the diagnosis — compare latency and payload with the column omitted, check the sort for temporary storage — and land on splitting list and detail projections rather than adding an index.
Own the prevention: how schema changes get reviewed against existing projections, and whether explicit column lists are enforced through convention, code review, or the data-access layer itself.
## Why an unchanged query got slower `SELECT *` is re-expanded every time the statement is prepared, against the table's current definition. So the deployment that ran `ALTER TABLE articles ADD COLUMN body text` also, invisibly, rewrote every star query against that table to project one more — very large — column. No code review saw a query change, because there wasn't one. This is the single most convincing story to have ready about the star, because it demonstrates the difference between a query being slow and a query becoming slow. ## Where the time actually goes **Serialisation and transfer.** A page of 100 rows whose new column averages 20 KB is roughly 2 MB of payload that previously was a few kilobytes. That cost is paid three times over: the server formats it, the network carries it, and the client driver allocates it. On a shared connection pool the transfer time is also connection-hold time, so concurrency drops even where per-request CPU is unchanged. **Buffered operators.** A list endpoint almost always sorts. If the sort cannot be satisfied by index order, the engine buffers the rows it is sorting — and it buffers the *projected* row, not just the sort key. Widening rows by 20 KB pushes that operator past its memory budget far sooner, at which point it starts writing to temporary storage. A query that sorted in memory yesterday can be doing disk-backed sorting today with no other change. **Out-of-line storage.** Engines commonly move oversized values out of the main row into separate storage, often compressed. The exact mechanism is engine-specific, but the consequence for the query author is uniform: a statement that does not project the column pays nothing for it, and a statement that does pays an extra fetch plus decompression per row. Adding the column to a projection is therefore not a small linear increase — it can introduce a per-row access the plan previously did not perform at all. **Access paths.** Whatever chance the query had of being served from an index without reading rows is now definitively gone. It was already gone under the star; the new column simply makes the row itself expensive to read. ## Confirming it rather than guessing The diagnosis is cheap because the hypothesis is cheap to test: 1. Compare timings for the same statement with an explicit list that excludes the new column. If the latency collapses, you are done. 2. Look at the response size, not just the response time — a latency change with a proportional payload change points at the projection rather than at the plan. 3. Look at the plan for the sort or hash step and check whether it now reports temporary storage use. Beware of concluding "the new column needs an index". Nothing here is a filtering problem: the predicate and the row count are unchanged. It is a bytes-per-row problem. ## The fix Name the columns the list renders: ```sql -- before: expands to include body SELECT * FROM articles WHERE status = 'PUBLISHED' ORDER BY published_at DESC FETCH FIRST 20 ROWS ONLY; -- after: the list screen never rendered body SELECT id, title, author_id, published_at FROM articles WHERE status = 'PUBLISHED' ORDER BY published_at DESC FETCH FIRST 20 ROWS ONLY; ``` If the list genuinely needs a preview, project a bounded expression rather than the whole value — `SUBSTRING(body FROM 1 FOR 200)` — so the payload is capped by the query rather than by whatever the largest document happens to be. And keep the full value on the detail request, where exactly one row is involved. ## The durable lesson The explicit list turns a schema change into a compile-time decision: someone must edit the query for the new column to appear, and that edit is reviewable. The star turns the same schema change into a production latency event discovered by a dashboard. That is the argument to make in an interview, because it explains why teams enforce explicit projections in application code even when the current tables are narrow — the rule is about what happens next year, not about today's row width. A related trap: an ORM or a view sitting between the application and the table can reintroduce the star even when the application code looks explicit. When you audit for this, audit what reaches the database, not what the application source appears to ask for.
- How would you confirm the new column is responsible before changing anything?Run the same statement with an explicit list that omits the column and compare both latency and payload size. A large drop in both points squarely at the projection. Also check whether the sort step now reports temporary storage use — wider rows pushing a sort past its memory budget is a common second-order effect.
- Would compression or out-of-line storage make this a non-issue?No. Storing large values apart from the row helps queries that do not project them — exactly the opposite of a star query. When you do project the column, you add a fetch and a decompression per row on top of transferring the expanded bytes. The storage design rewards a narrow projection; it does not rescue a wide one.
- The list needs a short preview of the text. What do you project?A bounded expression rather than the column: `SUBSTRING(body FROM 1 FOR 200) AS preview`. The payload is then capped by the query rather than by the largest stored document, and the full value stays on the single-row detail request.
Standing order for "one of each" from a supplier: the day they add a piano to the catalogue, your delivery van, not your order form, is what changes.
saying these in an interview costs you the question
- Suggests indexing the new TEXT column to fix latency
- Says nothing can have changed because the query is unchanged
- Assumes the database only sends columns the client reads
- Blames the optimizer or statistics rather than the projection
- Treats compression as making wide projections free