Duration of PostgreSQL ALTER COLUMN TYPE int to bigint

Question

Let's say I have a table that has an id that is an INTEGER GENERATED BY DEFAULT AS IDENTITY

I'm looking to document how to change the type, if in the future an integer is too small and I need to change the id type from integer to bigint. I'm mainly worried about the time complexity of the change, since it will likely occur when there number of rows in the table would be near the maximum number an integer type can store.

What would the time complexity for the following command be?

ALTER TABLE project ALTER COLUMN id TYPE BIGINT;

Answer 1

This command will have to rewrite the whole table, because bigint takes 8 bytes of storage rather than the 4 of an integer. The table will be locked from concurrent access while this is taking place, so with a big table you should be prepared for a longer downtime.

If you expect that this could be necessary, perform the change as soon as possible, while the table is still small.