Re: Effects of dropping a large table - Mailing list pgsql-general

From Laurenz Albe
Subject Re: Effects of dropping a large table
Date
Msg-id 74c9a14dc9f0be9ae4f131643b64149045d5ef80.camel@cybertec.at
Whole thread Raw
In response to Effects of dropping a large table  (Devin Ivy <devinivy@gmail.com>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Rajendra Kumar Dangwal
Date:
Subject: Pgoutput not capturing the generated columns
Next
From: Haidong Huang
Date:
Subject: Need help setting up Windows authentication SSPI user mapping