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.