On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane <htamfids@xxxxxxxxx> wrote:
On Wed, Feb 12, 2025 at 9:02 PM Marcelo Fernandes <marcefern7@xxxxxxxxx> 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
