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>
In-Reply-To: <CAM2F1VMdDSxLhccn=0pXriJrR6a7Oe9Kir1DB_0pW1v88bsvCg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 13 Feb 2025 10:08:27 -0500
Message-ID: <CAKAnmmJ04kH_gWR+cOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q@mail.gmail.com>
Subject: Re: Best Approach for Swapping a Table with its Copy
To: Marcelo Fernandes <marcefern7@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000359042062e077000"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmJ04kH_gWR%2BcOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q%40mail.gmail.com>
Precedence: bulk

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

On Wed, Feb 12, 2025 at 9:02=E2=80=AFPM Marcelo Fernandes <marcefern7@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.

Cheers,
Greg

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Feb 12, 2025 at 9:02=E2=80=AFPM M=
arcelo Fernandes &lt;<a href=3D"mailto:marcefern7@gmail.com">marcefern7@gma=
il.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=
-left:1px solid rgb(204,204,204);padding-left:1ex">What I am after is the s=
ame, but I seek a deeper understanding of what it does, and why it does it.=
 For example, it swaps relfilenode. 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 system catalog shenanigans.=C2=A0</div><div><br=
></div><div>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&#39;t want a whole new truck, =
we want to change out the trailer.</div><div><br></div><div>With VACUUM FUL=
L, you stop all traffic while you pull the truck to the side of the road an=
d 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 hooke=
d to the cab, and pulls away into the now-moving traffic.</div><div><br></d=
iv><div>With pg_repack, you keep driving full speed. A new truck pulls up a=
longside your truck, and the new trailer is filled based on the old one. At=
 the last moment, all the=C2=A0wires are pulled from the old trailer and ho=
oked to the new trailer. The old trailer is detached and left to crash into=
 the mutant bikers who have been pursuing you. It&#39;s the same cab, but t=
he trailer (e.g. relfilenodes) has been changed.</div><div><br></div><div>I=
t&#39;s technically possible to do something similar for your use case, but=
 it&#39;s not trivial. All the cab to trailer wires must be precisely chang=
ed. Everything directly related to the data must be swapped: heap, indexes,=
 toast.</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></div=
></div><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>--<=
/div><div>Crunchy Data - <a href=3D"https://www.crunchydata.com" target=3D"=
_blank">https://www.crunchydata.com</a></div><div>Enterprise Postgres Softw=
are Products &amp; Tech Support</div><div><br></div></div></div></div>

--000000000000359042062e077000--