PostgreSQL 18 introduces a very useful feature: VIRTUAL generated columns. These columns compute values on-the-fly at query time instead of storing them on disk, giving you the flexibility to choose between storage efficiency and query performance.

This is the third post in our series exploring PostgreSQL 18 new features, released about a month ago. If you want to catch up on the series, here are the previous posts:

• PG 18 - UUIDv7
• PG 18 - OLD and NEW for tracking UPSERTs Understanding Generated Columns

PostgreSQL has supported GENERATED columns since version 12. With that we can automatically compute values based on expressions defined at table creation or alteration. Up to PostgreSQL 17, these columns were always STORED - meaning values were computed when rows were inserted or updated and physically stored on disk, just like any other column.

PostgreSQL 18 changes the game by introducing the VIRTUAL modifier, and they set it as the new default for generated columns. Virtual columns don’t use any disk space because their values are computed at query time, only when needed.

VIRTUAL Columns in Action

Let’s create a users table that demonstrates the difference between virtual and stored generated columns:

CREATE TABLE users (
id uuid DEFAULT uuidv7() PRIMARY KEY,
email VARCHAR(100) UNIQUE NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
full_name_virtual VARCHAR GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL,
full_name_stored VARCHAR GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
full_name_indexed VARCHAR GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX users_full_name_indexed ON users (full_name_indexed);

In this example, we’ve created three full_name columns with identical implementations:

1. full_name_virtual - Computed at query time, no disk storage
2. full_name_stored - Computed at insert/update time, stored on disk
3. full_name_indexed - Stored on disk with an index for fast lookups

Note that you cannot create an index directly on a virtual generated column since the value isn’t stored. This limitation is important when deciding which strategy to use.

Populating Test Data

Let’s insert a million rows to see how these different approaches perform:

INSERT INTO users (email, first_name, last_name)
SELECT
(array_sample(ARRAY['green', 'yellow', 'red', 'blue'], 1))[1] || '+' || i || '@example.com',
(array_sample(ARRAY['Emma','Liam','Noah','Mia','Ava'], 1))[1] || ' ' || i,
(array_sample(ARRAY['Smith','Brown','Jones','Davis','Lopez'], 1))[1] || ' ' || i
FROM generate_series(1, 1000000) i;

Now let’s query the data:

SELECT first_name, last_name, full_name_virtual FROM users ORDER BY id DESC LIMIT 2;

Output:

email	first_name	last_name	full_name_virtual
blue+1@example.com	Mia 1	Lopez 1	Mia 1 Lopez 1
green+2@example.com	Emma 2	Smith 2	Emma 2 Smith 2

All three generated columns (full_name_virtual, full_name_stored, and full_name_indexed) produce identical results. The magic happens behind the scenes.

Choosing the Right Strategy

The decision between VIRTUAL and STORED generated columns depends on several factors:

Use VIRTUAL when:

• The computation is simple and fast
• You only need the value for display purposes
• You want to minimize disk usage

Use STORED when:

• The computation is complex or expensive
• You frequently filter or sort by the generated column
• Query performance is more critical than storage space Performance Considerations

To analyze query performance with virtual columns, you can use EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT *
FROM users
WHERE full_name_virtual ILIKE 'L%'
ORDER BY full_name_virtual DESC
LIMIT 1000;

This will show you the execution plan and actual timing, helping you make informed decisions about whether to keep columns virtual or convert them to stored (and potentially indexed) columns.

We Can Help

At Hashrocket, we specialize in building robust, high-performance applications with modern technologies. Whether you need help optimizing your PostgreSQL database, leveraging new features like virtual generated columns, or building full-stack applications, we’ve got you covered.

Our team has deep expertise in PostgreSQL and other databases, Elixir, Phoenix, Ruby on Rails, React and React Native

If you’re looking to modernize your database architecture, optimize query performance, or build scalable applications from the ground up, let’s talk. We’d love to help you leverage the latest technologies to build something great.

We’re proud to have launched hundreds of products for clients such as LensRentals.com, Engine Yard, Verisign, ParkWhiz, and Regions Bank, to name a few.

Let’s talk about your project