Backend
PostgreSQL Generated Columns for Computed Data
July 20269 min read
Photo by Markus Spiske markusspiske via Wikimedia Commons (CC0)
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.
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.
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.
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.
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.
Photo by Markus Spiske markusspiske via Wikimedia Commons (CC0)
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.
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.
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.
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.
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.
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.
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.
-- 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.