Table sizes for stored, virtual and no generated columns on 2 million rows, in Pilotbase

A generated column is a column whose value is an expression over other columns of the same row: a full name from first and last, a total from quantity and price, one key pulled out of a JSON document. PostgreSQL has had them since version 12, in one flavour only: STORED, computed when the row is written and kept on disk like any other value.

PostgreSQL 18 adds the second flavour, VIRTUAL, computed when the row is read and never stored, and makes it the default. So the same GENERATED ALWAYS AS (...) that the syntax required you to end with STORED in 17 now gives you a virtual column if you leave the keyword off. We wanted to know what that trade costs in each direction, so we built the same table three ways and measured it.

The test

sql
CREATE TABLE o_virtual (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id int NOT NULL, first_name text NOT NULL, last_name text NOT NULL, email text NOT NULL,
  qty int NOT NULL, unit_cents int NOT NULL, tax_rate numeric(5,4) NOT NULL, meta jsonb NOT NULL,
  created_at timestamp NOT NULL,
  full_name    text    GENERATED ALWAYS AS (first_name || ' ' || last_name),
  email_domain text    GENERATED ALWAYS AS (split_part(email, '@', 2)),
  total_cents  bigint  GENERATED ALWAYS AS (qty::bigint * unit_cents),
  total_gross  numeric GENERATED ALWAYS AS (round(qty * unit_cents * (1 + tax_rate))),
  country      text    GENERATED ALWAYS AS (meta ->> 'country'),
  search_text  text    GENERATED ALWAYS AS (lower(first_name || ' ' || last_name || ' ' || email))
);
-- o_stored: the same, with STORED after each expression
-- o_plain:  the ten base columns only

Two million rows in each, on PostgreSQL 18.6 with default settings. Six derived columns is a lot for one table, but not unusual for a table that feeds search, reports and an API at once. The one keyword is the only difference between the two, and \d shows it the other way round from before: stored columns now say stored, virtual ones say nothing.

Size and writes: virtual wins

text
2 million rows            no generated   6 virtual     6 stored
table size                364 MB         381 MB        537 MB
bytes per row             191            200           282
load (INSERT ... SELECT)  -              4.1-5.7 s     7.9-11.6 s
Pilotbase query result: o_stored with 6 stored columns is 537 MB at 282 bytes per row, o_virtual with 6 virtual columns is 381 MB at 200 bytes, o_plain is 364 MB at 191 bytes
The three tables in Pilotbase. pg_attribute.attgenerated is 's' for a stored column and 'v' for a virtual one.

The stored copy is 156 MB bigger, 41% more table for values that are all computable from the row next to them. That is 41% more to back up, to replicate, to keep in cache and to scan when a query reads the whole table. The load took about twice as long, because every row is longer and every expression runs at insert time (and again on every update that touches the row, whichever column changed).

Virtual columns aren't quite free. The table with six of them is 17 MB bigger than the one with none, about 9 bytes a row. A virtual column is kept in the row as a NULL, so each row now carries a null bitmap it didn't need before. On a narrow table with hundreds of millions of rows that's worth knowing; on most tables it's noise.

Reads: you pay when you use the column

Full scans over the 2 million rows, three runs each, parallel query off so the numbers are one process:

text
query                                        stored        virtual
count(*), sum(qty)   (no generated column)   298-315 ms    279-300 ms
sum(total_cents)     (int * int)             327-372 ms    321-325 ms
GROUP BY country     (meta ->> 'country')    485-501 ms    610-630 ms
sum(total_gross)     (numeric, round)        388-419 ms    695-729 ms
search_text LIKE '%tanaka user19%'           318-335 ms    1,101-1,196 ms
all six columns at once                      834-835 ms    2,167-2,198 ms
explainViewer comparing the two plans: the Seq Scan on o_stored takes 332 ms, on o_virtual 1,126 ms, 3.4 times slower, and the virtual plan's filter shows the full lower() expression
The search query on the stored and the virtual table in explainViewer. The filter in the right-hand plan is the column's expression written out.

The pattern is simple once you see it. A query that doesn't mention a virtual column pays nothing, and gets a smaller table to scan. A cheap expression (integer arithmetic) costs about as much to compute as a stored value costs to fetch from a wider row. An expensive one (string concatenation and lower(), numeric maths, a JSON lookup) is paid in full, for every row the query looks at, every time. The search query went from a third of a second to over a second because it builds and lowercases a string 2 million times to keep 8,547 rows.

The plan shows how it works: the virtual column's name is gone from the filter and the expression is in its place. Postgres expands a virtual column into its expression before planning, much like a view. That one fact explains most of what follows.

The planner knows nothing about a virtual column

ANALYZE collects statistics for stored values. A virtual column has none, so the planner falls back on a fixed guess. For WHERE country = 'DE', which matches 200,000 of our 2 million rows:

