Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane <htamfids(at)gmail(dot)com>
wrote:

> On Wed, Feb 12, 2025 at 9:02 PM Marcelo Fernandes <marcefern7(at)gmail(dot)com>
> wrote:
>
>> What I am after is the same, but I seek a deeper understanding of what it
>> does, and why it does it. For example, it swaps relfilenode. Why?
>
>
> It is surgically replacing all pointers to the old data with pointers to
> the new data. Yes, with lots of system catalog shenanigans.
> pg_repack is meant to do what vacuum full does, but in a faster way.
> Imagine your table is an 18-wheeler truck, with a cab (system catalog
> stuff) and a trailer (full of data). We don't want a whole new truck, we
> want to change out the trailer.
> With VACUUM FULL, you stop all traffic while you pull the truck to the
> side of the road and turn it off. A new truck is pulled alongside it, and
> everything from the old trailer is unloaded and placed in the new one. The
> new trailer is hooked to the cab, and pulls away into the now-moving
> traffic.
> With pg_repack, you keep driving full speed. A new truck pulls up
> alongside your truck, and the new trailer is filled based on the old one.
> At the last moment, all the wires are pulled from the old trailer and
> hooked to the new trailer. The old trailer is detached and left to crash
> into the mutant bikers who have been pursuing you. It's the same cab, but
> the trailer (e.g. relfilenodes) has been changed.
> It's technically possible to do something similar for your use case, but
> it's not trivial. All the cab to trailer wires must be precisely changed.
> Everything directly related to the data must be swapped: heap, indexes,
> toast.
>

Thanks for the colorful analogy Greg :).

Maybe the better option is to support ALTER TABLE to ADD an exclusion
constraint, no?
I get that it's not support now. But is it more difficult than the above?
And why then? --DD