Why must the expression behind a function-based index be deterministic, and what goes wrong if you index something whose result can change for the same stored row?
answer
- key computed once, trusted forever
- broken invariant → silent missed rows
- plan-dependent answers = the tell
- now(), session locale, other tables, random
- collation upgrade → rebuild text indexes
basics
~20 sThe result is computed once at write time and stored as the index key. If the expression's output later changes for the same row, the key no longer matches the row, so lookups silently miss rows or return wrong ones. Only deterministic, row-only expressions are safe.
solid answer
~50 sAn expression index stores the computed result as a key and does not recompute it on read — that is the whole point. So the engine relies on an invariant: for a given row, the expression always yields the same value until the row itself changes. Break that invariant and the index becomes silently inconsistent with the table. Lookups that should find a row miss it, because the search value is computed with today's behaviour while the stored key was computed with yesterday's. Nothing errors; queries just return incomplete results, and results differ depending on whether the plan used the index or scanned. Dangerous inputs: current time, session settings such as time zone or locale, collation-dependent comparisons that may change with a library upgrade, values read from other tables, random values, and user-defined functions that are not truly pure. Engines that let you declare a function's mutability will refuse to index the clearly-unsafe ones — but a function wrongly declared pure is accepted and silently rots the index.
go deeper
Know that the computed value is stored once, so the expression must always give the same answer for the same row.
Name concrete unsafe inputs — current time, session time zone or locale, other tables — and describe the missed-row symptom.
Emphasise silent, plan-dependent wrong answers, the falsely-declared-pure function hazard, and rebuilding text-expression indexes after collation changes.
Treat it as a durability-of-derived-data concern: anything materialised must depend only on inputs under version control, and the operational runbook must cover rebuild triggers.
## The invariant An expression index materialises the result of an expression as its key. At write time the engine computes the value once and stores it; at read time it compares the query's computed value against those stored keys. There is no re-derivation and no validation. That design is what makes the index fast — and it means the engine is trusting an invariant it cannot check: > For any given row, the expression yields the same value every time, and changes only when the row's own columns change. An expression satisfying this is variously called deterministic, immutable, or pure depending on the engine's vocabulary. The essential requirements are: depends only on its arguments, has no side effects, and gives identical output for identical input forever. ## What breaks When the invariant fails the index quietly diverges from the table. The failure is not a crash or an error — it is wrong answers: - **Missed rows.** A lookup computes the search value now, compares against keys computed earlier, finds no match, and returns nothing even though a matching row exists. - **Plan-dependent results.** The same query returns different rows depending on whether the optimiser chose the index or a scan. Two runs, two answers, no error — among the hardest classes of bug to diagnose, because the data looks correct when you inspect it directly. - **Constraint escape.** If the index is unique, entries computed under different behaviours no longer collide, so duplicates that the constraint was meant to forbid slip in — and remain after the underlying cause is fixed. ## The offenders **Current time.** Anything derived from "now" changes continuously. A key like "days since creation" is wrong the moment it is stored. If you need an age bucket, index the timestamp and compute the range in the query. **Session and environment settings.** Time zone, locale, numeric or date formatting, and search paths for resolving names are all per-session. The same expression evaluated by two sessions can produce different keys. **Collation and locale libraries.** String comparison and case conversion depend on collation rules supplied by the operating system's locale library. Upgrading that library can change the ordering or the case-folding of some characters, which invalidates ordered indexes over text — a real, documented operational hazard. Indexes over text expressions inherit it and must be rebuilt after such upgrades. **Data from elsewhere.** An expression that reads another table or another row is not a function of this row, so any change to that other data leaves stale keys. **Random or sequence-consuming functions.** Obviously non-repeatable. **User-defined functions.** The subtle one: many engines require you to declare a function's mutability, and a function *declared* pure but implemented with a lookup or a time dependency will be accepted and will corrupt the index. Wrongly declaring mutability to satisfy the index-creation check is a genuine production incident pattern. ## Operational consequences Because the corruption is silent, the discipline is preventive: - Treat the mutability declaration as a promise you must actually keep; review any function you mark as pure. - Prefer expressions built purely from the row's own columns and built-in deterministic functions. - Keep an inventory of indexes over text expressions and rebuild them after a locale or collation library upgrade, or after changing a column's collation. - If a computation is genuinely environment-dependent, move it out of the index: store the raw input, index that, and apply the environment-dependent part in the query where it is evaluated fresh every time. - After any suspicion of divergence, rebuild the index rather than reasoning about which entries are stale — you cannot tell by looking. ## The same rule for predicates A predicate-restricted index carries the same requirement for a different reason: membership in the index is decided by evaluating the predicate at write time. A predicate depending on the current time — "only rows from the last thirty days" — does not re-evaluate as time passes. Rows that were in the index yesterday are still in it, and the set it represents no longer means what the definition suggests. Engines generally reject clearly non-deterministic predicates, but the conceptual point matters: an index is a materialised decision, and materialised decisions must not depend on anything outside the row. ## How to answer State the invariant, state the failure mode as *silent wrong answers that depend on plan choice*, name three concrete offenders — current time, session locale or time zone, and functions falsely declared pure — and finish with the operational rule about rebuilding text-expression indexes after collation changes.
- How would you detect that an expression index has drifted out of sync with its table?Compare results of the same query forced down an index path and a sequential-scan path; a difference proves divergence. You can also recompute the expression for all rows and compare against the index contents, which is essentially what a rebuild does. Since the drift is invisible in the data itself, the practical response after any suspicion — a locale upgrade, a changed function — is to rebuild rather than to investigate.
- You need to query rows by their age in days. How do you index that safely?Do not index the age. Index the timestamp column itself, and express the query as a half-open range between two computed timestamps. The bounds are then evaluated fresh on every execution, while the index keys stay stable, which is both correct and sargable against an ordinary index.
saying these in an interview costs you the question
- Indexing an expression derived from the current time and expecting it to stay current
- Declaring a user-defined function immutable purely so the index creation succeeds
- Assuming the engine revalidates or recomputes index keys during reads
- Believing a locale or collation library upgrade cannot affect existing text indexes
- Expecting an error rather than silently missing rows when the invariant breaks