23

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;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Deniablesummer
  • 260
  • 1
  • 2
  • 11

1 Answers1

19

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Is this still the case in Postgres 12? The [docs](https://www.postgresql.org/docs/12/sql-altertable.html) state: _Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt._ – Matthias Lohscheidt Feb 12 '21 at 16:31
  • 1
    @MatthiasLohscheidt A data type of size 4 is never binary coercible to a data type of length 8. Binary coercible means that the storage format is identical. There are more complicated ways to achieve the goal that require no down time, but not a simple `ALTER TABLE`. – Laurenz Albe Feb 13 '21 at 16:32
  • Thanks for the clarification @Laurenz Albe! I wasn't sure what "unconstrained domain" meant, but now I guess it refers to [domain types](https://www.postgresql.org/docs/12/domains.html) (which are still binary coercible in the end). – Matthias Lohscheidt Feb 15 '21 at 10:05