Prompt and context
This question does not compare every generated-column option. It focuses on migrating a repeated row expression to a PostgreSQL 18 virtual generated column. The goal is one calculation contract without a stored-column backfill, while controlling read CPU, expression restrictions, and privilege changes.
What the interviewer evaluates
- Knowing that PostgreSQL 18 introduced virtual generated columns and made them the default.
- Proving the expression uses only the current row and immutable built-in functions and types.
- Validating with dual reads and production query plans, not just successful DDL.
- Understanding that virtual values are computed on read, occupy no row storage, and cannot be partition keys.
Clarifications before answering
Confirm that the expression uses only built-ins, identify reads and filters that reference it, check for an existing expression index, and ask whether the application can compare the old expression with the new column temporarily. Review roles too, because generated and base columns have separate privileges. If the generated column is meant to isolate the base column, verify that every function, operator, and cast in the expression satisfies the LEAKPROOF requirement.
A 30-second answer framework
I would first prove that the expression satisfies PostgreSQL 18 virtual-column restrictions, then add an explicitly VIRTUAL column. During migration, the application shadow-reads the old expression and new column across nulls, Unicode, unusual inputs, and historical partitions. I would compare CPU, tail latency, and plans for representative queries. After semantic, performance, and privilege gates pass, reads switch to the new column while retaining a fast rollback to the old expression.
Step-by-step deep dive
ALTER TABLE report_events
ADD COLUMN normalized_country text
GENERATED ALWAYS AS (lower(trim(country_code))) VIRTUAL;A virtual value is computed when read and occupies no row storage. Its expression can reference only the current row, cannot contain subqueries or another generated column, and must use immutable functions. A virtual column also cannot depend on user-defined functions or types. Writing VIRTUAL explicitly prevents the migration's intent from depending on PostgreSQL 18's default.
Scan historical data and compare normalized_country IS NOT DISTINCT FROM lower(trim(country_code)), including NULL, whitespace, case, and non-ASCII inputs. Then run representative EXPLAIN (ANALYZE, BUFFERS) checks. Read-time computation may raise CPU; if filters need an index, verify the exact supported index path and write cost rather than assuming no storage means no cost.
Release in stages: add the column; shadow-read both forms while the old expression remains authoritative; switch only after zero differences and acceptable performance. Rollback restores the old expression. Finally, test base and generated-column privileges with the real application roles. If any function, operator, or cast cannot be proved LEAKPROOF, generated-column privileges do not form a complete security boundary around the base column.
Strong sample answer
I would treat this as a query-contract migration. After proving the expression uses only current-row immutable built-ins, I would add an explicit virtual column. It avoids physical backfill but moves work to reads, so production-shaped CPU and tail-latency tests are mandatory.
The application first compares old and new results and groups discrepancies by input class. It switches only after semantic, plan, and privilege gates pass. If behavior regresses, the base data remains intact and queries return to the old expression immediately.
Common mistakes
- Omitting
VIRTUALand relying on a version-specific default to explain intent. - Discovering volatile functions, subqueries, or user-defined types only during DDL.
- Testing ordinary ASCII values while missing
NULLand Unicode behavior. - Treating no row storage as no query CPU.
- Removing the old expression at cutover and losing a quick rollback.
Follow-up questions
Why not use STORED here?
The task targets a cheap read-time normalization and wants to avoid physical backfill. If production scans, sorts, or filters make read CPU unacceptable, a stored column becomes a separate storage-and-replication decision.
Can the virtual column be a partition key?
No. PostgreSQL 18 does not allow a generated column as a partition key. Use a regular column maintained by the write path when partition routing needs the value.
How do you test that privileges did not expand?
Query the base and generated columns with actual application roles, checking column grants and execution privileges for functions in the expression. When the generated column is intended to hide the base column, also inspect whether every function, including functions behind operators and casts, is marked LEAKPROOF; PostgreSQL does not enforce this condition for the application. If any expression path cannot be proved leakproof, do not treat the generated-column grant as complete isolation.
When can dual reads end?
After a full business cycle, historical partitions, and peak load have passed with zero semantic differences and acceptable performance and privilege results.