public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dominique Devienne <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: Marcelo Fernandes <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: Best Approach for Swapping a Table with its Copy
Date: Thu, 13 Feb 2025 16:25:40 +0100
Message-ID: <CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit+fY5PpBFTvX927YC8w@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmmJ04kH_gWR+cOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q@mail.gmail.com>
References: <CAM2F1VP9nn0F6fSjbmhJCNSjv-dbAJcAytZ2HkTkZ0dGYZvxrw@mail.gmail.com>
<[email protected]>
<CAM2F1VOOn1izCrtcrDx4YUtCY-H64Vj5yvN5H1Lhk8kCY+_W6Q@mail.gmail.com>
<[email protected]>
<CAM2F1VONo8M9gcybhmf8-CdmRUC2QrFGZkZLYF-+uLZuhRKaxQ@mail.gmail.com>
<[email protected]>
<CAM2F1VMdDSxLhccn=0pXriJrR6a7Oe9Kir1DB_0pW1v88bsvCg@mail.gmail.com>
<CAKAnmmJ04kH_gWR+cOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q@mail.gmail.com>
On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane <[email protected]>
wrote:
> On Wed, Feb 12, 2025 at 9:02 PM Marcelo Fernandes <[email protected]>
> 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
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Best Approach for Swapping a Table with its Copy
In-Reply-To: <CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit+fY5PpBFTvX927YC8w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox