Message-ID: <ccf901c2-c0ce-496c-0fc7-4b0d663c7f8f@gmail.com>
Date: Thu, 4 Aug 2022 09:27:33 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101
 Thunderbird/91.11.0
Subject: Re: doubts
Content-Language: en-GB
To: pgsql-admin@lists.postgresql.org
References: 
 <CAHtRPB6atN3u+dccSOjiJdYpNGEiTJqMrHCpZ4NZc+1F4jEaaw@mail.gmail.com>
 <9493A23B-213C-4479-82FD-1AAE18127655@elevated-dev.com>
From: Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
In-Reply-To: <9493A23B-213C-4479-82FD-1AAE18127655@elevated-dev.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ccf901c2-c0ce-496c-0fc7-4b0d663c7f8f%40gmail.com>
Precedence: bulk

Sorry for being "Captain Obvious" -

If you use this method, just remember to have the necessary storage 
capacity available for two versions of the table in question.

Med venlig hilsen / Kind regards

Bo Victor Thomsen

Den 04-08-2022 kl. 01:18 skrev Scott Ribe:
> On Aug 3, 2022, at 5:06 PM, Thomaz Luiz Santos <thomaz.santos@gmail.com> wrote:
>> I have one question: is it possible to minimize the downtime for this process ( because this table is large. ), using another strategy, like one view and updating the view ?
> Yes, using a view and redefining it after the new data is loaded would work. You could also:
>
> - load new data into a new table
> - begin transaction
> - drop old table
> - rename new table
> - commit
>
> The drop/rename dance executes very quickly because it's just manipulating catalog entries--with the caveat that dropping the table requires an exclusive lock for the obvious reason, so if you have a long-running transaction using that table, you can wind up waiting for it.
>
> Look at the docs for CREATE TABLE and the "LIKE" option, which gives you a shortcut to creating a table with the structure of an existing one.
>
> One peculiarity you might or might not care about: when you create your indexes on the new table, they will be named based on that table's name, and when you rename it the indexes don't get renamed. Personally, I am OK with "my_table_temp_some_idx" on "my_table", but if this offends your sensibilities, you can always rename the indexes ;-) and constraints ;-)
>
>
>
>