Feedback-ID: i76984098:Fastmail
Message-ID: <22406cdf-83f5-4b98-b07e-7827afe97f93@aklaver.com>
Date: Wed, 12 Feb 2025 16:33:12 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Best Approach for Swapping a Table with its Copy
To: Marcelo Fernandes <marcefern7@gmail.com>
Cc: pgsql-general@lists.postgresql.org
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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAM2F1VONo8M9gcybhmf8-CdmRUC2QrFGZkZLYF-+uLZuhRKaxQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/22406cdf-83f5-4b98-b07e-7827afe97f93%40aklaver.com>
Precedence: bulk

On 2/12/25 14:04, Marcelo Fernandes wrote:
> On Thu, Feb 13, 2025 at 10:40 AM 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 copy
> in sync with the original table.

To confirm, this copy has the exclusion constraint defined?

> 
>> 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 something
> 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.

Not seeing it:

https://reorg.github.io/pg_repack/

"Details
Full Table Repacks

[...]

pg_repack will only hold an ACCESS EXCLUSIVE lock for a short period 
during initial setup (steps 1 and 2 above) and during the final 
swap-and-drop phase (steps 6 and 7). For the rest of its time, pg_repack 
only needs to hold an ACCESS SHARE lock on the original table, meaning 
INSERTs, UPDATEs, and DELETEs may proceed as usual."

During the ACCESS EXCLUSIVE stages you will not have access.

Not only that with pg_repack you are not changing the table definition, 
whereas in your case you are introducing a new constraint and associated 
index.

> 
> 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 sense
> creating the constraint without having a table copy is not viable for the size
> of this table.

Do you have a dev setup where you can test alternatives with a test 
sample of data?


> 
> Regards,

-- 
Adrian Klaver
adrian.klaver@aklaver.com