From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Thomaz Luiz Santos <thomaz.santos@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: doubts
Date: Wed, 3 Aug 2022 17:18:41 -0600
Message-ID: <9493A23B-213C-4479-82FD-1AAE18127655@elevated-dev.com> (raw)
In-Reply-To: <CAHtRPB6atN3u+dccSOjiJdYpNGEiTJqMrHCpZ4NZc+1F4jEaaw@mail.gmail.com>
References: <CAHtRPB6atN3u+dccSOjiJdYpNGEiTJqMrHCpZ4NZc+1F4jEaaw@mail.gmail.com>
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 ;-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, thomaz.santos@gmail.com
Subject: Re: doubts
In-Reply-To: <9493A23B-213C-4479-82FD-1AAE18127655@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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