Re: Best Approach for Swapping a Table with its Copy

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.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support