Postgres GENERATED ALWAYS performance when adding new column

Question

I have a fairly big PostgreSQL 13 table that I need to add a new TEXT column and with this new column I would like to add a GENERATED ALWAYS column with the following definition:

ALTER TABLE my_table
ADD COLUMN text_col TEXT,
ADD COLUMN text_col__tsvector GENERATED ALWAYS AS (to_tsvector('pt_unaccent', "text_col")) STORED;

However when I run the above DDL it takes a long time to complete. As I understand that is because the text_col__tsvector need to run the expression for all rows in the table even if the text_col is NULL.

My question is: knowing that the text_col column will always start as NULL (no default value is defined) is there a way to make postgres skip the first generation part in the creation of the text_col__tsvector? Or change the expression part (to_tsvector('pt_unaccent', "text_col")) of the GENERATED ALWAYS column to ignore NULL values?

Answer 1

Currently (postgresql 16) only stored generated columns are implemented and adding such a column always performs full table rewrite with the calculated and populated value. There is no way to avoid such table rewrite.

You can populate a field's value the old-fashioned way, using a trigger rather than a generated column. It is also possible that you do not need new column at all, but a index on expression. An index on expression can be built concurrently.