Changing POSTGRES column datatype - should I drop indexes and then re-create?

Question

I have a number of postgres tables with a column of type INT which I need to change to BIGINT. Each partition of the table contains about 100 million rows, my intention is to detach the individual partitions ALTER the column type, and then re-attach.

My question is this, the column that needs updating is indexed. Is likely to be quicker to drop the index, alter the column type and then re-create the index? Or should I leave the index in place and just change the column type?

note that the column values aren't going to change, just the datatype (not sure if that's relevant or not!)

thanks

Answer 1

The ALTER will automatically drop and rebuild the indexes (all indexes, not just on the column being altered). There is no point in micromanaging the process yourself, unless you want to build the indexes CONCURRENTLY or something like that, so that that part can be done outside the maintenance window.

Here is an extract from the docs for SET DATA TYPE:

This form changes the type of a column of a table. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression.

[...]

When this form is used, the column's statistics are removed, so running ANALYZE on the table afterwards is recommended.