In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to several ALTER TABLE operations (like changing the type of several columns).
The alternative presented is always to do something like:
CREATE TABLE new_table AS SELECT * FROM old_table ; -- recreate all indices and constraints ALTER TABLE old_table RENAME TO going_to_drop_table ; ALTER TABLE new_table RENAME TO old_table ; DROP TABLE going_to_drop_table ;
This would work in an scenario where there are no dependencies to
old_table (meaning there are not any views that depend on it, nor any foreign key constraints, functions, etc.), and that
old_table does not get any inserts or updates. But that would be an exception, not the rule, in most databases.
Is there any way to make such a 'table swap' without losing dependencies?
[For the sake of completeness: I'm specially interested in how to do it for PostgreSQL 9.5 or 9.6]
Research until now (about the underlying reason):
- Is it possible to run VACUUM FULL in Postgres asynchronously? =>
pg_repack. Caveats: might not be easy to implement on Windows, not tested with postgresql 9.6. Looks like the most promissing option.
pg_reorg: similar to pg_repack (was the basis for it) => Seems not to have been updated since postgresql 9.4, and it's mostly superseded by
- Old bash-based tool from OmniTI, referenced by Reduce Bloat of table without long/exclusive Locks => looks not up-to-date.
- Cleaning Up PostgreSQL Bloat => (for what I'm interested in), basically recommends using
--jobs=njobs. Looks like a way to speed-up vacuums, and an actual possibility. I don't like the sentence from the documentation that states "Note that using this mode together with the -f (FULL) option might cause deadlock failures if certain system catalogs are processed in parallel."
pgcompactfrom pgtoolkit => don't know if this is up-to-date. It's based on Reducing bloat without locking, which looks safe.
- Safe Operations For High Volume PostgreSQL: A good collection of workarounds to minimize table locking. Thanks to @hruske for the comment.
Which is the best way to avoid the exclusive locks associated with ALTER TABLE ALTER COLUMN ..., CLUSTER or VACUUM FULL?