MIME-Version: 1.0
References: <CAM2F1VP9nn0F6fSjbmhJCNSjv-dbAJcAytZ2HkTkZ0dGYZvxrw@mail.gmail.com>
 <8ac6a1dd-db82-4ea7-8186-0c354d79049f@aklaver.com> <CAM2F1VOOn1izCrtcrDx4YUtCY-H64Vj5yvN5H1Lhk8kCY+_W6Q@mail.gmail.com>
 <9fa75099-1ee9-43de-ac6e-945841b184eb@aklaver.com> <CAM2F1VONo8M9gcybhmf8-CdmRUC2QrFGZkZLYF-+uLZuhRKaxQ@mail.gmail.com>
 <22406cdf-83f5-4b98-b07e-7827afe97f93@aklaver.com> <CAM2F1VMdDSxLhccn=0pXriJrR6a7Oe9Kir1DB_0pW1v88bsvCg@mail.gmail.com>
 <CAKAnmmJ04kH_gWR+cOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q@mail.gmail.com>
In-Reply-To: <CAKAnmmJ04kH_gWR+cOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Thu, 13 Feb 2025 16:25:40 +0100
Message-ID: <CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit+fY5PpBFTvX927YC8w@mail.gmail.com>
Subject: Re: Best Approach for Swapping a Table with its Copy
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Marcelo Fernandes <marcefern7@gmail.com>, Adrian Klaver <adrian.klaver@aklaver.com>, 
	pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007b9cb0062e07ac05"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit%2BfY5PpBFTvX927YC8w%40mail.gmail.com>
Precedence: bulk

--0000000000007b9cb0062e07ac05
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Feb 13, 2025 at 4:09=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail=
.com>
wrote:

> On Wed, Feb 12, 2025 at 9:02=E2=80=AFPM Marcelo Fernandes <marcefern7@gma=
il.com>
> wrote:
>
>> What I am after is the same, but I seek a deeper understanding of what i=
t
>> 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. Th=
e
> 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

--0000000000007b9cb0062e07ac05
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Feb 13, 2025 at 4:09=E2=80=AFPM G=
reg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail=
.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr">On Wed, Feb 12, 2025 at 9:02=E2=80=AFPM Marcelo Fernandes &lt;<a h=
ref=3D"mailto:marcefern7@gmail.com" target=3D"_blank">marcefern7@gmail.com<=
/a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex">What I am after is the same, but I seek a deeper unde=
rstanding of what it does, and why it does it. For example, it swaps relfil=
enode. Why?</blockquote><div><br></div><div>It is surgically replacing all =
pointers to the old data with pointers to the new data. Yes, with lots of s=
ystem catalog shenanigans.=C2=A0</div><div>pg_repack is meant to do what va=
cuum full does, but in a faster way. Imagine your table is an 18-wheeler tr=
uck, with a cab (system catalog stuff) and a trailer (full of data). We don=
&#39;t want a whole new truck, we want to change out the trailer.</div><div=
>With VACUUM FULL, you stop all traffic while you pull the truck to the sid=
e of the road and turn it off. A new truck is pulled alongside it, and ever=
ything 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.</=
div><div>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. A=
t the last moment, all the=C2=A0wires are pulled from the old trailer and h=
ooked to the new trailer. The old trailer is detached and left to crash int=
o the mutant bikers who have been pursuing you. It&#39;s the same cab, but =
the trailer (e.g. relfilenodes) has been changed.</div><div>It&#39;s techni=
cally possible to do something similar for your use case, but it&#39;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.</div><=
/div></div></blockquote><div><br></div><div>Thanks for the colorful analogy=
 Greg :).</div><div><br></div><div>Maybe the better option is to support AL=
TER TABLE to ADD an exclusion constraint, no?</div><div>I get that it&#39;s=
 not support now. But is it more difficult than the above? And why then? --=
DD</div></div></div>

--0000000000007b9cb0062e07ac05--