pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddoubts
4+ messages / 4 participants
[nested] [flat]
* doubts
@ 2022-08-03 23:06 Thomaz Luiz Santos <thomaz.santos@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Thomaz Luiz Santos @ 2022-08-03 23:06 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: doubts
@ 2022-08-03 23:12 David G. Johnston <david.g.johnston@gmail.com>
parent: Thomaz Luiz Santos <thomaz.santos@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2022-08-03 23:12 UTC (permalink / raw)
To: Thomaz Luiz Santos <thomaz.santos@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: doubts
@ 2022-08-03 23:18 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Thomaz Luiz Santos <thomaz.santos@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Scott Ribe @ 2022-08-03 23:18 UTC (permalink / raw)
To: Thomaz Luiz Santos <thomaz.santos@gmail.com>; +Cc: pgsql-admin
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 ;-)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: doubts
@ 2022-08-04 07:27 Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bo Victor Thomsen @ 2022-08-04 07:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 ;-)
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-08-04 07:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-03 23:06 doubts Thomaz Luiz Santos <thomaz.santos@gmail.com>
2022-08-03 23:12 ` David G. Johnston <david.g.johnston@gmail.com>
2022-08-03 23:18 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-04 07:27 ` Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox