PG18 Hacktober: 31 Days of New Features : Virtual Generated columns

Welcome to Day 18 of the PG18 Hacktober !!

In PostgreSQL, generated columns are table columns whose values are computed automatically from other columns. You don’t insert or update them directly; PostgreSQL calculates them for you.

There are two kinds of generated columns:

Stored: Calculated on INSERT/UPDATE, persisted to disk.

Virtual: Virtual Calculated only when queried, not stored

What’s in the PG18 documentation?

A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables. There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view, and a stored generated column is similar to a materialized view (except that it is always updated automatically).

With PostgreSQL 18, the introduction of virtual generated columns changes that. Virtual columns are computed on the fly when queried, meaning they do not occupy any physical storage. This provides a huge benefit for performance and storage efficiency, especially when dealing with large datasets or frequently changing base columns

What’s new in PostgreSQL 18?

Before PostgreSQL 18:

Only stored generated columns were supported
Required extra storage
Slowed down insert/update due to recomputation

Now in PostgreSQL 18:

Virtual generated columns are computed on the fly
No extra disk usage
Faster INSERT/UPDATE performance
Simplifies schema-level logic for dynamic fields

Test Case: Using Stored Generated Columns

postgres=# CREATE TABLE payroll (
    emp_id serial PRIMARY KEY,
    emp_name text NOT NULL,
    base_salary numeric(10,2),
    bonus numeric(10,2),
    total_salary numeric(10,2)
        GENERATED ALWAYS AS (base_salary + bonus) STORED
);
CREATE TABLE
postgres=#
postgres=#
postgres=# INSERT INTO payroll (emp_name, base_salary, bonus)
VALUES
  ('Lahari', 60000.00, 5000.00),
  ('John', 45000.00, 2500.00),
  ('Asha', 52000.00, 3000.00);
INSERT 0 3
postgres=#
postgres=#
postgres=# SELECT * FROM payroll;
 emp_id | emp_name | base_salary |  bonus  | total_salary
--------+----------+-------------+---------+--------------
      1 | Lahari   |    60000.00 | 5000.00 |     65000.00
      2 | John     |    45000.00 | 2500.00 |     47500.00
      3 | Asha     |    52000.00 | 3000.00 |     55000.00
(3 rows)

postgres=#

Now, verify that the total_salary column is physically stored

postgres=#
postgres=# SELECT attname, attgenerated
FROM pg_attribute
WHERE attrelid = 'payroll'::regclass
  AND attnum > 0
  AND NOT attisdropped;
   attname    | attgenerated
--------------+--------------
 emp_id       |
 emp_name     |
 base_salary  |
 bonus        |
 total_salary | s
(5 rows)

postgres=#

Here ‘s’ denotes stored generated columns

Test Case: Using Virtual Generated Columns

postgres=#
postgres=# CREATE TABLE employee_salary (
    emp_id serial PRIMARY KEY,
    emp_name text NOT NULL,
    base_salary numeric(10,2),
    bonus numeric(10,2),
    total_salary numeric(10,2)
        GENERATED ALWAYS AS (base_salary + bonus) VIRTUAL
);
CREATE TABLE
postgres=#
postgres=# INSERT INTO employee_salary (emp_name, base_salary, bonus)
VALUES
  ('Alice', 60000.00, 4000.00),
  ('Bob', 45000.00, 2500.00),
  ('John', 52000.00, 3500.00);
INSERT 0 3
postgres=#
postgres=# SELECT * FROM employee_salary;
 emp_id | emp_name | base_salary |  bonus  | total_salary
--------+----------+-------------+---------+--------------
      1 | Alice  |    60000.00 | 4000.00 |     64000.00
      2 | Bob     |    45000.00 | 2500.00 |     47500.00
      3 | John      |    52000.00 | 3500.00 |     55500.00
(3 rows)

postgres=#

Now verify that the total_salary column is virtually stored:

postgres=# SELECT attname, attgenerated
FROM pg_attribute
WHERE attrelid = 'employee_salary'::regclass
  AND attnum > 0
  AND NOT attisdropped;
   attname    | attgenerated
--------------+--------------
 emp_id       |
 emp_name     |
 base_salary  |
 bonus        |
 total_salary | v
(5 rows)

postgres=#
postgres=#

Here`v`means virtual generated column

Key Differences

Features	Before PostgreSQL 18	PostgreSQL 18 and Later
Storage	Requires disk space	No storage used
INSERT/UPDATE Speed	Slight overhead (recompute)	Faster (no computation)
SELECT Speed	Very fast (pre-computed)	Slight overhead (calculated)
Can you index?	YES	NO (can’t index virtual)
Use case	Read-heavy fields	Write-heavy or dynamic fields

Best Practices

Use Virtual Columns for Frequently Changing Data – Avoids recomputation on updates.
Use Stored Columns for Query Performance – Better for frequently queried computed values.
Avoid Complex Expressions – Keep generated expressions simple for faster reads.
Index Only Stored Columns – Virtual columns cannot be directly indexed.
Ensure Determinism – Expression must produce consistent results for the same input.

Conclusion

PostgreSQL 18 marks a significant milestone in database flexibility by introducing Virtual Generated Columns.
You can:

Save space
Improve DML performance
Enforce consistent logic at the schema level
Simplify querying derived values

For apps managing frequently updated derived data, large dynamic tables, or schema-enforced logic, this is a feature worth adopting right now.

What’s next for tomorrow?

PG18 Hacktober shifts gears from database features to DevOps. We’ll explore how to automate the setup of PostgreSQL 18 and configure replication using modern infrastructure-as-code techniques, the DevOps way.