PostgreSQL 18 Generated Columns: VIRTUAL Is Now the Default

A column I added in August caused two failed deploys before I understood what Postgres was telling me. It had been live for three weeks, search returned the right rows, and staging never complained. The failure came from the index my teammate opened later: ERROR: indexes on virtual generated columns are not supported. I had created a virtual generated column by accident and did not know what it was.

The column, more or less as it shipped:

ALTER TABLE documents
  ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', body));

Look at what is missing. No STORED at the end, and on PostgreSQL 18 that is optional now. Before 18 the identical statement is a syntax error, because those versions had one kind of generated column and made you say which. The 18 docs spell the clause [ STORED | VIRTUAL ] and then tell you the part that bit me: VIRTUAL is the default.

A schema change that was impossible to get wrong became easy to get wrong, and it fails quietly. A virtual generated column is computed when read rather than written, so every SELECT returns correct values. Search worked. The column was real. The index was not, and by the time anyone tried to build one, the migration behind it had been merged a month earlier.

Why I reached for a column at all

The full text search chapter recommends this shape: a tsvector column kept current by a stored generated column, with a GIN index on top. The reasons it gives are real. Queries do not have to repeat the text search configuration to use the index, and matches need no fresh to_tsvector call to verify them. The expression route uses less disk, but the column reads faster. I stopped at the semicolon.

Here is the joke. The docs' own example writes the keyword. Mine did not, because I copied the clause shape from the syntax summary at the top of the page, not the example below it.

What a virtual column cannot do

The restrictions come from the commit that added virtual columns, and the list runs longer than the index failure suggests. You cannot index it, or reference it from an index expression, which also rules out a unique constraint. Extended statistics are out. Foreign keys are out. So is NOT NULL, though CHECK is allowed. A virtual column cannot have a domain type, and logical replication will not publish its values.

Two more traps. ANALYZE skips virtual columns, so the planner has no statistics for them. And a generated column cannot be part of a partition key, stored or virtual, so making it STORED does not unlock partitioning.

"Occupies no storage" is narrower than it sounds, since the column is still stored in the tuple as a null. You save the computed value, not the slot.

Finding them after an upgrade

The catalog tells you which kind each column is. attgenerated is empty for a normal column, s for stored, v for virtual. This query lists every virtual column in a schema with its expression. It found three more nobody had noticed:

SELECT c.relname  AS table_name,
       a.attname  AS column_name,
       a.attgenerated,
       pg_get_expr(d.adbin, d.adrelid) AS expression
FROM pg_attribute a
JOIN pg_class c     ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_attrdef d
       ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attgenerated = 'v'
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND n.nspname = 'public'
ORDER BY c.relname, a.attnum;

Run it after an upgrade to 18, and whenever a migration adds a generated column. A column and its index usually land in separate pull requests.

Repairing one

The obvious fix does not work. ALTER TABLE ... SET EXPRESSION AS (...) replaces the generation expression and leaves the kind alone. Useful when the formula is wrong, useless when the kind is. DROP EXPRESSION looks promising until the note that it only works on stored columns.

Nothing flips virtual to stored. You drop the column and add it back with the keyword:

ALTER TABLE documents DROP COLUMN search_vector;

ALTER TABLE documents
  ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;

Cheap on a small table. Ours was not: adding a stored generated column rewrites the table and every index on it. The docs warn that a rewrite can take a long time, needs as much as double the disk space while it runs, and is not MVCC safe: transactions holding a snapshot from before it see the table as empty. That was the real cost, and nobody mentioned it in review.

So I indexed the expression instead and left the column virtual:

CREATE INDEX documents_search_idx ON documents
  USING gin (to_tsvector('english', body));

That is not free either, and I skipped the detail the first time. A write-up from DB Gorilla measured 500,000 rows on 18.6. The expression index cost about the same insert time as a stored column with its own index, 2.9 seconds against 2.9, and used 36 MB against 40 MB. So it is not cheaper on writes. What it buys is no column and no rewrite; what it costs is naming the configuration in every query.

One oddity before you drop anything. A bug fixed in March 2026 made this index check read a garbage byte for attgenerated and raise the error spuriously, which could hit any expression index. It was backpatched to 18, so if one fails this way on an early 18.x, upgrade the minor version before rewriting your schema.

What I do now

Every generated column definition I write names its kind, even when the shorthand would parse. A version-dependent default in a migration file is a bug with a delivery date, and this one took three weeks.

The column and its index go in the same migration. If the index cannot be built, I want the deploy to fail while the person who wrote the column still remembers why.

I keep the audit query in my Snippet Ark library next to the plan-reading checklist from the index post I wrote two weeks ago. Both are about the same gap: the distance between a schema that works and one the planner can use. Worth running on any Postgres 18 database you inherit.