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
