adding generated stored columns on large tables in Postgres

[dbernheisel]

reference: https://stackoverflow.com/questions/77852268/how-to-add-a-stored-generated-column-to-a-very-large-table

tldr, when adding a generated stored column to an existing large table, it will lock the table so it can calculate the value for each row.

There seems to be a workaround:

1. Add the column as a normal nullable column
2. use a trigger BEFORE INSERT OR UPDATE with a function that is equivalent to what you would put as the as generated expression.
3. Backfill to fill the column
4. In Postgres 17, it may be possible to alter the column to set an expression, allowing you to drop the trigger.

However, there seems to be a trade-off in that INSERT times are slower with a triggered function. source (4yrs old, for postgres v12): https://www.ongres.com/blog/generate_columns_vs_triggers/

CREATE OR REPLACE FUNCTION generate_foo_immutable ()
    RETURNS TRIGGER
    AS $$
BEGIN
    NEW.foo = NEW.bar * 2;
    RETURN new;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE TRIGGER generate_foo_immutable_trigger
BEFORE INSERT OR UPDATE [OF other_column1, other_column2, ...] ON foo_table
FOR EACH ROW 
EXECUTE PROCEDURE public.generate_foo_immutable();