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

[Jeff Adams <jeff.adams@xxxxxxxx>]

Thanks for the ideas Steve. I am actually working with a partitioned table and the field I am modifying is the id field (I have reached the cap on the integer data type and need to modify it to bigint - very poor planning on my part!), but no related tables exist. The id field in the partitioned tables is inherited, so I figured I needed to alter the column in the parent table. Does this information point towards an optimal solution? Thanks again...

On Fri, Apr 13, 2012 at 12:31 PM, Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote:

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

-- 
Jeffrey D. Adams
National Marine Fisheries Service
Office of Protected Resources
1315 East West Hwy, Building SSMC3
Silver Spring, MD 20910-3282
phone: (301) 427-8434
fax: (301) 713-0376