On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane <htamf...@gmail.com> wrote:
> On Wed, Feb 12, 2025 at 9:02 PM Marcelo Fernandes <marcefe...@gmail.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
