Faster alter table column type in RDS Postgres?

has anyone figured out faster way of doing postgres 14 column type change in RDS? We currently have a table that is running out of int4 space and multiple columns needs to be converted to int8. Table also has quite many foreign keys pointing to it and lot of indexes. On top of this, database cannot be taken down for maintenance for a long period of time.

Tried doing the type conversion on warmed up copy of the database, but it takes almost 3 hours which is way too much.

things already considered:

• take a snapshot, restore with huge amount of iops, make the conversion, reduce iops (won't work as freshly restored RDS postgres seems to be really slow with disk)

• increase current iops to max and do the conversion, reduce iops later (would cost ton of money and probably won't work as volume optimizing reduces performance, etc.)

• exporting & re-importing table data (might be really fast but then would need to drop and re-create indexes otherwise import would be slow)

Any other ideas? are there some postgres settings or tricks that could be used for making the process faster?

There is no way to make that fast. What you can try to do is to minimize the downtime:

-
add a new bigint column to the table

-
create a trigger that copies the data from the old integer primary key to the new column

-
update the new column for the existing rows in batches, running VACUUM between batches to avoid excessive bloat

-
create a unique index on the new column with CONCURRENTLY

-
create a unique constraint on the new column that uses the index

-
add a NOT VALID check constraint that makes sure that the new column is NOT NULL

-
use ALTER TABLE ... VALIDATE CONSTRAINT to validate the check constraint without locking the table to modifications

-
set the new column to NOT NULL – the check constraint avoids scanning the table

-
you can drop the check constraint now

-
create foreign key constraints to the new column (first NOT VALID, then validate them as above)

-
lock the table

-
change the new column to be an identity column and set the starting value high enough to avoid conflicts

-
drop the old column and all that depends on it

-
rename the new column

-
release the lock

StackExchange Database Administrators Q#322868, answer score: 3