text
                 planner expected   actual
o_stored         194,000 rows       200,000
o_virtual        10,000 rows        200,000     (0.5% of the table, the default for an unknown expression)

Twenty times too low is enough to turn a hash join into a nested loop further up a real query. You can't fix it by naming the column: CREATE STATISTICS ... ON country, email_domain FROM o_virtual fails with "statistics creation on virtual generated columns is not supported". Statistics on the expression itself do work, and because the column is expanded into that same expression, queries that use the column name pick them up:

sql
CREATE STATISTICS o_virtual_domain_stx ON (split_part(email, '@', 2)) FROM o_virtual;
ANALYZE o_virtual;
-- WHERE email_domain = 'corp.example': planner now expects 507,000 rows, actual 500,000

If a plan on a table with virtual columns looks wrong, check the estimate on the filter first. When Postgres guesses wrong covers how to read those numbers and what CREATE STATISTICS does.

What a virtual column can't do

We tried each of these on 18.6. The error messages are PostgreSQL's own.

text
CREATE INDEX ON o_virtual (country)
  ERROR: indexes on virtual generated columns are not supported
ALTER TABLE o_virtual ADD UNIQUE (email_domain, id)
  ERROR: unique constraints on virtual generated columns are not supported
ALTER TABLE o_virtual ADD FOREIGN KEY (country) REFERENCES countries (code)
  ERROR: foreign key constraints on virtual generated columns are not supported
... GENERATED ALWAYS AS (net_cents(qty, unit_cents))          -- our own SQL function
  ERROR: generation expression uses user-defined function
... GENERATED ALWAYS AS (encode(digest(email, 'sha256'), 'hex'))   -- pgcrypto
  ERROR: generation expression uses user-defined function
... email_ci citext GENERATED ALWAYS AS (email::citext)
  ERROR: virtual generated column "email_ci" cannot have a user-defined type
... x bigint GENERATED ALWAYS AS (total_cents * 2)             -- another generated column
  ERROR: cannot use generated column "total_cents" in column generation expression
CREATE PUBLICATION pub FOR TABLE o_virtual (id, total_cents)
  ERROR: cannot use virtual generated column "total_cents" in publication column list

No index. This is the one that decides most cases. The usual reason to pull a key out of a jsonb column into its own column is to index it, and you can't index a virtual column, nor an expression that mentions one. What you can do is index the underlying expression, and here the expansion works in your favour:

sql
CREATE INDEX o_virtual_country_idx ON o_virtual ((meta ->> 'country'), created_at);

SELECT id, country, created_at FROM o_virtual
WHERE country = 'DE' ORDER BY created_at DESC LIMIT 20;
--  Index Scan Backward using o_virtual_country_idx on o_virtual
--    Index Cond: ((meta ->> 'country'::text) = 'DE'::text)

The query uses the column's name and the planner uses the expression index. It ran in well under a tenth of a millisecond, as did the same query on the stored column with a normal index. Both indexes were 60 MB and took 1.5 seconds to build. An expression index also brings statistics for its expression with it, so after ANALYZE the row estimate went from 10,000 to 197,000. You get a short name for the expression, an index on it, and nothing extra in the table. The catch is that the expression in the index and in the column must stay the same; change one and the index quietly stops matching.

Only built-in functions and types. "User-defined" includes everything that comes from an extension: citext, pgcrypto, PostGIS, your own enum or domain. A stored column takes all of those. The reason is security. A stored column's expression runs when the row is written, as the writer. A virtual column's expression runs when the row is read, as whoever reads it, so a function planted in a generation expression would run with the privileges of every later reader of the table, an administrator included. Postgres 18 rules that out by allowing built-ins only.

No logical replication. A virtual column can't be published. PostgreSQL 18 can replicate generated columns for the first time, stored ones only; a subscriber that needs the value has to compute it or define the same virtual column on its side.

Things that do work: NOT NULL and CHECK constraints on a virtual column (the expression is evaluated on every insert and update to enforce them, so that part of the write saving is gone), views over it, and pg_dump. Like stored generated columns, a virtual one can't be written to, can't be part of a partition key, and is left out of COPY orders TO. Naming it in the column list is an error ("Generated columns cannot be used in COPY"); export it with COPY (SELECT ...) TO.

Adding and changing: instant

text
on the 2 million row table                         STORED        VIRTUAL
ALTER TABLE ... ADD COLUMN ... GENERATED ...       5.9 s         1.5 ms
ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION    7.4 s         1.3 ms

Adding a stored generated column, or changing its expression, rewrites the whole table while holding an ACCESS EXCLUSIVE lock: no reads and no writes for those seconds, and for minutes on a large table. The virtual versions only change the catalog. They still need that lock for an instant, so set a lock_timeout as with any ALTER TABLE, but there is nothing to wait for once it's granted. A formula that is still changing (a price rule, a display name) is much easier to live with as a virtual column.

