Re: [Feature Request] INSERT FROZEN to Optimize Large Cold Data Imports and Migrations - Mailing list pgsql-hackers

From Greg Sabino Mullane
Subject Re: [Feature Request] INSERT FROZEN to Optimize Large Cold Data Imports and Migrations
Date
Msg-id CAKAnmmLdw882syy5vNwnGFjvyvtoqBNVK4iujTsNt1qpY47uUQ@mail.gmail.com
Whole thread Raw
In response to Re: [Feature Request] INSERT FROZEN to Optimize Large Cold Data Imports and Migrations  (Sébastien <bokanist@gmail.com>)
List pgsql-hackers
On Fri, Feb 14, 2025 at 1:41 AM Sébastien <bokanist@gmail.com> wrote:
I had huge problems on server 3 weeks after a 6 TB migration from other DB. I think it's sad to rewrite all data twice.

You could always COPY FREEZE into a new table, then move any remaining rows, and finally rename the tables. Should be a win if the majority of the table is newly-imported data. Another thing you might look at is increasing your three week forced freeze window by adjusting params and/or decreasing the number of transactions your cluster is using. (granted, still the same overall write volume). You could also do manual vacuum freezes at a time better for you (since you mention autovac is doing this at unpredictable times).

Cheers,
Greg

--
Enterprise Postgres Software Products & Tech Support

pgsql-hackers by date:

Previous
From: Sami Imseih
Date:
Subject: Re: pg_stat_statements and "IN" conditions
Next
From: Nathan Bossart
Date:
Subject: Re: describe special values in GUC descriptions more consistently