Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.100.31\))
Subject: Re: doubts
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CAHtRPB6atN3u+dccSOjiJdYpNGEiTJqMrHCpZ4NZc+1F4jEaaw@mail.gmail.com>
Date: Wed, 3 Aug 2022 17:18:41 -0600
Cc: pgsql-admin@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <9493A23B-213C-4479-82FD-1AAE18127655@elevated-dev.com>
References: 
 <CAHtRPB6atN3u+dccSOjiJdYpNGEiTJqMrHCpZ4NZc+1F4jEaaw@mail.gmail.com>
To: Thomaz Luiz Santos <thomaz.santos@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/9493A23B-213C-4479-82FD-1AAE18127655%40elevated-dev.com>
Precedence: bulk

On Aug 3, 2022, at 5:06 PM, Thomaz Luiz Santos <thomaz.santos@gmail.com> =
wrote:
>=20
> 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 ?=20

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 ;-)