Converting STORED to VIRTUAL

There is no ALTER COLUMN ... SET VIRTUAL in 18 (it's a syntax error), and DROP EXPRESSION goes the other way, turning a stored generated column into a plain one. The route is to drop the column and add it back as virtual, in one statement so no query ever sees the table without it:

sql
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders
  DROP COLUMN search_text,
  ADD COLUMN search_text text GENERATED ALWAYS AS (lower(first_name || ' ' || last_name || ' ' || email)) VIRTUAL,
  DROP COLUMN full_name,
  ADD COLUMN full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL;
COMMIT;

Swapping four of the six columns this way took 1.6 ms. Four things to know before you run it:

The space doesn't come back. The table was 537 MB before and 537 MB after. A dropped column's bytes stay in every row until the row is rewritten, so straight after the swap you have the worst of both: the stored table's size and the virtual column's read cost. Updates reclaim it row by row; VACUUM FULL (3.6 seconds here, exclusive lock) or an online repack reclaims it at once. Ours came down to 411 MB with the two columns we kept stored.

Anything built on the column blocks the drop. A view that selects it stops the statement: "cannot drop column full_name of table o_mig because other objects depend on it". Drop and recreate the view in the same transaction. An index on the column doesn't stop anything, which is worse: it is dropped with the column, without a word. Replace it with an expression index first if queries need it.

The column moves to the end. SELECT * returns columns in a new order, and so does anything that reads rows by position.

Check the expression qualifies. If it uses an extension function or type, the ADD COLUMN fails, the transaction rolls back, and you keep the stored column, which is the right outcome.

How MySQL compares

MySQL has had both kinds since 5.7, with VIRTUAL as the default all along, so people who work in both will find Postgres 18 now reads the same way. We ran the same shape of table on MySQL 8.4 (InnoDB, 2 million rows, four generated columns): 289 MB with virtual columns against 415 MB with stored ones, close to the Postgres ratio. The differences are in what you may do with a virtual column:

text
                                      PostgreSQL 18            MySQL 8.4 (InnoDB)
default kind                          VIRTUAL                  VIRTUAL
index on a virtual column             no (index the            yes, secondary indexes
                                      expression instead)
primary key on a virtual column       no                       no (stored: yes)
refer to another generated column     no                       yes, if defined earlier
your own functions in the expression  stored only              neither kind
add a virtual column                  instant                  instant (17 ms)
add a stored column                   rewrites the table       copies the table (13 s)
stored to virtual in place            no: drop and add         no: drop and add

The index row is the big one. In MySQL you declare the virtual column and index it; the index stores the computed values and the table doesn't. The optimizer even used that index when our query spelled out the expression instead of the column name. In Postgres you reach the same place from the other side, with an expression index next to a virtual column that only gives the expression a name. MySQL's ALTER TABLE ... MODIFY refused to flip a column from stored to virtual ("'Changing the STORED status' is not supported for generated columns"), so the conversion is drop and add there too, and it was just as instant, with the same unreclaimed space afterwards. If you're moving a schema between the two, the MySQL to PostgreSQL notes list what else changes.

Which to use

Virtual when the table is wide or large and the column is read now and then: display values, a key from a JSON document that only some queries want, a figure for an export. Also when the formula may change, or when the table takes far more writes than it gets reads of that column. If you need to filter on it, add an expression index.

Stored when most queries read the column and the expression is expensive (text assembly, numeric maths), when it needs a unique constraint or a foreign key, when it has to reach a logical replica, or when the expression needs an extension. A tsvector for full-text search stays stored: it's costly to build and you want the plain GIN index on it.

Watch the default when you upgrade. Existing columns keep their kind, and pg_dump writes STORED out for them. But a migration written on 18 without the keyword creates a virtual column, and the same file run on 17 is a syntax error at the closing bracket. Write STORED or VIRTUAL every time, so the file says what you meant.

Run the size query from this article on your own tables in Pilotbase and see what your stored generated columns cost, on Postgres and MySQL from the same window. Desktop for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Sources: PostgreSQL 18 documentation, Generated Columns (virtual is the default, built-in functions and types only, stored only for logical replication, the security note), ALTER TABLE (SET EXPRESSION, DROP EXPRESSION), CREATE STATISTICS; PostgreSQL 18 release notes; MySQL 8.4 Reference Manual, CREATE TABLE and Generated Columns and Secondary Indexes and Generated Columns. Every number, error message and screenshot is from our own runs: PostgreSQL 18.6 and MySQL 8.4.10 in Docker on a 12th-gen Core i5 laptop, default settings, 2 million rows, three runs per query with parallel query off; plans shown in explainViewer, sizes queried in Pilotbase.