Faster alter table column type in RDS Postgres?

0

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?

已提問 1 年前檢視次數 639 次
1 個回答
0

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.

AWS
已回答 1 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南