pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Effects of dropping a large table
10+ messages / 7 participants
[nested] [flat]

* Effects of dropping a large table
@ 2023-07-18 17:58  Devin Ivy <devinivy@gmail.com>
  0 siblings, 2 replies; 10+ messages in thread

From: Devin Ivy @ 2023-07-18 17:58 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi all,
I'm hoping to ensure I understand the implications of dropping a large
table and the space being reclaimed by the database and/or OS.  We're using
pg v14.

This table is quite large with a primary key and one additional index—all
together these are on the order of 1TB.  The primary key and index take-up
quite a bit more space than the table itself.  Our hope is to discontinue
use of this table and then eventually drop it.  However, the database is
under constant load and we'd like to avoid (or at least anticipate)
downtime or degraded performance.  The database also replicates to a
standby instance.

So in short, what can we expect if we drop this table?  Will the strategy
that pg takes to give that space back to the rest of the database and/or OS
have significant effects on availability or performance?  Finally, are
there any other considerations that we should take into account?  I
appreciate your time and input, thanks!

--
Devin Ivy

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Effects of dropping a large table
@ 2023-07-18 19:41  Rob Sargent <robjsargent@gmail.com>
  parent: Devin Ivy <devinivy@gmail.com>
  1 sibling, 1 reply; 10+ messages in thread

From: Rob Sargent @ 2023-07-18 19:41 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 7/18/23 11:58, Devin Ivy wrote:
> Hi all,
> I'm hoping to ensure I understand the implications of dropping a large 
> table and the space being reclaimed by the database and/or OS.  We're 
> using pg v14.
>
> This table is quite large with a primary key and one additional 
> index—all together these are on the order of 1TB. The primary key and 
> index take-up quite a bit more space than the table itself.  Our hope 
> is to discontinue use of this table and then eventually drop it.  
> However, the database is under constant load and we'd like to avoid 
> (or at least anticipate) downtime or degraded performance.  The 
> database also replicates to a standby instance.
>
> So in short, what can we expect if we drop this table? Will the 
> strategy that pg takes to give that space back to the rest of the 
> database and/or OS have significant effects on availability or 
> performance?  Finally, are there any other considerations that we 
> should take into account?  I appreciate your time and input, thanks!
>
> --
> Devin Ivy
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.

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Effects of dropping a large table
@ 2023-07-19 08:17  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Devin Ivy <devinivy@gmail.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Laurenz Albe @ 2023-07-19 08:17 UTC (permalink / raw)
  To: Devin Ivy <devinivy@gmail.com>; pgsql-general@lists.postgresql.org

On Tue, 2023-07-18 at 13:58 -0400, Devin Ivy wrote:
> I'm hoping to ensure I understand the implications of dropping a large table and
> the space being reclaimed by the database and/or OS.  We're using pg v14.
> 
> This table is quite large with a primary key and one additional index—all
> together these are on the order of 1TB.  The primary key and index take-up
> quite a bit more space than the table itself.  Our hope is to discontinue
> use of this table and then eventually drop it.  However, the database is
> under constant load and we'd like to avoid (or at least anticipate) downtime
> or degraded performance.  The database also replicates to a standby instance.
> 
> So in short, what can we expect if we drop this table?  Will the strategy
> that pg takes to give that space back to the rest of the database and/or
> OS have significant effects on availability or performance?  Finally, are
> there any other considerations that we should take into account?  I appreciate
> your time and input, thanks!

If you drop a table, the underlying files are deleted, and the disk space
becomes available.  Usually that is a cheap operation, but that of course
depends on the file system you are using.  On a copy-on-write file system,
the space won't be freed right away.

The only difficulty that can arise is if you have long running transactions
that involve the table.  To prevent a hanging DROP TABLE from blocking other
transactions for a long time, you can

  SET lock_timeout = '1s';
  DROP TABLE ...;

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Effects of dropping a large table
@ 2023-07-19 23:15  David Rowley <dgrowleyml@gmail.com>
  parent: Rob Sargent <robjsargent@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: David Rowley @ 2023-07-19 23:15 UTC (permalink / raw)
  To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On Wed, 19 Jul 2023 at 07:41, Rob Sargent <robjsargent@gmail.com> 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





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Effects of dropping a large table
@ 2023-07-19 23:33  Rob Sargent <robjsargent@gmail.com>
  parent: David Rowley <dgrowleyml@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Rob Sargent @ 2023-07-19 23:33 UTC (permalink / raw)
  To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On 7/19/23 17:15, David Rowley wrote:
