What is the difference between STORED and VIRTUAL generated columns in PostgreSQL?

A STORED generated column computes its value on every INSERT or UPDATE and writes it to disk, so it occupies storage and can be indexed. A VIRTUAL generated column stores nothing and recomputes at read time, which makes writes cheaper but means it cannot be indexed as of PostgreSQL 18. STORED was the only option before PostgreSQL 18, which introduced VIRTUAL as the new default.

Can you create an index on a PostgreSQL generated column?

You can index a STORED generated column exactly like a normal column, including b-tree and GIN indexes. You cannot index a VIRTUAL generated column, because its value is not stored anywhere for the index to point at. If you plan to filter, sort, or search on a derived value, define the column as STORED.

Why does my generated column expression fail with 'is not immutable'?

PostgreSQL requires the generation expression to be IMMUTABLE, meaning it is a pure function of the current row and returns the same result forever. Functions like now(), random(), or timezone-dependent casts are not immutable and are rejected. A subtle case is to_tsvector(body): pin the configuration as to_tsvector('english', body) to make it immutable.

How do I change the expression of an existing generated column?

You cannot ALTER the expression of a STORED or indexed generated column in place. You must DROP the column and ADD it back with the new expression, which rewrites the table and drops any index on it. Schedule this in a maintenance window and rebuild the index CONCURRENTLY afterward on large tables.

When should I use a generated column instead of computing in application code?

Use a generated column when the derived value is an invariant you never want any writer to get wrong, such as a line total, a normalized email, or a search vector. Putting it in the database guarantees every code path, migration, and manual query produces the same value. Prefer STORED when you read or index the value often, and VIRTUAL when writes dominate and reads are rare.

PostgreSQL Generated Columns: STORED, Indexing & Derived Data

When to push derived logic into STORED generated columns, how to index them, and the constraints that bite you in production.

Key Takeaway

A PostgreSQL generated column computes its value from other columns in the same row using an immutable expression. STORED columns write that value to disk so you can index them and read it for free; VIRTUAL columns compute at read time. Use STORED plus a matching index when you filter or search on derived data.

Every backend accumulates the same little tax: a value that is really just a function of other columns. A full name from first and last, a search vector from a title and body, a total from price times quantity, a lowercased email for case-insensitive lookups. The lazy answer is to compute it in application code on every write, and hope nobody forgets. The better answer, most of the time, is to let the database own it. That is what generated columns are for.

Generated columns landed in PostgreSQL 12 back in 2019, and until recently they came in exactly one flavour: STORED. PostgreSQL 18 added VIRTUAL columns and made virtual the default when you do not say which you want. That default change matters, because the two behave very differently the moment you try to index or search on them. This is a practical tour of how I actually use them on services I run.

What a generated column actually is

A generated column is defined with a GENERATED ALWAYS AS expression that reads other columns of the same row. You never write to it directly; PostgreSQL computes it for you. The classic example is a normalized total on a line-item table.

CREATE TABLE order_items (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    bigint NOT NULL,
  unit_price  numeric(12,2) NOT NULL,
  quantity    integer NOT NULL,
  -- computed on every write, physically stored on disk
  line_total  numeric(14,2) GENERATED ALWAYS AS (unit_price * quantity) STORED
);

INSERT INTO order_items (order_id, unit_price, quantity)
VALUES (1001, 19.90, 3);

-- line_total is 59.70 with no application code involved
SELECT unit_price, quantity, line_total FROM order_items;

The win here is not saving three characters of arithmetic. It is that line_total is now impossible to get wrong. Every writer through every code path, every raw SQL migration, every psql session an intern runs at 2am produces a consistent value. The invariant lives next to the data it constrains, not scattered across services that may or may not agree on the formula.

STORED versus VIRTUAL: pick by read pattern

The two kinds trade write cost against read cost. STORED pays at write time and on disk, so reads and indexes are free. VIRTUAL pays nothing at write but recomputes on every read, and critically, cannot be indexed as of PostgreSQL 18. If you ever filter, sort, join, or search on the derived value, you almost certainly want STORED.

