Exploring why PostgreSQL made generated columns virtual

Generated columns have existed in PostgreSQL since version 12. The idea was to allow people to make calculations based on other columns in the table and store them. It makes your life easier by ensuring data accuracy and preventing unauthorized manual modifications.

But why, after 6 versions, is PostgreSQL making generated columns virtual and also making this new way the default?

This is an example of the types of questions we had when exploring and playing around with this new feature. We therefore wrote this post to share what we found out to help you get your questions answered as well.

In a previous post on UUIDv7, we were putting together our crab store, where you can buy different types of crabs of all sizes. When you're buying crabs, you might need a place for them to live. Let's therefore add a table for aquariums for our crabs to live in.

As always, you can create a free Aiven for PostgreSQL® instance with the Aiven CLI to try it out for yourself.

avn service create -t pg cloud do-nyc --plan pg:free-1-1gb \
'crab-store' -c 'pg_version=18'

Now we'll create the aquariums table and randomly generate its dimensions.

-- Create a table called aquariums
-- with length, width, and height in millimeters

CREATE TABLE aquariums (
  id UUID default uuidv7() primary key,
  length float not null, 
  width float not null,
  height float not null
);

/* We need enough records to play around with, at least 1,000,000
should be enough
The aquariums are at least 100mmx100mmx100mm 
and no larger than 1m x .5m x .5m */

INSERT INTO aquariums (length, width, height)
SELECT
    ROUND((100 + random() * 9000)::numeric, 2) AS length,
    ROUND((100 + random() * 4900)::numeric, 2) AS width,
    ROUND((100 + random() * 4900)::numeric, 2) AS height
FROM generate_series(1,1_000_000);

-- We can see that the table takes up about 95 mb storage space
SELECT pg_size_pretty(pg_total_relation_size('aquariums'));

+----------------+
| pg_size_pretty |
|----------------|
| 95 MB         |
+----------------+

How do generated columns work traditionally?

Generated columns behave similarly to views, which allow you to create calculated columns based on other columns. However, instead of creating a new view, you just create a view-like column in the original table.

They also behave similarly to triggers, in the way that traditionally the calculation was triggered when you insert data into the table and stored on disk. Virtual columns change this, as we'll see in a bit.

First let's look at a traditional stored example where we add a generated column to our aquariums table.

If we want the area of these aquariums, we can calculate them based on the length, width and height.

-- This was the default for PG17 and lower
ALTER TABLE aquariums ADD COLUMN volumne FLOAT 
GENERATED ALWAYS AS (width * length * height) STORED; 
-- the 'STORED' flag writes the data to disk

Now, if we check the storage size of our aquariums table, we see it's about 8 mb larger than our original table.

-- Check the size of the aquariums table with the column added
SELECT pg_size_pretty(pg_total_relation_size('aquariums'));

+----------------+
| pg_size_pretty |
|----------------|
| 103 MB         |
+----------------+

Why make generated columns virtual?

This brings us to why it might make sense to make the generated columns virtual.

If we run the same example, but now making the column virtual instead, you can see the difference.

-- If you're following along with us,
-- Let's first remove the previous generated column
ALTER TABLE aquariums DROP COLUMN volume;

-- Let's then add it back in, but this time making it 'virtual'
ALTER TABLE aquariums ADD COLUMN volume FLOAT 
GENERATED ALWAYS AS (width * length * height); -- no 'stored' on this one

-- Check size of aquariums table
SELECT pg_size_pretty(pg_total_relation_size('aquariums'));

+----------------+
| pg_size_pretty |
|----------------|
| 95 MB          |
+----------------+

If you've been paying attention, you might remember that 95 mb was the size of our original table before we added the virtual column. This therefore shows us that just like with views, virtual generated columns can help you save storage space as they are calculated at run time.

This means that you can make a sensible tradeoff between storage and compute when needed.

How much are you saving, and is it worth the additional performance usage as the default?

The answer is, it depends... specifically, it depends on what is being generated and stored. Since in this example we're working with floats and doing simple mathematics, the storage savings are minimal.

Virtual columns are generated every time you read the column. Does this mean that you are taking a performance hit with each query? Not necessarily. PostgreSQL's optimizations and caching could actually make it just as fast to calculate on the fly as to retrieve.

That said, if you have several generated columns, tables with lots of rows, or generated columns that are heavy (perhaps stored text or JSON) you could save gigabytes of storage by generating those values on the fly, especially when your reads only focus on a subset of that data.

When should you use views instead of generated columns?

Generated columns have some limits according to the PostgreSQL documentation, such as:

• They can only refer to the table that the column is in.
• They cannot be created based on other generated columns.
• They cannot utilize user-created data types.
• They can't use functions (regardless of their contents).
• They can't be indexed.

Therefore, if you require any of the things above, it makes sense to use a view.

What if I want to delete a column that a depends on a generated column?

You'll first need to alter or delete the virtual column. You can do the two together using the CASCADE parameter.

Summary

Generated columns have been a great addition to PostgreSQL since version 12. Virtual generated columns make this feature even better by helping you reduce storage space by default, but also give you the flexibility to make your own tradeoff between storage and compute when needed.

For write-heavy workloads, virtual generated columns will help you achieve more write performance, whereas the traditional stored generated columns help you achieve more read performance.

Postgres 18 comes with a lot more exciting features, and you can leverage these exciting new features and explore what else Postgres 18 has to offer with Aiven today.