I have a huge number of large databases (300+!) that vary in size from teeny tiny to absolutely massive (120 GB+) in size. I've already determined a way to generate scripts to decide what indexes, foreign keys, primary keys, and default constraints need to be removed in order to convert a known set of columns from int to bigint data types.

The problem we're running into is on the larger databases, we're hitting 2-3 days runtime on this query, which isn't acceptable in production. Is there a method to make these conversions that is faster than the current drop-change-rebuild process?

  • 1
    It would help if you showed us what your conversion script is doing. – Hannah Vernon Jan 13 '17 at 16:16
  • @MaxVernon Its something like 2k lines of code? Would an outline of steps help? – Adam Wells Jan 13 '17 at 16:32
  • not the entire piece of code, just the parts that actually modify the tables. It might be useful for us to understand what you are actually doing to them. – Hannah Vernon Jan 13 '17 at 16:33
  • Take a look at Safe Operations For High Volume PostgreSQL. Some of the advice is not just Postgres-specific, and might apply to your case. – joanolo Jan 14 '17 at 12:24

I had a similar conversion of a key column from int to bigint that involved hundreds of tables, some over 100GB. I used an approach like the one below, which accomplished the migration a couple of hours against the large client databases. If the database is in the FULL recovery model, make sure sufficient transaction log space exists and/or perform log backups during the migration to avoid filling the log.

1) Drop referencing foreign keys on tables to be modified and schemabound objects

2) Optionally, drop all non-clustered indexes (including non-clustered primary key and non-clustered unique constraint indexes) on tables to be modified to reduce disk space requirements during the migration.

3) For each table, create new table using SELECT...INTO with an explicit CAST of the column to bigint, with ISNULL to coerce a NOT NULL column when necessary. Drop original table and rename new table to original name.

4) Recreate clustered indexes/constraints, non-clustered indexes/constraints, followed by other constraints and objects.

  • Can you clarify why you went with SELECT INTO? Was it to avoid scripting out a table create statement for each table or because you wanted to get parallelism on the insert and clustered index creation as opposed to a serial insert into a clustered index? – Joe Obbish Jan 14 '17 at 18:04
  • This seems like a good idea, but I'm going to have to check if I'm allowed to do this - they seem keen on keeping the same exact databases, if that makes any sense. IE, don't make anything new. – Adam Wells Jan 16 '17 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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