Backend interview: How do you choose PostgreSQL 18 virtual versus stored generated columns?

Prompt and scope

An order service has unitprice, quantity, and discount, and needs a netamount derived only from the same row. The interviewer asks whether to compute it on write or on every read, while the database must support indexes, logical replication, rollback, and an older subscriber. The core skill is deciding the persistence boundary, not recalling syntax.

What the interviewer evaluates

• Whether you distinguish VIRTUAL read-time computation from STORED write-time computation and storage cost.
• Whether you check that the expression uses only the current row, immutable functions, and supported types.
• Whether you know that virtual columns cannot use user-defined types or functions, while stored columns have fewer restrictions.
• Whether query heat, write volume, indexing, and replication topology drive the choice.
• Whether the PostgreSQL 18 publisher and older subscribers have an explicit compatibility and rollback path.

Questions to clarify first

• Is net_amount used for frequent filters, ordering, or uniqueness? An index usually favors materializing it on write.
• Is the workload read-heavy or write-heavy? A virtual column saves storage but computes on every read.
• Are logical-replication subscribers PostgreSQL 18? An older version does not copy generated columns during initial synchronization.
• Could the expression depend on a user function, external table, or current time? That changes determinism and support.

30-second answer framework

I would first fix the formula and consistency boundary. For a simple, infrequently read value that does not need a physical replica, I would use VIRTUAL and let PostgreSQL calculate it on read. If the value needs a stable index, lower read CPU, or must arrive already computed at a subscriber, I would use STORED. I would verify expression immutability and version support, test publisher, subscriber, indexes, and rollback, then compare read latency, write amplification, and replication behavior with production-shaped data.

Step-by-step reasoning

PostgreSQL 18 makes VIRTUAL the default generated-column kind: it occupies no row storage and is computed when read. STORED is computed on insert or update and occupies storage. Neither kind can be assigned directly in INSERT or UPDATE; the expression can reference only the current row and immutable functions.

The decision rule is to place cost where the workload is less sensitive. Frequent reads, indexes, or replicas that should consume the result favor STORED, trading space and write CPU for stable reads. Infrequent reads with a hot write path and a short expression favor VIRTUAL, trading storage and write work for read CPU. A virtual column is not a shared result cache merely because its name looks like one.

SQL and replication example

This example explicitly stores the amount; changing STORED to VIRTUAL makes reads evaluate the expression again:

CREATE TABLE order_line (
  id bigint PRIMARY KEY,
  unit_price numeric(12, 2) NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  discount numeric(5, 4) NOT NULL CHECK (discount BETWEEN 0 AND 1),
  net_amount numeric(12, 2)
    GENERATED ALWAYS AS (unit_price * quantity * (1 - discount)) STORED
);

CREATE INDEX order_line_net_amount_idx ON order_line (net_amount);

CREATE PUBLICATION order_pub
  FOR TABLE order_line
  WITH (publish_generated_columns = 'stored');

The publisher can opt to publish stored generated columns; a virtual column has no physical value to copy through the same path. If a subscriber is older than PostgreSQL 18, its initial synchronization does not copy generated columns even when the publisher enables the option, so the subscriber needs a recomputation or fallback plan.

Migration, indexes, and failure paths

Test both choices in a shadow table using the real data distribution. Compare write latency, read CPU, index size, and replica catch-up time. Introduce the new value as an ordinary dual-written column, reconcile it, and only then switch to a generated column; do not alter a large table during its busiest window.

For a mixed-version replication topology, record the publisher setting, subscriber version, and initial-sync state. If values diverge, pause downstream consumers that depend on the column and recompute from base columns instead of treating a replication gap as zero. Keep original columns and a formula version for rollback until sampled and full equality checks pass.

Common mistakes

• Treating VIRTUAL as a cache and forgetting that every read computes it.
• Assuming every expression may call current time, a subquery, or a user-defined function.
• Choosing a virtual column for an indexed path without verifying version and index support.
• Upgrading only the publisher and ignoring older-subscriber initial-sync behavior.
• Deleting source columns so replication and rollback can no longer recompute the value.

Follow-up questions

When would you prefer VIRTUAL?

Prefer it for a short expression, limited read frequency, hot writes, and no need for a physical index. Before rollout, use read CPU, tail latency, and concurrent-read tests to prove the storage saving does not become an unacceptable compute cost.

When is STORED required?

Use it when the value needs an index, uniqueness checks, stable replica reads, or a subscriber that cannot safely recompute. Include the value in write auditing and treat formula changes as data migrations.

How do you upgrade a mixed-version replication topology?

Inventory publisher and subscriber versions. Keep older subscribers on base-column recomputation or an ordinary replicated transition column; after upgrading and completing initial synchronization, enable publishgeneratedcolumns. Reconcile row counts, hashes, and sampled amounts, with a rollback path if any check fails.