Virtual vs Stored Generated Columns in PostgreSQL 18

Compare virtual and stored definitions

create table order_lines (
  id bigint generated always as identity primary key,
  quantity integer not null check (quantity > 0),
  unit_price numeric(12, 2) not null,
  discount numeric(12, 2) not null default 0,
  subtotal numeric(12, 2) generated always as (
    quantity * unit_price
  ) virtual,
  total numeric(12, 2) generated always as (
    quantity * unit_price - discount
  ) stored
);

Virtual values are computed on read

A virtual generated column occupies no per-row storage for its value. PostgreSQL computes it when the column is read. That fits simple arithmetic, normalization, and presentation values that are not expensive to calculate.

PostgreSQL 18 restricts virtual expressions to built-in immutable functions and types. The expression cannot use user-defined functions or types, even indirectly through an operator or cast.

Stored values move work to writes

A stored generated column is calculated during INSERT or UPDATE and written with the row. Reads avoid recalculation, but writes and storage pay the cost. Stored expressions can use immutable user-defined functions and types that virtual columns cannot.

A generated column cannot be written directly and cannot refer to another generated column. It can only use the current row, so cross-table totals and time-dependent values need another design.

Indexes change the tradeoff

An index stores its own key values, so indexing an expression or generated value moves work into index maintenance. Compare a generated column plus index with a direct expression index; the simpler schema depends on whether queries also need to select the derived value.

Use EXPLAIN to verify that the query expression matches the indexed definition. Small differences in casts or functions can prevent the intended index from matching.

Index the derived total

create index order_lines_total_idx
  on order_lines (total);

explain
select id, total
from order_lines
where total >= 1000;

Check replication and privileges

PostgreSQL 18 can logically replicate stored generated columns. Virtual values are computed at the subscriber when read and are not published as stored column values.

Generated columns have separate column privileges. A role can be allowed to read the derived value without receiving permission to select every underlying column, but expression functions still execute with their own security rules.

Migrate without changing meaning

When replacing a trigger-maintained column, compare old and generated values for every row before dropping the trigger. Confirm rounding, null handling, collation, and casts. Numeric expressions deserve explicit types when financial behavior matters.

Sketch the revised table in the schema designer and keep the final GENERATED expression in a migration. Generated columns are a schema contract, not a substitute for documenting the business rule they encode.