Re: Effects of dropping a large table

[Gus Spier <gus.spier@xxxxxxxxx>]

Isn’t this a perfect opportunity to use the TRUNCATE command to quickly remove the data? And follow up by deleting the now empty tables?

Regards,

Gus

Sent from my iPhone

On Jul 19, 2023, at 7:33 PM, Rob Sargent <robjsargent@xxxxxxxxx> wrote:



On 7/19/23 17:15, David Rowley wrote:

On Wed, 19 Jul 2023 at 07:41, Rob Sargent <robjsargent@xxxxxxxxx> wrote:

You might consider deleting portions of the table in separate (consecutive) batches (maybe 5% per delete).  And then truncate table is not logged so that might be an alternative.

Can you explain why this would be a useful thing to do?

It sounds to me like it would just create a load of needless WAL from
the deletes and the vacuum that cleans up the dead rows each of which
is more likely to cause lag problems on the replica servers, which the
OP is trying to avoid.

David

No, you're right.  I was remembering problems with _deleting_ essentially all of a large table (with limited resources).  The drop might not have the same problem.  But aren't they logged/transactional and then in the WALs anyway.