Subject: Re: Best Approach for Swapping a Table with its Copy

From: Adrian Klaver <[email-masked]>
Date: Wed, 12 Feb 2025 13:02:44 -0800

On 2/12/25 12:57 PM, Marcelo Fernandes wrote:
> Hi folks,
> 
> I have a scenario where I need to swap an original table with a copy of that
> table.
> 
> The copy has an exclusion constraint that the original does not have. The main
> challenge is to ensure that the swap does not break any existing foreign keys
> to the original table and handles the associated TOAST tables correctly.
> 
> Both tables are fairly large and exist in a system where there are no
> maintenance time windows where the application is shut down.

This needs more information:

1) Postgres version.

2) The table definition.

3) The exclusion constraint definition.

4) Definition of what 'fairly large' is.

5) How is the application interfacing with the database?

> 
> My key questions are:
> 
> - What is the best process for swapping the original table with the copy in
>    such a way that the foreign key relations are preserved?
> 
> - Are there any special considerations for managing the TOAST tables during
>    this swap?
> 
> - Should I perform this operation in multiple steps, or is there a
>    straightforward way to achieve this atomically?
> 
> - Are there any risks of potential issues I should be aware of when doing this
>    swap? Specifically related to foreign key integrity and TOAST data?
> 
> Thank you!
>    - Marcelo
> 
> 

-- 
Adrian Klaver
[email-masked]

---

From: Marcelo Fernandes <[email-masked]>
Date: Thu, 13 Feb 2025 10:27:55 +1300

> This needs more information:

That would be for Postgres 15.

> 2) The table definition.

This is a simplified version of the original table:

CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    resource_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);

> 3) The exclusion constraint definition.

The copy table would have an exclusion constraint such as:

ALTER TABLE bookings
ADD CONSTRAINT no_date_overlap_for_resource_id
EXCLUDE USING gist (
    resource_id WITH =,
    daterange(start_date, end_date, '[]') WITH &&
);

> 4) Definition of what 'fairly large' is.

This table is over 400GB

> 5) How is the application interfacing with the database?

This is a web application that interfaces with the database using psycopg.

Also pulling in your question in the other reply:

> Why can't you just add the exclusion constraint to the original table?

With unique constraints, one can use a unique index to create the constraint
concurrently.

With check constraints, one can create the constraint as invalid and then
validate it while only requiring a share update exclusive lock.

But with exclusion constraints, neither of those techniques are available. In
that sense, there is no way to create this type of constraint in a large table
without copying the original table, adding the constraint, and performing a
table swap.

This is done to avoid having to hold an exclusive lock for a long amount of
time, thus creating application outages.

Hope that clarifies the situation a bit better
  - Marcelo

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 12 Feb 2025 13:40:25 -0800

>      daterange(start_date, end_date, '[]') WITH &&

Do you know this will not fail on the existing data?

>> 4) Definition of what 'fairly large' is.

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

> time, thus creating application outages.

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

What sort of time frame is acceptable?

---

From: Marcelo Fernandes <[email-masked]>
Date: Thu, 13 Feb 2025 11:04:57 +1300

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

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

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.

Regards,

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 12 Feb 2025 16:33:12 -0800

> in sync with the original table.

To confirm, this copy has the exclusion constraint defined?

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

> creating the constraint without having a table copy is not viable for the size

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

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 13 Feb 2025 07:37:50 +0100

On Thu, 2025-02-13 at 11:04 +1300, Marcelo Fernandes wrote:
> Swapping tables without an outage is possible.

Yes, but only if you are willing to write C code that runs inside the
database server.  That way, you can do anything (and cause arbitrary
damage).

The big challenge here would be to do the swap in a safe way.  How do
you intend to guarantee that the foreign keys are valid without a table
scan?  How do you handle concurrent data modifications?

Yours,
Laurenz Albe

-- 

*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den 
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat 
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte, 
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder 
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich 
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.

*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are 
confidential and may be privileged or otherwise protected from disclosure 
and solely for the use of the person(s) or entity to whom it is intended. 
If you have received this message in error and are not the intended 
recipient, please notify the sender immediately and delete this message and 
any attachment from your system. If you are not the intended recipient, be 
advised that any use of this message is prohibited and may be unlawful, and 
you must not copy this message or attachment or disclose the contents to 
any other person.

---

From: Michał Kłeczek <[email-masked]>
Date: Fri, 14 Feb 2025 07:01:55 +0100

> On 12 Feb 2025, at 22:27, Marcelo Fernandes <[email-masked]> wrote:
> 

Just a wild idea (not sure if anyone suggested it in this thread and not sure if it is doable):

Create index concurrently and then fiddle with the catalog tables to define the constraint using this index?

—
Michal

---

From: Greg Sabino Mullane <[email-masked]>
Date: Fri, 14 Feb 2025 10:19:57 -0500

> define the constraint using this index?

You mean an ALTER TABLE ... ADD CONSTRAINT ... EXCLUDE without actually
doing an ALTER TABLE. Nope, that's far worse than the pg_repack
shenanigans, as we would be creating new catalog objects.

Ideally would be that someone adds support for USING INDEX for an exclusion
constraint.

Cheers,
Greg

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