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

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(at)pinpointresearch(dot)com> 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