Re: Updating pg_attribute to change field's data type from integer to bigint on very large table

On 04/13/2012 08:30 AM, Jeff Adams wrote:

so i can? if so, how do i go about? i should mention that, while i dabble in postgres dba activity, it is not my day job...

That really depends on details and your concerns. Is the database used for constant insert/update/select activity or is it a big table used for analysis and can be taken offline for some period? Is the column you want to update a primary or foreign key? How much available disk space do you have? Is a large portion of the data static (historical logs)?

Some possible approaches:

1. Just let it run to completion if you can afford the maintenance time.

2. Add a new column of the appropriate type, copy the data into that column then drop the old one and rename the new one. If you do the update all at once you will have severe table bloat but you may be able to do the updates of the new column in batches so that vacuum can reclaim space between update batches. This approach may be useful if you do not have enough maintenance time to do the change all at once.

3. Dump the table data. Truncate the table and modify the column definition. Restore the data. This requires downtime but will probably be faster than in-place modification. However it's not something that you can easily cancel part-way through and not a friendly method if there are foreign-keys involved.

4. Rename the table and create a new table with the structure you want. Copy the old data back into the new table - perhaps in batches. This might be useful if you need to constantly keep collecting data but can afford a delay in analysis of the data.

If partitioning the table would be beneficial, this might be a good time to consider that as well.

Cheers,
Steve