Faster alter table column type in RDS Postgres?

Question

Hello,

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?

Answer

Hello, Couple of options I would recommend that works for any RDBMS:
1.  Cast the column datatype and CTAS from the source(original) table into a new target table. Drop original table and rename new table to old. Ofcourse indexes have to be created on new table again. Quick and easy.
2. Export table data to S3. Empty the original table and modify the Datatype. Import the data back from S3 to the table again. You may refer below AWS Documentation for this approach:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postgresql-s3-export.html
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html

Good Luck with your next steps.

Faster alter table column type in RDS Postgres?

Relevant content