3

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?

7
  • Does your table hold close to 2^31 rows or did you burn most of the ID space somehow? Commented Feb 1, 2023 at 9:41
  • Closing on 2 billion rows.
    – anttik
    Commented Feb 1, 2023 at 9:43
  • "Many FKs poiting to it" is a pretty expensive design then ... Commented Feb 1, 2023 at 9:50
  • Correct. Foreign keys are pointing to wrong direction. This database design is quite old and bad.
    – anttik
    Commented Feb 1, 2023 at 9:52
  • I guess one option might be archiving and deleting lot of the old data, to make this change faster.
    – anttik
    Commented Feb 1, 2023 at 9:53

1 Answer 1

3

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

1
  • Thank you for in-depth answer. Need to think this through. Table has quite many columns that needs to be updated, many of them pointing to itself. Then bunch of triggers and foreign keys from another tables.
    – anttik
    Commented Feb 1, 2023 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.