Generated columns are a powerful database feature that allows column values to be derived automatically from expressions based on other columns. PostgreSQL introduced generated columns in version 12, but until PostgreSQL 17, only STORED generated columns were available.
With PostgreSQL 18, a new capability has been introduced: VIRTUAL generated columns. This enhancement brings PostgreSQL closer to feature parity with other advanced databases while offering better flexibility, storage efficiency, and query optimization possibilities.
What Are Generated Columns in PostgreSQL?
A generated column is a column whose value is automatically computed from an expression involving other columns in the same table.
Key properties:
- The value is not manually inserted or updated
- The expression is defined at table creation or alteration time
- The column is always consistent with its base columns
General syntax:
column_name data_type
GENERATED ALWAYS AS (expression) generation_type
Where generation_type is either STORED or VIRTUAL.
STORED Generated Columns (PostgreSQL 12–17)
A STORED generated column physically stores the computed value on disk. Whenever the base columns change, PostgreSQL recalculates and stores the new value.
This was the only supported generated column type from PostgreSQL 12 through PostgreSQL 17.
Example: STORED Generated Column
CREATE TABLE product (
id SERIAL PRIMARY KEY,
price NUMERIC(10,2),
quantity INTEGER,
total_value NUMERIC(12,2)
GENERATED ALWAYS AS (price * quantity) STORED
);
Insert Example
INSERT INTO product (price, quantity)
VALUES (100.50, 3);
Query Result
SELECT * FROM product;
id | price | quantity | total_value
----+--------+----------+-------------
1 | 100.50 | 3 | 301.50
Notice that total_value is automatically calculated and stored.
Update Behavior
UPDATE product
SET quantity = 5
WHERE id = 1;
The total_value column is recalculated automatically.
Check again the results from product table
postgres=# SELECT * FROM product;
id | price | quantity | total_value
----+--------+----------+-------------
1 | 100.50 | 5 | 502.50
(1 row)
Characteristics of STORED Generated Columns
- Physically stored on disk
- Occupy storage space
- Can be indexed normally
- Faster reads (no recomputation)
- Slower writes due to recomputation
- Cannot be manually inserted or updated
Limitations of STORED Generated Columns
While STORED generated columns are powerful, they come with trade-offs:
- Increased table size
- Higher I/O during INSERT and UPDATE
- Redundant storage of derivable data
These limitations motivated the introduction of VIRTUAL generated columns in PostgreSQL 18.
VIRTUAL Generated Columns (PostgreSQL 18)
A VIRTUAL generated column is not stored on disk. Instead, its value is computed on demand at query execution time.
This means:
- No physical storage
- No recomputation during INSERT or UPDATE
- Expression is evaluated during SELECT
Syntax
column_name data_type
GENERATED ALWAYS AS (expression) VIRTUAL
Example: VIRTUAL Generated Column
CREATE TABLE order_item (
id SERIAL PRIMARY KEY,
unit_price NUMERIC(10,2),
units INTEGER,
total_price NUMERIC(12,2)
GENERATED ALWAYS AS (unit_price * units) VIRTUAL
);
Insert Example
INSERT INTO order_item (unit_price, units)
VALUES (250.00, 4);
Query Example
SELECT id, unit_price, units, total_price
FROM order_item;
id | unit_price | units | total_price
----+------------+-------+-------------
1 | 250.00 | 4 | 1000.00
The total_price value is calculated at runtime.
Indexing Behavior
STORED Generated Column Index
CREATE INDEX idx_product_total
ON product (total_value);
This works like a normal column index.
VIRTUAL Generated Column Indexing
VIRTUAL columns are not stored, so direct indexing is not allowed. Instead, PostgreSQL supports expression indexes.
CREATE INDEX idx_order_item_total
ON order_item ((unit_price * units));
The postgresql planner can use this index when querying the virtual column.
System Catalogs and Metadata
PostgreSQL stores generated column metadata in system catalogs, mainly:
- pg_attribute
- pg_stat
- pg_class
You can explore more PostgreSQL system catalogs by typing pg_* in the psql terminal.
Inspecting Generated Columns
SELECT
attname,
attgenerated,
atttypid::regtype
FROM pg_attribute
WHERE attrelid = 'order_item'::regclass
AND attnum > 0;
You get result like this
attname | attgenerated | atttypid
-------------+--------------+----------
id | | integer
unit_price | | numeric
units | | integer
total_price | v | numeric
(4 rows)
Check the below query to inspect the metadata about the product table
SELECT
attname,
attgenerated,
atttypid::regtype
FROM pg_attribute
WHERE attrelid = 'product'::regclass
AND attnum > 0;
You get result like this
attname | attgenerated | atttypid
-------------+--------------+----------
id | | integer
price | | numeric
quantity | | integer
total_value | s | numeric
(4 rows)
attgenerated Values
- s – Indicates a STORED generated column, where the computed value is physically stored on disk.
- v – Indicates a VIRTUAL generated column, where the value is calculated at query execution time and not stored.
- (empty value) – Indicates a normal column that is neither stored nor virtual generated.
Expression Definition
SELECT
pg_get_expr(adbin, adrelid) AS generation_expression
FROM pg_attrdef
WHERE adrelid = 'order_item'::regclass;
This query reveals the generation logic stored internally by PostgreSQL.
Result :
generation_expression
----------------------------------------
nextval('order_item_id_seq'::regclass)
(unit_price * (units)::numeric)
(2 rows)
Planner and Execution Behavior
For VIRTUAL generated columns:
- Expression is expanded in the query plan
- Evaluated per row during execution
- Can be optimized using expression indexes
Example:
EXPLAIN ANALYZE
SELECT *
FROM order_item
WHERE total_price > 500;
The planner rewrites total_price as (unit_price * units) internally.
Example:
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on order_item (cost=0.00..1.03 rows=1 width=40) (actual time=0.012..0.015 rows=1.00 loops=1)
Filter: (((unit_price * (units)::numeric))::numeric(12,2) > '500'::numeric)
Buffers: shared hit=1
Planning:
Buffers: shared hit=16 read=1
Planning Time: 0.185 ms
Execution Time: 0.032 ms
(7 rows)
Use Cases
When to Use STORED Generated Columns
- Frequently queried derived values
- Heavy read workloads
- When indexing the derived value is critical
- When CPU cost must be minimized
When to Use VIRTUAL Generated Columns
- Storage-sensitive systems
- Write-heavy workloads
- Rarely queried derived values
- Avoiding redundant data storage
Migration Considerations
If migrating from PostgreSQL 17 to 18:
- Existing STORED generated columns continue to work
- You may refactor some STORED columns into VIRTUAL ones
- Evaluate query frequency before conversion
Example conversion:
ALTER TABLE product
DROP COLUMN total_value;
ALTER TABLE product
ADD COLUMN total_value NUMERIC(12,2)
GENERATED ALWAYS AS (price * quantity) VIRTUAL;
Advantages of Virtual Generated Columns
- Reduced table size
- Cleaner schema design
- No risk of stale derived data
- Improved write throughput
PostgreSQL 18 introduces VIRTUAL generated columns, giving developers and DBAs more control over how derived data is handled. While STORED generated columns are still useful when fast reads or indexing is required, VIRTUAL columns are a better choice when you want to save storage and avoid keeping extra data on disk.
By understanding when to use STORED and when to use VIRTUAL, schema design becomes more practical and easier to maintain. PostgreSQL 18 now lets you decide whether a value should be saved permanently or calculated only when it is needed.
This addition makes PostgreSQL more suitable for modern database designs where efficiency, clarity, and long-term maintenance matter.