pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gus Spier <gus.spier@gmail.com>
To: Rob Sargent <robjsargent@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Effects of dropping a large table
Date: Sat, 22 Jul 2023 16:37:39 -0400
Message-ID: <430335C4-3803-4F88-9042-96BA65166043@gmail.com> (raw)
In-Reply-To: <c9fea9c6-807c-474f-d698-968d3ac2a901@gmail.com>
References: <c9fea9c6-807c-474f-d698-968d3ac2a901@gmail.com>


--Apple-Mail-D6F3CEFE-53A3-42ED-9028-E8D9FD049292
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Isn=E2=80=99t this a perfect opportunity to=
 use the TRUNCATE command to quickly remove the data? And follow up by delet=
ing the now empty tables?<div>Regards,</div><div>Gus<br><br><div dir=3D"ltr"=
>Sent from my iPhone</div><div dir=3D"ltr"><br><blockquote type=3D"cite">On J=
ul 19, 2023, at 7:33 PM, Rob Sargent &lt;robjsargent@gmail.com&gt; wrote:<br=
><br></blockquote></div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF=

 =20
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF-8"=
>
 =20
 =20
    <div class=3D"moz-cite-prefix">On 7/19/23 17:15, David Rowley wrote:<br>=

    </div>
    <blockquote type=3D"cite" cite=3D"mid:CAApHDvojNTFb=3DA1zE7oiz02JaHkp2f8=
q=3DC8SOd+QwvwJYV2cLQ@mail.gmail.com">
      <pre class=3D"moz-quote-pre" wrap=3D"">On Wed, 19 Jul 2023 at 07:41, R=
ob Sargent <a class=3D"moz-txt-link-rfc2396E" href=3D"mailto:robjsargent@gma=
il.com">&lt;robjsargent@gmail.com&gt;</a> wrote:
</pre>
      <blockquote type=3D"cite">
        <pre class=3D"moz-quote-pre" wrap=3D"">You might consider deleting p=
ortions of the table in separate (consecutive) batches (maybe 5% per delete)=
.  And then truncate table is not logged so that might be an alternative.
</pre>
      </blockquote>
      <pre class=3D"moz-quote-pre" wrap=3D"">Can you explain why this would b=
e 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
</pre>
    </blockquote>
    <font face=3D"Courier New, Courier, monospace">No, you're right.&nbsp; I=

      was remembering problems with _deleting_ essentially all of a
      large table (with limited resources).&nbsp; The drop might not have th=
e
      same problem.&nbsp; But aren't they logged/transactional and then in
      the WALs anyway.<br>
      <br>
    </font>
 =20

</div></blockquote></div></body></html>=

--Apple-Mail-D6F3CEFE-53A3-42ED-9028-E8D9FD049292--





view thread (10+ messages)  latest in thread

Message-ID: <430335C4-3803-4F88-9042-96BA65166043@gmail.com>
Permalink:  ../430335C4-3803-4F88-9042-96BA65166043@gmail.com/
Also on:    postgresql.org/message-id/430335C4-3803-4F88-9042-96BA65166043@gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: gus.spier@gmail.com, robjsargent@gmail.com, dgrowleyml@gmail.com
  Subject: Re: Effects of dropping a large table
  In-Reply-To: <430335C4-3803-4F88-9042-96BA65166043@gmail.com>
  References: <c9fea9c6-807c-474f-d698-968d3ac2a901@gmail.com> <430335C4-3803-4F88-9042-96BA65166043@gmail.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