Re: Thoughts on how to avoid a massive integer update.

On 5/4/20 2:32 PM, Fehrle, Brian wrote:
> Hi all,
> 
> This is a shot in the dark in hopes to find a magic bullet to fix an 
> issue I have, I can’t personally think of any solution myself.
> 
> I have a database with hundreds of terabytes of data, where every table 
> has an integer column referencing a small table. For reasons out of my 
> control and cannot change, I NEED to update every single row in all 
> these tables, changing the integer value to a different integer.
> 
> Since I have to deal with dead space, I can only do a couple tables at a 
> time, then do a vacuum full after each one.

Why?
A regular vacuum would mark the space as available.

More below.

> Another option is to build a new table with the new values, then drop 
> the old one and swap in the new, either way is very time consuming.
> 
> Initial tests suggest this effort will take several months to complete, 
> not to mention cause blocking issues on tables being worked on.
> 
> Does anyone have any hackery ideas on how to achieve this in less time? 
> I was looking at possibly converting the integer column type to another 
> that would present the integer differently, like a hex value, but 
> everything still ends up requiring all data to be re-written to disk. In 
> a well designed database (I didn’t design it :) ), I would simply change 
> the data in the referenced table (200 total rows), however the key being 
> referenced isn’t just an arbitrary ID, it’s actual ‘data’, and must be 
> changed.

I'm not following above.

Could you show an example table relationship?


> 
> Thanks for any thoughts or ideas,
> 
>   * Brian F
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com