Is it safe to move big table to another tablespace while working with it?

Question

I have one big bloated table (real data size approx 65 Gb, 140 Gb on disk). Vacuum does not work cause of low space on default tablespace disk. So I want to clean the database from trash by moving fat tables to another tablespace, vacuuming them and returning back.

Can I do it safely while not turning off services working with that table? It is around 15 separate one row inserts and ~7 select operations per minute.

And few more insignificant questions:

1. What manuals/guides or parameters settings would you advice for autovacuuming table, that works in such load.
2. Is it better to pass old data delete operations with frequency of inserts or delete big stacks of rows (every day,week, month,etc..)?

Answer 1

The table will be AccessExclusive locked for the duration of its move to a new tablespace, so your application will seize up during that time.

You haven't really described what your workload is, so it is hard to recommend settings to accommodate it. Surely it wasn't 15 separate one row inserts and ~7 select operations per minute which lead to the existing bloat?

Generally it is best to delete rows once you become aware that they are eligible for deletion. Creating a deletion-queue table is just another table which can get bloated, needs to be maintained, have deletions done on it itself, be vacuumed, have code written to accommodate it, etc.