Generated columns are one of the features where MySQL was genuinely ahead. MySQL shipped them in 5.7 with both VIRTUAL and STORED flavors; PostgreSQL added stored generated columns in version 12 and, as of PostgreSQL 16 and 17, still has no virtual flavor. If you are migrating a MySQL schema that leans on generated columns, the mapping is not one-to-one, but PostgreSQL covers the same use cases through a different door: expression indexes. This article walks through both models and the translation rules between them.
The MySQL model: VIRTUAL and STORED
A MySQL generated column is declared with GENERATED ALWAYS AS (expression), followed by VIRTUAL or STORED. VIRTUAL is the default: the value occupies no row storage and is computed when read. STORED computes on insert and update and persists the result in the row like an ordinary column.
The expression rules are strict in both engines, and rightly so: deterministic built-in functions only, no subqueries, no references to other tables, no nondeterministic functions like NOW() or UUID(). A generated column can reference earlier generated columns in MySQL, which occasionally enables neat pipelines and occasionally enables regret.
The operational difference between the flavors shows up in ALTER TABLE. Adding a VIRTUAL column is an in-place metadata change, effectively instant on any table size. Adding a STORED column rebuilds the table, because every row must be computed and written. That asymmetry is why MySQL practitioners default to VIRTUAL and reach for STORED only when the expression is expensive enough that computing it on every read hurts.
Indexing computed values in MySQL
The trick that makes VIRTUAL columns powerful is that secondary indexes on them are materialized: the index stores the computed values even though the row does not. You get index-speed lookups on an expression while paying storage only in the index:
-- MySQL: instant virtual column, materialized index
ALTER TABLE orders
ADD COLUMN order_month CHAR(7)
GENERATED ALWAYS AS (DATE_FORMAT(created_at, '%Y-%m')) VIRTUAL,
ADD INDEX idx_orders_month (order_month);
SELECT COUNT(*) FROM orders WHERE order_month = '2026-06';Since 8.0.13, MySQL also has functional indexes written directly as CREATE INDEX ON t ((expression)); under the hood they are implemented as hidden virtual columns, so they are the same machinery with less schema noise. The optimizer can also substitute a generated column for a matching expression in a WHERE clause, so some queries written against the raw expression still use the index. That substitution has limits and version-specific edges, so I always verify with EXPLAIN rather than trusting it.
The PostgreSQL model: stored generated columns
PostgreSQL declares generated columns with the same standard syntax, but as of versions 16 and 17 the STORED keyword is mandatory because stored is the only implementation. The expression must use only immutable functions, cannot reference other generated columns, and the column cannot be written directly: INSERT and UPDATE statements must leave it out or specify DEFAULT.
-- PostgreSQL 16/17: stored is the only flavor
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL,
total_cents BIGINT NOT NULL,
total_dollars numeric(12,2)
GENERATED ALWAYS AS (round(total_cents / 100.0, 2)) STORED
);The immutability requirement bites hardest on date formatting: to_char and any timezone-dependent expression are not immutable, so the MySQL example above cannot port literally. You either compute a UTC-anchored expression through an immutable path, store the value from the application, or use the expression-index approach below. Like MySQL STORED columns, adding one to an existing table rewrites the table, and the value is recomputed on every update of the row, so an expensive expression taxes every write whether or not the write touched its inputs.
Expression indexes are PostgreSQL's virtual answer
What PostgreSQL lacks in virtual columns it compensates for with expression indexes, which have been there for decades and are the idiomatic answer to "index a computed value":
-- PostgreSQL: index the expression, skip the column
CREATE INDEX idx_orders_month
ON orders (date_trunc('month', created_at));
SELECT COUNT(*)
FROM orders
WHERE date_trunc('month', created_at)
= TIMESTAMPTZ '2026-06-01 00:00:00+00';The index stores computed values exactly like MySQL's materialized virtual-column index. Two properties are worth knowing. First, matching is syntactic: the query must use the same expression the index declares, or the planner will not consider it, so the expression should live in one place in your query layer rather than being retyped freely. Second, PostgreSQL collects statistics on expression index values, which can materially improve row estimates for skewed computed values; that is a planner benefit MySQL's approach does not give you as directly. One caveat mirrors MySQL: date_trunc on timestamptz is only immutable when given an explicit timezone argument or applied carefully, so building this index may require the two-argument form or a timestamp column; PostgreSQL will refuse the index creation rather than silently build a wrong one, which is the failure mode you want.
Deciding which expressions deserve an index is a workload question, not a schema question, and it changes as queries evolve. This is squarely the territory where the MonPG index advisor is useful: it reasons from the queries you actually run rather than the columns you happen to have.
Mapping rules for the migration
My translation table has three rows. A MySQL VIRTUAL column whose only purpose was backing an index becomes a PostgreSQL expression index, full stop; you lose nothing and gain expression statistics. A MySQL VIRTUAL column that queries select directly, without an index, becomes either a view over the base table or an expression repeated in the query layer; if neither appeals, promote it to a stored generated column and accept the storage. A MySQL STORED column becomes a PostgreSQL stored generated column, with the expression audited for immutability before you assume it ports.
Two extra checks pay off. Chains of generated columns referencing each other must be flattened into standalone expressions, since PostgreSQL forbids the reference. And any generated column feeding a UNIQUE index deserves a test on real data: subtle expression differences between engines, like division semantics or collation-sensitive string functions, can make values that were distinct in MySQL collide in PostgreSQL. Check your bulk-load tooling as well: COPY and INSERT statements into PostgreSQL must not supply values for generated columns, so migration scripts that SELECT * from the MySQL side need explicit column lists on the PostgreSQL side. pg_dump handles this correctly for you, but hand-rolled ETL frequently does not, and the error message arrives at row one of a long load. The classic use case of extracting and indexing a JSON path works in both engines, but if that is your main pattern, the tradeoffs are different enough that I covered them separately in the JSONB vs relational discussion.
Write cost is the tradeoff to respect
Whichever engine you are on, materializing a computed value moves cost from reads to writes, and the honest comparison is about where that cost lands. MySQL VIRTUAL plus index pays at index-maintenance time only. PostgreSQL expression indexes pay the same way. Stored generated columns in either engine pay in row storage plus recomputation on every update. None of these are free, and the expensive failure mode is the same in both engines: an expression that looked cheap in review, sitting on a hot write path, quietly consuming CPU on every insert. Measure the write path before and after adding one; five minutes with a benchmark script beats a quarter of slow-creep latency.
Index-only scans are the quieter benefit worth checking for on the PostgreSQL side. Because an expression index stores the computed values, a query that needs only the expression and the indexed columns can sometimes be answered from the index without touching the heap at all, provided the visibility map is current. That makes vacuum health part of the performance story for computed values, which surprises teams coming from InnoDB's very different storage model.
After the migration: MonPG on your PostgreSQL side
Generated columns and expression indexes fail quietly. The planner stops matching an expression after a seemingly harmless query rewrite, an index rebuild during a migration leaves a hot path unindexed for an hour, or a stored column's recomputation cost surfaces only when a batch update touches every row. MonPG monitors PostgreSQL only, and these are exactly the signals it keeps: pg_stat_statements history to spot a query family that lost its index, per-index usage so an expression index that stopped being scanned shows up as a trend rather than a mystery, and write-path timing evidence around deploys. If the migration's goal was a database you can reason about, PostgreSQL monitoring with query-level history is how you keep the reasoning honest after the traffic arrives.