Jay is a Staff Developer Advocate at Aiven. Jay has served as a keynote speaker and an avid member of the Python Community. When away from the keyboard, Jay can often be found cheering on their favorite baseball team.
Alexander does product marketing for databases at Aiven. Throughout his career he's worked across marketing, sales, analytics, data engineering and more.
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.
Loading code...
Now we'll create the aquariums table and randomly generate its dimensions.
Loading code...
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.
Loading code...
Now, if we check the storage size of our aquariums table, we see it's about 8 mb larger than our original table.
Loading code...
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.
Loading code...
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?
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.
-- Create a table called aquariums-- with length, width, and height in millimetersCREATETABLE aquariums ( id UUID default uuidv7()primarykey, length floatnotnull, width floatnotnull, height floatnotnull);/* 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 */INSERTINTO aquariums (length, width, height)SELECTROUND((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 spaceSELECT pg_size_pretty(pg_total_relation_size('aquariums'));+----------------+| pg_size_pretty ||----------------||95 MB |+----------------+
-- This was the default for PG17 and lowerALTERTABLE aquariums ADDCOLUMN volumne FLOATGENERATED ALWAYS AS(width * length * height) STORED;-- the 'STORED' flag writes the data to disk
-- Check the size of the aquariums table with the column addedSELECT pg_size_pretty(pg_total_relation_size('aquariums'));+----------------+| pg_size_pretty ||----------------||103 MB |+----------------+
-- If you're following along with us,-- Let's first remove the previous generated columnALTERTABLE aquariums DROPCOLUMN volume;-- Let's then add it back in, but this time making it 'virtual'ALTERTABLE aquariums ADDCOLUMN volume FLOATGENERATED ALWAYS AS(width * length * height);-- no 'stored' on this one-- Check size of aquariums tableSELECT pg_size_pretty(pg_total_relation_size('aquariums'));+----------------+| pg_size_pretty ||----------------||95 MB |+----------------+
