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>
In-Reply-To: <9fa75099-1ee9-43de-ac6e-945841b184eb@aklaver.com>
From: Marcelo Fernandes <marcefern7@gmail.com>
Date: Thu, 13 Feb 2025 11:04:57 +1300
Message-ID: <CAM2F1VONo8M9gcybhmf8-CdmRUC2QrFGZkZLYF-+uLZuhRKaxQ@mail.gmail.com>
Subject: Re: Best Approach for Swapping a Table with its Copy
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAM2F1VONo8M9gcybhmf8-CdmRUC2QrFGZkZLYF-%2BuLZuhRKaxQ%40mail.gmail.com>
Precedence: bulk

On Thu, Feb 13, 2025 at 10:40=E2=80=AFAM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> Do you know this will not fail on the existing data?

Yes, all the current data in the original table respects the constraint.

> Do you have room for a complete copy of the table?

Yes, in this scenario the copy is already created, and triggers keep the co=
py
in sync with the original table.

> I am not seeing how this can be done without some outage for that table.

Swapping tables without an outage is possible. I believe that this somethin=
g
involved in the process repacking a table when using pg_repack as it relies=
 on
copying the whole table and then swapping it for the original.

My main question is "How?". I know that the pg_repack involves swapping the
relfilenode values and something about TOAST tables, but I am not super
acquainted with pg_repack code or debugging tools to verify precisely what =
it
does.

> What sort of time frame is acceptable?

The scan phase in this table is very slow, on top of it the exclusion
constraint needs to create the underlying index to service the constraint.

Anything that takes more than 10s in this system is prohibitive, in this se=
nse
creating the constraint without having a table copy is not viable for the s=
ize
of this table.

Regards,