Keep a shared derivation close to the data

Repeating a line-total formula across reports, APIs and an administration interface can introduce inconsistent results. Generated columns express a derivation in the database model. PostgreSQL 18 supports virtual generated columns calculated when read. This is a capability review of version 18, not a claim that the version was released today.

STORED persists the computed result during writes; VIRTUAL computes it during reads. An inexpensive expression on rarely read rows differs from a costly expression in a frequently executed report. PostgreSQL 18 defaults to virtual generation, but explicit syntax makes the intended decision visible.

Choose where computation belongs

Generation expressions have restrictions, including immutable-function requirements. They cannot become arbitrary cross-table lookups. Virtual columns also impose limitations involving user-defined types and functions. Read the target-version documentation before treating the feature as a general calculation engine.

The sample derives a simple amount from integer quantity and numeric unit price. It does not preserve historical transaction terms: changing base inputs changes the derived result. A finalized invoice may need independently recorded price, tax and discount facts. Distinguish a reproducible derivation from a historical business record.

Measure representative read and write paths before migrating. Reduced storage does not automatically improve overall performance. Review ORM mappings, exports and replication with the exact versions in use. The example’s temporary table exists only in its database session.

Code example and verification

The notes state the prerequisites and expected outcome. Run the example in a test environment, then adapt it to your project’s data and constraints.

1. PostgreSQL 18 SQL; session-local temporary table

BEGIN;
CREATE TEMP TABLE demo_line_items (
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
    total numeric GENERATED ALWAYS AS
        (quantity * unit_price) VIRTUAL
);
INSERT INTO demo_line_items (quantity, unit_price)
VALUES (3, 12.50);
SELECT quantity, unit_price, total FROM demo_line_items;
ROLLBACK;

On PostgreSQL 18 expect total 37.50. ROLLBACK discards the demonstration changes. Older versions may reject VIRTUAL syntax. Tax, discounts and currency are outside the example.

2. Compare VIRTUAL and STORED after updating base data

This disposable session creates both column kinds from the same expression and inputs. INSERT supplies base values rather than writing a generated result directly.

-- PostgreSQL 18; disposable session.
BEGIN;
CREATE TEMP TABLE generated_price_demo (
    id integer PRIMARY KEY,
    quantity integer NOT NULL CHECK(quantity > 0),
    unit_price numeric(12,2) NOT NULL CHECK(unit_price >= 0),
    virtual_total numeric GENERATED ALWAYS AS
        (quantity * unit_price) VIRTUAL,
    stored_total numeric GENERATED ALWAYS AS
        (quantity * unit_price) STORED
);
INSERT INTO generated_price_demo(id,quantity,unit_price)
VALUES(1,3,12.50);
SELECT id,virtual_total,stored_total FROM generated_price_demo;
UPDATE generated_price_demo SET quantity=4 WHERE id=1;
SELECT id,virtual_total,stored_total FROM generated_price_demo;
ROLLBACK;

Both columns should initially show 37.50, then 50.00. Equal results do not imply equal computation costs. ROLLBACK discards this example without altering a business table.

3. Inspect metadata and verify invalid-input boundaries

Inspect metadata so migrations and ORM mappings can recognize a generated, non-ordinary writable column. The nested block then catches the expected constraint failure without leaving the test session unexplained.

-- PostgreSQL 18.
BEGIN;
CREATE TEMP TABLE generated_validation_demo (
    quantity integer NOT NULL CHECK(quantity > 0),
    price numeric(12,2) NOT NULL CHECK(price >= 0),
    total numeric GENERATED ALWAYS AS (quantity * price) VIRTUAL
);
SELECT column_name,is_generated,generation_expression
FROM information_schema.columns
WHERE table_name='generated_validation_demo'
  AND table_schema LIKE 'pg_temp_%'
ORDER BY ordinal_position;

DO $$
BEGIN
    BEGIN
        INSERT INTO generated_validation_demo(quantity,price) VALUES(0,10);
        RAISE EXCEPTION 'Expected a check violation';
    EXCEPTION WHEN check_violation THEN
        RAISE NOTICE 'Invalid quantity rejected as expected';
    END;
END $$;
SELECT count(*) AS rows_after_rejected_insert
FROM generated_validation_demo;
ROLLBACK;

Expect the rejection NOTICE and a final row count of zero. Removing the constraint makes the explicit exception fail the check. Business validation in CHECK is separate from the generated-column storage choice.

4. Inspect read paths with EXPLAIN on controlled data

This exercise inspects plans and result equality. Both columns occupy the same table, so it cannot establish the storage difference between separate designs. Production comparisons need separate schemas and representative writes and reads.

-- PostgreSQL 18; 10,000 synthetic rows, no performance promise.
BEGIN;
CREATE TEMP TABLE generated_read_demo (
    quantity integer NOT NULL,
    price numeric NOT NULL,
    virtual_total numeric GENERATED ALWAYS AS (quantity * price) VIRTUAL,
    stored_total numeric GENERATED ALWAYS AS (quantity * price) STORED
);
INSERT INTO generated_read_demo(quantity,price)
SELECT (n % 5) + 1, (n % 100) + 0.50
FROM generate_series(1,10000) AS numbers(n);
ANALYZE generated_read_demo;
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(virtual_total) FROM generated_read_demo;
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(stored_total) FROM generated_read_demo;
SELECT sum(virtual_total)=sum(stored_total) AS equal_results
FROM generated_read_demo;
ROLLBACK;

equal_results should be true. EXPLAIN ANALYZE actually executes the statement; do not apply it casually to unknown writes on production data. Execution order and cache state can affect timing, so one faster run is not an architecture decision.

Migrate against an actual workload

Choose a simple existing formula, compare results and define a performance target and rollback approach. Students can compare stored and virtual designs on equal inputs, while keeping conclusions limited to their measurements. Workload evidence and data meaning should drive adoption.

Implementation checklist

  • Verify the server version and expression restrictions.
  • Choose VIRTUAL or STORED explicitly.
  • Separate derivations from historical transaction facts.
  • Check ORM and export compatibility.

References

Editorial guidance: Liyan Knowledge.