> On Wed, 19 Jul 2023 at 07:41, Rob Sargent<robjsargent@gmail.com>  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.

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Effects of dropping a large table
@ 2023-07-22 20:37  Gus Spier <gus.spier@gmail.com>
  parent: Rob Sargent <robjsargent@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Gus Spier @ 2023-07-22 20:37 UTC (permalink / raw)
  To: Rob Sargent <robjsargent@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-general@lists.postgresql.org


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





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Effects of dropping a large table
@ 2023-07-23 09:51  Peter J. Holzer <hjp-pgsql@hjp.at>
  parent: Gus Spier <gus.spier@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Peter J. Holzer @ 2023-07-23 09:51 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 2023-07-22 16:37:39 -0400, Gus Spier wrote:
> 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?

What's the advantage of first truncating and then deleting a table over
just deleting it?

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Effects of dropping a large table
@ 2023-07-23 10:09  Gus Spier <gus.spier@gmail.com>
  parent: Peter J. Holzer <hjp-pgsql@hjp.at>
  0 siblings, 1 reply; 10+ messages in thread

From: Gus Spier @ 2023-07-23 10:09 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Ah! Truncating a table does not entail all of WAL processes. From the
documentation, "TRUNCATE quickly removes all rows from a set of tables. It
has the same effect as an unqualified DELETE on each table, but since it
does not actually scan the tables it is faster. Furthermore, it reclaims
disk space immediately, rather than requiring a subsequent VACUUM operation.
This is most useful on large tables."
https://www.postgresql.org/docs/14/sql-truncate.html

Regards,
Gus

On Sun, Jul 23, 2023 at 5:51 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

> On 2023-07-22 16:37:39 -0400, Gus Spier wrote:
> > 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?
>
> What's the advantage of first truncating and then deleting a table over
> just deleting it?
>
>         hp
>
> --
>    _  | Peter J. Holzer    | Story must make more sense than reality.
> |_|_) |                    |
> | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |       challenge!"
>

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Effects of dropping a large table
@ 2023-07-23 10:27  Peter J. Holzer <hjp-pgsql@hjp.at>
  parent: Gus Spier <gus.spier@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Peter J. Holzer @ 2023-07-23 10:27 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 2023-07-23 06:09:03 -0400, Gus Spier wrote:
> Ah! Truncating a table does not entail all of WAL processes. From the
> documentation, "TRUNCATE quickly removes all rows from a set of tables. It has
> the same effect as an unqualified DELETE on each table, but since it does not
> actually scan the tables it is faster. Furthermore, it reclaims disk space
> immediately, rather than requiring a subsequent VACUUM operation. This is most
> useful on large tables." https://www.postgresql.org/docs/14/sql-truncate.html

I assumed that by "deleting the now empty table" you meant DROPing it.
(Performing a «DELETE FROM t» just after a «TRUNCATE t» would obviously
be pointless).

So let me rephrase the question:

What's the advantage of 

    TRUNCATE t
    DROP t

over just

    DROP t

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Effects of dropping a large table
@ 2023-07-23 20:05  Ron <ronljohnsonjr@gmail.com>
  parent: Peter J. Holzer <hjp-pgsql@hjp.at>
  0 siblings, 0 replies; 10+ messages in thread

From: Ron @ 2023-07-23 20:05 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 7/23/23 05:27, Peter J. Holzer wrote:
> On 2023-07-23 06:09:03 -0400, Gus Spier wrote:
>> Ah! Truncating a table does not entail all of WAL processes. From the
>> documentation, "TRUNCATE quickly removes all rows from a set of tables. It has
>> the same effect as an unqualified DELETE on each table, but since it does not
>> actually scan the tables it is faster. Furthermore, it reclaims disk space
>> immediately, rather than requiring a subsequent VACUUM operation. This is most
>> useful on large tables." https://www.postgresql.org/docs/14/sql-truncate.html
> I assumed that by "deleting the now empty table" you meant DROPing it.
> (Performing a «DELETE FROM t» just after a «TRUNCATE t» would obviously
> be pointless).
>
> So let me rephrase the question:
>
> What's the advantage of
>
>      TRUNCATE t
>      DROP t
>
> over just
>
>      DROP t

Catalog or serialization locking?  (I don't know; just asking.)

-- 
Born in Arizona, moved to Babylonia.





^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2023-07-23 20:05 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-18 17:58 Effects of dropping a large table Devin Ivy <devinivy@gmail.com>
2023-07-18 19:41 ` Rob Sargent <robjsargent@gmail.com>
2023-07-19 23:15   ` David Rowley <dgrowleyml@gmail.com>
2023-07-19 23:33     ` Rob Sargent <robjsargent@gmail.com>
2023-07-22 20:37       ` Gus Spier <gus.spier@gmail.com>
2023-07-23 09:51         ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-07-23 10:09           ` Gus Spier <gus.spier@gmail.com>
2023-07-23 10:27             ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-07-23 20:05               ` Ron <ronljohnsonjr@gmail.com>
2023-07-19 08:17 ` Laurenz Albe <laurenz.albe@cybertec.at>

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