PostgreSQL 18 Virtual Generated Columns: What Changed and When to Use Them

PostgreSQL 18 changes generated columns in a way that is easy to miss during an upgrade review: virtual generated columns are now available and are the default. Instead of computing and storing the generated value when a row is written, PostgreSQL can compute it when the column is read.

That sounds like a syntax detail, but it affects schema design, storage, write cost, replication expectations, and how teams model derived data. If your application uses generated columns for normalized search fields, display names, JSON projections, or simple arithmetic, the PostgreSQL 18 behavior deserves a deliberate review before new migrations start relying on the default.

What changed in PostgreSQL 18

PostgreSQL generated columns now have two storage modes. A stored generated column is computed on insert or update and occupies storage like a normal column. A virtual generated column occupies no storage and is computed when queried, similar in spirit to a view expression attached to the table. The PostgreSQL 18 release notes call out virtual generated columns as a headline feature and note that virtual is now the default for generated columns.

The practical consequence is that a migration written without an explicit storage mode can mean something different from what many PostgreSQL users expect from older versions. In PostgreSQL 17 and earlier, generated columns were stored. In PostgreSQL 18, writing the storage mode explicitly is a safer habit because it makes intent visible in code review.

For example, a migration that defines a derived full name, lowercased email, or computed price should now make a choice: use virtual when avoiding storage and write amplification matters more, or use stored when read speed, indexing, or replication behavior require a materialized value.

Stored vs virtual generated columns

The choice is not about which mode is newer. It is about where you want to pay the cost and how the generated value is consumed. Virtual columns push work to reads. Stored columns push work to writes and keep the result on disk. That tradeoff is familiar from views versus materialized data, but generated columns make it part of the table definition.

For most application schemas, generated columns are strongest when the expression is deterministic, simple, and clearly part of the data model. They are weaker when the expression is really presentation logic or depends on data outside the row.

Why this matters for SaaS schemas

Many SaaS databases accumulate derived fields over time. A tenant-aware product might store normalized email addresses for lookup, calculated account status for filtering, or extracted JSON attributes for reporting. Generated columns can make those choices more consistent because the database owns the expression instead of each service reimplementing it.

Virtual generated columns are attractive for fields that are convenient but not hot. Suppose an admin screen occasionally displays a combined name or a derived label. Storing that value in every row may not be worth the write overhead or disk space. A virtual column keeps the schema readable without materializing another value.

Stored generated columns remain useful when the derived value is part of a performance path. If a login flow filters by a normalized email, or a billing job repeatedly groups by a derived month, recomputing the expression on every read may be the wrong trade. Stored values can also be easier to reason about when the value must be indexed or consumed by tooling that expects a physical column value.

The important operational habit is to treat generated-column storage mode as part of the contract. A migration should not rely on the default accidentally. Write the intended mode into the DDL so future readers know whether read cost or write cost was chosen deliberately.

Replication and migration notes

PostgreSQL 18 also improves the story for stored generated columns by allowing them to be logically replicated. That matters for teams using logical replication during migrations, major-version upgrades, or database moves. If a generated value is material to the target system, confirm whether it is stored, whether it participates in the publication as expected, and how the subscriber schema is defined.

Virtual generated columns require a different mental model because the value is computed by the database that reads it. During a migration, that means the expression definition and the functions it depends on need to be compatible on the target. The data itself may move correctly while the derived value changes if the expression, collation, or function behavior differs.

A careful migration plan tests more than row counts. It compares representative derived values before and after the move, validates the generated-column definitions on the target, and checks application queries that filter or sort by the derived field. If the column exists to support a user-facing path, run the same query plans against production-shaped data before cutting over.

A practical review checklist

Start by finding existing generated columns and migration files that create new ones. In PostgreSQL, generated columns appear in the catalog, and your migration history will usually show whether the team used explicit storage syntax. The goal is not to rewrite every column. The goal is to make the storage choice intentional before PostgreSQL 18 defaults surprise a future migration.

For each generated column, ask three questions. Is the value read often enough that recomputing it would matter? Does the application need to index, filter, or join on the derived value? Does a migration or logical replication path depend on the generated value being physically stored? If the answer to any of those is yes, stored may be the safer choice. If the value is mostly a convenient projection and not a performance path, virtual can keep the table leaner.

When adding a new column in PostgreSQL 18, prefer explicit DDL. A compact style is better than relying on team memory:

-- Use STORED when the derived value is read frequently or indexed.
total_cents integer GENERATED ALWAYS AS (quantity * unit_cents) STORED

-- Use VIRTUAL when the value is convenient but does not need storage.
display_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL

The exact expression rules matter. PostgreSQL documents restrictions for generated columns, including limits around volatile functions and references. Review those rules before turning application code into table definitions, especially if the expression uses custom functions or extension-provided behavior.

What managed PostgreSQL teams should do now

For teams on PostgreSQL 17, this is a planning item. Inventory generated columns, decide where explicit storage mode belongs in migration templates, and add generated-column checks to the PostgreSQL 18 upgrade review. For teams already testing PostgreSQL 18, create a small fixture that measures representative writes and reads with both modes before standardizing on one pattern.

Managed PostgreSQL does not remove the schema-design decision. It can reduce the operational burden around upgrades, backups, and restore testing, but your application still owns the meaning of derived data. If a generated column supports a latency-sensitive path, test the query plan with production-like data. If it supports a migration path, validate it in the same rehearsal as logical replication and backup restore checks.

ArmorDB currently emphasizes simple managed PostgreSQL operations, PgBouncer, and clear upgrade paths. If you are deciding whether to materialize derived values or keep them virtual, pair the database choice with the operational basics: documented migrations, tested backups at /docs/backups, and enough query history to spot regressions after a major-version change.

Takeaway

PostgreSQL 18 virtual generated columns give schema designers a useful new default, but defaults should not replace intent. Use virtual generated columns when a derived value is convenient, lightweight, and not central to a hot query path. Use stored generated columns when predictable read performance, indexing, or logical replication behavior matters. Most importantly, write the storage mode explicitly in migrations so the tradeoff is visible before production traffic depends on it.

Sources and further reading

• PostgreSQL 18 release notes, https://www.postgresql.org/docs/18/release-18.html
• PostgreSQL documentation: Generated Columns, https://www.postgresql.org/docs/18/ddl-generated-columns.html
• PostgreSQL 18 release announcement, https://www.postgresql.org/about/news/postgresql-18-released-3142/
• PostgreSQL documentation: CREATE TABLE generated column syntax, https://www.postgresql.org/docs/18/sql-createtable.html