Dimension	STORED	VIRTUAL
When computed	On INSERT / UPDATE	On every read
Disk usage	Occupies storage like a normal column	Zero — nothing is stored
Can be indexed	Yes	No (PostgreSQL 18)
Write speed	Slower — recomputes and writes	Faster — no work at write
Best for	Read-heavy, filtered, searched data	Write-heavy data read rarely
Default before your DDL says otherwise	Was the only option before v18	The new default in v18+

Because VIRTUAL is the new default in PostgreSQL 18, an old CREATE TABLE script that omitted the keyword now produces a virtual column instead of a stored one. If a later migration tries to index it, that index creation fails. Always write STORED or VIRTUAL explicitly in your DDL rather than trusting the default.

Indexing a stored column: the full-text search case

The place STORED generated columns earn their keep hardest is full-text search. Instead of maintaining a tsvector by hand in a trigger, you generate it from the source columns and index the result with GIN. The search vector stays in lockstep with the content, and queries never redo the to_tsvector call to verify a match.

ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

CREATE INDEX articles_search_idx
  ON articles USING GIN (search_vector);

-- fast, and no need to name the config in the query
SELECT id, title
FROM   articles
WHERE  search_vector @@ to_tsquery('english', 'postgres & index')
ORDER  BY id DESC
LIMIT  20;

One advantage of a generated column over a plain expression index is that queries do not have to repeat the exact expression to hit the index. A GIN index on search_vector is used by any query that references search_vector, which keeps your application code clean and your ORM happy.

The same pattern works for cheaper cases. A STORED lower(email) column with a unique index gives you case-insensitive uniqueness without a functional index that every insert path must remember to match. A STORED date_trunc('month', created_at) column lets you build a b-tree for fast monthly rollups. Anything you filter on repeatedly is a candidate.

The rules that will bite you

Generated columns are not magic; the expression has to satisfy real constraints, and PostgreSQL enforces them at DDL time. Learn these before you design around a column, not after a migration fails in staging.

The expression must be IMMUTABLE. now(), random(), and anything timezone-dependent are rejected — the value has to be a pure function of the stored row.
It can reference other columns of the same row, but not other generated columns, and not system columns except tableoid.
A generated column cannot have a DEFAULT or an identity definition, and it cannot be part of a partition key.
You cannot drop a column that a generated column depends on without dropping or altering the generated column first.
On PostgreSQL 18, virtual columns additionally cannot carry unique or foreign-key constraints, not-null constraints, or participate in logical replication.

Immutability is stricter than it looks. to_tsvector('english', body) with the config named as a literal is immutable and allowed; to_tsvector(body), which reads the session default config, is not. Always pin the configuration argument in a generated column expression.

Moving derived logic in safely

Pushing a formula from application code into the database is a real migration, not a config toggle. The dangerous part is the ALTER: you cannot change the expression of a STORED or indexed generated column in place. You have to drop the column and add it back, which rewrites the table and drops any index on it. On a large table that is a heavy, locking operation. Here is the sequence I follow.

Add the STORED generated column in one migration. Adding a stored column rewrites the table once, so schedule it in a low-traffic window.
Build the index CONCURRENTLY afterward so the table stays writable during index creation.
Verify parity: compare the generated value against the old application-computed value across the whole table before trusting it.
Delete the application-side computation only after the column and index are live and verified in production.
To change the formula later, drop and re-add the column in a maintenance window — treat every expression change as a table rewrite.

-- migration up
ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(body, ''))) STORED;

-- separate step, outside the transaction:
CREATE INDEX CONCURRENTLY articles_search_idx
  ON articles USING GIN (search_vector);

-- changing the expression later is a drop + re-add, NOT an ALTER:
ALTER TABLE articles DROP COLUMN search_vector;
ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

My rule of thumb: reach for a STORED generated column when the derived value is a hard invariant you never want an application to get wrong, and you read or index it far more than you rewrite the formula. Reach for VIRTUAL when the value is cheap to compute, you rarely read it, and writes dominate. And whichever you choose, write the keyword out loud in the DDL so the next migration does not inherit a surprise from the version default.

Sources

PostgreSQL Generated Columns for Computed Data