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..)?
up vote 0 down vote accepted

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.