Re: Effects of dropping a large table

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