public inbox for [email protected]help / color / mirror / Atom feed
Re: Best Approach for Swapping a Table with its Copy 8+ messages / 5 participants [nested] [flat]
* Re: Best Approach for Swapping a Table with its Copy @ 2025-02-12 21:02 Adrian Klaver <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Adrian Klaver @ 2025-02-12 21:02 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; [email protected] 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 protected] ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Approach for Swapping a Table with its Copy @ 2025-02-12 21:27 Marcelo Fernandes <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Marcelo Fernandes @ 2025-02-12 21:27 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: [email protected] On Thu, Feb 13, 2025 at 10:02 AM Adrian Klaver <[email protected]> wrote: > This needs more information: > 1) Postgres version. 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Approach for Swapping a Table with its Copy @ 2025-02-12 21:40 Adrian Klaver <[email protected]> parent: Marcelo Fernandes <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Adrian Klaver @ 2025-02-12 21:40 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; +Cc: [email protected] On 2/12/25 1:27 PM, Marcelo Fernandes wrote: > On Thu, Feb 13, 2025 at 10:02 AM Adrian Klaver > 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 && > ); Do you know this will not fail on the existing data? > >> 4) Definition of what 'fairly large' is. > > This table is over 400GB Do you have room for a complete copy of the table? > > This is done to avoid having to hold an exclusive lock for a long amount of > 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? > > Hope that clarifies the situation a bit better > - Marcelo -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Approach for Swapping a Table with its Copy @ 2025-02-12 22:04 Marcelo Fernandes <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Marcelo Fernandes @ 2025-02-12 22:04 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: [email protected] On Thu, Feb 13, 2025 at 10:40 AM Adrian Klaver <[email protected]> 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. > 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, ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Approach for Swapping a Table with its Copy @ 2025-02-13 00:33 Adrian Klaver <[email protected]> parent: Marcelo Fernandes <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: Adrian Klaver @ 2025-02-13 00:33 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; +Cc: [email protected] On 2/12/25 14:04, Marcelo Fernandes wrote: > On Thu, Feb 13, 2025 at 10:40 AM Adrian Klaver > <[email protected]> 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 [email protected] ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Approach for Swapping a Table with its Copy @ 2025-02-13 06:37 Laurenz Albe <[email protected]> parent: Marcelo Fernandes <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: Laurenz Albe @ 2025-02-13 06:37 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; Adrian Klaver <[email protected]>; +Cc: [email protected] On Thu, 2025-02-13 at 11:04 +1300, Marcelo Fernandes wrote: > > I am not seeing how this can be done without some outage for that table. > > 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. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Approach for Swapping a Table with its Copy @ 2025-02-14 06:01 Michał Kłeczek <[email protected]> parent: Marcelo Fernandes <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Michał Kłeczek @ 2025-02-14 06:01 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected] > On 12 Feb 2025, at 22:27, Marcelo Fernandes <[email protected]> wrote: > > On Thu, Feb 13, 2025 at 10:02 AM Adrian Klaver > > 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. > 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Approach for Swapping a Table with its Copy @ 2025-02-14 15:19 Greg Sabino Mullane <[email protected]> parent: Michał Kłeczek <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Greg Sabino Mullane @ 2025-02-14 15:19 UTC (permalink / raw) To: Michał Kłeczek <[email protected]>; +Cc: Marcelo Fernandes <[email protected]>; Adrian Klaver <[email protected]>; [email protected] On Fri, Feb 14, 2025 at 1:02 AM Michał Kłeczek <[email protected]> wrote: > Create index concurrently and then fiddle with the catalog tables to > 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-02-14 15:19 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-02-12 21:02 Re: Best Approach for Swapping a Table with its Copy Adrian Klaver <[email protected]> 2025-02-12 21:27 ` Marcelo Fernandes <[email protected]> 2025-02-12 21:40 ` Adrian Klaver <[email protected]> 2025-02-12 22:04 ` Marcelo Fernandes <[email protected]> 2025-02-13 00:33 ` Adrian Klaver <[email protected]> 2025-02-13 06:37 ` Laurenz Albe <[email protected]> 2025-02-14 06:01 ` Michał Kłeczek <[email protected]> 2025-02-14 15:19 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox