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