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
