How to get rid of locks when servicing partitions of an external table in a PostgreSQL database?

Question

PostgreSQL

Greetings!
The situation is as follows: there is a table in the database that is connected as an external one, and partitioning is configured on the remote server. Everything works fine, but the disk space is running out, and the question arose of optimizing partitions. Directly VACUUM, REINDEX and DROP cannot be performed because a large amount of data is constantly being written, and, as I understand it, the main database, when connecting an external table, requires an exclusive lock, which also applies to partitions. Tried to remove inheritance, but also to no avail.
Question: how to bypass the lock without disabling the external table?

Answer 1

vacuum full and reindex require a heavy lock and will hold the lock for a long time. Therefore, they are usually avoided.
drop of course also requires an exclusive lock, but for a short time. It is important to understand the slippery situation - if you are trying to take an exclusive lock, but cannot get it due to other transactions in progress - you will wait for these interfering transactions to finish, but will already block the work of subsequent transactions.
If you think that a lot of space is wasted, then you can calculate it (by putting the pgstattuple contrib extension) with these queries of ours: for tables and for indexes
If yes, the problem with space is this, then pgcompacttable or pg_repack will help you
If the data is mostly archived, it makes sense to replace the btree indexes with brin. They are slower, but very much more compact on the disk.
And unhooking partitions is to look in pg_stat_activity what is happening in general, whether there are abnormally long transactions or idle in transactions (ie, dullness on the application). Then, with statement_timeout and lock_timeout of 1 second (or even less), try to unhook until it works.