Thread: doubts
Hi everybody!
scenari:
I need to refresh the data in one table, but I use one truncate to clean the data and insert again the new data,
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 ?
Thank you
best regards.
--
------------------------------
Thomaz Luiz Santos
Linux User: #359356
http://thomaz.santos.googlepages.com/
Thomaz Luiz Santos
Linux User: #359356
http://thomaz.santos.googlepages.com/
On Wed, Aug 3, 2022 at 4:06 PM Thomaz Luiz Santos <thomaz.santos@gmail.com> wrote:
I need to refresh the data in one table, but I use one truncate to clean the data and insert again the new data,
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 ?
Temporary and/or unlogged tables?
Truncate and insert is the fastest you can do if every record has to change.
Otherwise, "maybe" is about the best you should expect if you are going to be that generic and non-specific regarding "process".
David J.
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 anotherstrategy, 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 droppingthe table requires an exclusive lock for the obvious reason, so if you have a long-running transaction using thattable, 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 structureof 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 basedon 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 ;-) andconstraints ;-)
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. ), usinganother 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 droppingthe table requires an exclusive lock for the obvious reason, so if you have a long-running transaction using thattable, 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 structureof 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 basedon 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 ;-) andconstraints ;-) > > > >