Generated Columns: Stored vs Virtual

~12 min · extensions, generated

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Two flavours of derived columns

A generated column derives its value from other columns in the same row. STORED generated columns are written to disk on every insert/update — they take space but can be indexed. VIRTUAL generated columns (PG 18+) are computed on read — no storage, always current, cannot be indexed.

Pick STORED if you'll index it

Indexing a virtual column is impossible (the value isn't stored). Indexing a stored column works exactly like indexing any other column. So the decision is usually: "will I query/filter/sort by this derived value?" — if yes, STORED; if no, VIRTUAL is cheaper.

Use cases that pay off

Slugs computed from titles.
Search-friendly normalised text (lowercased, accent-stripped).
Pre-computed totals (price × quantity, before-tax + tax).
tsvector for full-text search (always STORED + GIN).

Code

STORED generated column + index·sql

ALTER TABLE articles
ADD COLUMN slug TEXT GENERATED ALWAYS AS (
    LOWER(REGEXP_REPLACE(title, '[^a-zA-Z0-9]+', '-', 'g'))
) STORED;

CREATE INDEX articles_slug_idx ON articles (slug);

SELECT id FROM articles WHERE slug = 'pippa-quest-launch';

VIRTUAL generated column (PG 18+)·sql

ALTER TABLE products
ADD COLUMN price_with_tax NUMERIC GENERATED ALWAYS AS (
    price * (1 + tax_rate)
) VIRTUAL;

-- No storage cost; value always reflects current price/tax_rate.
SELECT id, price, tax_rate, price_with_tax FROM products LIMIT 5;

External links

PostgreSQL Generated Columns

Exercise

Add a STORED generated column for a value you currently compute in application code (slug, lowercased email, full name). Add an index that uses it. Compare query speed against the application-side approach.