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개 답변
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
답변함 일 년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