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.
